MySQL ODBC Connector UDF

[James]

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.

expandcollapse popup

#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 November 20, 2010 by JamesBrooks

[hench]

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

-hench

[autoall]

very nice and useful, please continue developing

[James]

very nice and useful, please continue developing

Well what else would you like to see included?

[schuc]

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!

[schuc]

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

[ProgAndy]

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

[schuc]

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

[Yoskos]

I run the script on my computer. Performs normally. The data was entered into Mysql, but the queries display nothing.

[argumentum]

..you'll have to show more code. If the AutoIt code works well then post your PHP or what not code. Otherwise how can anyone help @Yoskos ?

and, welcome to the forum 

[Yoskos]

1 hour ago, argumentum said:

..você terá que mostrar mais código. Se o código AutoIt funcionar bem, poste seu PHP ou não. Caso contrário, como alguém pode ajudar o @Yoskos  ?

e seja bem-vindo ao fórum 

My code is the example. I just updated the user's password.

[argumentum]

On 11/20/2010 at 2:54 PM, James said:

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

 

2 hours ago, Yoskos said:

My code is the example.

This UDF may not be complete  
Look around the forums for something better supported  

[mikezunya]

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

[jchd]

Switch to @mLipok ADO.au3 UDF.

[skynet0504]

how to fix "charset=UTF-8" before insert into ???

 

 

[mLipok]

it depends on ms sql collation settings.

[skynet0504]

6 hours ago, mLipok said:

it depends on ms sql collation settings.

as before

database charset utf8 too