ADO.au3 UDF - BETA - Support Topic

[mLipok]

About the shema: 
The important remark is that as each SQL statement is related to specyfic RDBMS, so usually you can not use MSSQL schema on PostgreSQL, or MySQL ....... 

 

Edited September 28, 2016 by mLipok

[jchd]

SQL engine offer several kinds of triggers that may act BEFORE or AFTER something, with something being INSERT, UPDATE or DELETE. Then a trigger may act FOR EACH ROW (fires for every row, as the name says) or FOR EACH STATEMENT (may fire once irrespective of the number of rows affected by the statement). You can do a lot of things in std SQL inside a trigger, including acting on other table(s). There are some limitations but still triggers are a powerful tool to keep data integrity/validation correct with SQL code put inside the DB (opposed to coded in every application that uses the DB, with code sync issues, aso).

You can also use INSTEAD OF triggers, which act on views and allow modifying data from views (which are otherwise nothing more than the result of a select).

Of course, every engine vendor has added its own flavor, set of extensions and limitations to triggers, so they are rarely portable "as is" accross engines.

[TheDcoder]

Thanks for the UDF @mLipok, I am planning to use it as an alternative to the independent MySQL library .

[antonioj84]

I included the ado udf By mlipok  ?

can someone help with that  ADO snippet, I DOWNLOADED  from the forum  it works with xls,  however i changed the connection string to make it work with xlsx

i am getting the error below

$oConnection.Open($sConnectionString)
$oConnection^ ERROR

https://www.autoitscript.com/wiki/ADO_Example_Excel

excel spreadsheet

a              b                     c

F1	F2	F3
F1.1	F2.1	F3.1
F1.2	F2.2	F3.2

 

expandcollapse popup

Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $sFilename = @ScriptDir & "\ADO_Example_Excel.xlsx"
;Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"' ;xls

Global $sConnectionString = 'Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & 'Extended Properties="Excel 12.0 Xml;HDR=No"' ;xlsx
$oConnection.Open($sConnectionString) ; Open the connection
;"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields of the selected range A1:B2

$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
If Not @error Then
    With $oRecordset
        While Not .EOF ; repeat until End-Of-File (EOF) is reached

            ; Write the content of all fields to the console separated by | by processing the fields collection
            ConsoleWrite("Process the fields collection:          ")
            For $oField In .Fields
                ConsoleWrite($oField.Value & "|")
            Next
            ConsoleWrite(@CR)

            ; Write a second line by accessing all fields of the collection by name or item number
            ConsoleWrite("Process the fields by name/item number: " & .Fields("F1").Value & "|" & .Fields(1).Value & "|" & @CR)
            .MoveNext ; Move To the Next record
        WEnd
    EndWith

    ; Clean Up Recordset
    $oRecordset.Close ; Close the recordset
    $oRecordset = 0 ; Release the recordset object
EndIf

; Clean Up Connection
$oConnection.Close ; Close the connection
$oConnection = 0 ; Release the connection object

 

Edited October 23, 2016 by antonioj84

[mLipok]

Remark 1:

Wiki page are talking about ADO in general - I mean not related to my UDF.
Wiki examples for ADO are very similar as I changed many of them to use the same variable names as in my ADO.au3 UDF.
 

Remark 2:
In some cases to correctly use Wiki Examples for ADO you should also use COM Error Handler.
Wiki Examples for ADO showing to you only the concept how to use ADO in AutoIt.
Of course it should use out of the box, but you are doing something wrong (I think so) and for this case you should use COM Error Handler
Here is example:
 

expandcollapse popup

; Error monitoring. This will trap all COM errors while alive.
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")


Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $sFilename = @ScriptDir & "\ADO_Example_Excel.xlsx"
;Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"' ;xls

Global $sConnectionString = 'Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & 'Extended Properties="Excel 12.0 Xml;HDR=No"' ;xlsx
$oConnection.Open($sConnectionString) ; Open the connection
;"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields of the selected range A1:B2

$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
If Not @error Then
    With $oRecordset
        While Not .EOF ; repeat until End-Of-File (EOF) is reached

            ; Write the content of all fields to the console separated by | by processing the fields collection
            ConsoleWrite("Process the fields collection:          ")
            For $oField In .Fields
                ConsoleWrite($oField.Value & "|")
            Next
            ConsoleWrite(@CR)

            ; Write a second line by accessing all fields of the collection by name or item number
            ConsoleWrite("Process the fields by name/item number: " & .Fields("F1").Value & "|" & .Fields(1).Value & "|" & @CR)
            .MoveNext ; Move To the Next record
        WEnd
    EndWith

    ; Clean Up Recordset
    $oRecordset.Close ; Close the recordset
    $oRecordset = 0 ; Release the recordset object
