Can't connect to MySQL server - (Moved)

[20Ice18]

Hello,
I am facing difficulties connecting to SQL Server using AutoIt and would greatly appreciate some help. I have already tried using the MySQL UDF (User Defined Function) and the latest ODBC connect driver from the provided link here

However, I have been unable to establish a successful connection.
I have come across posts about this topic, but most of them are over 10 years old, and I suspect they might be outdated.
I even attempted to change the driver name in the https://www.autoitscript.com/forum/applications/core/interface/file/attachment.php?id=15889 UDF from "MySQL ODBC 3.51 Driver" to the latest driver I obtained, but I still haven't been able to connect.
If anyone has any guidance, suggestions, or updated information on connecting to SQL Server using AutoIt, I would be grateful for your assistance.
Thank you in advance for your help!
Best regards,

Here is a code im trying to connect with...
 

expandcollapse popup

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

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 3.51 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


; Example usage
Local $host = "host"
Local $user = "user"
Local $password = "password"
Local $database = "database"

Local $conn = _MySQLConnect($host, $user, $password, $database)
If $conn Then
    MsgBox(64, "Success", "Connected to MySQL database successfully!")
    ; Disconnect from the MySQL server
    _MySQLEnd($conn)
else 
    MsgBox(64, "error ", "connection failed")
    
EndIf

 

[20Ice18]

Additionally, if possible, I would prefer an alternative method to connect to the SQL Server without relying on a specific driver that needs to be downloaded separately. Since this functionality is intended to be part of a larger script, I am aiming for a seamless integration where I can directly send the relevant information about the script's execution to the database. If there are any suggestions or examples of other methods or libraries that can accomplish this, I would greatly appreciate your insights. Thank you once again for your assistance!

[Melba23]

Moved to the appropriate forum.

Moderation Team

[20Ice18]

After encountering connection issues, I later found the ADO.au3 UDF https://www.autoitscript.com/forum/files/file/389-adoau3-udf. However, even after using the UDF, I faced the same error message: "The requested action with $oConnection.Open($sConnectionString) object has failed.

I can't find out what I do wrong
 

#include "ADO.au3"
; Example usage
Local $sServer = "my_server"
Local $sUsername = "my_username"
Local $sPassword = "my_password"
Local $sDatabase = "my_database"



; Connect to the database
Local $oConnection = _ADO_Connection_Create()
Local $sConnectionString = "DRIVER={SQL Server};SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";"
_ADO_Connection_OpenConString($oConnection, $sConnectionString)

If @error Then
    MsgBox($MB_ICONERROR, "Error", "Failed to connect to the database!")
Else
    MsgBox($MB_ICONINFORMATION, "Success", "Connected to the database successfully!")
    ; Perform database operations here...

    ; Disconnect from the database
    _ADO_Connection_Close($oConnection)
    _ADO_Connection_Destroy($oConnection)

    MsgBox($MB_ICONINFORMATION, "Disconnected", "Disconnected from the database.")
EndIf

 

Edited June 14 by 20Ice18

[20Ice18]

Later, I encountered the same error again, even after I copied and pasted the code from Example2, mentioning @mLipok. The error message I'm still receiving is "The requested action with $oConnection.Open($sConnectionString) object has failed."

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

Global $sDSN = 'PostgreSQL35W'
Global $sDatabase = 'my_database'
Global $sServer = 'my_server'
Global $sPort = 'my_port'
Global $sUser = 'my_username'
Global $sPassword = 'my_password'

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


_Example2()

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 = 'Computers'
    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

Please let me know what you think, even if its just that my given credentials are wrong. Although I think they are right. 

Edited June 14 by 20Ice18

[mLipok]

1 hour ago, 20Ice18 said:

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

show result from console.

[mLipok]

1 hour ago, 20Ice18 said:

I later found the ADO.au3 UDF https://www.autoitscript.com/forum/files/file/389-adoau3-udf.

Did you download recent version?
ADO 2.1.19 BETA.zip

Edited June 14 by mLipok

[20Ice18]

Thank you for your reply!
You were right, I didn't download the latest version before, but I have done so now. However, I encountered an error while running the code again .

 ==> Cannot assign values to constants.:
