ADO.au3 UDF - BETA - Support Topic

[mLipok]

Try to chane this following static declaration:

Func _ADO_API($sQuery, $bReturnArray = True)

    ; Parameters validation
    If Not IsString($sQuery) Then
        Return SetError($ADO_ERR_INVALIDPARAMETERTYPE, $ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    ElseIf $sQuery = '' Then
        Return SetError($ADO_ERR_INVALIDPARAMETERVALUE, $ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    EndIf

    ; declaring static Connection String
    Local Static $sConnectionString = _ADO_API_ConnectionString()
    If @error Then Return SetError(@error, 1, Null)

    ; declaring static Connection Object
    Local Static $oConnection = _ADO_Connection_Create()
    If @error Then Return SetError(@error, 2, $ADO_RET_FAILURE)

    ; declaring static Connection Object
    Local Static $iOpenStatus = _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, 3, $ADO_RET_FAILURE)

change to normal local variables

Func _ADO_API($sQuery, $bReturnArray = True)

    ; Parameters validation
    If Not IsString($sQuery) Then
        Return SetError($ADO_ERR_INVALIDPARAMETERTYPE, $ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    ElseIf $sQuery = '' Then
        Return SetError($ADO_ERR_INVALIDPARAMETERVALUE, $ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    EndIf

    ; declaring static Connection String
    Local $sConnectionString = _ADO_API_ConnectionString()
    If @error Then Return SetError(@error, 1, Null)

    ; declaring static Connection Object
    Local $oConnection = _ADO_Connection_Create()
    If @error Then Return SetError(@error, 2, $ADO_RET_FAILURE)

    ; declaring static Connection Object
    Local $iOpenStatus = _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, 3, $ADO_RET_FAILURE)

 

[Skysnake]

Hi @dsm-sas I also use ADO extensively.

; create first ADO connection
    $g_DB1 = _ADO_Connection_Create() ; $g_DB is an ADO COM Object!!!

    _ADO_Connection_OpenConString($g_DB1, $sConnectionString) ; first unique connection string

    $OnlineStatus = __ADO_Connection_IsReady($g_DB1)
    
    
; create second ADO connection
    $g_DB2 = _ADO_Connection_Create() ; $g_DB is an ADO COM Object!!!

    _ADO_Connection_OpenConString($g_DB2, $sConnectionString) ; subsequent unique connection string

    $OnlineStatus = __ADO_Connection_IsReady($g_DB2)

Now execute on $g_DB1 or  $g_DB2 at will. I often use this kind of mechanism to migrate data between bases. I use PostGres.

Skysnake

Edited December 30, 2022 by Skysnake

[noellarkin]

Trying to use ADO for mySQL, getting this error when running the example script:
 

! 1667 == 0
###############################
ADO.au3 v.2.1.19 BETA (1163) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  1163
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

I've installed the SQL Server, the driver is present in my ODBC:

Edited January 6, 2023 by noellarkin
clarity

[Skysnake]

On 1/6/2023 at 9:58 PM, noellarkin said:

$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied

At the command prompt type odbcad32 to open the ODBC Data Source Administator. Load that driver or get permission? It may be a security issue. Perhaps a User Account permission issue?

 

To use ADO you need

• data source
• driver
• script
• credentials

The error is telling you driver is not available

Edited January 11, 2023 by Skysnake

[Valiante]

Hi @mLipok

I just discovered this UDF - first, thank you for creating and continuing to develop and support it all this time!

I just set up a simple test MySQL database, which I verified I can connect to and query via DBeaver and phpMyAdmin, so I know the server & DB are good.  I then opened and edited your ADO_EXAMPLE.au3 from the latest beta (2.1.19), commented out _Example_MSSQL_SQLServerAuthorization() and uncommented _Example_MySQL(), then edited the function as follows:

Func _Example_MySQL()
    ; Link to Windows MySQL ODBC drivers
    ; https://dev.mysql.com/downloads/connector/odbc/

    Local $sDriver = 'MySQL ODBC 5.3 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = 'ds1520' ; change this string to YourServerLocation
    Local $sDatabase = 'test' ; change this string to YourDatabaseName
    Local $sPort = '3307' ; change this string to If your Server use non standard PORT
    Local $sUser = 'test' ; change this string to YourUserName
    Local $sPassword = '<redacted>' ; change this string to YourPassword

    ; Local $sConnectionString = 'Driver={' & $sDriver & '};SERVER=' & $sServer & ';PORT=' & $sPort & ';DATABASE=' & $sDatabase & ';User=' & $sUser & ';Passwd=' & $sPassword & ';'
    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)

    ConsoleWrite("Connection string: " & $sConnectionString & @LF)

;~     _Example_1_RecordsetToConsole($sConnectionString, "SELECT * FROM city")
;~     _Example_2_RecordsetDisplay($sConnectionString, "SELECT * FROM country WHERE `region` LIKE '%Europe%'")
;~     _Example_2_RecordsetDisplay($sConnectionString, "SELECT Name , CountryCode , District , Population FROM city WHERE name='Cary'")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MySQL

I'm getting the Connection string output to the console that I added, so I know the function is running, but I'm getting no further output, not even an error, even if I change the connection values to invalid strings:

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\Users\Valiante\Downloads\ADO 2.1.19 BETA\ADO_EXAMPLE.au3" /UserParams    
+>13:23:23 Starting AutoIt3Wrapper (21.316.1639.1) from:SciTE.exe (4.4.6.0)  Keyboard:00000809  OS:WIN_11/2009  CPU:X64 OS:X64  Environment(Language:0809)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\Valiante\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\Valiante\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.16.1)  params:-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7  from:C:\Program Files (x86)\AutoIt3  input:C:\Users\Valiante\Downloads\ADO 2.1.19 BETA\ADO_EXAMPLE.au3
+>13:23:23 AU3Check ended.rc:0
>Running:(3.3.16.1):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\Valiante\Downloads\ADO 2.1.19 BETA\ADO_EXAMPLE.au3"    
+>Setting Hotkeys...--> Press Ctrl+Alt+Break to Restart or Ctrl+BREAK to Stop.
Connection string: Driver={MySQL ODBC 5.3 ANSI Driver};SERVER=ds1520;PORT=3307;DATABASE=test;User=test;Password=<redacted>;
+>13:23:23 AutoIt3.exe ended.rc:0
+>13:23:23 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 1.166

