Jump to content
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

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

Share this post


Link to post
Share on other sites
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 : ILA2:

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

Share this post


Link to post
Share on other sites

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

Share this post


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)

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

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...