Global Enum $ADO_ERR_SUCCESS, $ADO_ERR_GENERAL, $ADO_ERR_COMERROR, $ADO_ERR_COMHANDLER, $ADO_ERR_CONNECTION, $ADO_ERR_ISNOTOBJECT, $ADO_ERR_INVALIDOBJECTTYPE, $ADO_ERR_INVALIDPARAMETERTYPE, $ADO_ERR_INVALIDPARAMETERVALUE, $ADO_ERR_INVALIDARRAY, $ADO_ERR_RECORDSETEMPTY, $ADO_ERR_NOCURRENTRECORD, $ADO_ERR_ENUMCOUNTER
Global Enum ^ ERROR

 

#include "path\ADO.au3"
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDataBase = 'my_database'
Global $sServer = 'my_server'
Global $sPort = 'my_port'
Global $sUserName = 'my_username'
Global $sPassword = 'my_password'
Global $sDriver = 'SQL Server'


_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)
_Example_ListProperties()

Func _Example_ListProperties()
    Local $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the connection object!")

    
    $oADOConnection.open("DRIVER={" & $sDriver & "};SERVER=" & $sServer & ";DATABASE=" & $sDataBase & ";uid=" & $sUserName & ";pwd=" & $sPassword & ";APP=" & @ScriptName & ";")
    

    ; Open the connection
    Local $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
    $oADOConnection.Open($sADOConnectionString)
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")

    Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)

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

EndFunc    ;==>_Example_ListProperties

 

[20Ice18]

2 hours ago, 20Ice18 said:

Thank you for your reply!
You were right, I didn't download the latest version before, but I have done so now. However, I encountered an error while running the code again .

 ==> Cannot assign values to constants.:
Global Enum $ADO_ERR_SUCCESS, $ADO_ERR_GENERAL, $ADO_ERR_COMERROR, $ADO_ERR_COMHANDLER, $ADO_ERR_CONNECTION, $ADO_ERR_ISNOTOBJECT, $ADO_ERR_INVALIDOBJECTTYPE, $ADO_ERR_INVALIDPARAMETERTYPE, $ADO_ERR_INVALIDPARAMETERVALUE, $ADO_ERR_INVALIDARRAY, $ADO_ERR_RECORDSETEMPTY, $ADO_ERR_NOCURRENTRECORD, $ADO_ERR_ENUMCOUNTER
Global Enum ^ ERROR

 

#include "path\ADO.au3"
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDataBase = 'my_database'
Global $sServer = 'my_server'
Global $sPort = 'my_port'
Global $sUserName = 'my_username'
Global $sPassword = 'my_password'
Global $sDriver = 'SQL Server'


_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)
_Example_ListProperties()

Func _Example_ListProperties()
    Local $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the connection object!")

    
    $oADOConnection.open("DRIVER={" & $sDriver & "};SERVER=" & $sServer & ";DATABASE=" & $sDataBase & ";uid=" & $sUserName & ";pwd=" & $sPassword & ";APP=" & @ScriptName & ";")
    

    ; Open the connection
    Local $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
    $oADOConnection.Open($sADOConnectionString)
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")

    Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)

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

EndFunc    ;==>_Example_ListProperties

 

I decided to delete the duplicated definitions. Although I'm not entirely sure if this was the correct approach, it did eliminate the initial error. Despite resolving the Global Enum issue, I am unable to establish a connection to the database. I've double-checked the connection details, such as the server address, username, password, and database name, and they appear to be accurate and I  also do have {SQL Server} driver installed in ODBC Data Sources.
 
console output is this :

==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

which is the exact same one I got with with older version of ADO or other UDFs

The database that I'm trying to connect to is not entirely mine, I'm just working with credentials that were given to me so if you think that reason of this error is somewhere else, please let me know. 


 

Edited June 14 by 20Ice18

[20Ice18]

I think I might just  be lost in usage of functions.

[20Ice18]

Run Example2() again 

 

#include "path\ADO.au3"
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDatabase = 'my_database'
Global $sServer = 'mt_server'
Global $sPort = 'my_port'
Global $sUser = 'my_user'
Global $sPassword = 'my_password'
Global $sDriver = 'SQL Server'
Global $sDSN = 'PostgreSQL35W'

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
_Example2()

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 = 'Computers'
    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