Any advice or help debugging this would be greatly appreciated.

Edited January 30, 2023 by Valiante

[Valiante]

Update:

I created a simple test script and identified the cause:

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

$oConnection = ObjCreate("ADODB.Connection")
$oConnection.Open("Driver={MySQL ODBC 5.3 ANSI Driver};SERVER=ds1520;PORT=3307;DATABASE=test;User=test;Password=<redacted>;")

If Not @error Then $oConnection.Close

Func MyErrFunc()
    ConsoleWrite("COM ERROR -  Description: " & $oMyError.description & @LF)
    ConsoleWrite("COM ERROR - Error Number: " & hex($oMyError.number, 8) & @LF)
    ConsoleWrite("COM ERROR -  Script Line: " & $oMyError.scriptline & @LF)
    ConsoleWrite("COM ERROR - Error Source: " & $oMyError.source & @LF)
EndFunc

This traps an error and returns the following to the console:

COM ERROR -  Description: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
COM ERROR - Error Number: 80020009
COM ERROR -  Script Line: 4
COM ERROR - Error Source: Microsoft OLE DB Provider for ODBC Drivers

Using this I was able to identify the cause as missing MySQL drivers (I needed 32-bit as Scite runs in 32-bit space, as does (I assume) AutoIt3.exe

After installing mysql-connector-odbc-8.0.32-win32.msi and updating the connection string to "MySQL ODBC 8.0 ANSI Driver", I get no error and a successful connection, and I'm able to get a response from _Example_3_ConnectionProperties in your example, but I still don't know why your UDF doesn't return an error, when it should.  I'll keep digging.

Edited January 30, 2023 by Valiante

[mLipok]

Will be watch your results in digging

 

[mLipok]

 

@Valiante any results ?

[Skysnake]

-- 2023.03.20 Postgres INSERT UNNEST Example
CREATE TABLE IF NOT EXISTS demounnest (dname text, dbday text);
TRUNCATE TABLE demounnest;

INSERT INTO demounnest (dname, dbday)
    VALUES
         ('Abe' -- dname
           , '1969-07-04')
         ,('Ben' -- dname
           , '1979-08-05')
;          

INSERT INTO demounnest (dname, dbday)
    SELECT 
          UNNEST(ARRAY['Devon','Earl'])
         ,UNNEST(ARRAY['1989-01-31','1999-02-28'])       
;          

SELECT * FROM   demounnest;

-- RESULT
"Abe"  |"1969-07-04"
"Ben"  |"1979-08-05"
"Devon"|"1989-01-31"
"Earl" |"1999-02-28"

PostgreSQL: Documentation: 15: 9.19. Array Functions and Operators

unnest ( anyarray ) → setof anyelement

Expands an array into a set of rows. The array's elements are read out in storage order.

unnest(ARRAY[1,2]) →

 1
 2
unnest(ARRAY[['foo','bar'],['baz','quux']]) →

 foo
 bar
 baz
 quux

The real power of UNNEST is the speed. When we select data from tables we tend to select rows, and insert rows. This is the standard approach. But it is slow.

UNNEST, even in this mini  example, is 0.25s faster than the conventional insert. 

UNNEST has been available since before version 9.0 and in 9.15 (Current) UNNEST for SELECT has been expanded. 

Edited March 20, 2023 by Skysnake

[Champak]

Any update on getting errors to show? 

My main issue. I'm using MYSQL and I'm unable to update with the _ADO_Execute function. I know I have a connection because I can get info from the database, I know my syntax works for updating because I can execute it directly in phpmyadmin....but I just can't get it to execute here. Here's what I have

 

Local $sDriver = 'MySQL ODBC 8.0 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = 'XX.XX.XX.XX' ; change this string to YourServerLocation
    Local $sDatabase = 'XXXXXXXXXXXX' ; change this string to YourDatabaseName
    Local $sPort = 'XXXXXXXX' ; change this string to If your Server use non standard PORT
    Local $sUser = 'XXXXXXXXXXX' ; change this string to YourUserName
    Local $sPassword = 'XXXXXXXXXXX' ; change this string to YourPassword

    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)

