Jump to content

MySQL ODBC Connector UDF


Recommended Posts

Hey all,

This is a basic UDF and needs work. I know.

There are no headers on the functions as they should be pretty easy to understand anyway. The functions that I have written are named after the PHP counterparts since I use them a lot and they make sense to me.

Notes:

  • mysql_connect() will attempt to create the Driver string for you based on the ODBC connector version installed. Not only that but mysql_query() and mysql_fetch_array() allow you to change the connected database using an optional second parameter.
  • mysql_fetch_array() returns a 3-dimensional array which means they are not visible in _ArrayDisplay() so you'll have to attempt to output your array using similar code to that of the example I have attached.
  • mysql_error() will return the last error string caught by the internal __obj_error() function.
  • mysql_errno() returns the Hex error number which after a bit of testing does match any Google search.
  • mysql_drop_db() is just a wrapper for a mysql_query() and should be deprecated. There is no matching mysql_drop_table() function!
  • It's primitive and I use it solely at work for running very basic queries but I'm in the process of continuing development.
Hopefully I've covered everything for now.

#include-once

Global $lastError = False, $lastErrorNo = -1
Global $objConn

Global $oMyError = ObjEvent("AutoIt.Error", "__obj_error")

Global Const $ODBCDriver = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"
Global $MYSQL_REG = __mysql_odbc($ODBCDriver)
Global Const $MYSQL_VERSION = $MYSQL_REG[1]

Func mysql_connect($sHost, $sUserName, $sPassword, $sDatabase, $iPort = 3306, $sDriver = Default)
    If Not IsString($sHost) Then Return __mysql_seterr("$sHost parameter is not a string in mysql_connect!")
    If Not IsString($sUserName) Then Return __mysql_seterr("$sUserName parameter is not a string in mysql_connect!")
    If Not IsString($sPassword) Then Return __mysql_seterr("$sPassword parameter is not a string in mysql_connect!")
    If Not IsString($sDatabase) Then Return __mysql_seterr("$sDatabase parameter is not a string in mysql_connect!")
    If Not IsNumber($iPort) Then Return __mysql_seterr("$iPort parameter is not a string in mysql_connect!")
    If $sDriver = Default Then $sDriver = "{MySQL ODBC " & $MYSQL_VERSION & " Driver}"
    If Not IsString($sDriver) Then Return __mysql_seterr("$sDriver parameter is not a string in mysql_connect!")

    Local $strDriverConn

    $objConn = ObjCreate("ADODB.Connection")

    $strDriverConn = "DRIVER=" & $sDriver & ";SERVER=" & $sHost & ";DATABASE=" & $sDatabase & ";UID=" & $sUserName & ";PWD=" & $sPassword & ";PORT=" & $iPort

    $objConn_ect = $objConn.Open($strDriverConn)
    If @error Then Return False

    Return $objConn
EndFunc   ;==>mysql_connect

Func mysql_query($sQuery, $sConn = Default)
    If Not IsString($sQuery) Then Return __mysql_seterr("[" & $MYSQL_VERSION & "] $sQuery must be a string in mysql_query")
    If Not $sConn = Default Then $objConn = $sConn

    If IsObj($objConn) Then
        Local $arSQLRet
        $objConn_quer = $objConn.Execute($sQuery)
        If Not $objConn_quer Then Return False

        Return $objConn_quer
    Else
        Return False
    EndIf
EndFunc   ;==>mysql_query

Func mysql_fetch_array($arrObj, $sConn = Default)
    If Not $sConn = Default Then $objConn = $sConn

    If IsObj($objConn) Then
        Local $iCol = 0, $iFields = 0
        Local $__mysql_temp = $objConn.Execute($arrObj)
        If @error Then Return False

        Local $__mysql_field_count = $__mysql_temp.Fields.Count
        Local $__mysql_arr[1][$__mysql_field_count][2]

        Do
            For $i In $__mysql_temp.Fields
                ReDim $__mysql_arr[$iCol + 1][$iFields + 1][2]

                $__mysql_arr[$iCol][$iFields][0] = $i.Name
                $__mysql_arr[$iCol][$iFields][1] = $i.Value

                $iFields += 1
            Next

            $iCol +=1
            $iFields = 0
            $__mysql_temp.MoveNext
        Until $__mysql_temp.EOF

        Return $__mysql_arr
    Else
        Return False
    EndIf
EndFunc   ;==>mysql_fetch_array

Func mysql_drop_db($sDatabaseName, $sConn = Default)
    If Not $sConn = Default Then $objConn = $sConn

    If IsObj($objConn) Then
        ; This function is deprecated, it is preferable to use mysql_query() to issue an sql DROP DATABASE statement instead.
        Local $sQuery
        $sQuery = "DROP DATABASE " & $sDatabaseName & ";"

        Local $__mysql_temp = $objConn.Execute($sQuery)
        If @error Then Return False
    Else
        Return False
    EndIf
EndFunc

Func mysql_error()
    Return $lastError
EndFunc   ;==>mysql_error

Func mysql_errno()
    Return $lastErrorNo
EndFunc

Func mysql_close($sConn = Default)
    If Not $sConn = Default Then $objConn = $sConn
    $objConn.Close
EndFunc   ;==>mysql_close

;;--------------------------------------------------------------------------
;; Internal Functions
;; __mysql_odbc()   - Matches registry string for version
;; __mysql_seterr() - Sets the last error string
;; __mysql_getver() - Returns the installed ODBC connector version
;; __obj_error()    - Returns any ODBC errors thrown by the connector
;;--------------------------------------------------------------------------

