ADO.au3 UDF - BETA - Support Topic

[mLipok]

Cześć == Hi.

If you are using MS SQL try this way:

Select * from [TEST]
Select * from [TEST 123]

or even:

Select * from [catalog].[dbo].[TEST]
Select * from [catalog].[dbo].[TEST 123]

In PostgreSQL you should be able to use double quotes in both following cases:

Select * from "TEST"
Select * from "TEST 123"

 

[Viszna]

In PostgreSQL syntax

Select * from "TEST"

result

###############################
ADO.au3 v.2.1.15 BETA (1286) : ==> COM Error intercepted !
$oADO_Error.description is:     B£¥D: relacja "TEST" nie istnieje;
Error while executing the query
$oADO_Error.windescription:     Wyst¹pi³ wyj¹tek.

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

The server PostgreSQL works like this

look results select in psql.exe 

bazaviszna=# select * from "TEST";
BŁĄD:  relacja "TEST" nie istnieje
LINIA 1: select * from "TEST";
                       ^

bazaviszna=# select * from TEST;
 id |                url                |        name         | description | rel
----+-----------------------------------+---------------------+-------------+-----
  1 | http://www.postgresqltutorial.com | PostgreSQL Tutorial |             |
(1 wiersz)


bazaviszna=# select * from "TEST 123";
    pesel    |                       nr_zam
-------------+----------------------------------------------------
 abc         | 1234
(1 wiersz)


bazaviszna=# select * from TEST 123;
BŁĄD:  błąd składni w lub blisko "123"
LINIA 1: select * from TEST 123;
                            ^

Solution
Remember to use " " in tablename 2 words and space
Remember to NOT use " " in tablename 1 word

[faustf]

hi guys  in particolar @mLipok , i have a little problem with Ado  last version  i have a  remote machin with db mysql  i try to connect by remote , with HeidiSQL  work and  i can see all db table change  etcc..  but  i tryed to connect with ado  and return me always  this error

###############################
ADO.au3 v.2.1.15 BETA (1100) : ==> COM Error intercepted !
$oADO_Error.description is:     [MySQL][ODBC 5.3(w) Driver]Access denied for user 'xxxxx'@'192.168.0.%' to database 'gestionale'
$oADO_Error.windescription:     Exception occurred.

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

my code

expandcollapse popup

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>
#include <WinAPIFiles.au3>
#include "ADO.au3"


Global $StatusBar1, $oRecordset, $oConnection, $sConnectionString, $aRecordsetContent
 
 


Global $sDriver = "MySQL ODBC 5.3 Unicode Driver"
Global $sDatabase = "gestionale"
Global $sServer = "192.168.0.103"
Global $sPort = "3306"
Global $sUser = "xxxxx"
Global $sPassword = "xxxx"

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

_ConnectDB_gest() ;  decomment for test connection

Func _ConnectDB_gest()

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

    ConsoleWrite($sDriver & "---" & $sServer & "---" & $sPort & "---" & $sDatabase & "---" & $sUser & "---" & $sPassword & @CRLF)
    ;_GUICtrlStatusBar_SetText($StatusBar1, "Connecting DB")
    ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 82))

    $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
        ;   _GUICtrlStatusBar_SetText($StatusBar1, "Offline DB")
        ;   _GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 80))
    Else

        ;_GUICtrlStatusBar_SetText($StatusBar1, "Online DB")
        ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 81))
    EndIf

EndFunc   ;==>_ConnectDB_gest

you have some suggest ??

thankz at all

[Skysnake]

First, confirm 32 or 64 bit ODBC, and how do you select that driver? 

 

My guess is that there is a problem with the username. It may be the way the connection string is scripted. Try wrapping the username in "double quotes". 

Try

ConsoleWrite( $sConnectionString & @CRLF)

What happens if you try to connect from the console/command line? 

Edited January 27, 2019 by Skysnake

[mLipok]

@Skysnake suggestion is good.

[faustf]

thankz, i been resolved a problem to connection , but  now  i not can display a  row  of table   , probably i do some error

expandcollapse popup

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>
#include <WinAPIFiles.au3>
#include "ADO.au3"


Global $StatusBar1, $oRecordset, $oConnection, $sConnectionString, $aRecordsetContent
 
 


Global $sDriver = "MySQL ODBC 5.3 Unicode Driver"
Global $sDatabase = "gestionale"
Global $sServer = "192.168.0.103"
Global $sPort = "3306"
Global $sUser = "xxxxx"
Global $sPassword = "xxxx"

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