EndIf

; Clean Up Connection
$oConnection.Close ; Close the connection
$oConnection = 0 ; Release the connection object

; 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

 

 

QUESTION:
in ADO_EXAMPLE.au3 there is a good example:
 

Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Excel File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

 

Did you try it ?

 

Edited October 23, 2016 by mLipok

[antonioj84]

Thanks Mlipok. Actually, I use a combination of both, your ado_example.au3 save my life. I am going to use it instead, however  what is the difference or the advantage of using over the other 

expandcollapse popup

#include<ADO.au3>
#include<ADO_CONSTANTS.au3>


Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table



_Example_MSExcela()

Func _Example_MSExcela()

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $sFileFullPath = @ScriptDir & "\ADO_Example_Excela.xlsx" ;Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
Global $sProvider = "Microsoft.ACE.OLEDB.12.0"
Global $sExtProperties = 'Excel 12.0; Xml'
Global $HDR ="No" ; with heading select Yes
Global $IMEX ="1"

Global $oConnection = _ADO_ConnectionString_Excel($sFileFullPath , $sProvider , $sExtProperties , $HDR , $IMEX)
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields of the selected range A1:B2
$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not @error Then
    With $oRecordset
        While Not .EOF ; repeat until End-Of-File (EOF) is reached

            ; Write the content of all fields to the console separated by | by processing the fields collection
            ConsoleWrite("Process the fields collection:          ")
            For $oField In .Fields
                ConsoleWrite($oField.Value & "|")
            Next
           ConsoleWrite(@CR)

            ; Write a second line by accessing all fields of the collection by name or item number
            ConsoleWrite("Process the fields by name/item number: " & .Fields("F1").Value & "|" & .Fields(1).Value & "|" & @CR)
            .MoveNext ; Move To the Next record
        WEnd
    EndWith

    ; Clean Up Recordset
    $oRecordset.Close ; Close the recordset
    $oRecordset = 0 ; Release the recordset object

EndIf


; Clean Up Connection
_ADO_Connection_Close($oConnection) ; close connection
$oConnection = Null



EndFunc

 

[antonioj84]

Hi Mlipok, I will be using this one instead great coding

expandcollapse popup

#include "ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>





_Example_MSExcelb()


Func _Example_MSExcelb()

    Local $sFileFullPath = @ScriptDir & "\ADO_Example_Excelb.xlsx" ;Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = "Microsoft.ACE.OLEDB.12.0"
    Local $sExtProperties = 'Excel 12.0; Xml'
    Local $HDR ="No"
    Local $IMEX ="1"

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath , $sProvider , $sExtProperties , $HDR , $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

    $sConnectionString.Close ; Close the connection
    $sConnectionString = 0 ; Release the connection object

EndFunc    ;==>_Example_MSExcelb













