getting count of results from SQLEXEC UPDATE 2

[stfaprc]

sing VFP8, how can I get the SQLEXEC() command to return how many records got updated with an UPDATE TABLE command? I am only going a return value of 0 or 1 (for failure and success) when trying any of the SQLEXEC() formats.
I was hoping that the cursor name passed in for the results would result in a cursor being populated, but UPDATE TABLE does not result in any cursors

 

[Mike Lewis]

The SQLEXEC() function can't do that, but it should be possible for the back end to report it. However, the method will be different for different servers.

For example, in SQL Server, you can do it like this:

UPDATE MyTable SET x = y WHERE a = b
SELECT @@RowCount

The resulting cursor will contain a single row/column containing the required figure.

If you could let us know which back-end database you are using, no doubt someone will be able to give you the equivalent syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[stfaprc]

it is an MS SQL Server 2003

so I would do

sqlexec( ghSQL, "UPDATE MyTable SET x = y WHERE a = b")
sqlexec( ghSQL, "SELECT @@RowCount")
display

?

 

[Mike Lewis]

Not quite. You would have to send the two commands at the same time:

lcCmd = "UPDATE MyTable SET x = y WHERE a = b" + chr(13) + ;
  "SELECT @@RowCount")
sqlexec( ghSQL, lcCmd, "csrCount")

The rowcount will then appear in the (only) field in the csrCount cursor.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

On reflection ....

I'm not sure you even need the CHR(13) It might be enough to separate the two commands with a single space. Try it and see what you get.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

I typically use TEXT..ENDTEXT to compose SQL for better readability. You can also use the Textmwerge option of Text..Endtext, but you can also use the Textmerge() function with a normal string literal.

What's needed to seperate two commands is a semikolon. A space would not really be a good seperator for SQL, don't you agree, Mike?

TEXT To lcSQL Noshow Textmerge
Update sometable Set somefield = somevalue WHERE somecondition;

Select @@Rowcount As nCount;
ENDTEXT

Sqlexec(handle, lcSQL, 'curCount')

Bye, Olaf.

 

[cbasoz]

OLEDB is a little slower than ODBC calls with SQLExec. If anyway you think to use ADO, then simply you could pass a parameter by reference to Execute method and get the number of affected records. ie:

Local loCommand As 'ADODB.Command',loConnection As 'ADODB.Connection'
loCommand = Createobject('ADODB.Command')

SQLCreateParameter(loCommand, 'X')
SQLCreateParameter(loCommand, 'USA')

loCommand.CommandText = "Update Customers set region = ?+region where Country = ?"

loConnection = Createobject('ADODB.Connection')
loConnection.ConnectionString = 'Provider=SQLNCLI;server=.\sqlexpress;Trusted_connection=yes;Database=Northwind'
loConnection.Open

loCommand.ActiveConnection = loConnection

lnAffected = 0
loCommand.Execute(@lnAffected)

loConnection.Close
? m.lnAffected


Procedure SQLCreateParameter
  Lparameters toCommand As ADODB.Command, tuParameter, tcName
  tcName = Evl(m.tcName, Textmerge('P<<toCommand.Parameters.Count+1>>'))
  Local loParm As 'ADODB.Parameter'
  loParm = toCommand.CreateParameter(m.tcName)
  loParm.Direction=1
  Do Case
    Case Type('m.tuParameter') = 'C'
      If Len(m.tuParameter) > 4000
        loParm.Type = 201  && adLongVarChar
        loParm.Size = -1
      Else
        loParm.Type = 200  && adVarChar
        loParm.Size = Max(1,Len(Nvl(m.tuParameter,'')))
      Endif
    Case Type('m.tuParameter') = 'D'
      loParm.Type = 133  && adDBDate
    Case Type('m.tuParameter') = 'T'
      loParm.Type = 135  && adDBTimeStamp
    Case Type('m.tuParameter') = 'Y'
      loParm.Type = 6  && adCurrency
    Case Type('m.tuParameter') = 'N'
      loParm.Type= 3  && adNumeric
    Case Type('m.tuParameter') = 'Q'
      loParm.Type = 205  && adLongVarBinary
      loParm.Size = -1
    Case Type('m.tuParameter') = 'L'
      loParm.Type = 11  && adBoolean
  Endcase
  If loParm.Type = 205  && adLongVarBinary
    loParm.Value = Createbinary(m.tuParameter)
  Else
    loParm.Value = m.tuParameter
  Endif
  toCommand.Parameters.Append( loParm )
Endproc

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

And since SQL server 2005 you could also get the affected records back as an output value. ie:

handle=SQLSTRINGCONNECT('Driver={SQL Native Client};server=.\sqlexpress;Trusted_connection=yes;Database=Northwind')

TEXT TO lcSQL noshow
DECLARE @results table(
    CustomerID varchar(10),
    oldRegion varchar(15),
    newRegion varchar(15),
    Country varchar(20));

UPDATE Customers 
SET region = ?m.Region + region 
Output 
	Inserted.CustomerID, 
	Deleted.Region, 
	Inserted.region, 
	Inserted.Country into @results
where Country = ?m.Country;
select * from @results;
ENDTEXT

region = 'X'
Country = 'USA'
SQLEXEC(m.handle, m.lcSQL, 'result')
SQLDISCONNECT(0)

SELECT result
browse

Cetin Basoz
MS Foxpro MVP, MCP

 

[stfaprc]

well, we are not moving to msSql2005

lcCmd = "UPDATE MyTable SET x = y WHERE a = b" + ";" + ;
  "SELECT @@RowCount")
sqlexec( ghSQL, lcCmd, "csrCount")
?csrcount.exp

worked fine.