Can't connect to MySQL server - (Moved)

20Ice18 · June 14, 2023

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

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 · June 14, 2023

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 · June 14, 2023

Moved to the appropriate forum.

Moderation Team

20Ice18 · June 14, 2023

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, 2023 by 20Ice18

20Ice18 · June 14, 2023

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, 2023 by 20Ice18

mLipok · June 14, 2023

On 6/14/2023 at 9:27 AM, 20Ice18 said:
; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
Expand

show result from console.

mLipok · June 14, 2023

On 6/14/2023 at 9:04 AM, 20Ice18 said:

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

Expand

Did you download recent version?
ADO 2.1.19 BETA.zip

Edited June 14, 2023 by mLipok

20Ice18 · June 14, 2023

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 · June 14, 2023

On 6/14/2023 at 10:55 AM, 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

Expand

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, 2023 by 20Ice18

20Ice18 · June 14, 2023

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

20Ice18 · June 14, 2023

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, 2023 by 20Ice18

mLipok · June 14, 2023

are you still trying to use

Func _Example_ListProperties()

or the latest snippet is entire repro code ?

20Ice18 · June 15, 2023

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, 2023 by 20Ice18

mLipok · June 15, 2023

On 6/15/2023 at 5:56 AM, 20Ice18 said:

I'm trying to connect to MySQL database

Expand

this following part:

On 6/14/2023 at 1:05 PM, 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 & ';'

Expand

is wrong.

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

Edited June 15, 2023 by mLipok

20Ice18 · June 15, 2023

How do I fix it

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

Edited June 15, 2023 by 20Ice18

mLipok · June 15, 2023

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, 2023 by mLipok

mLipok · June 15, 2023

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 · June 15, 2023

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, 2023 by 20Ice18

mLipok · June 15, 2023

I suppose that you are still not showing entire console LOG

You should get more log from this:

On 6/15/2023 at 8:28 AM, 20Ice18 said:
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
Expand

20Ice18 · June 15, 2023

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, 2023 by 20Ice18

Can't connect to MySQL server - (Moved)

Recommended Posts

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

bogQ

Melba23

mLipok

Posted Images

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content