FreeTDS Development Group

[Peter Deacon <peterd AT iea-software.com>]

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