MySQL UDFs

[sanjsimi17]

Drivers installed

[mLipok]

Try to use my ADO.au3 UDF - look in my signature.

[sanjsimi17]

I try but no success with it.

[mLipok]

Did you ask in ADO support topic ?

 

[Skysnake]

@sanjsimi17, like @mLipok suggests, use the ADO UDF and post the Console output in that thread.  Do you have a ODBC drive active?

Skysnake

[RonildoDSouza]

Add this function...

 

expandcollapse popup

#cs
    Function Name: _MySQLUpdate
    Description: Send a command DML (Data Manipulation Language) to the database.
    
Parameter(s): 
$oConnectionObj - As returned by _MySQLConnect.
    $sTable - The table to the update.
    $aFieldsAndValues - A array of fields and values (delimiter for pipe |) to the update.
    ---- Example.: Local $arr[] = ["field1|value1", "field2|value2", n...]
    $sWhereArgs - The args of clause where in the command DML.

    Requirement(s): Autoit 3 with COM support
    Author(s): ronildo
#ce
Func _MySQLUpdate($oConnectionObj, $sTable, $aFieldsAndValues, $sWhereArgs)
  
    Local $sSet = ""
  
    For $i = 0 To (UBound($aFieldsAndValues) - 1) Step +1
        $aResult = StringSplit($aFieldsAndValues[$i], "|", $STR_NOCOUNT)
        $sSet &= $aResult[0] & " = " & $aResult[1]
  
        If ($i < (UBound($aFieldsAndValues) - 1)) Then
            $sSet &= ", "
        EndIf
    Next
  
    If (StringStripWS($sWhereArgs, $STR_STRIPLEADING + $STR_STRIPTRAILING + $STR_STRIPSPACES) <> "") Then
        $sUpdate = "UPDATE " & $sTable _
                 & " SET " & $sSet _
                 & " WHERE " & $sWhereArgs & ";"
    Else
        $sUpdate = "UPDATE " & $sTable _
                 & " SET " & $sSet & ";"
    EndIf
  
    $oConnectionObj.Execute($sUpdate)
  
EndFunc ;==>_MySQLUpdate

Edited October 19, 2017 by RonildoDSouza

[MojoeB]

Is this UDF still up to date?
which driver *exe should i download on the page are only *zip & *msi files to be found ?

I wanted to use 10.6.12-MariaDB with it. Is this still possible ??^^

[Andreik]

It's clearly stated in the first post what driver it's required.

Quote

The MySQL ODBC 3.51 DRIVER IS REQUIRED FOR THIS

it can be downloaded here: http://dev.mysql.com/downloads/connector/odbc/3.51.html

 

[MojoeB]

#include <mysql.au3>
 _MySQLConnect('user123', 'pass123', 'neuuu', '11.87.4.3', "{MySQL ODBC 3.51 Driver}", 3306)

Thanks, I thought you have to compile ODBC_DRIVER_SETUP with the driver, but now I realized that it is only an automated installation.

So now I have the driver on it but it still doesn't work.
but unfortunately the script still does not work well now I get an error message from the udf itself.

 

: ==> The requested action with this object has failed.:
$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)
$Objconn^ ERROR

I have adjusted the following in the UDF, HKEY as I did not find the driver under the default path.

"HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers"

 

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\WOW6432Node\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

 

 

 

[Andreik]

3 minutes ago, MojoeB said:

I have adjusted the following in the UDF, HKEY as I did not find the driver under the default path.

If you don't find the driver in ODBC Data Sources then it's not installed. Be sure you installed appropriate x86 or x64 version of the driver.

[MojoeB]

so in ODBC data sources the driver was visible see picture.

now I have installed a 64x bit driver over it.
now it is also in regedit on the correct path.

but I still get the same error message.

Edited November 7, 2023 by MojoeB

[Andreik]

Try this and let me know if it's working and if not what do you have in console:

expandcollapse popup

#include <Array.au3>
$oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

$sDriver = ''
$aDrivers = Get_ODBC_Drivers(@AutoItX64 ? True : False)
For $Index = 1 To $aDrivers[0]
    If StringLeft($aDrivers[$Index], 5) = 'MySQL' Then
        $sDriver = $aDrivers[$Index]
        ExitLoop
    EndIf
Next

ConsoleWrite('Selected driver: ' & $sDriver & @CRLF)

; Change the server address and credentials below if it's needed
$oMySQL = MySQL_Connect($sDriver, 'localhost', 'information_schema', 'root')
$oRowset = $oMySQL.Execute('SELECT * FROM FILES')
$aData = $oRowset.GetRows()
_ArrayDisplay($aData)

Func MySQL_Connect($sDriver = 'MySQL ODBC 8.0 Unicode Driver', $sServer = 'localhost', $sDatabase = '', $sUsername = '', $sPassword = '', $iPort = 3306, $iOption = 10)
    Local $oADO = ObjCreate('ADODB.Connection')
    ConsoleWrite('ADODB Connection object created: ' & IsObj($oADO) ? True : False & @CRLF)
    $oADO.ConnectionString = 'Driver={' & $sDriver & '};Server=' & $sServer & ';Database=' & $sDatabase & ';User=' & $sUsername & ';Password=' & $sPassword & ';Port=' & $iPort & ';Option=' & $iOption & ';'
    $oADO.Open()
    Return $oADO
EndFunc

