ADO.au3 UDF - BETA - Support Topic

[ale1981]

Not sure if this has been corrected or not but I downloaded the most recent version (2.1.16 BETA), there is a bug in __ADO_MSSQL_CONNECTION_STRING_WinAuth and caused me a huge headache as I was getting provider not found errors when I knew the provider was installed fine.

The curly brackets are not needed around the PROVIDER=

$sConnectionString = "PROVIDER={" & _ADO_MSSQL_GetProviderVersion() & "};SERVER=" & $sServer & ";DATABASE=" & $sDataBase & ";"

should be 

$sConnectionString = "PROVIDER=" & _ADO_MSSQL_GetProviderVersion() & ";SERVER=" & $sServer & ";DATABASE=" & $sDataBase & ";"

I also added Integrated Security=SSPI; to this line...

$sConnectionString &= "Trusted_Connection=True;Integrated Security=SSPI;"

 

[Nates]

I've discovered a couple other things while using this Library:

1. Because it echos all commands to the console, if you write a very long query of about 2m chars, things get very sketchy.
2. The COM error catching does not seem to catch internal SQL errors. (Stored procedure not found, or invalid division...etc).

To fix (1), I re-wrote the following function to cap the length of the message:

Func __ADO_ConsoleWrite_Blue($sText)
    ConsoleWrite(BinaryToString(StringToBinary('>>' & StringLeft($sText,200) & @CRLF, 4), 1))
EndFunc   ;==>__ADO_ConsoleWrite_Blue

To fix (2), well, it appears the ADO command silently fails when the command text is longer than 2m chars (or somewhere near there).

So, I switched to using [_ADO_Command_CreateParameter] for the parameters, and now i properly get the com Errors. 🙂

Edited October 16, 2020 by Nates
Found solution

[mLipok]

REMARK:

For all interested:

I just change/edit OpeningPost.

Simply added:

Quote

FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
I will relay on this database in my examples.

 

 

 

Edited October 16, 2020 by mLipok
AdventureWorks2014.bak >> AdventureWorksDW2016_EXT.bak

[mLipok]

15 hours ago, Nates said:

So, I switched to using [_ADO_Command_CreateParameter] for the parameters, and now i properly get the com Errors. 🙂

Edited 34 minutes ago by Nates
Found solution

It would be very kind to describe the problem again/back and share your solution.

[mLipok]

I have plan to make few changes in this UDF but it will take some time.....

 

[mLipok]

Interestning about InfoMessage:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-events

 

[mLipok]

New version:

2020/10/18 '2.1.18 BETA'
    .   EXAMPLE: ADO_EXAMPLE__EventHandling.au3 - mLipok

    2020/10/18 '2.1.17 BETA'
    .   Added: Function: _ADO_Connection_Create() - Assign __ADO_EVENT__* functions by default - mLipok
    .   Added: Global Const $__g_oADO_EventsHandler - not needed as event function is assigned by default - mLipok
    .   Added: Function: __ADO_EVENT__ExecuteComplete() - mLipok
    .   Added: Function: __ADO_RowAffected() - mLipok
    .   Added: Function: __ADO_EVENTS_ErrorCollectionAnalyzer() - mLipok
    .   Refactored: Function: _ADO_Connection_OpenMSSQL() - mLipok
    .   Refactored: Function: _ADO_Connection_OpenMSSQL_WinAuth() - mLipok
    .   Added: Function: __ADO_EVENTS_ErrorCollectionAnalyzer() - mLipok
    .   Added: Function: _ADO_EVENTS_ShowOnly_InfoMessages() - mLipok
    .   
    .   !!!!! SCRIPT BREAKING CHANGE !!!!!
    .   Removed: Funcion: __ADO_EVENTS_INIT() - mLipok
    .   Removed: Funcion: _ADO_EVENTS_SetUp() - mLipok
    .   Removed: Global $__g_fnFetchProgress - mLipok
    .   Renamed: Function: _ADO_COMErrorHandler() >>> _ADO_COMErrorHandler_Function() - mLipok

Download link:

 

 

Edited October 18, 2020 by mLipok

[mLipok]

NEW Example ( catching InfoMessage and #RowAffected )

expandcollapse popup

#include "ADO.au3"

#REMARK
#cs
    To use this example here:
    You should create [AutoIt_Test_DataBase] in MS SQL Server (could be Express edition)
#ce

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)
ConsoleWrite('EXAMPLE START' & @CRLF)
_Example()
ConsoleWrite('EXAMPLE START' & @CRLF)
ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)

Func _Example()

    Local $sPassword = 'AutoIt' ; CHANGE TO YOUR OWN PASS
    Local $sInstance = 'localhost\SQLExpress' ; CHANGE TO YOUR OWN MS SQL SERVER INSTANCES
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, "PROVIDER=SQLOLEDB.1;SERVER=" & $sInstance & ";uid=sa;pwd=" & $sPassword & ";")

    ; TRY TO USE EACH OF THIS 2 FOLLOWING LINE, JUST COMMENT FIRST OR SECOND LINE
