Sign in to follow this  
Followers 0
James

MySQL ODBC Connector UDF

8 posts in this topic

#1 ·  Posted (edited)

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



thank you for your time making this, this is going to be of some use !!

-hench

Share this post


Link to post
Share on other sites

very nice and useful, please continue developing

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  
Followers 0