The number of records affected is not returned for SQLServer Compact

[xavierh]

This SQL CE feature works fine under vbscript, but not under AutoIT.

In the vbscript code below, only 1 record is affected, and iRecAffected is correctly set to 1. The value is set by ref.

set oCon = createobject("ADODB.Connection")

set oRS = createobject("ADODB.Recordset")

oCon.Open "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\data\test.sdf;"

oRS.CursorLocation = 2 'adUseServer

Dim iRecAffected

oCon.Execute ("update tests set status = 'nothang' where id = 10", iRecAffected)

msgbox iRecAffected ' this works fine

In the equivalent autoIT code, though the update query succeeds, $RecAffected is not populated. It looks like the byref value is not updated:

Dim $oCon = ObjCreate("ADODB.Connection")

Dim $oRS = ObjCreate ( "ADODB.Recordset" )

Dim $RecAffected

$oCon.Open("Microsoft=SQLSERVER.CE.OLEDB.4.0;Data Source='C:\data\test.sdf;")

oCon.Execute ("update tests set status = 'nothing' where id = 10", $RecAffected)

msgbox ( 0, "", $RecAffected ) ; the displayed value is empty

Short of running another query to verify the update, any ideas? Has anyone had success with a different syntax? I have tried the recordset route, which does return the rows affected for some databases, but it looks like the ado driver does not support update queries that way. So far the only way I have managed to get update queries to work is using Connection.Execute().

Thanks,

Xavier

Edited June 15, 2011 by xavierh

[Joon]

How about with parenthesis?

$oCon.Execute ("update tests set status = 'nothing' where id = 10", $RecAffected)

[xavierh]

hmm, thanks, that's what I meant. The actual code that fails had the parentheses. Typo... now it's fixed in the first post. But the issue is still the same

Edited June 15, 2011 by xavierh

[jchd]

Currently, ByRef values on COM object methods don't work out of the box. It's even hard to make them work at all.

The simplest route is to issue another SQL statement.

[mostyr]

On 15. 6. 2011 at 10:41 PM, jchd said:

Currently, ByRef values on COM object methods don't work out of the box. It's even hard to make them work at all.

The simplest route is to issue another SQL statement.

Hello,

please is there any way how get number of Affected Records ? My code below returns $iRecordsAffected = 0

Local $sODBCQuery = "UPDATE ""Some_Table"" SET ""Some_Column""='Some_Value' WHERE ""Some_Column""='Some_Value'"
Local $iRecordsAffected = 0
$oRS = $oConn.Execute($sODBCQuery, $iRecordsAffected)

I need manage "LOST UPDATE" problem, so I need know if UPDATE RecordsAffected is zero or not..

Thanks for your help..

Edited April 29, 2016 by mostyr

[mLipok]

jchd told that this not works, and this still not works.

 

Edited April 29, 2016 by mLipok

[jchd]

Use a trigger in standard SQL. Some engine offer a proprietary function just for that (e.g. for SQLite: SQL extra function changes() or engine API function sqlite3_changes()). A trigger is obviously more portable.

[mLipok]

http://stackoverflow.com/questions/1103260/return-number-of-rows-affected-by-update-statements/1103272#1103272

CREATE PROCEDURE UpdateTables
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @RowCount1 INTEGER
    DECLARE @RowCount2 INTEGER
    DECLARE @RowCount3 INTEGER
    DECLARE @RowCount4 INTEGER

    UPDATE Table1 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount1 = @@ROWCOUNT
    UPDATE Table2 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount2 = @@ROWCOUNT
    UPDATE Table3 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount3 = @@ROWCOUNT
    UPDATE Table4 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount4 = @@ROWCOUNT

    SELECT @RowCount1 AS Table1, @RowCount2 AS Table2, @RowCount3 AS Table3, @RowCount4 AS Table4
END

http://stackoverflow.com/questions/7005225/sql-server-does-trigger-affects-rowcount

 

Edited April 29, 2016 by mLipok

[jchd]

Agreed this can be a solution for some engines but it's far from universal.

[robertocm]

On 6/15/2011 at 6:27 PM, xavierh said:

it looks like the ado driver does not support update queries that way. So far the only way I have managed to get update queries to work is using Connection.Execute().

An example of the Add and Update methods of ADO Recordset object (with MS Access):

expandcollapse popup

Opt("MustDeclareVars", 1)
;Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

Global $sFilePath =  @DesktopDir & "\test.mdb"