Func Get_ODBC_Drivers($x64 = False)
    Local $sDrivers, $sDriver, $iInstance = 1
    Do
        $sDriver = RegEnumVal('HKLM' & ($x64 ? '64' : '') & '\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers', $iInstance)
        If $sDriver Then $sDrivers &= $sDriver & Chr(1)
        $iInstance += 1
    Until Not $sDriver
    Return StringSplit(StringTrimRight($sDrivers, 1), Chr(1))
EndFunc

Func _ErrFunc($oError)
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc

 

Edited November 7, 2023 by Andreik

[MojoeB]

8 minutes ago, Andreik said:

Try this and let me know if it's working and if not what do you have in console:

 

ip has been changed for security reasons, even if it is only accessible in the internal network.

But of course I use the correct one for testing, so please don't get confused.
I have of course also adapted the user and password correctly for my purposes.

This is the result : 

 

expandcollapse popup

Starte Datei testeneue.au3...

Selected driver: MySQL ODBC 3.51 Driver
Truetesteneue.au3 (42) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '88.11.55.11' (10061)
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  42
    err.retcode is:     0x80004005

testeneue.au3 (34) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen.
    err.source is:      ADODB.Connection
    err.helpfile is:    C:\Windows\HELP\ADO270.CHM
    err.helpcontext is:     1240653
    err.lastdllerror is:    0
    err.scriptline is:  34
    err.retcode is:     0x800A0E78

testeneue.au3 (35) : ==> COM Error intercepted !
    err.number is:      0x000000A9
    err.windescription: Variable must be of type 'Object'.
    err.description is:     
    err.source is:      
    err.helpfile is:    
    err.helpcontext is:     
    err.lastdllerror is:    0
    err.scriptline is:  35
    err.retcode is:     0x00000000

 

 

[MojoeB]

wait a minute i think i just tested in the wrong file

the ado.au3 is still included there.

here is the result with the mysql.au3 :

Selected driver: MySQL ODBC 3.51 Driver
Trueaaaa.au3 (28) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '11.44.4.43' (10061)
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  28
    err.retcode is:     0x80004005

aaaa.au3 (20) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen.
    err.source is:      ADODB.Connection
    err.helpfile is:    C:\Windows\HELP\ADO270.CHM
    err.helpcontext is:     1240653
    err.lastdllerror is:    0
    err.scriptline is:  20
    err.retcode is:     0x800A0E78

aaaa.au3 (21) : ==> COM Error intercepted !
    err.number is:      0x000000A9
    err.windescription: Variable must be of type 'Object'.
    err.description is:     
    err.source is:      
    err.helpfile is:    
    err.helpcontext is:     
    err.lastdllerror is:    0
    err.scriptline is:  21
    err.retcode is:     0x00000000

sry, but is the same^^

Edited November 7, 2023 by MojoeB

[Andreik]

Probably your connection is refused by a firewall. Just try to disable the firewall or allow the network communication on port 3306.

[MojoeB]

hm, I haven't even checked whether the FW has blocked.
Unfortunately I can only check again on Wednesday as I'm working somewhere else tomorrow.

I thought I didn't need an extra free port for the company intranet.

But I will have a look thanks for the help :).

[Andreik]

Error 10061 indicates that the network connection has been refused. This might be a possible cause.

[MojoeB]

@Andreik

 

Hi, I actually wanted to get back to you much earlier, but our IT department hasn't gotten back to me all week.
So there is no port release yet.

However, I'm going to set up my RS-PI again at the weekend and install a database there for testing.

I hope that I can then report back with positive news on the subject.

[MojoeB]

Sorry it took so long but now I can finally continue testing.

the it has cleared the port and I can get through 
the ODBC data source window64x -> user DNS MySQL ODBC 8.2 Unicode Driver | input from server test button says successful.

Unfortunately I still get error messages: 

expandcollapse popup

COM ERROR -  Description: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
COM ERROR - Error Number: 80020009
COM ERROR -  Script Line: 17
COM ERROR - Error Source: Microsoft OLE DB Provider for ODBC Drivers
Selected driver: MySQL ODBC 8.2 Unicode Driver
Truetesteneue.au3 (55) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  55
    err.retcode is:     0x80004005

testeneue.au3 (38) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen.
    err.source is:      ADODB.Connection
    err.helpfile is:    C:\Windows\HELP\ADO270.CHM
    err.helpcontext is:     1240653
    err.lastdllerror is:    0
    err.scriptline is:  38
    err.retcode is:     0x800A0E78

testeneue.au3 (39) : ==> COM Error intercepted !
    err.number is:      0x000000A9
    err.windescription: Variable must be of type 'Object'.
    err.description is:     
    err.source is:      
    err.helpfile is:    
    err.helpcontext is:     
    err.lastdllerror is:    0
    err.scriptline is:  39
    err.retcode is:     0x00000000

I have written in the driver manually, MySQL ODBC 8.2 Unicode Driver. 

Unfortunately this does not find the driver: 

$sDriver = ''
$aDrivers = Get_ODBC_Drivers(@AutoItX64 ? True : False)
For $Index = 1 To $aDrivers[0]
    If StringLeft($aDrivers[$Index], 5) = 'MySQL' Then
        $sDriver = $aDrivers[$Index]
        ExitLoop
    EndIf
Next

edit :
i have to correct myself i found your function in another thread.
I copied the code again. 
Now it also finds the 64x drivers with the Get_ODBC_Drivers function.
However, I still have the listed error message with the test script.

Edited December 6, 2023 by MojoeB

[MojoeB]

@Andreik

oh my god now after trying several times I have not received any more error messages.

It seems to be due to switching the architecture from 32 to 64 (autoit).

I no longer get an error message, thank you, now I have to familiarize myself with the other functions.

 Thanks :