;~  _ADO_EVENTS_ShowOnly_InfoMessages(False)
    _ADO_EVENTS_ShowOnly_InfoMessages(True)

    _ADO_Execute($oConnection, "CREATE TABLE [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]( [Test] [nchar](100) NULL) ON [PRIMARY]")
    If @error Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)

    ; LET INSERT SOME DATA
    Local $sContent = 'Testing ADO EVENTS'
    Local $sContent_Updated = 'Testing ADO EVENTS UPDATED'
    Local $sSQL_Query_Insert = _
            "INSERT INTO [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]" & @CRLF & _
            "([Test]) VALUES ('" & $sContent & "')" & @CRLF & _
            ""
    _ADO_Execute($oConnection, $sSQL_Query_Insert)
    _ADO_Execute($oConnection, $sSQL_Query_Insert)
    _ADO_Execute($oConnection, $sSQL_Query_Insert)

    Local $sSQL_Query_Update = _
            "UPDATE [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]" & @CRLF & _
            "SET [Test]='" & $sContent_Updated & "'" & @CRLF & _
            "WHERE [Test]='" & $sContent & "'" & @CRLF & _
            ""
    _ADO_Execute($oConnection, $sSQL_Query_Update)
    ConsoleWrite('- UPDATE: RowAffected=' & @extended & @CRLF)

    ; SHOW TABLE CONTENT
    Local $oRecordeset = _ADO_Execute($oConnection, _
            "SELECT *" & @CRLF & _
            "FROM [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]" & @CRLF & _
            "")
    _ADO_Recordset_Display($oRecordeset, 'SHOW TABLE CONTENT')

    ; DELETE THEM
    _ADO_Execute($oConnection, _
            "DELETE" & @CRLF & _
            "FROM [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]" & @CRLF & _
            "WHERE [Test]='" & $sContent_Updated & "'" & @CRLF & _
            "")
    If @error Then
        ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : ' & @CRLF)
    Else
        ConsoleWrite('- DELETE: RowAffected=' & @extended & @CRLF)
    EndIf

    _ADO_EVENTS_ShowOnly_InfoMessages(True)
    ConsoleWrite('! ' & @ScriptLineNumber & ' : ' & _ADO_EVENTS_ShowOnly_InfoMessages() & @CRLF)
    _ADO_Execute($oConnection, "PRINT 'this is a test - hello - Line 1'")

    _ADO_EVENTS_ShowOnly_InfoMessages(False)
    ConsoleWrite('! ' & @ScriptLineNumber & ' : ' & _ADO_EVENTS_ShowOnly_InfoMessages() & @CRLF)
    _ADO_Execute($oConnection, "DROP TABLE [AutoIt_Test_DataBase].[dbo].[AutoIt_ADO_UDF_TestTable]")
EndFunc   ;==>_Example

Results:

EXAMPLE START
- UPDATE: RowAffected=3
- DELETE: RowAffected=3
! 72 : True
>> [ADO InfoMessage]=this is a test - hello - Line 1
! 76 : False
>> ADO EVENT fired function: __ADO_EVENT__WillExecute:
>>   $sSource=DROP TABLE [AutoIt_Test_DataBase].[dbo].[AutoIt_ADO_UDF_TestTable]
>>   $iCursorType=-1
>>   $iLockType=-1
>>   $iOptions=-1
>>   $i_adStatus=1
>> ADO EVENT fired function: __ADO_EVENT__ExecuteComplete:
>>   $iRecordsAffected=-1
>>   $i_adStatus=1
EXAMPLE START

 

Edited October 18, 2020 by mLipok

[mLipok]

haha...

I just bougth this book:

https://www.ebay.com/itm/302366891714

Edited October 18, 2020 by mLipok

[mLipok]

new example: How to work with transactions:

expandcollapse popup

#include "ADO.au3"

#REMARK
#cs
    To use this example here:
    You should create [AutoIt_Test_DataBase] in MS SQL Server (could be Express edition)
#ce


ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)
ConsoleWrite('EXAMPLE START' & @CRLF)
_Example()
ConsoleWrite('EXAMPLE START' & @CRLF)
ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)

Func _Example()
    ; ADO.au3 presets
    _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
    _ADO_EVENT_Wrapper(_ADO_EVENT_UserHandler)

    ; Open connection with ConnectionString
    Local $sPassword = 'AutoIt' ; CHANGE TO YOUR OWN PASS
    Local $sInstance = 'localhost\SQLExpress' ; CHANGE TO YOUR OWN MS SQL SERVER INSTANCES
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, "PROVIDER=SQLOLEDB.1;SERVER=" & $sInstance & ";uid=sa;pwd=" & $sPassword & ";")

    ; TRY TO USE EACH OF THIS 2 FOLLOWING LINE, JUST COMMENT FIRST OR SECOND LINE
    _ADO_EVENTS_ShowOnly_InfoMessages(False)