_ConnectDB_gest() ;  decomment for test connection

Func _ConnectDB_gest()

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

    ConsoleWrite($sDriver & "---" & $sServer & "---" & $sPort & "---" & $sDatabase & "---" & $sUser & "---" & $sPassword & @CRLF)
    ;_GUICtrlStatusBar_SetText($StatusBar1, "Connecting DB")
    ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 82))

    $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
        ;   _GUICtrlStatusBar_SetText($StatusBar1, "Offline DB")
        ;   _GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 80))
    Else

        ;_GUICtrlStatusBar_SetText($StatusBar1, "Online DB")
        ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 81))
    EndIf

Local $SQLCode = "SELECT * FROM Anagrafica ORDER BY Ana_ID DESC LIMIT 1;"
Local $aRecordset = _ADO_Execute($oConnection, $SQLCode, True)
    ;_ArrayDisplay($aRecordset)
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
    _ADO_Recordset_Display($aRecordset, 'Recordset content')
    
EndFunc   ;==>_ConnectDB_gest

dont have  error but not  display nothing  o_O

[mLipok]

try this:

....
Local $aRecordset = _ADO_Execute($oConnection, $SQLCode, True)
If @error Then MsgBox($MB_ICONERROR, 'ERROR', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
....

 

Edited January 29, 2019 by mLipok

[faustf]

dont show nothing, in console scite not give error

Edited January 29, 2019 by faustf

[faustf]

hi @mLipok i try tyo resolve problem  of script , i sobstituted your  ado udf  with  classical  attach at odbc  , (in this mode  work)

i have 2 scritpi to include   one is  data of db like user pass and more , and second is udf for connect and much more ..  i publish follow

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.6.1
    Author:         faustf

    Script Function:
    Gestionale  per ufficio

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

#include <MySQL.au3>

Global $Nome ,  $TableContents , $SQLInstance , $SQLCode
 

Dim $UserName = "xxxxxxx"
Dim $Password = "xxxxxxxx"
Dim $Database = "azienda"
Dim $MySQLServerName = "192.168.0.102"

second

expandcollapse popup

#include-once

#cs
    Function Name:    _MySQLConnect
    Description:      Initiate a connection to a MySQL database.
    Parameter(s):     $username - The username to connect to the database with.
    $password - The password to connect to the database with. $Database - Database to connect to.
    $server - The server your database is on.
    $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}"
    Requirement(s):   Autoit 3 with COM support
    Return Value(s):  On success returns the connection object for subsequent functions. On failure returns 0 and sets @error
    @Error = 1
    Error opening connection
    @Error = 2
    MySQL ODBC Driver not installed.
    Author(s):        cdkid
#ce

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 5.3 Unicode Driver}", $iPort=3306)
    Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)
    If @error or $val = "" Then
        SetError(2)
        Return 0
    EndIf
    $ObjConn = ObjCreate("ADODB.Connection")
    $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)
    If @error Then
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc   ;==>_MySQLConnect

#cs
    Function name: _Query
    Description:     Send a query to the database
    Parameter(s):  $oConnectionObj - As returned by _MySQLConnect. $query - The query to execute
    Return Value(s):On success returns the query result. On failure returns 0 and sets @error to 1
    Requirement(s):Autoit3 with COM support
    Author(s):        cdid
#ce


Func _Query($oConnectionObj, $sQuery)
    If IsObj($oConnectionObj) Then
        Return $oConnectionobj.execute ($sQuery)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf

EndFunc   ;==>_Query

#cs
    Function name: _MySQLEnd
    Description:      Closes the database connection (see notes!)
    Parameter(s):   $oConnectionObj - The connection object as returned by _MySQLConnect()
    Requirement(s):Autoit 3 with COM support
    Return Value(s):On success returns 1. On failure returns 0 and sets @error to 1
    Author(s):         cdkid
#ce

Func _MySQLEnd($oConnectionObj)
    If IsObj($oConnectionObj) Then
        $oConnectionObj.close
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_MySQLEnd

#cs
    Function name: _AddRecord
    Description:     Adds a record to the specified table
    Note(s):           to add to multiple columns use an array with one blank element at the end as the $sColumn, and $value parameter
    Parameter(s):   $oConnectionObj - As returned by _MySQL Connect. $sTable - The table to put the record in
                    $row - The row to put the record in. $value - The value to put into the row
                    $vValue - OPTIONAL default will be default for the column (will not work with array, see notes)
    Requirement(s): Autoit 3 with COM support
    Return value(s): On success returns 1. If the connectionobj is not an object returns 0 and sets @error to 2. If there is any other error returns 0 and sets @error to 1.
    Author(s): cdkid
