AutoIT MySQL Connection and Query Script

AutoIT MySQL Connection and Query Script

AutoIt
Spread the love

I put this little example together thanks to the MySQL UDF for AutoIT by Progandy, using the error handling function provided by michaelslamet in the Progandy AutoIT MYSQL help forum.

I started developing this AutoIT script for a couple of projects that I am working on currently. I have successfully tested running queries against a cloud based MySQL server to date. I will be posting more updates to this script, as I test inputs into the database.

I did find that I had to use the libmysql.dll that is included in the UDF Zip File, and it has a file date of 08/06/2006 and the image below shows the version:

libmysql.dll Version Information
;~ MySQL Database Connection and Query Example Script
#include <Debug.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <MySQL.au3>
/~ ERROR HANDLER FUNCTION
$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

/~ 
; Start MYSQL, DLL in PATH (also contains @ScriptDir), specify path to DLL. DLL must be named libmysql.dll.
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "could not init MySQL")
MsgBox(0, "DLL Version:","Client Version: " & _MySQL_Get_Client_Version()&@CRLF& "Client Information: " & _MySQL_Get_Client_Info())

$MysqlConn = _MySQL_Init()

_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "")
$MysqlConn = _MySQL_Init()

;~ HERE IS AN EXAMPLE OF SETTING THE VARIABLES FOR LOGGING INTO THE DATABASE
;~ $dUser = InputBox("Enter Your Username", "Database Username")
;~ $dPwd = InputBox("Enter Database Password", "Database Password", "Default", "*")

$connected = _MySQL_Real_Connect($MysqlConn, 'host', 'databaseuser', 'password','database')
If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn))

$query = "SELECT * dbTABLE ORDER BY tblFIELDNAME"
_MySQL_Real_Query($MysqlConn, $query)

MsgBox(0, "Test Results", "This is what our query looks like: " &@CRLF& $query)

;------------------------------------------------------
;~ ERROR HANDLER FUNCTION TO REPORT COM ERRORS - BEGIN
;~ ----------------------------------------------------
Func MyErrFunc()

$hexnum=hex($objErr.number,8)

Msgbox(0,"","We intercepted a COM Error!!"      & @CRLF                & @CRLF & _
             "err.description is: " & $objErr.description   & @CRLF & _
             "err.windescription is: " & $objErr.windescription & @CRLF & _
             "err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & $objErr.scriptline    & @CRLF & _
             "err.number is: "       & $hexnum               & @CRLF & _
             "err.source is: "       & $objErr.source        & @CRLF & _
             "err.helpfile is: "       & $objErr.helpfile      & @CRLF & _
             "err.helpcontext is: " & $objErr.helpcontext _
            )
exit
EndFunc
;------------------------------------------------------
;~ ERROR HANDLER FUNCTION TO REPORT COM ERRORS - END
;------------------------------------------------------

$result = _MySQL_Store_Result($MysqlConn)
$fields = _MySQL_Num_Fields($result)
$rows = _MySQL_Num_Rows($result)

MsgBox(0, "Number of Rows and Columns (Fields)", "[" & $rows & "]" & " Rows - " & "[" & $fields & "]" & " Fields")
ConsoleWrite("DataType=" & VarGetType($result) & @LF)
ConsoleWrite("DataType=" & VarGetType($fields) & @LF)
ConsoleWrite("DataType=" & VarGetType($rows) & @LF)
MsgBox(0, "Number of Fields", "There are " & $fields & " fields in the table.")
MsgBox(0, "Number of Rows", "There are " & $rows & " rows in the table.")

MsgBox(0, '', "Access Method 1 - Handwork")
Dim $array[$rows][$fields]
For $k = 1 To $rows
    $mysqlrow = _MySQL_Fetch_Row($result,$fields)

    $lenthsStruct = _MySQL_Fetch_Lengths($result)

    For $i = 1 To $fields
        $length = DllStructGetData($lenthsStruct, 1, $i)
        $fieldPtr = DllStructGetData($mysqlrow, 1, $i)
        $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)
        $array[$k - 1][$i - 1] = $data
    Next
Next
_ArrayDisplay($array, "Results")

; METHOD 2 ROW BY ROW
MsgBox(0, '', "Access Method 2 - Row by Row")
_MySQL_Data_Seek($result, 0) ; just to reset to the beginning of the query
Do
$row1 = _MySQL_Fetch_Row_StringArray($result)
If @error Then ExitLoop
_ArrayDisplay($row1, "Results")
Until @error

MsgBox(0, '', "Access Method 3 - all in a 2D array")
$array = _MySQL_Fetch_Result_StringArray($result)
_ArrayDisplay($array, "Results")

; FIELD INFORMATION
MsgBox(0, '', "Access Method 3 - Field Information all in a 2D array")
Dim $arFields[$fields][3]
For $i = 0 To $fields - 1
    $field = _MySQL_Fetch_Field_Direct($result, $i)
    $arFields[$i][0] = _MySQL_Field_ReadValue($field, "name")
    $arFields[$i][1] = _MySQL_Field_ReadValue($field, "table")
    $arFields[$i][2] = _MySQL_Field_ReadValue($field, "db")
Next
_ArrayDisplay($arFields, "Results")

; Cleanup
_MySQL_Free_Result($result)

_MySQL_Close($MysqlConn)

_MySQL_EndLibrary()
msgbox(0,"","This Completes The Example")
exit(0)
%d bloggers like this: