ADO.au3 UDF - BETA - Support Topic

mLipok · February 24, 2016

I want to present BETA Version of my ADO.au3 UDF.

This is modifed version of _sql.au3 UDF.

For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH

This is first public release , and still is as BETA

DOWNLOAD LINK (in download section):

Have fun,

mLipok

EDIT: 2016-06-03
Below some interesting topics about databases:

Spoiler

EDIT 2016/07/04:
For more info about ADO look here:
https://www.autoitscript.com/wiki/ADO

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.

Here is link to post which shows how "ODBC Data Source Administrator" looks like.

Edited January 26, 2021 by mLipok
EXAMPLE DATABASE

mLipok · February 24, 2016

First example:

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

Global $sDSN = 'PostgreSQL35W'
Global $sDatabase = ''
Global $sServer = ''
Global $sPort = ''
Global $sUser = ''
Global $sPassword = ''

; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_Example1()
_Example2()
_Example3()

Func _Example1()
    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $sTableName = 'SOME_TABLE_NAME'
    Local $sQUERY = 'Select * from ' & $sTableName
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    Local $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)

    Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordset_inner[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; CleanUp
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc    ;==>_Example1

Func _Example2()
    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $sTableName = 'SOME_TABLE_NAME'
    Local $sQUERY = 'Select * from ' & $sTableName
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; CleanUp
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    _ADO_REcordset_Display($aRecordset, $sTableName & '  - Recordset content')

EndFunc    ;==>_Example2

Func _Example3()
    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)

    ; CleanUp
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

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

REMARK: Not tested on PostgreSQL

Edited February 24, 2016 by mLipok
Added: _Example3(), Chagned: CleanUp

mLipok · February 24, 2016

As you can see many of the features is not fully described. However, the UDF is working and fully functional.
Recently, I found that I am making over and over again, so I decided to go public, in the hope of new concepts, or help in writing/documenting .....

REMARKS:
To be sure that function return success or failure you should check @error macro instead "Return Value".

Do not use Magic Numbers especially when you will check @error -- I mean You should use predefined enumerations:

Global Enum _
        $ADO_ERR_SUCCESS, _ ;               No Error
        $ADO_ERR_GENERAL, _ ;               General - some ADO Error - 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 Type
        $ADO_ERR_INVALIDOBJECTTYPE, _ ;     Function Parameters error - Expected/Required Object Type is returned in Return Value as string
        $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 - The Recordset is Empty
        $ADO_ERR_RECORDSETEMPTY, _ ;        The Recordset is Empty
        $ADO_ERR_NOCURRENTRECORD, _ ;       The Recordset has no current record
        $ADO_ERR_ENUMCOUNTER ;------------- just for testing

Global Enum _
        $ADO_EXT_DEFAULT, _ ;               default Extended Value
        $ADO_EXT_PARAM1, _ ;                Error Occurs in 1-Parameter
        $ADO_EXT_PARAM2, _ ;                Error Occurs in 2-Parameter
        $ADO_EXT_PARAM3, _ ;                Error Occurs in 3-Parameter
        $ADO_EXT_PARAM4, _ ;                Error Occurs in 4-Parameter
        $ADO_EXT_PARAM5, _ ;                Error Occurs in 5-Parameter
        $ADO_EXT_PARAM6, _ ;                Error Occurs in 6-Parameter
        $ADO_EXT_INTERNALFUNCTION, _ ;      Error Related to internal Function - should not happend - UDF Dev make something wrong ???
        $ADO_EXT_ENUMCOUNTER ;------------- just for testing

Global Enum _
        $ADO_RET_FAILURE = -1, _ ;          Failure result
        $ADO_RET_SUCCESS = 1, _ ;           Successful result
        $ADO_RET_ENUMCOUNTER ;------------- just for testing

Edited March 1, 2016 by mLipok

BrewManNH · February 24, 2016

You should declare all of your constants in the constants file, and not in the UDF itself, otherwise why have a separate file for only some of the constants?

mLipok · February 24, 2016

So far, I preferred to keep some enums in main UDF to have easy way to develop the function (Enums POPUP in SciTE).
Moreover, I had a tendency to change their names.
But currently this is a good point to move the to ADO_CONSTANTS.au3.

Edited February 24, 2016 by mLipok

mLipok · February 24, 2016

New version in download section.