#ce

Func _AddRecord($oConnectionObj, $sTable, $vRow, $vValue = "")
    If IsObj($oConnectionObj) Then
        $query = "INSERT INTO " & $sTable & " ("

        If IsArray($vRow) Then
            For $i = 0 To UBound($vRow, 1) - 1
                If $i > 0 And $i <> UBound($vRow, 1) - 1 Then
                    $query = $query & "," & $vRow[$i] & ""
                ElseIf $i = UBound($vRow, 1) - 1 And $vRow[$i] <> "" Then
                    $query = $query & "," & $vRow[$i] & ") VALUES("
                ElseIf $i = 0 Then
                    $query = $query & "" & $vRow[$i] & ""
                ElseIf $vRow[$i] = "" Then
                    $query = $query & ") VALUES("
                EndIf
            Next
        EndIf
        If Not IsArray($vRow) And Not IsArray($vValue) And Not IsInt($vValue) Then
            $oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")
            return 1
        ElseIf IsInt($vValue) And Not IsArray($vRow) And Not IsArray($vValue) Then
            $oconnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES(" & $vValue & ")")
            return 1
        EndIf

        If IsArray($vValue) Then
            For $i = 0 To UBound($vValue, 1) - 1
                If $i > 0 And $i <> UBound($vValue, 1) - 1 And Not IsInt($vValue[$i]) Then
                    $query = $query & ",'" & $vValue[$i] & "'"
                ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" And Not IsInt($vValue[$i]) Then
                    $query = $query & ",'" & $vValue[$i] & "');"
                ElseIf $i = 0 And Not IsInt($vValue[$i]) Then
                    $query = $query & "'" & $vValue[$i] & "'"
                ElseIf $vValue[$i] = "" Then
                    $query = $query & ");"
                ElseIf IsInt($vValue[$i]) And $vValue[$i] <> "" Then
                    $query = $query & "," & $vValue[$i]
                EndIf
            Next
        EndIf
        If StringRight($query, 2) <> ");" Then
            $query = $query & ");"

        EndIf
        $oconnectionobj.execute ($query)
    EndIf
    If Not IsObj($oConnectionObj) Then
        SetError(2)
        Return 0
    EndIf
    If @error And IsObj($oConnectionObj) Then
        Return 0
        SetError(1)
    Else
        Return 1
    EndIf

EndFunc   ;==>_AddRecord


#cs
    Function name: _DeleteRecord
    Description:     Deletes a record from the specified table
    Parameter(s):  $oConnectionObj - As returned by _MySQLConnect. $sTable - The table to delete from.
    $sColumn - The column to check value (see the example in the next post) $vRecordVal -
    The value to check in $sColumn (see example).
    $iLimit (optional) - the max number of record to delete if multiple match the criteria (default 1)
    Return Value(s): On success returns 1. If there $oConnectionObj is not an object returns 0 and sets @error to 1. If there are any other errors returns 0 and sets @error to 2
    Requirement(s): Autoit 3 with COM support
#ce

Func _DeleteRecord ($oConnectionObj, $sTable, $sColumn, $vRecordVal, $iLimit = 1)
    If IsObj($oConnectionObj) And Not IsInt($vRecordVal) Then
        $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vRecordVal & "' LIMIT " & $iLimit & ";")
    ElseIf IsInt($vRecordVal) Then
        $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = " & $vRecordVal & " LIMIT " & $iLimit & ";")
        If Not @error Then
            Return 1
        ElseIf Not IsObj($oConnectionObj) Then
            SetError(1)
            Return 0
        ElseIf @error And IsObj($oConnectionObj) Then
            SetError(2)
            Return 0
        EndIf
    EndIf
EndFunc   ;==>_DeleteRecord

#cs
    Function name: _CreateTable()
    Description: Creates a table
    Parameters: $oConnectionObj - as returned by _MySQLConnect, $sTbl - The name of the table to create, $sPrimeKey - The name of the
    primary key column. $keytype - The datatype of the primary key (default is integer), $sNotNull - "yes" = must be filled out whenever
    a record is added "no" does not need to be filled out ("yes" default). $keyautoinc - "yes" = Auto incrememnts "no" = does not.
    $sType - The table type (default is InnoDB)
    Requirements: Autoit V3 with COM support
    Return value(s): on success returns 1 on failure sets @error to 1 and returns 0
    Author: cdkid