;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global $cn, $rst, $sSQL
Global $CreateTestDataBase = True

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath & ';uid=;pwd=MyPassword;'

If $CreateTestDataBase Then
   ;mLipok, https://www.autoitscript.com/forum/topic/180848-example-xls-mdb-from-scratch-with-adox/
   ;https://forums.autodesk.com/t5/visual-basic-customization/ado-connection-create-file-excel/td-p/1675928
   ;https://msdn.microsoft.com/en-us/library/ms675849(v=vs.85).aspx
   ;https://msdn.microsoft.com/en-us/library/ms680934(v=vs.85).aspx
   Global $oCatalog = ObjCreate('ADOX.Catalog')
   FileDelete(@DesktopDir & "\test.mdb")  ; <<< I'm not using $sFilePath for security: in reusing parts of the code
   $cn = $oCatalog.Create($sADOConnectionString)

   ;https://docs.microsoft.com/es-es/office/client-developer/access/desktop-database-reference/create-table-statement-microsoft-access-sql
   ;https://www.w3schools.com/sql/sql_create_table.asp
   ;http://www.vbforums.com/showthread.php?529221-RESOLVED-create-table-as-select-in-MS-Access
   ;https://docs.microsoft.com/es-es/office/client-developer/access/desktop-database-reference/select-into-statement-microsoft-access-sql
   $sSQL = "CREATE TABLE EMPLOYEES" _
      & " (ID int identity, LastName varchar(40), FirstName varchar(40), Title varchar(40)," _
      & " CONSTRAINT MyTableConstraint_PrimaryKey PRIMARY KEY (ID)," _
      & " CONSTRAINT MyTableConstraint_Unique UNIQUE (LastName, FirstName));"
   $cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80
      ;Some notes:
      ;Create Table Using Another Table
      ;A copy of an existing table can also be created using CREATE TABLE.
      ;The new table gets the same column definitions. All columns or specific columns can be selected.
      ;CREATE TABLE new_table_name AS
      ;SELECT column1, column2,...
      ;FROM existing_table_name
      ;WHERE ....;
      ;The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):
      ;CREATE TABLE TestTable AS
      ;SELECT customername, contactname
      ;FROM customers;

      ;$sSQL = "SELECT *" _
      ; & " INTO NEWTABLE" _
      ; & " FROM [" & $sFilePath2 & ";PWD=123].SOMETABLE"
      ;$cn.Execute($sSQL, Default, 1 + 0x80)

      ;~ For $i = 1 To 5
      ;~    $sSQL = "INSERT INTO EMPLOYEES (LastName, FirstName, Title)" _
      ;~    & " VALUES ('" & "LastName_" & $i & "', '" & "FirstName_" & $i & "', '" & "Title_" & $i & "')"
      ;~    $cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80
      ;~ Next

      $rst.Open("EMPLOYEES", $cn, $iCursorType, $iLockType, 2) ;Source (table name), ActiveConnection, CursorType, LockType, Options (adCmdTable)
      $cn.BeginTrans
      For $i = 1 To 5
         $rst.AddNew
         $rst("LastName").Value = "LastName_" & $i
         $rst.Fields("FirstName") = "FirstName_" & $i
         $rst(3).Value = "Title_" & $i
      Next
      $rst.Update
      MsgBox(0,"","Last Auto-increment value is: " & $rst(0).Value)
      $rst.Close
      $cn.CommitTrans
Else
   $cn = ObjCreate("ADODB.Connection") ; Create a connection object
   $cn.Open($sADOConnectionString) ; Open the connection
EndIf
;MsgBox(0, "", $cn.ConnectionString)

$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 60

;https://support.microsoft.com/en-us/help/194973/prb-ado-recordcount-may-return--1
;When you request the RecordCount for a serverside recordset, a -1 may return. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic. Testing with the OLEDB provider for JET and SQL Server produces varying results, depending on the provider.
;Cause: The number of records in a dynamic cursor may change. Forward only cursors do not return a RecordCount.
;Resolution: Use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors
;or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType you select.

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;https://www.w3schools.com/asp/prop_rec_mode.asp
;$cn.Mode = 1 ;Read-only

;To update an existing record, create a Recorset with one record at a time.
;This requires that there is some kind of unique key when identifying the records.
;After opening the recordset, use the .Fields property to change the field in question
;and then the .Update method to make changes to the database.

$sSQL = "SELECT LastName, FirstName, Title FROM EMPLOYEES WHERE ID = 1"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
If Not $rst.EOF = True Then
   $rst("LastName").Value = "Davolio"
   $rst.Fields("FirstName") = "Nancy"
   $rst.Fields(2) = "President"