Quote

   2016/02/24 '2.1.7 BETA'
   .   Changed: Function: _ADO_Recordset_ToArray: Parameter is now Optional: $bFieldNamesInFirstRow = False - mLipok
   .   Removed: Function: _ADO_ExecuteQueryToArray --> look in _ADO_Execute --> third parameter $bReturnAsArray - mLipok
   .   Changed: Enums and constants moved to: ADO_CONSTANTS.au3 - mLipok
   .       Thanks to @BrewManNH
   .   Changed: ADO_CONSTANTS.au3: New region: #Region ADO_CONSTANTS.au3 - ADO.au3 UDF Constants - mLipok
   .   Changed: ADO_CONSTANTS.au3: New region: #Region ADO_CONSTANTS.au3 - MSDN Enumerated Constants - mLipok
   .   New: Function: _ADO_UDFVersion() - mLipok
   .   Removed: Global Variable $__sSQL_UDFVersion -->> look for: _ADO_UDFVersion() - mLipok
   .   Added: New example: ADO_EXAMPLE__PostgreSQL.au3 - mLipok

Edited February 24, 2016 by mLipok

mLipok · February 25, 2016

New version in download section.

Quote

2016/02/26 '2.1.8 BETA'
.   Added: Function: _ADO_ConnectionString_Access - mLipok
.   Added: Function: _ADO_ConnectionString_Excel - mLipok
.   Removed: Function: _ADO_Connection_OpenAccess - mLipok
.       Look for: _ADO_Connection_OpenConString and _ADO_ConnectionString_Access
.   Changed: Example: ADO_EXAMPLE__PostgreSQL.au3 >> ADO_EXAMPLE.au3 - mLipok
.   ADO_EXAMPLE.au3: New Comments in script - mLipok
.   ADO_EXAMPLE.au3: New Function: _Example_MSAccess() - mLipok
.   ADO_EXAMPLE.au3: New Function: _Example_MSExcel() - mLipok
.   ADO_EXAMPLE.au3: New Function: _Example_MSSQL() - mLipok
.   ADO_EXAMPLE.au3: Renamed Function: _Example_PostgreSQL() - mLipok

mLipok · February 26, 2016

here:

I asked @faustf If he want to make a wrapper function to make this UDF easier.

So I make it easier (look in attachment for ADO_API.au3).

Here is example:

#include-once
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include "ADO_API.au3"

_Example()
Func _Example()

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'BaseName'
    Local $sServer = 'localhost\SQLExpress'
    Local $sUser = 'xxxxx'
    Local $sPassword = 'xxxxx'

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

    ; first you must SetUp Connection String
    _ADO_API_ConnectionString('DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';')

    ; now to execute query you should only do a "ONE LINER" to get ArrayOfArray :)
    Local $aResult_1 = _ADO_API('select * from [BaseName2].dbo.[Table21]')
    ; or to get standard array you can execute query with this "ONE LINER" :)
    Local $aResult_2 = _ADO_API2('select * from [BaseName3].dbo.[Table35]')

    ; if you want to show the result you should use ADO.au3 function
    _ADO_Recordset_Display($aResult_1,'$aResult_1')
    ; or normal _ArrayDisplay
    _ArrayDisplay($aResult_2,'Inner $aResult_2')

EndFunc

ADO_API.ZIP

mLipok · February 26, 2016

Here is better example:

#include-once
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include "ADO_API.au3"

_Setup()

_Main()

Func _Main()
    ; now to execute query you should only do a "ONE LINER" to get ArrayOfArray :)
    Local $aResult_1 = _ADO_API('select * from [BaseName2].dbo.[Table21]')
    ; or to get standard array you can execute query with this "ONE LINER" :)
    Local $aResult_2 = _ADO_API2('select * from [BaseName3].dbo.[Table35]')

    ; if you want to show the result you should use ADO.au3 function
    _ADO_Recordset_Display($aResult_1,'$aResult_1')
    ; or normal _ArrayDisplay
    _ArrayDisplay($aResult_2,'Inner $aResult_2')

EndFunc