#ce

Func _CreateTable($oConnectionObj, $sTbl, $sPrimeKey, $keytype = "INTEGER", $sNotNull = "yes", $keyautoinc = "yes", $sType = "InnoDB")
    If IsObj($oConnectionObj) And Not @error Then
        $str = "CREATE TABLE " & $sTbl & " " & "(" & $sPrimeKey & " " & $keytype & " UNSIGNED"
        If $sNotNull = "yes" Then
            $str = $str & " NOT NULL"
        EndIf

        If $keyautoinc = "yes" Then
            $str = $str & " AUTO_INCREMENT,"
        EndIf

        $str = $str & " PRIMARY KEY (" & $sPrimeKey & " )" & " ) " & "TYPE = " & $sType & ";"
        $oConnectionObj.execute ($str)
        Return 1


    ElseIf @error Then
        Return 0
        SetError(1)
    EndIf

EndFunc   ;==>_CreateTable

#cs
    Function Name: _CreateColumn
    Description: Creates a column in the given table
    Requirements: AutoitV3 with COM support
    Parameters: $oConnectionObj - as returned by _MySQLConnect. $sTable - the name of the table to add the column to.
    $sAllowNull - if 'yes' then does not add 'NOT NULL' to the SQL statement (default 'yes') $sDataType - The data type of the column
    default('VARCHAR(45)').     $sAutoInc - if 'yes' adds 'AUTO_INCREMENT' to the MySQL Statement (for use with Integer types)
    default('no').      $sUnsigned - if 'yes' adds 'UNSIGNED' to the MySQL statement. default('no') $vDefault - the default value of the column
    default('')
    Author: cdkid
#ce

Func _CreateColumn($oConnectionObj, $sTable, $sColumn, $sAllowNull = "no", $sDataType = "VARCHAR(45)", $sAutoInc = "no", $sUnsigned = "no", $vDefault = '')
    If IsObj($oConnectionObj) And Not @error Then
        $str = "ALTER TABLE `" & $sTable & "` ADD COLUMN `" & $sColumn & "` " & $sDataType & " "
        If $sAllowNull = "yes" Then
            $str = $str & "NOT NULL "
        EndIf
        If $sAutoInc = 'yes' Then
            $str = $str & "AUTO_INCREMENT "
        EndIf
        If $sUnsigned = 'yes' Then
            $str = $str & "UNSIGNED "
        EndIf
        $str = $str & "DEFAULT '" & $vDefault & "';"
        $oConnectionObj.execute ($str)
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf

EndFunc   ;==>_CreateColumn

#cs
    Function Name: _DropCol()
    Description: Delete a column from the given table
    Requirements: AutoitV3 with COM support
    Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - The name of the table to delete the column from
    $sColumn - THe name of the column to delete
    Author: cdkid
#ce

Func _DropCol($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) & Not @error Then
        $oConnectionObj.execute ("ALTER TABLE " & $sTable & " DROP COLUMN " & $sColumn & ";")
        Return 1
    ElseIf @error Then
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_DropCol

#cs
    Function Name: _DropTbl()
    Description: Deletes a table from the database
    Requirements: AutoitV3 with COM support
    Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to delete
    Author: cdkid
#ce

Func _DropTbl($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) And Not @error Then
        $oConnectionObj.execute ("DROP TABLE " & $sTable & ";")
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_DropTbl


#cs
    Function name: _CountRecords()
    Description: Get the number of records in the specified column
    Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table that the column is in
    $value - If not = "" then it is put in the select statement in the WHERE clause (default "")
    Return value(s): On success returns the number of records. On failure sets @error to 1 and returns 0
    Author: cdkid
#ce
Func _CountRecords($oConnectionObj, $sTable, $sColumn, $vValue = '')
    If IsObj($oConnectionObj) And Not @error Then

        If $sColumn <> "" And $vValue <> "" And Not IsInt($vValue) Then
            $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vValue & "'"
        ElseIf $sColumn <> "" And $vValue = '' And Not IsInt($vValue) Then
            $constr = "SELECT " & $sColumn & " FROM " & $sTable
        ElseIf IsInt($vValue) And $sColumn <> '' And $vValue <> '' Then
            $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = " & $vValue
        EndIf
        $sql2 = ObjCreate("ADODB.Recordset")
        $sql2.cursorlocation = 3
        $sql2.open ($constr, $oConnectionObj)
        With $sql2
            $ret = .recordcount
        EndWith
        $sql2.close
        Return $ret
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_CountRecords