Func _Example_1_RecordsetToConsole($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetArray)

    ; Go through the array variable (Recorset Conent)
    Local $iColumn_count = UBound($aRecordsetContent, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordsetContent) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordsetContent[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc    ;==>_Example_1_RecordsetToConsole

Func _Example_2_RecordsetDisplay($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc    ;==>_Example_2_RecordsetDisplay

Func _Example_3_ConnectionProperties($sConnectionString)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Get all connection properties to Array
    Local $aProperties = _ADO_Connection_PropertiesToArray($oConnection)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Show connection properties
    _ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")

EndFunc    ;==>_Example_3_ConnectionProperties

tead

[mLipok]

18 minutes ago, antonioj84 said:

however  what is the difference or the advantage of using over the other 

What exactly you want to compare ?

[antonioj84]

The advantage or disadvantage  of using one over the other the 2 snippets.I find  a typo in your code, the "connection closing" was erroring out, I made the correction below.

 

Func _Example_MSExcelb()

    Local $sFileFullPath = @ScriptDir & "\ADO_Example_Excelb.xlsx" ;Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = "Microsoft.ACE.OLEDB.12.0"
    Local $sExtProperties = 'Excel 12.0; Xml'
    Local $HDR ="No"
    Local $IMEX ="1"

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath , $sProvider , $sExtProperties , $HDR , $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)


   _ADO_Connection_Close($sConnectionString)
   $sConnectionString = Null
   $sConnectionString = 0 ; Release the connection object

EndFunc    ;==>_Example_MSExcelb

 

Edited October 23, 2016 by antonioj84

[mLipok]

There is no need for this:

_ADO_Connection_Close($sConnectionString)
   $sConnectionString = Null
   $sConnectionString = 0 ; Release the connection object

EndFunc    ;==>_Example_MSExcelb

 

as each of this following functions:
 

_Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

is opening and cleaning up it internally.

btw.

$sConnectionString = Null
$sConnectionString = 0 ; Release the connection object

$sConnectionString is string not object.

 

[antonioj84]

thanks for the correction Mlipok  well noted

[antonioj84]

how do  iterate with please give  just give me an idea. I am using  ADO_EXAMPLE.au3  below is my table structure

$max = Sheet
$checknum = 5357

SELECT <storeNUm>,<blacklan>/26,<svr-lan>/28
FROM [sheet];

while not.eod

for $i = 1 to UBound($max)-1
$check = False
if $max[$i][0] = $checknum Then msgbox(0,"found","",3)

next

wend

 

[mLipok]

Try this:
 

...
...
...
Local $sSQL_Query = "SELECT '<storeNUm>', '<blacklan>/26' ,'<svr-lan>/28' FROM [sheet$]"

_Example_1_RecordsetToConsole($sConnectionString, $sSQL_Query)
_Example_2_RecordsetDisplay($sConnectionString, $sSQL_Query)
_Example_3_ConnectionProperties($sConnectionString)
...
...
...

 

[antonioj84]

mlIPOK, Thanks. that what's I wanted

Local $sSQL_Query = "SELECT '<storeNUm>', '<blacklan>/26' ,'<svr-lan>/28' FROM [sheet$]"

_Example_1_RecordsetToConsole($sConnectionString, $sSQL_Query)
_Example_2_RecordsetDisplay($sConnectionString, $sSQL_Query)
_Example_3_ConnectionProperties($sConnectionString)

[TheDcoder]

13 minutes ago, antonioj84 said:

mlIPOK

That's the best interpretation of @mLipok I have ever seen

[Turranius]

I could use a tip here.

 

I use this to connect to a MS SQL 2008 R2. Works fine from my machine when testing it.

 

However, on most other machines I get

 

###############################
ADO.au3 v.2.1.13 BETA (1026) : ==> COM Error intercepted !
$oADO_Error.description is:     [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:     80020009
$oADO_Error.lastdllerror is:     0
$oADO_Error.scriptline is:     1026
$oADO_Error.source is:     Microsoft OLE DB Provider for SQL Server
$oADO_Error.helpfile is:     
$oADO_Error.helpcontext is:     0
###############################

 

I am unsure how to specify the instance name in the connection string. I know what it is and apparently I have to specify it somewhere?

 

Snippet

Global $sDatabase = 'DriveMaps'
Global $sServer = 'SEDBS154.domain.org'
Global $sUser = 'SecretUser'
Global $sPassword = 'Secret pass'
    ; Create connection object
Local $oConnection = _ADO_Connection_Create()
    ; Open connection with $sConnectionString
_ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword)

Edited December 22, 2016 by Turranius

[mLipok]

Global $sServer = 'SEDBS154.domain.org\InstanceName'

 

[Turranius]

12 hours ago, mLipok said:

Global $sServer = 'SEDBS154.domain.org\InstanceName'

 

Sorry, I should have been more clear. Yes, I tried that. It does not understand that it seems.

 

###############################
ADO.au3 v.2.1.13 BETA (1026) : ==> COM Error intercepted !
$oADO_Error.description is:     [DBNETLIB][ConnectionOpen (Connect()).]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:     1026
$oADO_Error.source is:     Microsoft OLE DB Provider for SQL Server
$oADO_Error.helpfile is:     
$oADO_Error.helpcontext is:     0
###############################

[mLipok]

 

On 22.12.2016 at 1:45 PM, Turranius said:

I use this to connect to a MS SQL 2008 R2. Works fine from my machine when testing it.

Check firewall settings or other security setting on SQL server.

 

[mLipok]

For reference:

Conclusion: When you want to use MS ACCESS *.accdb files you should have instaled ODBC driver  "AccessDatabaseEngine.exe"