Func _Setup()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'BaseName'
    Local $sServer = 'localhost\SQLExpress'
    Local $sUser = 'xxxxx'
    Local $sPassword = 'xxxxx'

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

    ; first you must SetUp Connection String
    _ADO_API_ConnectionString('DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';')

EndFunc

REMARK:

I think that this example is better because it shows that _Setup() is separated and has no direct relationship with any other functions.

Regards,

mLipok

faustf · February 26, 2016

o thankz so much but , i think try to build all , at made home , because i must mixed many record of different database

but when your software is in stable version and have help , i will use sure , i like so much your work

mLipok · February 26, 2016

Here is next example for: OpenSchema Method

;~ https://support.microsoft.com/en-us/kb/186246

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

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

_Example_MSSQL()

Func _Example_MSSQL()
    Local $oCOMERRHander = ObjEvent("AutoIt.Error", _ADO_COMErrorHandler)
    #forceref $oCOMERRHander

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'YourBASENAME'
    Local $sServer = 'localhost\SQLExpress'
    Local $sUser = 'YourUserName'
    Local $sPassword = 'YourPassword'

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

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

    Local $aSchema_Catalogs = _ADO_Schema_GetAllCatalogs($oConnection)
    _ADO_Recordset_Display($aSchema_Catalogs)

    Local $aCriteria_Table[4]
    Local $aCriteria_Column[4]

    $aCriteria_Table[3] = "TABLE"
    Local $oRecordset_tables = $oConnection.OpenSchema($ADO_adSchemaTables, $aCriteria_Table)
    _ADO_Recordset_Display($oRecordset_tables)


    Local $oRecordset_columns
    While Not $oRecordset_tables.EOF
        $aCriteria_Column[2] = $oRecordset_tables.Fields("TABLE_NAME").Value

        $oRecordset_columns = $oConnection.OpenSchema($ADO_adSchemaColumns, $aCriteria_Column)
        _ADO_Recordset_Display($oRecordset_columns, "List of column for table: " & $aCriteria_Column[2])
        $oRecordset_tables.MoveNext
    WEnd
    $oRecordset_tables.Close

    While Not $oRecordset_columns.EOF
        ConsoleWrite($oRecordset_columns.Fields("COLUMN_NAME").Value & @CRLF)
        $oRecordset_columns.MoveNext
    WEnd
    $oRecordset_columns.Close

EndFunc    ;==>_Example_MSSQL
#Region Common / internal


Func _ADO_Schema_GetAllCatalogs(ByRef $oConnection)
    Local $oRecordset_catalogs = $oConnection.OpenSchema($ADO_adSchemaCatalogs)
    Local $aSchema_Catalogs = _ADO_Recordset_ToArray($oRecordset_catalogs)
    $oRecordset_catalogs.Close
    $oRecordset_catalogs = Null

    Return $aSchema_Catalogs
EndFunc


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

Func _Example_4_MSSQL($sServer, $sDatabase, $sUser, $sPassword, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID')
    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_4_MSSQL
#EndRegion  Common / internal

Reference:

OpenSchema Method (ADO)
https://msdn.microsoft.com/en-us/library/ee275721(v=bts.10).aspx

SchemaEnum
https://msdn.microsoft.com/en-us/library/ms806250.aspx

How To Use the ADO OpenSchema Method in Visual Basic
https://support.microsoft.com/en-us/kb/186246

mLipok · February 26, 2016

I do not plan major changes in the UDF.
It is only necessary to improve the documentation (headers).
Checking the examples for other than MS SQL databases work correctly.

Adding further examples and support for other databases.

I look forward to the comments how and what can be changed or improved.
Even on the proposed amendments in Function Headers (documentation).

I also thinking about the concept of _ADO_API should be also attached as a standard component of the ADO.au3 UDF.

EDIT:
There is also a many work to do in internal function behavior - I mean validation of all parameters, as this is not already completed.

Edited February 26, 2016 by mLipok

mLipok · February 27, 2016

I want to ask if anyone sees benefits from the following Functions:

_SQL_FetchData
_SQL_FetchNames

As you can see I even not renamed them to _ADO_Fetch....

For me this two function are useless as I never used them.

Instead of this two function I always using:

_ADO_Recordset_ToArray
_ADO_RecordsetArray_GetFieldNames
_ADO_RecordsetArray_GetContent

What you think ?

I'm going to also remove the function:
_SQL_GetTableName

As I want to make other functions to OpenSchema methods.

mLipok · March 1, 2016

New version:

Quote

2016/03/01 '2.1.9 BETA'
.   ADO_CONSTANTS.au3: CleanUp: $ADO_adErr - mLipok
.   ADO_CONSTANTS.au3: CleanUp/Fixed: _ADO_ERROR_Description() - mLipok
.   Moved: Function: _ADO_ERROR_Description - From: ADO_CONSTANTS.au3 To: ADO.au3 - mLipok
.   Added: Function: _ADO_GetProvidersList - mLipok
.       Thanks to @water for wiki tutorial: https://www.autoitscript.com/wiki/ADO_Tools
.   Removed: Function: _SQL_FetchData - mLipok
.   Removed: Function: _SQL_FetchNames - mLipok
.   Changed: Function: _ADO_EVENTS_SetUp - Default is Disabled - mLipok
.   Refactored: Function: __ADO_RecordsetArray_Display - mLipok
.   ADO_EXAMPLE.au3: New Function: _Example_MySQL() - mLipok

Download in download section:

mLipok · March 1, 2016

New version:

Quote

2016/03/01 '2.1.10 BETA'
.   New: Function: _ADO_ConnectionString_MySQL() - mLipok
.   Added: in few function added COM Error Handler - mLipok
.   ADO_EXAMPLE.au3: Function: _Example_MySQL() - some change - mLipok

mLipok · March 8, 2016

New version:

Quote

2016/03/08 '2.1.11 BETA'
.   New: Function: _ADO_OpenSchema_Catalogs - mLipok
.   New: Function: _ADO_OpenSchema_Tables - mLipok
.   New: Function: _ADO_OpenSchema_Columns - mLipok
.   New: Function: _ADO_OpenSchema_Indexes - mLipok
.   New: Function: _ADO_OpenSchema_Views - mLipok
.   New: Function: _ADO_Schema_GetAllCatalogs - mLipok
.   New: Function: _ADO_Schema_GetAllTables - mLipok
.   New: Function: _ADO_Schema_GetAllViews - mLipok
.   Removed: Function: _SQL_GetTableName - mLipok
.   Removed: Function: _ADO_Connection_OpenExcel - mLipok
.       Look for: _ADO_Connection_OpenConString and _ADO_ConnectionString_Excel
.   Changed: ADO_EXAMPLE.au3 - _Example_MySQL() - mLipok
.   Changed: ADO_EXAMPLE.au3 - _Example_PostgreSQL() - mLipok
.   Renamed: Function: _ADO_Command >> _ADO_Command_Create - mLipok
.   Changed: Function: _ADO_Command_Create: Parameters removed - $sQuery - mLipok
.   New: Function: _ADO_Command_CreateParameter - mlipok
.   New: Function: _ADO_Command_Execute - mlipok   .
.   Added: ADO_EXAMPLE.au3 - _Example_MSSQL_COMMAND_StoredProcedure() - mLipok

Download link:

This new example showing how to create stored procedure on MS SQL, and how to get variable from SQL Query (TransactSQL) to AutoIt

Func _Example_MSSQL_COMMAND_StoredProcedure()
    Local $oCOMERRHander = ObjEvent("AutoIt.Error", _ADO_COMErrorHandler)
    #forceref $oCOMERRHander

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'YourBASENAME' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLExpress' ; change this string to YourServerLocation
    Local $sUser = 'AutoIt' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

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

    Local $oCommand = _ADO_Command_Create($oConnection, $ADO_adCmdStoredProc)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Create procedure (local temp procedure    )
    ; https://msdn.microsoft.com/pl-pl/library/ms188655(v=sql.110).aspx
    Local $sQUERY1 = _
            "CREATE PROCEDURE #testing1 @Param1 varchar(1), @Result1 varchar(1000) OUTPUT" & @CRLF & _
            "AS" & @CRLF & _
            "   SET @Param1 =1" & @CRLF & _
            "   SET @Result1 = 'Starting @@TRANCOUNT=' +  CAST(@@TRANCOUNT AS VARCHAR(10)) + CHAR(10)" & @CRLF & _
            "IF @Param1 = 1" & @CRLF & _
            "BEGIN" & @CRLF & _
            "   SET @Result1 = @Result1 + '@Param1 = ' + @Param1 + CHAR(10)" & @CRLF & _
            "END" & @CRLF & _
            ""
    _ADO_Execute($oConnection, $sQUERY1)

    _ADO_Command_CreateParameter($oCommand, '@Param1', 1, 1, $ADO_adChar, $ADO_adParamInput)
    If @error Then MsgBox($MB_ICONERROR, '@Param1', _
            '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    _ADO_Command_CreateParameter($oCommand, '@Result1', 1000, Default, $ADO_adChar, $ADO_adParamOutput)
    If @error Then MsgBox($MB_ICONERROR, '@Result1', _
            '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    Local $oParameters_coll = $oCommand.parameters
    If @error Then MsgBox($MB_ICONERROR, 'Parameters', '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    ; Enumerate parametrs to check if are properly added
    ; Local $iParam_count = $oParameters_coll.count
    For $oParameter In $oParameters_coll
        ConsoleWrite($oParameter.name & @CRLF)
    Next

    _ADO_Command_Execute($oCommand, "#testing1")

    MsgBox($MB_ICONINFORMATION, '@Result1', $oCommand.Parameters.Item("@Result1").Value)

EndFunc   ;==>_Example_MSSQL_COMMAND_StoredProcedure

mLipok · March 8, 2016

Example for MySQL {MySQL ODBC 5.3 ANSI Driver} and SSL :

Just now trying to use MySQL with remote server - I mean my WebHosting provider (He makes for me separate VirtualServer with MySQL and SSL certificates)
This is how I'm using connection string:

Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)
$sConnectionString &= "sslca=ca-cert.pem;sslcert=client-cert.pem;sslkey=client-key.pem;sslverify=1;Option=3;"

I begin to develop some CRM app for my own needs.

Edited March 8, 2016 by mLipok

mLipok · March 8, 2016

I made better example for ADO_API

Here is example:

#include-once
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include "ADO_API.au3"

_Setup()

_Main()

Func _Main()
    Example1()
    Example2()
EndFunc   ;==>_Main

Func _Setup()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'BaseName'
    Local $sServer = 'localhost\SQLExpress'
    Local $sUser = 'xxxxx'
    Local $sPassword = 'xxxxx'

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

    ; first you must SetUp Connection String
    _ADO_API_ConnectionString('DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';')

EndFunc   ;==>_Setup

Func Example1()
    ; now to execute query you should only do a "ONE LINER" to get ArrayOfArray :)
    Local $aResult_1 = _ADO_API('select * from [DataBaseName2].dbo.[SomeTableName]')

    ; if you want to show the result you should use ADO.au3 function
    _ADO_Recordset_Display($aResult_1, '$aResult_1')
EndFunc   ;==>Example1

Func Example2()
    ; to get standard array you can execute query with this "ONE LINER" :)
    Local $aResult_2 = _ADO_API2('select * from [DataBaseName3].dbo.[SomeOtherTableName]')

    ; if you want to show the result you should use ADO.au3 function
    ; or normal _ArrayDisplay
    _ArrayDisplay($aResult_2, 'Inner $aResult_2')
EndFunc   ;==>Example2

mLipok · March 8, 2016

new version:

2016/03/09 '2.1.12 BETA'
.   New: Enums: $ADO_ERR_ISCLOSEDOBJECT - mLipok
.   New: Function: __ADO_Connection_IsOpen - mLipok
.           __ADO_Connection_IsOpen is a wrapper for __ADO_Connection_IsValid  which also check for $oConnection.state and set $ADO_ERR_ISCLOSEDOBJECT
.           __ADO_Connection_IsOpen is now used in few functions which uses $oConnection
.   Changed: Function: __ADO_Recordset_IsNotEmpty - checking $oRecordset.state and return $ADO_ERR_ISCLOSEDOBJECT - mLipok
.   Changed: Function: _ADO_Command_Execute - mlipok
.           Now return recordset
.   Changed: ADO_EXAMPLE.au3 - _Example_MSSQL_COMMAND_StoredProcedure() - mLipok
.   Removed: Function: _ADO_Connection_OpenJet - mLipok
.           Look for: _ADO_Connection_OpenConString or _ADO_ConnectionString_Excel

Download link

faustf · March 9, 2016

hi @mLipok , i try to use your ADO i include in my new script ADO.au3 but when i run it i have this error in autoitconstant.au3

C:\Program Files\AutoIt3\Include\AutoItConstants.au3"(96,57) : error: $IDI_APPLICATION previously declared as a 'Const'.
Global Const $IDI_APPLICATION = 32512 ; Application icon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files\AutoIt3\Include\AutoItConstants.au3"(97,51) : error: $IDI_ASTERISK previously declared as a 'Const'.
Global Const $IDI_ASTERISK = 32516 ; Asterisk icon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files\AutoIt3\Include\AutoItConstants.au3"(98,63) : error: $IDI_EXCLAMATION previously declared as a 'Const'.
Global Const $IDI_EXCLAMATION = 32515 ; Exclamation point icon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files\AutoIt3\Include\AutoItConstants.au3"(99,48) : error: $IDI_HAND previously declared as a 'Const'.
Global Const $IDI_HAND = 32513 ; Stop sign icon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files\AutoIt3\Include\AutoItConstants.au3"(100,56) : error: $IDI_QUESTION previously declared as a 'Const'.
Global Const $IDI_QUESTION = 32514 ; Question-mark icon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files\AutoIt3\Include\AutoItConstants.au3"(101,84) : error: $IDI_WINLOGO previously declared as a 'Const'.
Global Const $IDI_WINLOGO = 32517 ; Windows logo icon. Windows XP: Application icon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"E:\_GESTIONALE_NEW\GEST_NEW.au3"(40,12) : error: _ConnectDB(): undefined function.
_ConnectDB()

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