#cs
    Function name: _CountTables
    Description: Counts the number of tables in the database
    Parameter(s): $oConnectionObj - As returned by _MySQLConnect
    Return value(s): if error - returns 0 and sets @error to 1. on success returns the number of tables in the database
    Author: cdkid
#ce

Func _CountTables($oConnectionObj)
    If IsObj($oConnectionObj) Then
        $quer = $oConnectionObj.execute ("SHOW TABLES;")
        $i = 0
        With $quer
            While Not .EOF
                $i = $i + 1
                .MoveNext
            WEnd
        EndWith
        Return $i
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf

EndFunc   ;==>_CountTables

#cs
    Function name: _GetColNames
    Description: Get's the names of all columns in a specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to get the column names from
    Return values: On success returns an array where $array[0] is the number of elements in the array and all the rest are column names.
    On failure returns 0 and sets @error to 1
    Author: cdkid
#ce
Func _GetColNames($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) And Not @error Then
        Dim $ret[1], $rs

        $rs = $oConnectionObj.execute ("SHOW COLUMNS FROM " & $sTable & ";")

        With $rs
            While Not .EOF

                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = $rs.Fields (0).Value
                .MoveNext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
    If @error Then
        Return 0
        SetError(1)
    EndIf
EndFunc   ;==>_GetColNames


#cs
    Function name: _GetTblNames
    Description: Gets the names of all tables in the database
    Parameters: $oConnectionObj - As returned by _MySQLConnect
    Return value(s): On success returns an array where $array[0] is the number of tables and $array[n] is the nth table's name
    on failure - returns 0 and sets @error to 1
    Author: cdkid
#ce

Func _GetTblNames($oConnectionObj)
    If IsObj($oConnectionObj) Then
        Dim $ret[1]
        $quer = $oConnectionObj.execute ("SHOW TABLES;")
        With $quer
            While Not .eof
                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = .fields (0).value
                .movenext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
EndFunc   ;==>_GetTblNames

#cs
    Function name: _GetColVals
    Description: Gets all of the values of a specified column in a specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect(), $sTable - the table that the column is in
    $sColumn - the column to get values from.
    Return value(s): On success returns an array where $array[0] is the number of values and $array[n] is the Nth value
    On failure sets @error to 1 and returns 0
    Author: cdkid
#ce

Func _GetColVals($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) Then
        Dim $ret[1]
        $quer = $oConnectionObj.execute ("SELECT " & $sColumn & " FROM " & $sTable & ";")
        With $quer
            While Not .EOF
                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = .Fields (0).value
                .MoveNext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
EndFunc   ;==>_GetColVals

#cs
    Function name: _GetColCount
    Description: Gets the number of columns in the specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table to count the columns in
    Return Value(s): On success returns the number of columns in the table. On failure returns -1 and sets @error to 1
    Author: cdkid
#ce
Func _GetColCount($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) Then
        $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable)
        With $quer
            $i = 0
            While Not .eof
                $i = $i + 1
                .movenext
            WEnd
        EndWith
        Return $i
    EndIf
    If @error Then
        Return -1
        SetError(1)
    EndIf

EndFunc   ;==>_GetColCount

#cs
    Function name: _GetColType
    Description: Gets the DATA TYPE of the specified column
    Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table that the column is in. $sColumn - the column
    to retrieve the data type from.
    Return value(s): On success returns the data type of the column. On failure returns 0 and sets @error to 1
    Author: cdkid
#ce
Func _GetColType($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) Then
        $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable)
        With $quer
            $i = 0
            While Not .eof
                If .fields (0).value = $sColumn Then
                    $ret = .fields (1).value
                EndIf
                .MoveNext
            WEnd
        EndWith
        Return $ret
    EndIf
    If @error Then
        Return 0
        SetError(1)
    EndIf
EndFunc   ;==>_GetColType

#cs
    Function: _GetDBNames
    Description: Get a count and list of all databases on current server.
    Parameters: $oConObj - As returned by _MySQLConnect
    Return Value(s): Success - An array where $array[0] is the number of databases and $array[n] is the nth database name.
    Failure - -1 and sets @error to 1
    Author: cdkid
#ce
Func _GetDBNames($conobj)
    If IsObj($conobj) Then
        Local $arr[1], $m
        $m = $conobj.Execute ("SHOW DATABASES;")
        With $m
            While Not .eof
                ReDim $arr[UBound($arr, 1) + 1]
                $arr[UBound($arr, 1) - 1] = .Fields (0).Value
                .MoveNext
            WEnd
        EndWith
        $arr[0] = UBound($arr, 1) - 1
        Return $arr
    Else
        SetError(1)
        Return -1
    EndIf
