Jump to content

MySQL UDFs


cdkid
 Share

Recommended Posts

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

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

Did you ask in ADO support topic ?

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

  • 4 months later...

Add this function...

 

#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 by RonildoDSouza
Link to comment
Share on other sites

  • 6 years later...
#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


 


 

 

Link to comment
Share on other sites

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.

When the words fail... music speaks.

Link to comment
Share on other sites

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.

odbc.png

Edited by MojoeB
Link to comment
Share on other sites

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

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

When the words fail... music speaks.

Link to comment
Share on other sites

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 : 

 

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

 

 

Link to comment
Share on other sites

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 by MojoeB
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

  • 3 weeks later...

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: 

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 by MojoeB
Link to comment
Share on other sites

@Andreik

oh my god :D 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 :

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...