Jump to content
Sign in to follow this  
James

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

Share this post


Link to post
Share on other sites

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!

Share this post


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.

Share this post


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

Share this post


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 ()

Thank you. I will have a look. :x

Share this post


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
Sign in to follow this  

×
×
  • Create New...