EndFunc   ;==>_GetDBNames

#cs
    Function: _ChangeCon
    Description: Change your connection string
    Parameters:
    $oConnectionObj
    As returned by _MySQLConnect
    $username
    OPTIONAL: the new username to use
    If omitted, the same username will be used.
    $password
    OPTIONAL: the new password to use
    If omitted, the same password will be used.
    $database
    OPTIONAL: the new database to connect to
    If omitted, the same database will be used.
    $driver
    OPTIONAL: the new driver to use
    If omitted, the MySQL ODBC 3.51 DRIVER will be used.
    $server
    OPTIONAL: the new server to connect to
    If omitted, the same server will be used.
    $iPort
    OPTIONAL: the new port to be used to connect
    if omitted, the default port (3306) will be used
    Return Value:
    On success, a new connection object for use with subsequent functions.
    On failure, -1 and sets @error to 1
    Author: cdkid
#ce

Func _ChangeCon($oConnectionObj, $username = "", $password = "", $database = "", $driver = "", $server = "", $iPort = 0)
    Local $constr, $db, $usn, $pwd, $svr
    If IsObj($oConnectionObj) Then
        $constr = $oConnectionObj.connectionstring
        $constr = StringReplace($constr, 'Provider=MSDASQL.1;Extended Properties="', '')
        $constr = StringSplit($constr, ";")
        For $i = 1 To $constr[0]
            If StringLeft($constr[$i], 3) = "UID" Then
                If $username <> "" Then
                    $usn = $username
                Else
                    $usn = StringMid($constr[$i], 5)
                EndIf
                $usn = StringTrimRight($usn, 1)
            EndIf
            If StringLeft($constr[$i], 3) = "PWD" Then
                If $password <> "" Then
                    $pwd = $password
                Else
                    $pwd = StringMid($constr[$i], 5)
                EndIf
            EndIf
            If StringLeft($constr[$i], 8) = "DATABASE" Then
                If $database <> "" Then
                    $db = $database
                Else
                    $db = StringMid($constr[$i], 10)
                EndIf
            EndIf
            If StringLeft($constr[$i], 6) = "SERVER" Then
                If $server <> "" Then
                    $svr = $server
                Else
                    $svr = StringMid($constr[$i], 8)
                EndIf
            EndIf
            If StringLeft($constr[$i], 6) = "DRIVER" Then
                If $driver <> "" Then
                    $dvr = $driver
                Else
                    $dvr = "{MySQL ODBC 3.51 DRIVER}"
                EndIf
            EndIf
            If StringLeft($constr[$i], 4) = "PORT" Then
                if $iport <> 0 Then
                    $port = $iport
                Else
                    $port = 3306
                EndIf
            EndIf
        Next
        $oConnectionObj.close
        $oConnectionObj.Open ("DATABASE=" & $db & ";DRIVER=" & $dvr & ";UID=" & $usn & ";PWD=" & $pwd & ";SERVER=" & $svr & ";PORT=" & $port & ";")
        Return $oConnectionObj
    Else
        SetError(1)
        Return -1
    EndIf
EndFunc   ;==>_ChangeCon

i substitute your code   with   this

(in  top  insert  a new include  of  2 script  that i published)

your code 

