Jump to content
Sign in to follow this  
sivaramanm

Unable to retrieve inserted row count in MySQL using ADO from AutoIT

Recommended Posts

sivaramanm

From AutoIT script (Pretty much same syntax as VBA), Tried connecting to MySQL Server. While i am able to insert a new row successfully, unable to verify the rowcount (# of inserted row - to verify success or failure).

Have tried two different methods -

  1. to use the RecordsAffected variable from Connection Execute function
  2. to use the RecordSet and retrieve the rowcount

But have been missing something and none of these methods return the actual row count.

Any help would be appreciated.!!!

Cross-posted in http://stackoverflow.com/questions/27411599/unable-to-retrieve-inserted-row-count-in-mysql-using-ado-from-autoit

MySQLConnect()

$EVENT_TIME= "2014-12-12 12:12:12"
$LSMCName='LSMC1'
$NEType='MME0001'
$OMTarFile='A_MME0011-60MIN-20141212-12-v.tar'
$CSVFile='12-00-S1AP.csv'
$KPIType='S1AP_HO'
$UpdateStatus='NotUpdated'
$ReTries='0'

If Not (InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries)=1) Then
WriteLog("[Error] Record insertion failed for " & $EVENT_TIME & '" ' & $LSMCName & ' ' & $NEType & ' ' & $OMTarFile & ' ' & $CSVFile & ' ' & $KPIType & ' ' & ' NotUpdated 0')
EndIf
MysqlDisconnect()

;~ ####################### Sub Function Definitions

Func MySQLConnect()
Local $sDriver="MySQL ODBC 5.3 ANSI Driver"
Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $sDriver)
If @error or $val = "" Then
SetError(2)
Return -1
EndIf

$constrim="DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost;DATABASE=pmdemo;uid=rootuser;pwd=rootpass;"
$oDBConnect = ObjCreate ("ADODB.Connection") ; <== Create SQL connection
$oDBConnect.Open ($constrim) ; <== Connect with required credentials

if @error Then
WriteLog("[Error] Failed to connect to the database")
SetError(2)
Return -2
Else
;MsgBox(0, "Success!", "Connection to database successful!")
Return 1
EndIf
EndFunc

Func MySQLDisConnect()
$oDBConnect.Close ; ==> Close the database
EndFunc

Func InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries)
Local $RowCount = 0
Local $result = ObjCreate("ADODB.Recordset")

$sQuery = "INSERT INTO 4gc_fileupdatelog (id,EVENT_TIME,LSMC,NEType,TarFile,CSVFile,KPIFile,UpdateStatus,ReTries) VALUES ('0'," & _
"'" & $EVENT_TIME & "'," & _
"'" & $LSMCName & "'," & _
"'" & $NEType & "'," & _

"'" & $OMTarFile & "'," & _
"'" & $CSVFile & "'," & _
"'" & $KPIType & "'," & _
"'" & $UpdateStatus & "'," & _
"'" & $ReTries & "'" & _
") ON DUPLICATE KEY UPDATE ReTries=ReTries+1,UpdateStatus='" & $UpdateStatus & "';"

$result = $oDBConnect.Execute($sQuery,$RowCount)
If @error Then
MsgBox(1,1,"Error executing query...")
Return -2
EndIf

;# Method-1 : To use records affected from Execute function
If $RowCount >= 1 Then
MsgBox(1,1,"Success")
Else
MsgBox(1,1,"Failed, rowcount is:" & $RowCount )
EndIf

If Not ($result.bof AND $result.eof) Then
WriteLog("[Error] No Rows found")
Return 0
EndIf

;# Method-2 : To use recordsset object and retrieve the rows/columns count
If IsObj($result) And $result.EOF=False Then
$myarray=$result.GetRows()
$rows = UBound($myarray,1)
$cols = UBound($myarray,2)
MsgBox(1,1," rows: " & $rows & " cols: " & $cols)

If ($rows = 1) Then
WriteLog("[Info] Record inserted successfully")
Return 1
ElseIf ($rows = 2) Then
WriteLog("[Alert] Record updated successfully. affected row(s) is " & $rows)
Return $rows
Else

> Blockquote

WriteLog("[Error] Record insertion failed. affected row(s) is " & $rows)
Return 0
EndIf
EndIf

EndFunc
Edited by sivaramanm

Share this post


Link to post
Share on other sites
Radiance

I had almost the same problem, Rowcount and shit didnt work, no matter what I tried.
When you do an .GetRows on your executed statement, you will get a 2-dimensional array back.

UBound($Array) gets you the # of rows

UBound($Array, 2) gets you the # of columns

I ended up using this method since it would give me the same result as rowcount would.

Edited by Radiance

Share this post


