Finding rows affected by T-SQL Update or Delete

I'm doing some T-SQL queries and am encountering some strange issues. I did
most of my development in VFP9, where I did something along the lines of:

SQLEXEC(hSQL, 'DELETE FROM table1 WHERE Value = 'Bad'' + CHR(13) + CHR(10) +
'SELECT @@RowCount AS SQLTally', 'SQLTally')
m.nRowsAffected = SQLTally.SQLTally

When I went to test this in our production VFP6 environment, the SQL
statement refuses to generate the SQLTally cursor. It appears to execute
fine, just doesn't return any results. If I execute the two lines as two
seperate SQLEXEC() statements, I can get the result cursor. My question is,
does anyone know why this works in VFP9 and not in VFP6, and how to make it
work in VFP6?

Also, when executing the query in Query Analyzer I get a nice (X row(x)
affected) summary in the Messages tab. Is there anyway to capture and parse
this Messages info in VFP after a SQLEXEC, rather than executing a @@ROWCOUNT
query? I think I've seen code in the past that captures this type of info
(can also be generated SQL side with PRINT) but I can't find any examples
anywhere. This would seem to be the more accurate and efficient way to get
the information I want.

Christian
Wed May 11 18:06:59 CDT 2005

Hello,

can't give you the answer for your problem with VFP6

just wanted to mention that in VFP9 you'll now have to option to pass an
array as the last parameter which will contain the count of
inserted/updated/deleted columns after SQLEXEC returns
SQLEXEC(nStatementHandle [, cSQLCommand [, cCursorName[, aCountInfo]]])

this should also work with Oracle, MySQL and other backends .. and you
wouldn't have to hardcode backend specific commands (@@Rowcount) into your
select statements

Regards
Christian


"dirknerpin" <dirknerpin@discussions.microsoft.com> schrieb im Newsbeitrag
news:8375BB40-7747-4F7B-B614-98EDC9D79129@microsoft.com...
> I'm doing some T-SQL queries and am encountering some strange issues. I
did
> most of my development in VFP9, where I did something along the lines of:
>
> SQLEXEC(hSQL, 'DELETE FROM table1 WHERE Value = 'Bad'' + CHR(13) + CHR(10)
+
> 'SELECT @@RowCount AS SQLTally', 'SQLTally')
> m.nRowsAffected = SQLTally.SQLTally
>
> When I went to test this in our production VFP6 environment, the SQL
> statement refuses to generate the SQLTally cursor. It appears to execute
> fine, just doesn't return any results. If I execute the two lines as two
> seperate SQLEXEC() statements, I can get the result cursor. My question
is,
> does anyone know why this works in VFP9 and not in VFP6, and how to make
it
> work in VFP6?
>
> Also, when executing the query in Query Analyzer I get a nice (X row(x)
> affected) summary in the Messages tab. Is there anyway to capture and
parse
> this Messages info in VFP after a SQLEXEC, rather than executing a
@@ROWCOUNT
> query? I think I've seen code in the past that captures this type of info
> (can also be generated SQL side with PRINT) but I can't find any examples
> anywhere. This would seem to be the more accurate and efficient way to
get
> the information I want.

Christian
Wed May 11 18:10:15 CDT 2005

Hi Christian :))

review you posts more carefully ..

< VFP9 you'll now have to option
the option ..

> wouldn't have to hardcode backend specific commands (@@Rowcount) into your
> select statements
into your SQL statements ..

Christian
Thu May 12 05:14:39 CDT 2005

Hello,

an alternate solution that should work for VFP6 is:

lnRet = SQLEXEC(hSQL, 'DELETE FROM table1 WHERE Value = 'Bad'' + CHR(13) +
CHR(10) + ;
'IF @@RowCount = 0' + CHR(13) + CHR(10) + "RAISEERROR('D', 16, 1)")
IF lnRet = -1
LOCAL laError
AERROR(laError)
IF laError[3] == 'D'
? "No row was deleted"
ELSE
? "Real error occured" && handle it
ENDIF
ENDIF

you probably want to wrap this into a function e.g.

FUNCTION SQLDML(lnConn,lcSql,laError)
LOCAL lnRet
EXTERNAL ARRAY laError
lcSql = lcSql + CHR(13) + CHR(10) + ;
'IF @@RowCount = 0' + CHR(13) + CHR(10) + "RAISEERROR('D', 16, 1)"

lnRet = SQLEXEC(lnConn,lcSql)
IF lnRet = -1
AERROR(laError)
IF laError[3] = 'D'
RETURN 0
ELSE
RETURN -1
ENDIF
ELSE
RETURN 1
ENDIF
ENDFUNC

so you don't have to repeat all these lines for every DML statement you send
to the DB ..

also take into consideration that this is probably considered "bad" design
by some hardcore Stored Procedure advocates ..

but in the end it's up to you to make the design decisions

Regards
Christian