#cs
        Local $SQLCode = "SELECT * FROM Anagrafica ORDER BY Ana_ID DESC LIMIT 1;"
        Local $aRecordset = _ADO_Execute($oConnection, $SQLCode, True)
        If @error Then MsgBox($MB_ICONERROR, 'ERROR', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
        _ADO_Recordset_Display($aRecordset, 'Recordset content')
        _ADO_Connection_Close($oConnection)
        $oConnection = Null
    #ce
new code 


Dim $TableName = "Anagrafica"
    ; azzeramento ID list server  per  verificare  anche dopo un inserimento
    $IDList = ""
    ; azzeramento ID list

    $SQLInstance = _MySQLConnect($UserName, $Password, $Database, $MySQLServerName)
    $SQLCode = "SELECT * FROM Anagrafica ORDER BY Ana_ID DESC LIMIT 1"
    $TableContents = _Query($SQLInstance, $SQLCode)
    With $TableContents
        While Not .EOF
            $IDList &= .Fields("Ana_ID").value
            .MoveNext
        WEnd
    EndWith
    _MySQLEnd($SQLInstance)

    MsgBox(0, '78', $IDList)

 

why with new code work ?  you have some idea??   thankz

 

 

Edited January 30, 2019 by faustf

[faustf]

hi @mLipok i try to resolve a problem  with your  ADO  i do this test ,

in ADO.au3   in function  recordset to array

expandcollapse popup

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ; Error handler, automatic cleanup at end of function
    Local $oADO_COM_ErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_InternalFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COM_ErrorHandler

    __ADO_Recordset_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; save current Recordset rows position to $oRecordset_Bookmark
    Local $oRecordset_Bookmark = Null
    If $oRecordset.Supports($ADO_adBookmark) Then $oRecordset_Bookmark = $oRecordset.Bookmark

    $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
    _ArrayDisplay($aRecordset_GetRowsResult)
    If @error Then ; Trap COM error, report and return
        Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE)
    ElseIf UBound($aRecordset_GetRowsResult) Then
        Local $aResult[0]

        ; Restore Recordset row position from stored $oRecordset_Bookmark
        If $oRecordset_Bookmark = Null Then
            $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf

        Local $iColumns_count = UBound($aRecordset_GetRowsResult, $UBOUND_COLUMNS)
        Local $iRows_count = UBound($aRecordset_GetRowsResult)

        If $bFieldNamesInFirstRow Then
            ; Adjust the array to fit the column names and move all data down 1 row
            ReDim $aRecordset_GetRowsResult[$iRows_count + 1][$iColumns_count]

            ; Move all records down
            For $iRow_idx = $iRows_count To 1 Step -1
                For $y = 0 To $iColumns_count - 1
                    $aRecordset_GetRowsResult[$iRow_idx][$y] = $aRecordset_GetRowsResult[$iRow_idx - 1][$y]
                Next
            Next

            ; Add the coloumn names
            For $iCol_idx = 0 To $iColumns_count - 1 ;get the column names and put into 0 array element
                $aRecordset_GetRowsResult[0][$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult = $aRecordset_GetRowsResult

            Return SetError($ADO_ERR_SUCCESS, $iRows_count + 1, $aResult)
        Else

            ReDim $aResult[$ADO_RS_ARRAY_ENUMCOUNTR]
            Local $aFiledNames_Temp[$iColumns_count]

            For $iCol_idx = 0 To $iColumns_count - 1 ;get the column names and put into 0 array element
                $aFiledNames_Temp[$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
            $aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
            $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult
            Return SetError($ADO_ERR_SUCCESS, $iRows_count, $aResult)
        EndIf
    EndIf

    Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
EndFunc   ;==>_ADO_Recordset_ToArray

_ArrayDisplay($aRecordset_GetRowsResult) 

and return an array with my data  table 

but if i  insert an

_ArrayDisplay($aResult)

return  me an array blank with 3 raw

i hope will be help  , thankz  again

[faustf]

hi @mLipok  i have ia little  answer ,  why you use in ADO.au3  in line 554   in function ado_rECORDSET_to array , this code???

    $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult

when i _arraydisplay ($aResult) i have  record {ARRAY},  why you not  return  directly  $aRecordset_GetRowsResult ?

 

[mLipok]

Take a loo for:

_ADO_RecordsetArray_GetContent()

This is intentional feature to keep 

$aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
$aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
$aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult

 

As this is much more faster to use only $oRecordset.GetRows with $bFieldNamesInFirstRow = False

 

[faustf]

but  if i use  ADO_Recordset_Display   what  i could  see  ???   a popup with  array  full of data ?? or  write in console ??

i dont  understund  how return a data

i try to  run examle but return me error

Edited January 30, 2019 by faustf

[mLipok]

9 minutes ago, faustf said:

what  i could  see  ???   ...........YES.... a popup with  array  full of data ??

 

[faustf]

other questions , 

how pass a data  _ADO_Recordset_ToArray   to   ADO_Recordset_Display    ,  if  you dont  use a return ???

and  why if  i  display this  array

$aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult

_arraydisplay($aResult)

why return 2  the variable   $ADO_RS_ARRAY_RSCONTENT  you declare in ado constant  , and  in function you never assign  number ? o_O

[faustf]

other  questions @mLipok

return me  this error 

 : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)

in  function __ADO_RecordsetArray_Display 

if i comment ;  arraydisplay  go   but not  display nothing

whats wrong ?

 

 

[faustf]

i birth  i  remove   a "" because  arraydisplay  only 7  options and  your array display  have  8   becuase  a new version of autoit  change debug arraydisplay and arraydisplay  probably

(but  we really felt the need for debuggarraydisplay ?? )

upgrade

thankz again

[faustf]

anotheer little bit  questions in script for return my table data  in array i use  this  code

Local $aSelect = _ADO_RecordsetArray_GetContent($aRecordset)

is correct because  when i do _arraydisplay ($aSelect)  , work good

but if i do  a classical for

$Combo1 = GUICtrlCreateCombo("------", 153, 58, 145, 25, BitOR($CBS_DROPDOWN, $CBS_AUTOHSCROLL))
        For $i = 0 To UBound($aSelect) - 1
            GUICtrlSetData($Combo1, $aSelect[$i])
        Next

return me this error

 : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
GUICtrlSetData($Combo1, $aSelect[$i])

is a array rfeturned not correct ??  for your experience what do you think ?

thankz

[faustf]

hi @mLipok suggestion for ADO 

if possible  simplify  little bit  like   this

expandcollapse popup

Local $aLReturnSQL = _DB_Sql_Execute("SELECT Ana_Nome FROM Anagrafica;", 1)


    _ArrayDisplay($aLReturnSQL)
     
        For $i = 0 To UBound($aLReturnSQL) - 1
            GUICtrlSetData($Combo1, $aLReturnSQL[$i])
        Next
        








Func _DB_Sql_Execute($SQLCode, $Close)

    Local $aRecordset = _ADO_Execute($oConnection, $SQLCode) ; $oConnection global variable
    If @error Then
        MsgBox($MB_ICONERROR, 'ERROR', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
    Else
        Local $aRecordsetArray = _ADO_Recordset_ToArray($aRecordset, False)
        Local $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)
        If $Close = 1 Then
            _ADO_Connection_Close($oConnection)
            $oConnection = Null
        EndIf
        If UBound($aRecordset_inner, $UBOUND_COLUMNS) = 1 Then
            Local $aLRetData[UBound($aRecordset_inner)]
            Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
            For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
                For $iColumn_idx = 0 To $iColumn_count - 1
                    $aLRetData[$iRecord_idx] = $aRecordset_inner[$iRecord_idx][$iColumn_idx]
                Next
            Next
            Return $aLRetData
        Else
            Local $aLRetData[UBound($aRecordset_inner)]
            Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
            For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
                For $iColumn_idx = 0 To $iColumn_count - 1
                    ConsoleWrite($aRecordset_inner[$iRecord_idx][$iColumn_idx])
                Next
            Next
            Return $aRecordset_inner
        EndIf
    EndIf
EndFunc   ;==>_DB_Sql_Execute

the  func _DB_Sql_Execute ......   insert in ADO.au3 , i think is better 

i hope have give you a idea  thankz again for ADO  bye

 

[Skysnake]

@faustf I use it like this:

$aResult = _ADO_Execute($g_DB, $query, True, True)     ; select, expect table
        If @error <> $ADO_ERR_SUCCESS And @error <= $ADO_ERR_RECORDSETEMPTY Then
            ; a serious error
            ;ConsoleWrite("7.1 a serious error " & @error  & _ADO_UDFVersion() & @CRLF)
        Else
            ; includes SQL blank result set
            ; process the acquired data
            ;ConsoleWrite("Proceed " & @error & @CRLF)
        EndIf
        ; now populate gui :)
        Local $valdb2gui = $aResult[1][1] ;;; :)

Local $valdb2gui = $aResult[1][1] ; ==> use a For Loop to extract all the required data

Where the $aResult represents an Array returned (table). Note that this could be a blank array!

 

I posted elsewhere code on how I use the ADO for INSERT UPDATE DELETE commands, which do not expect a table returned, and this format for SELECTs that expect a table to be returned, note the two params TRUE TRUE

$aResult = _ADO_Execute($g_DB, $query, True, True)     ; select, expect table

 

Here is the code not expecting a table:

If Not _ADO_Execute($g_DB, $sSQL) = $ADO_ERR_SUCCESS Then     ; insert, does not expect a return
                    ;;;ConsoleWrite("An error occurred, please try again later?" & $ADO_ERR_SUCCESS & @CRLF)
                    MsgBox(0, "Error New Account", "An error occurred, please try again later?")     
                Else
                    ;;;ConsoleWrite("0. $ADO_ERR_SUCCESS Success " & $ADO_ERR_SUCCESS & @CRLF)
                EndIf

Note the absence of the TWO TRUE params

 

Edited February 2, 2019 by Skysnake