Link to post
Share on other sites
sivaramanm

Thanks for that info. But as mentioned in my post, i am trying that GetRows option also.

That doesnt work either. PFA the error im getting.

BTW as i said, i am trying to insert a new row. Did GetRows work for you for an insert statement? if so would you mind sharing the code to cross check if i am missing anything?

Appreciate your help!

post-79561-0-89296800-1418324502_thumb.j

Edited by sivaramanm

Share this post


Link to post
Share on other sites
Radiance

Oh, I misread that. I thought you were going to do a select.

>This here is your problem, isn't it?

Share this post


Link to post
Share on other sites
sivaramanm

Pretty much yes, except that im trying to do an "insert into table... on duplicate key update " query which means it can be 1 inserted or 2 rows updated. And thats the value what i wanted to check for.

P.S. To be more specific - in this situation, row count will return 1 if a row was inserted, 2 if a row was updated and 0 if it was an identical match to an existing record (so neither insert nor update).

Share this post


Link to post
Share on other sites
Radiance

The only solution I came up with for this is to do a SELECT right after your INSERT/UPDATE to check if the table contains the data you expected.

Share this post


Link to post
Share on other sites
jchd

I don't have enough knowledge of MySQL capabilities/bugs but maybe there exists something similar to SQLite's sqlite3_changes() which returns the number of DB changes within the last terminated statement over the current DB connection.

Edited by jchd
  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • robertocm
      By robertocm
      In the code below i can write the excel sheet with an array from an ADODB GetRows command
      But not using _Excel_RangeWrite function
      Thanks for your comments,
      #include <Array.au3> #include <Excel.au3> ;Help: COM Error Handling Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB") Local $sFilePath = @ScriptDir & "\db.mdb" Local Const $iCursorType = 0 ; adOpenForwardOnly, 3 adOpenStatic Local Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic Local Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object Global $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object ;Global $sADOConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $sFilePath ;Global $sADOConnectionString = 'DRIVER={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath & ';uid=;pwd=MyPassword;' Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123" $cn.CursorLocation = 3 ; adUseClient ;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 ;$cn.CommandTimeout = 0 $cn.Open($sADOConnectionString) ; Open the connection Local $sSQL = "SELECT * FROM TABLE1" $rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query If Not $rst.EOF = True Then Local $rstArray = $rst.GetRows() _ArrayDisplay ($rstArray) $rst.Close ;Create application object Local $oAppl = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; Create a new workbook with only 1 worksheet Local $oWorkbook = _Excel_BookNew($oAppl, 1) If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") Local $sSheet = $oWorkbook.ActiveSheet Local $aArray1D[3] = ["Field1", "Field2", "Field3"] $sSheet.Range("A1:C1").Font.Bold = True $sSheet.Range("A1:C1").value = $aArray1D Local $RecCount = UBound($rstArray) Local $TrstArray = $rstArray _ArrayTranspose($TrstArray) ;$sSheet.Range("A2:C" & $RecCount + 1).value = $TrstArray ;<<<<<<<<<<<<<< This writes the data OK ;This fails _Excel_RangeWrite($oWorkbook, Default, $TrstArray, "A2:C" & $RecCount + 1) If @error Then MsgBox(0, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $rst = 0 ;Release the recordset object $cn.Close ;Close the connection $cn = 0 ;Release the connection object Else $rst.Close $rst = 0 ; Release the recordset object $cn.Close ; Close the connection $cn = 0 ; Release the connection object MsgBox(262144, "", "Empty" & @CRLF & "Empty recordset" , 5) EndIf 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 $cmd = 0 $cn.Close $cn = 0 Exit EndFunc  
    • Case85
      By Case85
      Hi for all!
       
      After a long time I wasted to find the best way to connect to any external MySQL server, I'm ready to hire "C" or "C++" developer to use MySQL connector dll file for create a stable way to connect to a MySQL server.
      I already tried to use the UDF "miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject" which is working perfect (this way what I looking for), but unfortunately after 1-2 hours usage failed and exit the script.
      I plan to use the MySQL connector heavily and continuously on long time.
      The developed connector must be have a small memory footprint.
      My budget is moderated, I need this connector quick as possible, please contact with me in PM for more details.
      Thank you for all.
    • Fhelipe
      By Fhelipe
      Nothing More
    • rynow
      By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
    • macran
      By macran
      I use mysql.au3 to connect mysql database .  If restart the server ,the client how to reconnect mysql?
      The function prototype is
      _MySQL_Options($MySQL_ptr, $option, $arg = 0, $type = "ptr")
      so,
      _MySQL_Options($MysqlConn,$MYSQL_OPT_RECONNECT, ?, ?)
      pls help me,thanks!
       
       
       
×