Jump to content

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


Recommended Posts

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
Link to post
Share on other sites

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
Link to post
Share on other sites

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
Link to post
Share on other sites

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).

Link to post
Share on other sites

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

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)

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Arual
      We have AutoIt code that performs a connection to an MS SQL database running SQL server 2012, tls 1.0. It has worked successfully for years.
      Today, our IT department migrated our database to a new server that is running SQL server 2016, tls 1.2. Now our connection string is no longer working.
      Here is the original that was working in SQL server 2012
      $sConnStr = "DRIVER={SQL Server};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" When that would run on the new server in SQL server 2016. We are getting the error [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
      We tried changing the connection string to 
      $sConnStr = "DRIVER={SQL Server Native Client 11.0};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" The new error we received is [Microsoft]
      TCP Provider: An existing connection was forcibly closed by the remote host. We can log in successfully using SSMS using the server name, port and user/pass.
      Any suggestions on what we need to change in order to get the connection to work again?
    • By robertocm
      An example of updating an excel file with a join between excel range and access tables.
      #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ;#include <WinAPIFiles.au3> ;Permitir unha única instancia da aplicación #include <Misc.au3> ;_Singleton("ADO_Update_Excel_From_Access", 0) If _Singleton("ADO_Update_Excel_From_Access", 1) = 0 Then MsgBox($MB_SYSTEMMODAL, "Warning", "An occurrence of test is already running") Exit EndIf Opt("MustDeclareVars", 1) Opt("TrayIconDebug", 1) OnAutoItExitRegister("OnAutoItExit") #===== CONFIG ===== Global $sFilePath = @ScriptDir & "\test.xlsx" Global $sFilePath2 = @ScriptDir & "\test.mdb" Global $testmdb = "[;Database=" & $sFilePath2 & ";PWD=123]" ;~ Global $excel = "[Excel 12.0 Xml;HDR=NO;IMEX=1;DATABASE=" & $sFilePath& "]" ;Global $testsqlserver = "[odbc;Driver={SQL Server};SERVER=10.0.0.99;DATABASE=MyDatabaseName;UID=MyUser;PWD=MyPassword]" #===== ADODB ===== Global $cn, $rst, $sSQL, $SubSQL ;Help: COM Error Handling ;_ErrADODB From spudw2k ;https://www.autoitscript.com/forum/topic/105875-adodb-example/ Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB") 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 $cn = ObjCreate("ADODB.Connection") ; Create a connection object $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123" ;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123" ;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath2 & ';uid=;pwd=MyPassword;' ;~ ;Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=MyUser;Password=MyPassword;' ;~ ;Or if you’re using native client: ;~ ;stConnect = "Provider=SQLNCLI10;Data Source=... ;http://www.connectionstrings.com/ ;Xlsx files: Excel 2007 (and later) files with the Xlsx file extension ;[Also valid for] Using the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks ;cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & RutaXls & ";Extended Properties=Excel 12.0 Xml;" Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";" ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";" ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=Excel 8.0;" ;Global $sADOConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & $sFilePath & ";ReadOnly=0;" ;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp ;A Recordset object inherits this setting from the associated Connection object. ;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object. $cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient $cn.CommandTimeout = 30 ;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode ;try Mode = adModeRead instead ;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead ;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1 ;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado" ;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode. ;https://www.w3schools.com/asp/prop_rec_mode.asp ;$cn.Mode = 1 ;Read-only $cn.Open($sADOConnectionString) ; Open the connection ;MsgBox(0, "", $cn.ConnectionString) $sSQL = "UPDATE (([Sheet1$A2:C11] a" _ & " INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID)" _ & " INNER JOIN " & $testmdb & ".[Orders] c ON b.ID = c.ID)" _ & " INNER JOIN " & $testmdb & ".[Customers] d ON c.CustomerID = d.ID" _ & " SET a.F2 = c.OrderDate, a.F3 = d.CompanyName;" $cn.Execute($sSQL, Default, 1 + 0x80) ;adCmdText = 1 , adExecuteNoRecords = 0x80 $sSQL = "SELECT F2, F3, Sum(Quantity * UnitPrice) As Amount" _ & " FROM [Sheet1$A2:C11] AS a INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID" _ & " GROUP BY F2, F3" _ & " ORDER BY F2;" $rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query If Not $rst.EOF = True Then Local $rstArray = $rst.GetRows() _ArrayDisplay($rstArray, "Test", "", $ARRAYDISPLAY_NOROW, "", "F1|F2|F3") $rst.Close $rst = 0 ;Release the recordset object ;$cmd = 0 $cn.Close ;Close the connection $cn = 0 ;Release the connection object Global $RecCount = UBound($rstArray) #===== EXCEL ===== Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. ;_DebugSetup() ;_DebugCOMError() ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True) ;Global $oAppl = _Excel_Open(True, False, False, Default, True) Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509 ;Restaurar en cada arquivo (algún Application.Run pudo cambiar) ;$oAppl.EnableEvents = False $oAppl.DisplayAlerts = False ;~ ;Arquivo non bloqueado ;~ Global $iFileExists ;~ For $j = 0 To 60 ;~ $iFileExists = FileExists($sFilePath2) ;~ If $iFileExists Then ;~ While _WinAPI_FileInUse($sFilePath2) ;~ Sleep(1000) ;~ WEnd ;~ ExitLoop ;~ Else ;~ Sleep(1000) ;~ EndIf ;~ Next ;Create a new workbook with only 1 worksheet Global $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.") ;~ Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath5, False, True) ;~ ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ ;Create a new workbook with only 1 worksheet ;~ ;Global $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.") ;Sleep(3000) ;~ Global $sMessage, $sMessage2 ;~ SplashTextOn("TitleFoo", $sMessage, 580, 60, 900, 840, 1 + 4, "", 16) ;~ For $i = 1 To 10 ;~ $sMessage = $sMessage & "." ;~ $sMessage2 = @TAB & "Pausa " & $sMessage ;~ ControlSetText("TitleFoo", "", "Static1", $sMessage2) ;~ Sleep(1000) ;~ Next $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever ;Global $oSheets = $oWorkbook.Sheets Global $oSheet = $oWorkbook.ActiveSheet ;Global $oSheet = $oWorkbook.Sheets("Sheet1") ;MsgBox(0, "", $oSheet.Name) $oSheet.Range("A1:C1").Font.Bold = True $oSheet.Range("A1:A" & $RecCount + 1).NumberFormat = "dd/mm" $oSheet.Range("B1:B" & $RecCount + 1).NumberFormat = "@" Global $oPageSetup = $oSheet.PageSetup With $oPageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "&D" .CenterHeader = "Report" .RightHeader = "&P of &N" ;.LeftFooter = "&F {&A}" .CenterFooter = "" .RightFooter = "" .LeftMargin = 28 .RightMargin = 28 .TopMargin = 28 .BottomMargin = 28 .HeaderMargin = 15 .FooterMargin = 15 .PrintHeadings = False .PrintGridlines = True .PrintComments = -4142 .CenterHorizontally = False .CenterVertically = False .Orientation = 1 ;2 .Draft = False .FirstPageNumber = -4105 .Order = 1 .BlackAndWhite = True .Zoom = 100 EndWith ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/ Global $TrstArray = $rstArray _ArrayTranspose($TrstArray) $oSheet.Range("A2:C" & $RecCount + 1).Value = $TrstArray Global $aArray2D[1][4] = [["Date", "Client", "Amount"]] _Excel_RangeWrite($oWorkbook, $oSheet, $aArray2D, $oSheet.Cells(1, 1)) ;Global $aArray1D[11] = ["ID", "Udes", "Descrip", "Matricula", "Kilos", "Proveedor", "Corredor", "Fecha", "Contrato", "Restan", "Tanque"] ;$oSheet.Range("A1:K1").value = $aArray1D ;Global $aArray2D[1][6] = [[$rstArray[$i][1], $rstArray[$i][2], $rstArray[$i][3], $rstArray[$i][4], $rstArray[$i][5], $rstArray[$i][6]]] ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, $oSheet.Cells($UltimaFila, 1).Resize(1, 6)) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_RangeWrite: " & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;Global $aArray2D[3][5] = [[11, 12, 13, 14, 15], [21, 22, 23, 24, 25], [31, 32, 33, 34, 35]] ;_Excel_RangeWrite($oWorkbook, Default, $aArray2D, "B1") ;Local $aArray1D[13] = ["Ped", "Archivo", "Abono", "NomCli", "H+I", "ACIDEZ", "CERAS", "E+U", "aa", "aa", "aa", "aa", "aa"] ;$oSheet.Range("A1:M1").value = $aArray1D ;$oSheet.Cells(1, 1).Resize(1, 13).value = $aArray1D ;_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook 51 ;$xlExcel8 56 ;_Excel_BookClose($oWorkbook, False) ;_Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ While _WinAPI_FileInUse($sFilePath5) ;~ Sleep(1000) ;~ Wend ;~ Sleep(3000) ;$oAppl.EnableEvents = True $oAppl.DisplayAlerts = True ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433 ;$oAppl.Application.Quit ;$oAppl = "" ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/ ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478 ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE) ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830 ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try: ;#include <Excel.au3> ;$oExcel = _Excel_Open(False, False, False, False, True) ;$oExcelClose = _Excel_Close($oExcel, False, True) ;~ _Excel_Close($oAppl, False, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ Sleep(1000) ;~ ;Check excel closed ;~ Local $aProcesses = ProcessList("Excel.exe") ;~ ;_ArrayDisplay($aProcesses) ;~ If $aProcesses[0][0] > 0 Then ;~ ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191 ;~ ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :) ;~ ;For now I will use a crude workaround by closing the most recent Excel.exe instance: ;~ ProcessClose($aProcesses[$aProcesses[0][0]][1]) ;~ Sleep(100) ;just to allow some time for the process to definitely close (if it does close) ;~ EndIf Else $rst.Close $rst = 0 ; Release the recordset object $cn.Close ; Close the connection $cn = 0 ; Release the connection object ;Disconnect MsgBox(262144, "", "Empty Recordset", 5) EndIf ;This is a custom error handler Func ErrFunc() Local $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc 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 ;Devolver datos error Local $sFilePath = @DesktopDir & "\error.txt" ;Open the file for write access. Local $hFileOpen = FileOpen($sFilePath, 2) ;If $hFileOpen = -1 Then ;MsgBox(0, "", "An error occurred when reading/writing the file.") ;EndIf FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _ "err.description is: " & @TAB & $errADODB.description & Chr(1) & _ "err.windescription:" & @TAB & $errADODB.windescription & Chr(1) & _ "err.number is: " & @TAB & hex($errADODB.number,8) & Chr(1) & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & Chr(1) & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & Chr(1) & _ "err.source is: " & @TAB & $errADODB.source & Chr(1) & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & Chr(1) & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext _ ) ;Close the handle returned by FileOpen. FileClose($hFileOpen) $rst = 0 ;$cmd = 0 $cn.Close $cn = 0 ;Disconnect 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  
      example_files.zip
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
    • By Blois
      Hi Guys,
      Fine?
      I have this code and I use it to perform the query, however when I change the query to INSERT it is not working return error.
       
      #include <GUIConstants.au3> #include <MsgBoxConstants.au3> #include <Array.au3> Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Example() Func Example()     Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)     If @error then Return SetError(@error, @extended, 0)     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")     $adoRs = ObjCreate("ADODB.Recordset")     GUICreate("listview items", 550, 250, 100, 200, -1, $WS_EX_ACCEPTFILES)     Local $idListview = GUICtrlCreateListView("Codigo    |Nome         |Valor  ", 10, 10, 520, 150) ;,$LVS_SORTDESCENDING     $queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')     Local $aResult     With $adoRs         .CursorType = 2         .LockType = 3         .Open($queryInsert, $adoCon)         If @error Then             ; deal with Probable SQL error             Return SetError(1)         EndIf         If Not .EOF Then $aResult = .GetRows()         .Close()     EndWith     $adoRs = 0     _ArrayDisplay($aResult, 'UBound($aResult)=' & UBound($aResult))     For $iRow_idx = 0 To UBound($aResult) - 1         GUICtrlCreateListViewItem($aResult[$iRow_idx][0], $idListview)     Next     $adoCon.Close     GUISetState()     ; Loop until the user exits.     While 1         Switch GUIGetMsg()             Case $GUI_EVENT_CLOSE                 ExitLoop ;~             Case $idButton ;~                 MsgBox($MB_SYSTEMMODAL, "listview item", GUICtrlRead(GUICtrlRead($idListview)), 2)             Case $idListview                 MsgBox($MB_SYSTEMMODAL, "listview", "clicked=" & GUICtrlGetState($idListview), 2)         EndSwitch     WEnd EndFunc   ;==>Example ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError)     ; Do anything here.     ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _             @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _             @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _             @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _             @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _             @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _             @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _             @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _             @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _             @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc   ;==>_ErrFunc Help, Please!
       
×
×
  • Create New...