;~  _ADO_EVENTS_ShowOnly_InfoMessages(True)

    ;https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-example-vb?view=sql-server-ver15
    $oConnection.BeginTrans()

    _ADO_Execute($oConnection, "CREATE TABLE [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]( [Test] [nchar](100) NULL) ON [PRIMARY]")
    If @error Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)

    ; LET INSERT SOME DATA
    Local $sSQL_Query_Insert = _
            "INSERT INTO [AutoIt_Test_DataBase].dbo.[AutoIt_ADO_UDF_TestTable]" & @CRLF & _
            "([Test]) VALUES ('XXX')" & @CRLF & _
            ""
    For $iLoop_idx =1 To 100
        _ADO_Execute($oConnection, StringReplace($sSQL_Query_Insert,'XXX', $iLoop_idx))
    Next

    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, 'Question', _
            'Save all changes ?') Then
        $oConnection.CommitTrans()
    Else
        $oConnection.RollbackTrans()
    EndIf

    $oConnection.Close

EndFunc   ;==>_Example

Func _ADO_EVENT_UserHandler($param0, ByRef $oConnection_param1, ByRef $oCommand_param3, ByRef $oRecordset_param3, ByRef $oError_param4, $param5 = Null, $param6 = Null, $param7 = Null, $param8 = Null, $param9 = Null)
    Switch $param0
        Case 'Disconnect'
            MsgBox($MB_OK + $MB_TOPMOST + $MB_ICONINFORMATION, 'Information', 'ADO Connection was disconnected.')
        Case Else
    EndSwitch
    #forceref $param0, $oConnection_param1, $oCommand_param3, $oRecordset_param3, $oError_param4, $param5, $param6, $param7, $param8, $param9
EndFunc   ;==>_ADO_EVENT_UserHandler

 

Edited November 8, 2020 by mLipok
#CS #CE fix

[mLipok]

New version:

2020/11/09 '2.1.19 BETA'
.   Added: Function: __ADO_EVENT_InternalWrapper()  - mLipok
.   Added: Function: _ADO_EVENT_Wrapper()  - mLipok
.   Changed: Function: _ADO_OpenSchema_*  - added checking __ADO_Recordset_IsNotEmpty($oRecordset) - mLipok
.   Refactored: Function: _ADO_OpenSchema_*  - refactored + cleaned - mLipok
.   Added: New example: ADO_EXAMPLE__Transactions.au3 - mLipok
.   Renamed: Function: __ADO_ComErrorHandler_InternalFunction() >> __ADO_ComErrorHandler_WrapperFunction() - mLipok
.   Removed: #AutoIt3Wrapper_Au3Check_Parameters - as should be only in examples - because should not force user to use Au3Check - mLipok
.   Changed: suplemented/checked "Function Header's" - mLipok

Download link:

 

[mLipok]

ADO_EXAMPLE__Transactions.au3 

Is strongly refactored/rewrited

expandcollapse popup

#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7
#include "ADO.au3"

#REMARK
#cs
    To use this example here:
    You should create ["&$sTesting_DataBaseName&"] in MS SQL Server (could be Express edition)
#ce


ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)
ConsoleWrite('EXAMPLE START' & @CRLF)
_Example()
ConsoleWrite('EXAMPLE START' & @CRLF)
ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)

Func _Example()
    ; ADO.au3 presets
    _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)
    _ADO_EVENT_Wrapper(_ADO_EVENT_UserHandler)

    ; Open connection with ConnectionString
    Local $sPassword = 'AutoIt' ; CHANGE TO YOUR OWN PASS
    Local $sInstance = 'localhost\SQLExpress' ; CHANGE TO YOUR OWN MS SQL SERVER INSTANCES
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, "PROVIDER=SQLOLEDB.1;SERVER=" & $sInstance & ";uid=sa;pwd=" & $sPassword & ";")

    ; TRY TO USE EACH OF THIS 2 FOLLOWING LINE, JUST COMMENT FIRST OR SECOND LINE
    _ADO_EVENTS_ShowOnly_InfoMessages(False)