and got this error  in console 
 

==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

 

Edited June 14 by 20Ice18

[mLipok]

are you still trying to use 

Func _Example_ListProperties()

or the latest snippet is entire repro code ?

[20Ice18]

Func _Example_ListProperties() end up working. 

latest snipped is the example from the link in your signature 

I'm trying to  connect to MySQL database write values to the columns in tables and  then disconnect, but there is always error while connecting. I'm going to try again. 

Edited June 15 by 20Ice18

[mLipok]

1 hour ago, 20Ice18 said:

I'm trying to  connect to MySQL database

 this following part:

18 hours ago, 20Ice18 said:

Global $sDatabase = 'my_database'
Global $sServer = 'mt_server'
Global $sPort = 'my_port'
Global $sUser = 'my_user'
Global $sPassword = 'my_password'
Global $sDriver = 'SQL Server'
Global $sDSN = 'PostgreSQL35W'

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
_Example2()

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

is wrong.

EDTI:
You wanted connect to MySQL but you are using connection string for mixed PostgreSQL+MS SQL Server

Edited June 15 by mLipok

[20Ice18]

How do I fix it 

is it the wrong $sConnectionString or $sDriver or $sDSN

Edited June 15 by 20Ice18

[mLipok]

https://www.connectionstrings.com/mysql/

Specifying TCP port

Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

 

EDIT:

So the solution should look like:

 

Func _Example2()
    ; https://www.connectionstrings.com/mysql/
    ; Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    Local $sConnectionString = 'Server=' & $sServer & ';Port=' & $sPort & ';Database=' & $sDatabase & ';Uid=' & $sUser & ';Pwd=' & $sPassword & ';'

Edited June 15 by mLipok

[mLipok]

You can also specify Driver:

https://www.connectionstrings.com/mysql-connector-odbc-5-2/

Specifying TCP/IP port

Driver={MySQL ODBC 5.2 ANSI Driver};Server=myServerAddress;Port=3306;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;

[20Ice18]

Thank youu, I fixed that but I'm still getting the same error either with specified driver 

 

;includes...
;variables...

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
_Example2()

Func _Example2()
    Local $sConnectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" & "Server=" & $sServer & "; Port=3306;" & "Database=" & $sDatabase & "; Uid=" & $sUsername & "; Pwd=" & $sPassword & "; Option=3;"


    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then 
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Else 
        MsgBox(1,  ":D",  "working")
    EndIf

    ; CleanUp
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

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

EndFunc    ;==>_Example2

 or without 
 

;includes...
;variables...

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
_Example2()

Func _Example2()
    Local $sConnectionString = "Server=" & $sServer  & "; Database=" & $sDatabase & "; Uid=" & $sUsername & "; Pwd=" & $sPassword & ";"


    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then 
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Else 
        MsgBox(1,  ":D",  "working")
    EndIf

    ; CleanUp
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

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

EndFunc    ;==>_Example2

this in console 

 

==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

 

Edited June 15 by 20Ice18

[mLipok]

I suppose that you are still not showing entire console LOG

 

You should get more log from this:

8 minutes ago, 20Ice18 said:

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

 

[20Ice18]

this was all I got in console..., I'm using ISN Autoit Studio as an environment instead of Scite script editor, could that be a problem? 

Starting file temprun.au3...

ADO.au3 (731): ==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

temprun.au3 -> Exit Code: 1 (Runtime: 2.82 sec)

this was the console in Scite Editor if it helps 

ADO.au3"(163,90) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
Array.au3"(486,180) : REF: definition of _ArrayDisplay().
Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iDesired_Colwidth = Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
ADO.au3"(170,91) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aRocordset, $sTitle, "", 0, Default, Default, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
Array.au3"(486,180) : REF: definition of _ArrayDisplay().
Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iDesired_Colwidth = Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
temp.au3 - 2 error(s), 0 warning(s)
!>10:50:59 AU3Check ended. Press F4 to jump to next error.rc:2
+>10:50:59 AutoIt3Wrapper Finished.
>Exit code: 2    Time: 1.182

 

Edited June 15 by 20Ice18