Func __mysql_odbc($strKey)
    Local $i, $arODBC
    While 1
        $i += 1
        $regVal = RegEnumVal($strKey, $i)
        $arODBC = StringRegExp($regVal, "MySQL ODBC (.*?) Driver", 2)
        If IsArray($arODBC) Then Return $arODBC
    WEnd
EndFunc   ;==>__mysql_odbc

Func __mysql_seterr($__sString)
    $lastError = $__sString
    Return $lastError
EndFunc   ;==>__mysql_seterr

Func __mysql_getver()
    If __mysql_odbc($ODBCDriver) Then Return __mysql_odbc($ODBCDriver)
EndFunc   ;==>__mysql_getver

Func __obj_error()
    $lastErrorNo = Hex($oMyError.Number, 8)
    __mysql_seterr($oMyError.description)
    SetError(Hex($oMyError.number, 8), $oMyError.lastdllerror)
EndFunc   ;==>__obj_error

And an example remember to modify the connection strings.

#include "mysql.au3"

Global $objDB, $obQuery, $arArray

Local $strHost = "127.0.0.1", $strUID = "root", $strPass = "", $strDB = "")

$objDB = mysql_connect($strHost, $strUID, $strPass, $strDB)
If mysql_error() Then
    ConsoleWrite("!>" & mysql_error() & @CRLF)
EndIf

$obQuery = mysql_query("INSERT INTO test (`value`) VALUES('This is a test!')")

$arArray = mysql_fetch_array("SELECT * FROM test")
If IsArray($arArray) Then
    For $i = 0 to Ubound($arArray, 1) - 1
        For $j = 0 To UBound($arArray, 2) - 1
            ConsoleWrite($arArray[$i][$j][1] & @CRLF)
        Next
    Next
Else
    ConsoleWrite("!>" & mysql_error() & @CRLF)
EndIf

mysql_close()

Have fun.

James

Edited by JamesBrooks
Link to post
Share on other sites
  • 2 weeks later...
  • 1 month later...

Hi James and thank you for sharing this UDF.

Good timing for me because I need to start working on a MySQL app to talk to a remote database, but my utility will be run from many different locations and bringing along an install of ODBC is really not going to be practical.

I have a few comments and also issues running the test script provided. My comments are below:

  • In your example, there is an extra brace at the end of the Local var declarations line: $strDB = ""). This causes a syntax compile error.
  • I made my own changes to your example code and when I ran it, it just said running with no output or Console entries. So, I went back to your original sample code and added a few more ConsoleWrite lines and see that none of those are being printed either. Odd.. I'll copy my modified code below.
  • Just as an FYI, I copied your UDF to my includes directory as "MySQL_ODBC.au3"
  • When the program executes, the console shows that it started (w/no warnings or errors), but just runs until I kill it

Here is your example code with only a few ConsoleWrite() calls that are not being triggered..which I don't get because they are right inline. Can someone tell me if they see the same behavior?

#include "MySQL_ODBC.au3"
;~ #include "mysql.au3"

ConsoleWrite("<--------------------- DEBUG" &@CRLF) ;This does NOT print to my Console!
Global $objDB, $obQuery, $arArray

Local $strHost = "127.0.0.1", $strUID = "root", $strPass = "", $strDB = ""

ConsoleWrite("<--------------------- DEBUG" &@CRLF) ;This does NOT print to my Console!
$objDB = mysql_connect($strHost, $strUID, $strPass, $strDB)
ConsoleWrite("<--------------------- DEBUG" &@CRLF) ;This does NOT print to my Console!
If mysql_error() Then
    ConsoleWrite("!>" & mysql_error() & @CRLF)
EndIf

$obQuery = mysql_query("INSERT INTO test (`value`) VALUES('This is a test!')")

$arArray = mysql_fetch_array("SELECT * FROM test")
If IsArray($arArray) Then
    For $i = 0 to Ubound($arArray, 1) - 1
        For $j = 0 To UBound($arArray, 2) - 1
            ConsoleWrite($arArray[$i][$j][1] & @CRLF)
        Next
    Next
Else
    ConsoleWrite("!>" & mysql_error() & @CRLF)
EndIf

mysql_close()

Thanks!

Link to post
Share on other sites

  • When the program executes, the console shows that it started (w/no warnings or errors), but just runs until I kill it

I think I see the problem. In the included udf file, it doesn't just define functions, it also calls __mysql_odbc() looking for a MySQL installed driver and if none is found, it sits spinning in an endless loop. In my case here, I see that this machine doesn't currently have a MySQL ODBC driver installed.

Link to post
Share on other sites

@schuc: If you don't like ODBC, there are 2 UDFs relying on libmysql.dll instead of ODBC. One is called and should be simpler, and the other one is more powerful, but more complicated ()

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to post
Share on other sites
  • 9 years later...
  • 1 month later...

First of all, thank you very much for James's approach, but I found that when executing select multiple columns, the content of the query in the second column is empty.

such as “select name from test” return value is ok,but “select name, user from test” then return value is error

The test table data is as follows

name             user

Zhang           San

Li                  Ming

Link to post
Share on other sites

Switch to @mLipok ADO.au3 UDF.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
  • 2 years later...

it depends on ms sql collation settings.

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 for 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) *

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: 2022-03-07

Link to post
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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...