;~  _ADO_EVENTS_ShowOnly_InfoMessages(True)

    Local $sTesting_DataBaseName = 'AutoIt_Test_DataBase'
    Local $sTesting_Table = 'TestTransactions'

        Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $sTesting_DataBaseName, Default, $sTesting_Table)
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        _ADO_Execute($oConnection, "CREATE TABLE [" & $sTesting_DataBaseName & "].dbo.[" & $sTesting_Table & "] ([ColumnTest] [nchar](100) NULL) ON [PRIMARY]")
        If @error Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $sTesting_DataBaseName, Default, $sTesting_Table)
    EndIf
    _ADO_Recordset_Display($oRecordset_Tables, 'Schema for table: ' & $sTesting_Table)

    ; LET INSERT SOME DATA
    Local $sSQL_Query_Insert = _
            "INSERT INTO [" & $sTesting_DataBaseName & "].dbo.[" & $sTesting_Table & "]" & @CRLF & _
            "([ColumnTest]) VALUES ('XXX')" & @CRLF & _
            ""
    _ADO_Execute($oConnection, $sSQL_Query_Insert)

    ;https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-example-vb?view=sql-server-ver15
    #Region - Transactional SQL queries
    $oConnection.BeginTrans()

    For $iLoop_idx = 1 To 10 ; now add 10 new rows
        _ADO_Execute($oConnection, StringReplace($sSQL_Query_Insert, 'XXX', $iLoop_idx))
    Next

    Local $oRecordset_test = _ADO_Execute($oConnection, "SELECT * FROM [" & $sTesting_DataBaseName & "].dbo.[" & $sTesting_Table & "]")
    _ADO_Recordset_Display($oRecordset_test, 'Test Insterted data')

    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, 'Question', _
            'Save all changes ?') Then
        $oConnection.CommitTrans()
    Else
        $oConnection.RollbackTrans()
    EndIf
    #EndRegion - Transactional SQL queries

    Local $oRecordset_Final = _ADO_Execute($oConnection, "SELECT * FROM [" & $sTesting_DataBaseName & "].dbo.[" & $sTesting_Table & "]")
    _ADO_Recordset_Display($oRecordset_Final, 'Finally testing data')

    _ADO_Execute($oConnection, "DROP TABLE [" & $sTesting_DataBaseName & "].[dbo].[" & $sTesting_Table & "]")

    $oConnection.Close

EndFunc   ;==>_Example

Func _ADO_EVENT_UserHandler($param0, ByRef $oConnection_param1, ByRef $oCommand_param3, ByRef $oRecordset_param3, ByRef $oError_param4, $param5 = Null, $param6 = Null, $param7 = Null, $param8 = Null, $param9 = Null)
    Switch $param0
        Case 'Disconnect'
            MsgBox($MB_OK + $MB_TOPMOST + $MB_ICONINFORMATION, 'Information', 'ADO Connection was disconnected.')
        Case Else
    EndSwitch
    #forceref $param0, $oConnection_param1, $oCommand_param3, $oRecordset_param3, $oError_param4, $param5, $param6, $param7, $param8, $param9
EndFunc   ;==>_ADO_EVENT_UserHandler

 

I was trying to answer @Nas to his question from this topic:

 

Edited November 9, 2020 by mLipok
#CE >> #ce

[mLipok]

Could someone post a screenshot here with this GUI but in a different language ?

 

[JockoDundee]

11 hours ago, mLipok said:

Could someone post a screenshot here with this GUI but in a different language ?

[mLipok]

And 32 bit version 

[JockoDundee]

36 minutes ago, mLipok said:

And 32 bit version 

 

[jchd]

French W10

[DonChunior]

German:

[mLipok]

Thanks for this screenshots to all of yours.
Now  It would be much easier to explain (for many forum members) the diferences ... why ADO won't work on x64....
Because how we can see Microsoft in Windows x64 still prefer x32bit drivers

There is much more driver in the list for x32 than in x64.
No one had x64 bit driver version for EXCEL .

To add support for EXCEL to ADO in your x64 environment, you should download and install :  Microsoft Access Database Engine 2010 Redistributable

There are 2 versions:

AccessDatabaseEngine.exe
AccessDatabaseEngine_X64.exe

After when you install  AccessDatabaseEngine_X64.exe you will see  4 new drivers for x64bit environment

 

REMARK:

If you install x32 bit version, then you won't be able to install x64 bit version.
If you install x64 bit version, then you won't be able to install x32 bit version.

 

btw:
You can read something about downloading/instaling here:
http://help.loftware.com/pages/viewpage.action?pageId=27099554

Also for this one who do not know how to open "ODBC Data Source Administrator" here you can found a guide:
http://help.loftware.com/display/LPSKB/How+To+Find+ODBC+Data+Sources+(32-bit)+Shortcut+in+Windows

 

Edited November 10, 2020 by mLipok

[JockoDundee]

37 minutes ago, mLipok said:

Thanks for this screenshots to all of yours.
Now  It would be much easier to explain (for many forum members) the diferences ...

Nice misdirection with the “post screenshot in a different language” bit

And to think I was this close from switching my default language from bland English to an to an exciting, if unfamiliar one just to give you some lingual diversity.

I would have but I was worried about logging back in in Afrakan...