_ADO_Execute($sConnectionString, "UPDATE roottesCart_product SET isbn='9876' WHERE product_id='700'")
If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
            
    _ADO_Connection_Close($sConnectionString)

 

The consolewrite @error is returning "---> @error=5 @extended=7", I don't know what that is or see how to reference it.

Note, I am using beta 2.1.19 because when I try to use the none beta downloads it seems every other function is throwing up an error.

 

[Skysnake]

@Champak, have you tried simple select queries? Is it possible that your default connection is READ ONLY?

[mLipok]

On 5/12/2023 at 6:56 AM, Champak said:

The consolewrite @error is returning "---> @error=5 @extended=7", I don't know what that is or see how to reference it.

 

oops. I mised this question.

Your answer is in: ADO_CONSTANTS.au3

Global Enum _
        $ADO_ERR_SUCCESS, _ ;               No Error
        $ADO_ERR_GENERAL, _ ;               General - some not classified type of Error
        $ADO_ERR_COMERROR, _ ;              COM Error - check your COM Error Handler
        $ADO_ERR_COMHANDLER, _ ;            COM Error Handler Registration
        $ADO_ERR_CONNECTION, _ ;            $oConection.Open    - Opening error
        $ADO_ERR_ISNOTOBJECT, _ ;           Function Parameters error - Expected/Required Object
        $ADO_ERR_ISCLOSEDOBJECT, _ ;        Object state error - Expected/Required state is $ADO_adStateOpen - but currently is $ADO_adStateClosed
        $ADO_ERR_ISNOTREADYOBJECT, _ ;      Object state error - Expected/Required state is $ADO_adStateOpen - but currently is $ADO_adStateConnecting or $ADO_adStateExecuting or $ADO_adStateFetching
        $ADO_ERR_INVALIDOBJECTTYPE, _ ;     Function Parameters error - Expected/Required different Object Type
        $ADO_ERR_INVALIDPARAMETERTYPE, _ ;  Function Parameters error - Invalid Variable type passed to the function
        $ADO_ERR_INVALIDPARAMETERVALUE, _ ; Function Parameters error - Invalid value passed to the function
        $ADO_ERR_INVALIDARRAY, _ ;          Function Parameters error - Invalid Recordset Array
        $ADO_ERR_RECORDSETEMPTY, _ ;        The Recordset is Empty - this not always mean error but in this case will not be returned any data
        $ADO_ERR_NOCURRENTRECORD, _ ;       The Recordset has no current record - but in this case will not be returned any data
        $ADO_ERR_ENUMCOUNTER ;------------- just for testing

 

[mLipok]

btw.
you should also use:

; SetUP internal ADO.au3 UDF COMError Handler
    _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

this way:

_Example()

