How to retrieve information from SQL Server about affected rows in Visual FoxPro 7.0 and later versions

MORE INFORMATION

When you are working with data on a server, you must know how many rows in the table were affected by a certain SQL command. This information is similar to the information that is provided by the _TALLY variable in Visual FoxPro.

This information is not available to you directly in Visual FoxPro, but there are ways to obtain that information if you want.

Visual FoxPro 7.0 and later versions support output parameters from stored procedures. This means that you can call a procedure that is located on the SQL Server-based computer and then that procedure returns information to the client application.

The following code example uses an existing table in the SQL Server Pubs database and creates the stored procedure on the SQL Server-based server. Create a program in Visual FoxPro, copy the code into the program, and then run the program from the Visual FoxPro Command window:

Clear
lcServername = "(local)"  && change the SQL Server name if you have to.
lcDatabase = "pubs"


TEXT to lcText noshow textmerge
 CREATE PROCEDURE upd_pubsauthors @state char(2), @zip char(5), @retcount int
 OUTPUT AS
  UPDATE authors SET state = @state
          WHERE  zip = @zip
     SELECT @retcount = @@ROWCOUNT
ENDTEXT
 

Mvar = 0
nhandle=Sqlstringconnect("Driver=SQL Server;SERVER="+lcServername+";DATABASE="+lcDatabase)
 
=SQLExec(nhandle,'drop procedure upd_pubsauthors') = -1
 
If SQLExec(nhandle,lcText) = -1
 Aerror(atest)
 List Memory Like atest
 Return
Endif
 

If SQLExec(nhandle,"{CALL upd_pubsauthors ('CA', '94609', ?@mvar)}") = -1
 Aerror(atest)
 List Memory Like atest
Else
 ? Mvar
Endif
Return

After the code runs, view the value that is echoed to the Visual FoxPro desktop. This value indicates the number of rows in the SQL Server table that are affected.