[freetds] SQL_NO_DATA or SQL_SUCCESS

Peter Deacon peterd at iea-software.com
Wed Feb 27 20:29:30 EST 2008


On Wed, 27 Feb 2008, James K. Lowden wrote:

> Peter Deacon wrote:
>>> (I do not know what a "searched update" is.  I guess it means one with
>>> a WHERE clause.  If I issue "UPDATE tablename SET columname = 0" on a
>>> table with zero rows, it affects zero rows.  What should the driver
>>> return then?)

>> Yea, searched updates are exactly that.

> Hi Peter,
> What should I expect if the following are executed as two batches?

> 	CREATE TABLE B (b int)
> then
> 	DELETE FROM B

isqlw           16c-584	EXIT  SQLExecDirectW  with return code 100 (SQL_NO_DATA_FOUND)
 		HSTMT               014F1C00
 		WCHAR *             0x0016A980 [      -3] "DELETE FROM B\ d\ a\ d\ a\ 0"
 		SDWORD                    -3

-- If you turn off rowcount first :-)

isqlw           16c-dc8	EXIT  SQLExecDirectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
 		HSTMT               014F1C00
 		WCHAR *             0x0016AAE8 [      -3] "DELETE FROM B\ d\ a\ 0"
 		SDWORD                    -3

-- DELETE FROM B enclosed in a stored procedure

isqlw           16c-10e8	EXIT  SQLExecDirectW  with return code 100 (SQL_NO_DATA_FOUND)
 		HSTMT               014F1C00
 		WCHAR *             0x00169880 [      -3] "{CALL blah}\ d\ a\ 0"
 		SDWORD                    -3

-- Batch of two DELETE FROM B's

isqlw           16c-1290	EXIT  SQLExecDirectW  with return code 100 (SQL_NO_DATA_FOUND)
 		HSTMT               014F1C00
 		WCHAR *             0x001696D0 [      -3] "DELETE FROM B\ d\ aDELETE FROM B\ d\ a\ d\ a\ 0"
 		SDWORD                    -3


-- Result set then DELETE FROM B

isqlw           16c-4bc	EXIT  SQLExecDirectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
 		HSTMT               014F1C00
 		WCHAR *             0x001696D0 [      -3] "SELECT * FROM B\ d\ aDELETE FROM B\ d\ a\ d\ a\ 0"
 		SDWORD                    -3

-- DELETE FROM B then a Result set

isqlw           16c-2ac	EXIT  SQLExecDirectW  with return code 100 (SQL_NO_DATA_FOUND)
 		HSTMT               014F1C00
 		WCHAR *             0x00169718 [      -3] "DELETE FROM B\ d\ aSELECT * FROM B\ d\ a\ d\ a\ 0"
 		SDWORD                    -3

-- Result set sandwiched between two DELETE FROM Bs

isqlw           16c-11e4	EXIT  SQLExecDirectW  with return code 100 (SQL_NO_DATA_FOUND)
 		HSTMT               014F1C00
 		WCHAR *             0x00168F18 [      -3] "\ aDELETE FROM B\ d\ aSELECT * FROM B\ d\ aDELETE FROM B\ d\ a\ 0"
 		SDWORD                    -3

I origionally looked up search update elsewhere to try and get context on 
it and thats what I took away from reading what it said about conditions.

Its all nonsense to me - I can't see myself or anyone checking exec return 
over rowcount to see if rows were updated and its obvious the documented 
description can't match the actual behavior.

The only argument I'd make in favor of is the generic argument to 
emulate behaviors as closely as possible.  Having said that I have a 
feeling that will end up doing more harm than good.

> It's not "searched", but it affects no rows.  SQL_SUCCESS or SQL_NO_DATA?
> It's very hard for me to believe there's any *point* in distinguishing
> cases like this.  It smells like documentation to cover a bug, not a
> design choice.

> If you want to know if there's a resultset, examine at the column count.
> If you want to know if rows were affected, examine the rowcount.  The
> purpose of SQL_NO_DATA is (obviously, if you ask me) to let you know
> something else: when you've read the last row.

take care,
Peter


More information about the FreeTDS mailing list