Func _Example()
    Local $sDriver = 'MySQL ODBC 8.0 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = 'XX.XX.XX.XX' ; change this string to YourServerLocation
    Local $sDatabase = 'XXXXXXXXXXXX' ; change this string to YourDatabaseName
    Local $sPort = 'XXXXXXXX' ; change this string to If your Server use non standard PORT
    Local $sUser = 'XXXXXXXXXXX' ; change this string to YourUserName
    Local $sPassword = 'XXXXXXXXXXX' ; change this string to YourPassword

    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)

    ; SetUP internal ADO.au3 UDF COMError Handler
    _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

    _ADO_Execute($sConnectionString, "UPDATE roottesCart_product SET isbn='9876' WHERE product_id='700'")
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
                
    _ADO_Connection_Close($sConnectionString)
EndFunc

 

[Champak]

Thanks

[mLipok]

as reference:

 

[Champak]

How can I get the error description as it's printed in the console to return in a msgbox compiled? I did something to the UDF that got the description back to me, but as it states, that wont work compiled. I read through a few scripts posted, but it wasn't too clear to me.

[mLipok]

51 minutes ago, Champak said:

How can I get the error description as it's printed in the console to return in a msgbox compiled?

Which description you mean ?

 

[Champak]

$sSQL_ComErrorDescription &= "$oADO_Error.description is: " & @TAB & $oADO_Error.description & @CRLF

from

Func _ADO_COMErrorHandler_Function(ByRef $oADO_Error)

What I did prior to compiling it, because I couldn't figure this out, was just made a global variable and set the description to it. But since that wont work anymore, I need to find out how to pick it up now.

 

[Champak]

So I fixed my immediate issue and got what I want by commenting out the top two lines of 

Func _ADO_COMErrorHandler_Function(ByRef $oADO_Error)

I don't understand why that condition would be put on that. But it still remains, I'm sure there is a better/proper way of getting this info without editing the UDF.

[mLipok]

If you want to use your own ERRROR handler function
you should define which ERRROR handler function you want to use:

 

expandcollapse popup

_ADO_ComErrorHandler_UserFunction(_My_ADO_COMErrorHandler)

Func _My_ADO_COMErrorHandler(ByRef $oADO_Error)
;~  If @Compiled Then Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)

    ; Error Object
    ; https://msdn.microsoft.com/en-us/library/windows/desktop/ms677507(v=vs.85).aspx

    ; Error Object Properties, Methods, and Events
    ; https://msdn.microsoft.com/en-us/library/windows/desktop/ms678396(v=vs.85).aspx

    Local $HexNumber = Hex($oADO_Error.number, 8)
    Local $sSQL_ComErrorDescription = ''
    $sSQL_ComErrorDescription &= "ADO.au3 v." & _ADO_UDFVersion() & " (" & $oADO_Error.scriptline & ") : ==> COM Error intercepted !" & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.description is: " & @TAB & $oADO_Error.description & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.windescription: " & @TAB & $oADO_Error.windescription & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.number is: " & @TAB & $HexNumber & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.lastdllerror is: " & @TAB & $oADO_Error.lastdllerror & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.scriptline is: " & @TAB & $oADO_Error.scriptline & @CRLF

    ; Source Property (ADO Error)
    ; https://msdn.microsoft.com/en-us/library/windows/desktop/ms675830(v=vs.85).aspx
    $sSQL_ComErrorDescription &= "$oADO_Error.source is: " & @TAB & $oADO_Error.source & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.helpfile is: " & @TAB & $oADO_Error.helpfile & @CRLF
    $sSQL_ComErrorDescription &= "$oADO_Error.helpcontext is: " & @TAB & $oADO_Error.helpcontext & @CRLF

    #cs
        ; NativeError Property (ADO)
        ; https://msdn.microsoft.com/en-us/library/windows/desktop/ms678049(v=vs.85).aspx
        $sSQL_ComErrorDescription &= "$oADO_Error.NativeError is: " & @TAB & $oADO_Error.NativeError & @CRLF

        ; SQLState Property
        ; https://msdn.microsoft.com/en-us/library/windows/desktop/ms681570(v=vs.85).aspx
        $sSQL_ComErrorDescription &= "$oADO_Error.SQLState is: " & @TAB & $oADO_Error.SQLState & @CRLF
    #ce

    MsgBox($MB_OK + $MB_TOPMOST + $MB_ICONINFORMATION, '', "###############################" & @CRLF & $sSQL_ComErrorDescription & "###############################" & @CRLF)

    ConsoleWrite("###############################" & @CRLF & $sSQL_ComErrorDescription & "###############################" & @CRLF)
    ; SetError($ADO_ERR_GENERAL, $ADO_EXT_DEFAULT, $sSQL_ComErrorDescription)
EndFunc   ;==>_My_ADO_COMErrorHandler

then internally in _My_ADO_COMErrorHandler() you can do with $oADO_Error whatever you want