ADO.au3 UDF - BETA - Support Topic

ale1981 · September 30, 2020

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 · October 16, 2020

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

Because it echos all commands to the console, if you write a very long query of about 2m chars, things get very sketchy.
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 · October 16, 2020

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.

Expand

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

mLipok · October 16, 2020

On 10/16/2020 at 12:28 AM, 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

Expand

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

mLipok · October 16, 2020

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

mLipok · October 16, 2020

Interestning about InfoMessage:

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

mLipok · October 18, 2020

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 · October 18, 2020

NEW Example ( catching InfoMessage and #RowAffected )

#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 · October 18, 2020

haha...

I just bougth this book:

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

Edited October 18, 2020 by mLipok

mLipok · October 23, 2020

new example: How to work with transactions:

#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 · November 9, 2020

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 · November 9, 2020

ADO_EXAMPLE__Transactions.au3

Is strongly refactored/rewrited

#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 · November 9, 2020

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

JockoDundee · November 9, 2020

On 11/9/2020 at 10:42 AM, mLipok said:

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

Expand

mLipok · November 9, 2020

And 32 bit version

JockoDundee · November 9, 2020

On 11/9/2020 at 11:01 PM, mLipok said:

And 32 bit version

Expand

jchd · November 9, 2020

French W10

DonChunior · November 10, 2020

German:

mLipok · November 10, 2020

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 · November 10, 2020

On 11/10/2020 at 6:10 PM, mLipok said:

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

Expand

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

ADO.au3 UDF - BETA - Support Topic

Recommended Posts

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

mLipok

mLipok

jchd

Posted Images

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content