@@ROWCOUNT

[wgcs]

What is the MySql variable corresponding to SQL Server's @@ROWCOUNT?

Essentially, after an UPDATE or DELETE command, I need to know how many records were affected.

The ODBC client I'm using (VFP v6) doesn't support the SqlRowCount() API function, so I need another way...

 

[ingresman]

Hi,

what is the client software is vfp visual fox pro ??. Can you execute C procedures from it ?, you might be able to call sqlrowcount from their by passing in the statement handle, have you any idea why you can't call it directly ?

 

[wgcs]

The client software is Visual Foxpro v6

The reason I can't call SqlRowCount directly is because VFP provides "wrappers" for the ODBC API functions (like SqlConnect, SqlStringConnect, SqlExecute, etc) but doesn't provide a wrapper for SqlRowCount.

When using the statement handle from SqlGetProp, and calling SqlRowCount() after using the built in VFP SqlExec() wrapper funciton, SqlRowCount() just returns an error.

So the statement handle VFP uses internally seems to be different than the statement handle returned by SqlGetProp()...

I've tried getting the statement handle and executing SQL directly through SqlExecDirect, then calling SqlRowCount(), and it works properly.

However, If I want to use any parameters in the query, I'd have to re-code all the parameter binding functionality provided by VFP, using SQLBindParameter, which is a fairly heavy amount of work, which I'm not confident I can do without a significant amount of more research into the parameter value formatting/type conversions, etc.

So, is there any other way to get the value of SqlRowCount()?

 

[ingresman]

Ok, so far so good. It would be usefull to know if the foxpro commands like SQLCONNECT are really wrappers over ODBC or not. One interesting thing in the msdn documention about for SQLGETPROP is this:

Parameters
nConnectionHandle
Specifies the connection handle to the data source returned by SQLCONNECT( ).

The key thing here is you get the connection handle returned NOT the statement handle which is what you want. You might be able to prove this by issueing something like
xx = SQLGETROP(handle,"DataSource&quot which should return the original data source name. You might have to look at the manuals for exact details. However this just gives us some background, we still need to get to the statement handle.
Looking at the documentation it seems that SQLEXEC (which i presume you are using) can take a prepaired statment (done using SQLPREPARE). When you use SQLPREPARE it looks like you get a statment handle back, so you could try:

x="delete where fred=1"
stmt=SQLPREPARE(x)
SQLEXEC
SQLROWCOUNT(stmt)

or words to that effect, it's ages since I used ODBC at API level but I'm sure the SQLPrepare call uses statment handles.
If all else fails you could wrap the functions you need e.g.
handle creation,connect,prepare, execute and rowcount and use those ignoring the foxprop stuff all together. A usefull link is

http://www.news2news.com/vfp/?group=103&=0

keep in touch, id like to know how you get on

Kevin