EndIf
$rst.Update
MsgBox(0, "UPDATED RECORD", $rst.RecordCount)
$rst.Close

$rst = 0
$cn.Close ;Close the connection
$cn = 0 ;Release the connection object

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

   Local $err = $errADODB.number
   If $err = 0 Then $err = -1

   $rst = 0
   $cn.Close
   $cn = 0

   Exit
EndFunc

Func OnAutoItExit()
   $rst = 0 ; Release the recordset object
   If IsObj($cn) Then
      If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection
      $cn = 0 ; Release the connection object
   EndIf
EndFunc

 

Edited November 17, 2019 by robertocm

[cramaboule]

On 4/29/2016 at 12:21 PM, mLipok said:

jchd told that this not works, and this still not works.

 

Looks like it is still the case...
works in php but not in AutoIt 😞

C.

[mLipok]

This is already solved in different way

 

[jchd]

Check if your engine supports UPDATE ... RETURNING xyz

[mLipok]

On 6/15/2011 at 6:27 PM, xavierh said:

oCon.Open "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\data\test.sdf;"

oRS.CursorLocation = 2 'adUseServer

Dim iRecAffected

oCon.Execute ("update tests set status = 'nothang' where id = 10", iRecAffected)

msgbox iRecAffected ' this works fine

 

In the equivalent autoIT code, though the update query succeeds, $RecAffected is not populated. It looks like the byref value is not updated:

This may be associated with the selected provider/driver in the connection string.
Eventually it is related to global ADO bug.

Do not know.

But it is possible to get number of affected rows with EVENTS yous should check my ADO UDF.
 

Func __ADO_EVENT__ExecuteComplete($iRecordsAffected, ByRef $oError, $i_adStatus, ByRef $oCommand, ByRef $oRecordset, ByRef $oConnection)
    __ADO_RowAffected($iRecordsAffected)

 

[mLipok]

Similar problem was how to get print messages which I solve recently here:

 

 

[cramaboule]

Thanks a lot for your answers... it is for 'HFSQL'. Windev. I use ADODB.
 

PROVIDER=PCSoft.HFSQL;Data Source=\\myserver\myfolder\myfile.wdd;Initial Catalog=\\Myserver\myfolder\TEST\TEST\TEST24\;

I will check your answer and come back to you.

 

C.

 

[mLipok]

1 hour ago, cramaboule said:

it is for 'HFSQL'. Windev

https://windev.com/pcsoft/hfsql.htm

?

[cramaboule]

It seems not working...
here is my code:
 

$sQuery = "update produit set typpr = 'USA' where typpr = 'USB'"
;~ $sQuery = "update produit set typpr = 'USB' where typpr = 'USA'"
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")     ; Initialize a COM error handler

Global $adoCon = ObjCreate("ADODB.Connection")
Global $adoCmd = ObjCreate("ADODB.Command")


$dsn = "PROVIDER=" & $PROVIDER & ";Data Source=" & $SERVER & $WDDPATH & ";Initial Catalog=" & $sPathDsn & ";"
ConsoleWrite($dsn & @CRLF)
$adoCon.ConnectionString = $dsn
;~ $adoCon.CursorLocation = 3     ;A indiquer pour PC Soft sinon ne fonctionne pas
$adoCon.Open

; Execute the SQL query
$adoCmd.ActiveConnection = $adoCon
$adoCmd.CommandText = $sQuery

Local $RecordsAffected

Local $status
$orst = _ADO_Command_Execute($adoCmd, $sQuery)
__ADO_EVENT__ExecuteComplete($RecordsAffected, $oMyError, $status, $adoCmd, $orst, $adoCon)

; Output the number of affected rows
ConsoleWrite("Number of rows affected: " & $RecordsAffected & @CRLF)

The query IS executed correctly. The row is modified in the db, but the affected row(s) are NOT returning.

[mLipok]

You should have set events handling together with errors handling

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")     ; Initialize a COM error handler
Local $oADO_EventsHandler = ObjEvent($oConnection, "__ADO_EVENT__")
#forceref $oADO_EventsHandler

and

_ADO_EVENT__ExecuteComplete(...)

will be fired automatically

[cramaboule]

@mLipok ... Still the same...

When I add :

Local $oADO_EventsHandler = ObjEvent($adoCon, "__ADO_EVENT__")

the script doesn't exit automatically... Very strange behavior. (In PHP it works well, select in AutoIt works well...)