Jump to content
Yoriz

EzMySql UDF - Use MySql Databases with autoit

By Yoriz, in AutoIt Example Scripts

Recommended Posts

Yoriz    6

Yoriz
Posted (edited)

This is a UDF called EzMySql for using a MySql database with autoit, it uses similar syntax as SQLite but for MySql, for a more comprehensive MySql UDF see MySQL UDF by ProgAndy.

Credit goes to ProgAndy as i used his UDF to be able to create this standalone UDF.

I have only been able to test it on windowsXP x32

Download EzMySql_Dll.au3

There are two scripts, EzMySql.au3 which contains the functions and EzMySql_Dll.au3 which has an embeded 32 and 64 dll.

The required dll will be created if it does not exist in the path given when calling _EzMySql_Startup,

if no path is given the dll will be created in the script directory.

Please give it a try and let me know of any problems/improvements:)

Example Script

#include "EzMySql.au3"
#include <Array.au3>

If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$Pass = "Your password here"

If Not _EzMySql_Open("", "root", $Pass, "", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS EzMySqlTest") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SelectDB("EzMySqlTest") Then
    MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$sMySqlStatement = "CREATE TABLE IF NOT EXISTS TestTable (" & _
                   "RowID INT NOT NULL AUTO_INCREMENT," & _
                   "Name TEXT NOT NULL ," & _
                   "Age INT NOT NULL ," & _
                   "EyeColour TEXT NOT NULL ," & _
                   "HairColour TEXT NOT NULL ," & _
                   "PRIMARY KEY (`RowID`) ," & _
                   "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"

If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

Local $aEyeColours[7] = ["Amber","Blue","Brown","Grey","Green","Hazel","Red"]
Local $aHairColours[6] = ["Brown","Black","Blond","Grey","Green","Pink"]

Local $sMySqlStatement = ""
For $i = 1 To 50 Step 1
    $sMySqlStatement &= "INSERT INTO TestTable (Name,Age,EyeColour,HairColour) VALUES (" & _
                        "'Person" & $i & "'," & _
                        "'" & Random(1, 100, 1) & "'," & _
                        "'" & $aEyeColours[Random(0, 6, 1)] & "'," & _
                        "'" & $aHairColours[Random(0, 5, 1)] & "');"
Next

If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

MsgBox(0, "Last AUTO_INCREMENT columnID2", _EzMySql_InsertID())

$aOk = _EzMySql_GetTable2d("SELECT Name,EyeColour FROM TestTable WHERE EyeColour = '"& $aEyeColours[Random(0, 6, 1)] & "';")
$error = @error
If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error)
_ArrayDisplay($aOk, "2d Array Names of certain eyecolour")

MsgBox(0, "", "Following is how to get a row at a time of a query as a 1d array")
If Not _EzMYSql_Query("SELECT * FROM TestTable WHERE HairColour = '"& $aHairColours[Random(0, 5, 1)] & "' LIMIT 5;") Then
MsgBox(0, "Query Error", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

For $i = 1 To _EzMySql_Rows() Step 1
    $a1Row = _EzMySql_FetchData()
    _ArrayDisplay($a1Row, "Result: " & $i)
Next

_EzMySql_Exec("DROP TABLE TestTable")

_EzMySql_Close()
_EzMySql_ShutDown()
Exit

EzMySql.au3

#include-once
#include <EzMySql_Dll.au3>
; #INDEX# =======================================================================================================================
; Title .........: EzMySql
; AutoIt Version : 3.3.6.1
; Language ......: English
; Description ...: Functions that assist access to an MySql database.
; Author(s) .....: Yoriz
; Based on ......: MySQL UDFs working with libmysql.dll by Prog@ndy and the autoit built in Sqlite functionality
; Dll ...........: libmysql.dll or libmySQL_x64.dll

; #CURRENT# =====================================================================================================================
; _EzMySql_Startup
; _EzMySql_ShutDown
; _EzMySql_Open
; _EzMySql_Close
; _EzMySql_Exec
; _EzMySql_GetTable2d
; _EzMySql_AddTable2d
; _EzMySql_Changes
; _EzMySql_ErrMsg
; _EzMySql_FetchData()
; _EzMySql_Query
; _EzMySql_QueryFinalize
; _EzMySql_FetchNames
; _EzMySql_Rows
; _EzMySql_Columns
; _EzMySql_ChangeUser
; _EzMySql_InsertID
; _EzMySql_SelectDB
; ===============================================================================================================================

; #VARIABLES# ===================================================================================================================
Global $hEzMySql_Dll = -1, $hEzMySql_Ptr, $sEzMySql_Result, $sEzMySql_Mutltiline = 1

; struct from MySQL UDFs working with libmysql.dll by Prog@ndy
Global Const $hEzMySql_Field = _
        "ptr name;" & _                 ;/* Name of column */ [[char *
        "ptr orgName;" & _             ;/* Original column name, if an alias */ [[char *
        "ptr table;" & _                ;/* Table of column if column was a field */ [[char *
        "ptr orgTable;" & _            ;/* Org table name, if table was an alias */ [[char *
        "ptr db;" & _                   ;/* Database for table */ [[char *
        "ptr catalog;" & _        ;/* Catalog for table */ [[char *
        "ptr def;" & _                  ;/* Default value (set by mysql_list_fields) */ [[char *
        "ulong length;" & _       ;/* Width of column (create length) */
        "ulong maxLength;" & _   ;/* Max width for selected set */
        "uint nameLength;" & _
        "uint orgNameLength;" & _
        "uint tableLength;" & _
        "uint orgTableLength;" & _
        "uint dbLength;" & _
        "uint catalogLength;" & _
        "uint defLength;" & _
        "uint flags;" & _         ;/* Div flags */
        "uint decimals;" & _      ;/* Number of decimals in field */
        "uint charsetnr;" & _     ;/* Character set */
        "int type;" & _ ;/* Type of field. See mysql_com.h for types */
        "ptr extension;"
; ===============================================================================================================================

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Startup
; Description ...: Locates or creates the libmysql.dll and creates a MySQL struct
; Syntax.........: _EzMySql_Startup($hEzMySql_DllLoc = "")
; Parameters ....: $hEzMySql_DllLoc - Path to libmysql.dllor libmySQL_x64.dll, if path = "" @scripdir used
;                   | if a path is given and no dll exists it will be created
; Return values .: On Success - 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - Failed to write dll file
;                    2 - Failed to open DLL
;                    3 - Failed to create MySql struct
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Startup($hEzMySql_DllLoc = "")
    Local $sDll_Filename, $hFileCreate, $hFileWriteOk
    If @AutoItX64 = 0 Then
        $sDll_Filename = "libmysql.dll"
    Else
        $sDll_Filename = "libmySQL_x64.dll"
    EndIf
    If $hEzMySql_DllLoc Then
        If StringRight($hEzMySql_DllLoc, StringLen($sDll_Filename)) <> $sDll_Filename Then
            $hEzMySql_DllLoc = StringRegExpReplace($hEzMySql_DllLoc, "[\\/]+\z", "") & "\"
            $hEzMySql_DllLoc &= $sDll_Filename
        EndIf
    Else
        $hEzMySql_DllLoc = @ScriptDir & "\" & $sDll_Filename
    EndIf
    If Not FileExists($hEzMySql_DllLoc) Then
        $hFileCreate = FileOpen($hEzMySql_DllLoc, 10)
        $hFileWriteOk = FileWrite($hFileCreate, _EzMySql_Dll())
        FileClose($hFileCreate)
        If Not $hFileWriteOk Then Return SetError(1, 0, 0)
    EndIf
    $hEzMySql_Dll = DllOpen($hEzMySql_DllLoc)
    If $hEzMySql_Dll = -1 Then Return SetError(2, 0, 0)
    Local $hPtr = DllCall($hEzMySql_Dll, "ptr", "mysql_init", "ptr", 0)
    If @error Then Return SetError(3, 0, 0)
    $hEzMySql_Ptr = $hPtr[0]
    Return 1
EndFunc   ;==>_EzMySql_Startup

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Open
; Description ...: Open a MySql Database
; Syntax.........: _EzMySql_Open($Host, $User, $Pass, $Database = "", $Port = 0, $unix_socket = "", $Client_Flag = 0)
; Parameters ....: $Host        - hostname or an IP address
;                  $User        - MySQL login ID
;                  $Pass        - password for user (no password: "" (empty string))
;                  $Database    - default database (no default db: "" (empty string))
;                  $Port        - If port is not 0, the value is used as the port number for the TCP/IP connection.
;                  $unix_socket - specifies the socket or named pipe that should be used. (no pipe: "" (empty string))
;                  $Client_Flag - flags to enable features
; Return values .: On Success - 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Open Call failed
;                    3 - Database error - check _EzMySql_ErrMsg() for error
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Open($Host, $User, $Pass, $Database = "", $Port = 0, $unix_socket = "", $Client_Flag = 0)
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    Local $PWType = "str", $DBType = "str", $UXSType = "str"
    If $Pass = "" Then $PWType = "ptr"
    If $Database = "" Then $DBType = "ptr"
    If $unix_socket = "" Then $UXSType = "ptr"
    Local $conn = DllCall($hEzMySql_Dll, "ptr", "mysql_real_connect", "ptr", $hEzMySql_Ptr, "str", $Host, "str", $User, $PWType, $Pass, $DBType, $Database, "uint", $Port, $UXSType, $unix_socket, "ulong", $Client_Flag)
    If @error Then Return SetError(2, 0, 0)
    If _EzMySql_ErrMsg() Then Return SetError(3, 0, 0)
    Return 1
EndFunc   ;==>_EzMySql_Open

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_ChangeUser
; Description ...:  Changes the user and causes the database specified by db to become the default (current) database.
; Syntax.........: _EzMySql_ChangeUser($User, $Pass, $Database = "")
; Parameters ....: $User        - MySQL login ID
;                  $Pass        - password for user (no password: "" (empty string))
;                  $Database    - default database (no default db: "" (empty string))
; Return values .: On Success - 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Open Call failed
;                    3 - Database error - check _EzMySql_ErrMsg() for error
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_ChangeUser($User, $Pass, $Database = "")
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    Local $PWType = "str", $DBType = "str"
    If $Pass = "" Then $PWType = "ptr"
    If $Database = "" Then $DBType = "ptr"
    Local $conn = DllCall($hEzMySql_Dll, "int", "mysql_change_user", "ptr", $hEzMySql_Ptr, "str", $User, $PWType, $Pass, $DBType, $Database)
    If @error Then Return SetError(2, 0, 1)
    If _EzMySql_ErrMsg() Then Return SetError(3, 0, 0)
    Return 1
EndFunc   ;==>_EzMySql_ChangeUser

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_SelectDB
; Description ...:  Causes the database specified by db to become the default
; Syntax.........: _EzMySql_SelectDB($Database)
; Parameters ....: $Database    - The new default database name
; Return values .: On Success - 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Open Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_SelectDB($Database)
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    Local $conn = DllCall($hEzMySql_Dll, "int", "mysql_select_db", "ptr", $hEzMySql_Ptr, "str", $Database)
    If @error Then Return SetError(2, 0, 1)
    Return 1
EndFunc   ;==>_EzMySql_SelectDB

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Query
; Description ...: Query a single line MySql statement
; Syntax.........: _EzMySql_Query($querystring)
; Parameters ....: $querystring - MySql Statement
; Return values .: On Success - Returns 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Query Call failed
;                    3 - Database error - check _EzMySql_ErrMsg() for error
;                    4 - Dll Store result call failed
;                    5 - Empty $querystring parameter passed to function
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Query($querystring)
    If Not $sEzMySql_Mutltiline Then _EzMySql_MultiLine(False)
    If $sEzMySql_Result Then _EzMySql_QueryFinalize()
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    If Not $querystring Then Return SetError(5, 0, 0)
    $querystringlength = StringLen($querystring)
    Local $query = DllCall($hEzMySql_Dll, "int", "mysql_real_query", "ptr", $hEzMySql_Ptr, "str", $querystring, "ulong", $querystringlength)
    If @error Then Return SetError(2, 0, 0)
    Local $result = DllCall($hEzMySql_Dll, "ptr", "mysql_store_result", "ptr", $hEzMySql_Ptr)
    If @error Then Return SetError(4, 0, 0)
    If _EzMySql_ErrMsg() Then Return SetError(3, 0, 0)
    $sEzMySql_Result = $result[0]
    Return 1
EndFunc   ;==>_EzMySql_Query

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_QueryFinalize
; Description ...: Finalizes the last query, freeing the allocated memory
; Syntax.........: _EzMySql_QueryFinalize()
; Parameters ....: None
; Return values .: On Success - None
; Return values .: On Failure - returns 0 and @error value
;                    2 - Dll Query Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_QueryFinalize()
    DllCall($hEzMySql_Dll, "none", "mysql_free_result", "ptr", $sEzMySql_Result)
    If @error Then Return SetError(2, 0, 0)
    $iEzMySql_Rows = 0
    $iEzMySql_Columns = 0
    $sEzMySql_Result = 0
EndFunc   ;==>_EzMySql_QueryFinalize

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Exec
; Description ...: Executes a MySql query. Can be multi line . does not handle result
; Syntax.........: _EzMySql_Exec($querystring)
; Parameters ....: $querystring - MySql Statement
; Return values .: On Success - Returns 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Query Call failed
;                    3 - Database error - check _EzMySql_ErrMsg() for error
;                    4 - Dll Store result call failed
;                    5 - Empty $querystring parameter passed to function
; Author ........: Yoriz
Func _EzMySql_Exec($querystring)
    Local $execError, $iNextResult
    If $sEzMySql_Result Then _EzMySql_QueryFinalize()
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    If Not $querystring Then Return SetError(5, 0, 0)
    $querystringlength = StringLen($querystring)
    _EzMySql_MultiLine()
    Local $query = DllCall($hEzMySql_Dll, "int", "mysql_real_query", "ptr", $hEzMySql_Ptr, "str", $querystring, "ulong", $querystringlength)
    If @error Then $execError = 2
    If _EzMySql_ErrMsg() Then Return SetError(3, 0, 0)
    Do
        Local $result = DllCall($hEzMySql_Dll, "ptr", "mysql_store_result", "ptr", $hEzMySql_Ptr)
        $sEzMySql_Result = $result[0]
        _EzMySql_QueryFinalize()
        $iNextResult = DllCall($hEzMySql_Dll, "int", "mysql_next_result", "ptr", $hEzMySql_Ptr)
    Until $iNextResult[0] <> 0
    _EzMySql_MultiLine(False)
    If $execError Then Return SetError($execError, 0, 0)
    Return 1
EndFunc   ;==>_EzMySql_Exec

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Rows
; Description ...: Returns row qty of the last MySql Query
; Syntax.........: _EzMySql_Rows()
; Parameters ....: None
; Return values .: On Success - Returns amount of rows
; Return values .: On Failure - returns -1 and @error value
;                    1 - Dll Rows Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Rows()
    Local $aRows = DllCall($hEzMySql_Dll, "uint64", "mysql_num_rows", "ptr", $sEzMySql_Result)
    If @error Then Return SetError(1, 0, -1)
    $iEzMySql_Rows = $aRows[0]
    Return $iEzMySql_Rows
EndFunc   ;==>_EzMySql_Rows

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Columns
; Description ...: Returns column qty of the last MySql Query
; Syntax.........: _EzMySql_Columns()
; Parameters ....: None
; Return values .: On Success - Returns amount of columns
; Return values .: On Failure - returns -1 and @error value
;                    1 - Dll column Call failed
;                    2 - No result querry to check against
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Columns()
    If Not $sEzMySql_Result Then Return SetError(2, 0, 0)
    Local $aColumns = DllCall($hEzMySql_Dll, "uint", "mysql_num_fields", "ptr", $sEzMySql_Result)
    If @error Then Return SetError(1, 0, -1)
    $iEzMySql_Columns = $aColumns[0]
    Return $iEzMySql_Columns
EndFunc   ;==>_EzMySql_Columns

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_FetchNames
; Description ...: Returns column names of the last MySql Query
; Syntax.........: _EzMySql_FetchNames()
; Parameters ....: None
; Return values .: On Success - Returns 1d array of column names
; Return values .: On Failure - returns 0 and @error value
;                    1 - Dll column Call failed or Coloumns = 0
;                    2 - Dll column names Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_FetchNames()
    Local $numberOfFields = _EzMySql_Columns()
    If $numberOfFields < 1 Then Return SetError(1, 0, $numberOfFields)
    Local $fields = DllCall($hEzMySql_Dll, "ptr", "mysql_fetch_fields", "ptr", $sEzMySql_Result)
    If @error Then Return SetError(2, 0, 0)
    $fields = $fields[0]
    Local $struct = DllStructCreate($hEzMySql_Field, $fields)
    Local $arFields[$numberOfFields]
    For $i = 1 To $numberOfFields
        $arFields[$i - 1] = _EzMySql_PtrStringRead(DllStructGetData($struct, 1))
        If $i = $numberOfFields Then ExitLoop
        $struct = DllStructCreate($hEzMySql_Field, $fields + (DllStructGetSize($struct) * $i))
    Next
    Return $arFields
EndFunc   ;==>_EzMySql_FetchNames

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_FetchData
; Description ...: Fetches 1 row of data from the last MySql Query
; Syntax.........: _EzMySql_FetchData()
; Parameters ....: None
; Return values .: On Success - Returns 1d array of row data
; Return values .: On Failure - returns 0 and @error value
;                    1 - no columns found
;                    2 - no rows found
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_FetchData()
    Local $NULLasPtr0 = False

    $fields = _EzMySql_Columns()
    If $fields <= 0 Or $sEzMySql_Result = 0 Then Return SetError(1, 0, 0)

    Local $RowArr[$fields]

    Local $mysqlrow = _EzMySql_Fetch_Row()
    If Not IsDllStruct($mysqlrow) Then Return SetError(2, 0, 0)

    Local $lenthsStruct = _EzMySql_Fetch_Lengths()

    Local $length, $fieldPtr
    For $i = 1 To $fields
        $length = DllStructGetData($lenthsStruct, 1, $i)
        $fieldPtr = DllStructGetData($mysqlrow, 1, $i)
        Select
            Case $length ; if there is data
                $RowArr[$i - 1] = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)
            Case $NULLasPtr0 And Not $fieldPtr ; is NULL and return NULL as Ptr(0)
                $RowArr[$i - 1] = Ptr(0)
;~          Case Else ; Empty String or NULL as empty string
                ; Nothing needs to be done, since array entries are default empty string
;~              $RowArr[$i - 1] = ""
        EndSelect
    Next
    Return $RowArr
EndFunc   ;==>_EzMySql_FetchData

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_GetTable2d
; Description ...: Passes out a 2Dimensional array containing Column names and Data of executed Query
; Syntax.........: _EzMySql_GetTable2d($querystring)
; Parameters ....: $querystring - MySql Statement
; Return values .: On Success - Returns 2d array with Column names in index[0] and rows of data
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Query Call failed
;                    3 - Database error - check _EzMySql_ErrMsg() for error
;                    4 - Dll Store result call failed
;                    5 - Empty $querystring parameter passed to function
;                    6 - Fetch column names failed
;                    7 - Fetch row qty failed
;                    8 - Fetch column qty failed
; Author ........: Yoriz
; ===============================================================================================================================
Func _EzMySql_GetTable2d($querystring)
    Local $aResult
    Local $QueryResult = _EzMySql_Query($querystring)
    If Not $QueryResult Then Return SetError($QueryResult, 0, 0)
    Local $FetchNameResult = _EzMySql_FetchNames()
    If Not IsArray($FetchNameResult) Then Return SetError(6, 0, 0)
    Local $iRows = _EzMySql_Rows()
    If $iRows = -1 Then Return SetError(7, 0, 0)
    Local $iColumns = _EzMySql_Columns()
    If $iColumns = -1 Then Return SetError(8, 0, 0)
    Local $aResult[$iRows + 1][$iColumns]
    For $i = 0 To $iColumns - 1 Step 1
        $aResult[0][$i] = $FetchNameResult[$i]
    Next
    If $iRows Then
        For $iRowNo = 1 To $iRows
            $aResultFetched = _EzMySql_FetchData()
            For $iColumnNo = 0 To $iColumns - 1 Step 1
                $aResult[$iRowNo][$iColumnNo] = $aResultFetched[$iColumnNo]
            Next
        Next
    EndIf
    Return $aResult
EndFunc   ;==>_EzMySql_GetTable2d

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_AddTable2d
; Description ...: Add an array of data to a speicifed table
; Syntax.........: _EzMySql_AddTable2d($sTableName, $aData, $sDelimeter)
; Parameters ....: $sTableName - Name of the table to add data to
; Parameters ....: $aData      - An array of data to add with the column names in index 0
; Return values .: On Success - Returns 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Query Call failed
;                    3 - Database error - check _EzMySql_ErrMsg() for error
;                    4 - Dll Store result call failed
;                    5 - Empty $querystring parameter passed to function
;                    6 - Emprty $sTableName parameter passed to function
;                    7 - $aData is not an array
;                    9 - $aData is not a 2d array
; Author ........: Yoriz
Func _EzMySql_AddTable2d($sTableName, $aData)
    Local $querystring, $iResult
    If Not $sTableName Then Return SetError(6, 0, 0)
    If Not IsArray($aData) Then Return SetError(7, 0, 0)
    If Not UBound($aData) > 1 Then Return SetError(8, 0, 0)
    If Not UBound($aData, 2) Then Return SetError(9, 0, 0)
    Local $iColumns = UBound($aData,2)-1
    For $iRow = 1 To UBound($aData)-1
        $querystring &= "INSERT INTO " & $sTableName & " ("
        For $i = 0 To $iColumns Step 1
            $querystring &= $aData[0][$i] & ","
        Next
        $querystring = StringTrimRight($querystring, 1)
        $querystring &= ") VALUES ('"
        For $i = 0 To $iColumns Step 1
            $querystring &= $aData[$iRow][$i] & "','"
        Next
        $querystring = StringTrimRight($querystring, 2)
        $querystring &= ");"
    Next
    If Not _EzMySql_Exec($querystring) Then Return SetError(@error, 0, 0)
    Return 1
EndFunc

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Changes
; Description ...: After executing a statement returns the number of rows changed
; Syntax.........: _EzMySql_FetchNames()
; Parameters ....: None
; Return values .: On Success - Returns the number of rows changed
; Return values .: On Failure - returns -1 and @error value
;                    1 - Dll column Call failed
;                    2 - A MySQL struct does not exist
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Changes()
    If Not $hEzMySql_Ptr Then Return SetError(2, 0, -1)
    Local $row = DllCall($hEzMySql_Dll, "uint64", "mysql_affected_rows", "ptr", $hEzMySql_Ptr)
    If @error Then Return SetError(1, 0, -1)
    Return $row[0]
;~  Return __MySQL_ReOrderULONGLONG($row[0])
EndFunc   ;==>_EzMySql_Changes

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_ErrMsg
; Description ...: returns a null-terminated string containing the error message for the most recen function that failed.
; Syntax.........: _EzMySql_ErrMsg()
; Parameters ....: None
; Return values .: On Success - A null-terminated character string that describes the error. An empty string if no error occurred
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_ErrMsg()
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    Local $errors = DllCall($hEzMySql_Dll, "str", "mysql_error", "ptr", $hEzMySql_Ptr)
    If @error Then Return SetError(2, 0, 0)
    If $errors[0] Then Return $errors[0]
    Return 0
EndFunc   ;==>_EzMySql_ErrMsg

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_InsertID
; Description ...: Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement.
; Syntax.........: _EzMySql_InsertID()
; Parameters ....: None
; Return values .: On Success - AUTO_INCREMENT columnID
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_InsertID()
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    Local $row = DllCall($hEzMySql_Dll, "uint64", "mysql_insert_id", "ptr", $hEzMySql_Ptr)
    If @error Then Return SetError(2, 0, 0)
    Return $row[0]
EndFunc   ;==>_EzMySql_InsertID

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Close
; Description ...: Closes MySql Database
; Syntax.........: _EzMySql_Close()
; Parameters ....: None
; Return values .: On Success - 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Close()
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    If $sEzMySql_Result Then _EzMySql_QueryFinalize()
    DllCall($hEzMySql_Dll, "none", "mysql_close", "ptr", $hEzMySql_Ptr)
    If @error Then Return SetError(2, 0, 0)
    Return 1
EndFunc   ;==>_EzMySql_Close

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_ShutDown
; Description ...: Closes MySQL DLL to free memory used by MySQL and closes Dll
; Syntax.........: _EzMySql_ShutDown()
; Parameters ....: None
; Return values .: None
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_ShutDown()
    DllCall($hEzMySql_Dll, "none", "mysql_server_end")
    DllClose($hEzMySql_Dll)
    $hEzMySql_Ptr = 0
    $hEzMySql_Dll = 0
EndFunc   ;==>_EzMySql_ShutDown

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_MultiLine
; Description ...: Allow multiple statements in a single string (separated by “;”).
; Syntax.........: _EzMySql_MultiLine($fBol = True)
; Parameters ....: $fBol - True = on, False = off
; Return values .: On Success - 1
; Return values .: On Failure - returns 0 and @error value
;                    1 - A MySQL struct does not exist
;                    2 - Dll Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_MultiLine($fBol = True)
    $sEzMySql_Mutltiline = 1
    If $fBol Then $sEzMySql_Mutltiline = 0
    If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)
    Local $mysql = DllCall($hEzMySql_Dll, "int", "mysql_set_server_option", "ptr", $hEzMySql_Ptr, "dword", $sEzMySql_Mutltiline)
    If @error Then Return SetError(2, 0, 0)
    Return 1
EndFunc   ;==>_EzMySql_MultiLine

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Fetch_Row
; Description ...: Retrieves the next row of a result set.
; Syntax.........: _EzMySql_Fetch_Row()
; Parameters ....: None
; Return values .: On Success - DLLStruct with pointers to data fields
; Return values .: On Failure - returns 0 and @error value
;                    1 - Dll column Call failed or Coloumns = 0
;                    2 - Dll fetch row Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Fetch_Row()
    Local $numberOfFields = _EzMySql_Columns()
    If $numberOfFields <= 0 Then Return SetError(2, 0, 0)
    Local $row = DllCall($hEzMySql_Dll, "ptr", "mysql_fetch_row", "ptr", $sEzMySql_Result)
    If @error Then Return SetError(1, 0, 0)
    Return DllStructCreate("ptr[" & $numberOfFields & "]", $row[0])
EndFunc   ;==>_EzMySql_Fetch_Row

; #FUNCTION# ====================================================================================================================
; Name...........: _EzMySql_Fetch_Lengths
; Description ...: Returns the lengths of the columns of the current row within a result set.
; Syntax.........: _EzMySql_Fetch_Lengths()
; Parameters ....: None
; Return values .: On Success - DLLStruct with ulong Array get data [ DLLStructGetData($struct,1, $n ) ]
; Return values .: On Failure - returns 0 and @error value
;                    1 - Dll column Call failed or Coloumns = 0
;                    2 - Dll fetch length Call failed
; Author ........: Yoriz
; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy
; ===============================================================================================================================
Func _EzMySql_Fetch_Lengths()
    Local $numberOfFields = _EzMySql_Columns()
    If $numberOfFields <= 0 Then Return SetError(1, 0, 0)
    Local $lengths = DllCall($hEzMySql_Dll, "ptr", "mysql_fetch_lengths", "ptr", $sEzMySql_Result)
    If @error Then Return SetError(2, 0, 0)
    Return DllStructCreate("ulong lengths[" & $numberOfFields & "]", $lengths[0])
EndFunc   ;==>_EzMySql_Fetch_Lengths

;===============================================================================
; Function Name:   _EzMySql_PtrStringRead
; Description::    Reads a string by pointer
; Parameter(s):    $ptr       - Pointer to String
;                  $IsUniCode - Is a unicode string default. False
; Requirement(s):  libmysql.dll
; Return Value(s): read string
; Author(s):       Prog@ndy
;===============================================================================
Func _EzMySql_PtrStringRead($ptr, $IsUniCode = False, $StringLen = -1)
    Local $UniCodeString = ""
    If $IsUniCode Then $UniCodeString = "W"
    If $StringLen < 1 Then $StringLen = _EzMySql_PtrStringLen($ptr, $IsUniCode)
    If $StringLen < 1 Then Return SetError(1, 0, "")
    Local $struct = DllStructCreate($UniCodeString & "char[" & ($StringLen + 1) & "]", $ptr)
    Return DllStructGetData($struct, 1)
EndFunc   ;==>_EzMySql_PtrStringRead

;===============================================================================
; Function Name:   _EzMySql_PtrStringLen
; Description::    Gets length for a string by pointer
; Parameter(s):    $ptr       - Pointer to String
;                  $IsUniCode - Is a unicode string default. False
; Requirement(s):  libmysql.dll
; Return Value(s): Length of the string
; Author(s):       Prog@ndy
;===============================================================================
Func _EzMySql_PtrStringLen($ptr, $IsUniCode = False)
    Local $UniCodeFunc = ""
    If $IsUniCode Then $UniCodeFunc = "W"
    Local $Ret = DllCall("kernel32.dll", "int", "lstrlen" & $UniCodeFunc, "ptr", $ptr)
    If @error Then Return SetError(1, 0, -1)
    Return $Ret[0]
EndFunc   ;==>_EzMySql_PtrStringLen

Edit 20/6/2010 added _EzMySql_AddTable2d

Edited by Yoriz
mLipok, PoojaKrishna, robertocm and 1 other reacted to this
GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post

Link to post
Share on other sites

UnknownWarrior    0

UnknownWarrior
Posted

Nice ;)... I know it's an outdated-ish thread, but this is exactly what I need. Appears the other UDFs don't work anymore.

Any chance of a Column data retriever? I need all data from column in an array (array[0] amount of data, array[n] the data)

Thanks

Share this post

Link to post
Share on other sites

dmob    38

dmob
Posted

Gives me (crazy) idea to create app that uses SQLite for local (single user) access

and MySQL for network / multi-user access.... hmmmm

Skysnake reacted to this

Share this post

Link to post
Share on other sites

Yoriz    6

Yoriz
Posted (edited)

Sorry not used autoit or been on forum in a while, the download still works ok for me.

Edited by Yoriz
GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post

Link to post
Share on other sites

Reekod    1

Reekod
Posted (edited)

Hello,

is the connection to a distant web DB is possible 

If Not _EzMySql_Open("myserver.net", "root", $Pass, "", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS test_DB") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SelectDB("_DB1") Then
    MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

where can i sepcify the database name ? is 3306 the tcp port ?

how correctly do a connection to :

host : MyServer.net

user : root

$pass : 'pass'

DB : _DB1

Thanks for help

Regards

Edited by Reekod

Share this post

Link to post
Share on other sites

Manic    0

Manic
Posted

Reekod,

did you manage to connect to your host? I'm still trying. Does anyone know how to connect to your host? Or any functions for this MySQL addition to AutoIt?

Thank you!

Manic

Share this post

Link to post
Share on other sites

dixonpete    0

dixonpete
Posted

I'm trying to use _EzMySql_SelectDB() to verify that a database exists but it seems to always return 1 whether or not it's successful. A known bug?

Here's the EZMySQL UDF code:

; #FUNCTION# ====================================================================================================================

; Name...........: _EzMySql_SelectDB

; Description ...: Causes the database specified by db to become the default

; Syntax.........: _EzMySql_SelectDB($Database)

; Parameters ....: $Database - The new default database name

; Return values .: On Success - 1

; Return values .: On Failure - returns 0 and @error value

; 1 - A MySQL struct does not exist

; 2 - Dll Open Call failed

; Author ........: Yoriz

; Based on script: MySQL UDFs working with libmysql.dll by Prog@ndy

; ===============================================================================================================================

Func _EzMySql_SelectDB($Database)

If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)

Local $conn = DllCall($hEzMySql_Dll, "int", "mysql_select_db", "ptr", $hEzMySql_Ptr, "str", $Database)

If @error Then Return SetError(2, 0, 1)

Return 1

EndFunc ;==>_EzMySql_SelectDB

Share this post

Link to post
Share on other sites

xtehbx    0

xtehbx
Posted (edited)

Have some problem with charset, may be somebody can help me.

In my programm on my Cirilic PC i Use code like this:

_EzMySql_Startup()
  $mysqlconnect =  _EzMySql_Open($host,$user,$password,$database,$port)
  If $mysqlconnect = 0 Then
   MsgBox(0,"Error", "Can`t connect to database")
   Return 0
  EndIf
  _EzMySql_Query("SET NAMES cp1251")

And all cyrilic data is ok. New Inserted and Selected from DB to GUI.

But on Chines PC, i have a problem with charset, i cant see in GUI Cirilic data and can`t insert in DB Cyrilic data.

What "SET NAMES " i should use on chines pc

HKEY_CURRENT_USER\Control Panel\International\Locale = 00000804.

My database has charset and collation utf8_general_ci, and mysql server also utf8_general_ci at any parameter.

Was tryed any chinese charset but result was in my GUI " ??????????? "

Edited by xtehbx

Share this post

Link to post
Share on other sites

shx    0

shx
Posted

Capturing the SQL error code

I am testing out trying to insert a duplicate using _EzMySql_Exec .

What I'm finding is that even though the code fails because it is trying to insert a duplicate it doesn't come back with an error and an SQL error message. ( looking at @error and & _EzMySql_ErrMsg().

Is there any way of capturing SQL errors that occur?

Steven

Share this post

Link to post
Share on other sites

kpuk    0

kpuk
Posted

I use this UDF and needed to escape string. That I little rewrote this func (based on Prog@ndy UDF function) to use with EzMySql.au3

Func _EzMySql_Real_Escape_String($From, $FromLen = Default)
If Not $hEzMySql_Ptr Then
  MsgBox(0, "", "errer")
  Return SetError(1, 0, 0)
EndIf
If $FromLen <= 0 Or $FromLen = Default Then $FromLen = StringLen($From)
Local $TO = DllStructCreate("char[" & $FromLen * 2 + 1 & "]")
Local $query = DllCall($hEzMySql_Dll, "int", "mysql_real_escape_string", "ptr", $hEzMySql_Ptr, "ptr", DllStructGetPtr($TO), "str", $From, "ulong", $FromLen)
If @error Then Return SetError(1, 0, 0)
Return StringLeft(DllStructGetData($TO, 1), $query[0])
EndFunc   ;==>_EzMySql_Real_Escape_String

ps: sorry for my English

Share this post

Link to post
Share on other sites

shlasi    0

shlasi
Posted

Hi guys,

I'm trying to set the connection to my DB,

I put my host, password, DB name and user to the _EzMySql_Open function.

That's all I did with it, now, when running the script I get all kinds of errors about "missing separator character after keyword".

Don't know exactly what it meens...

Anyone?

Share this post

Link to post
Share on other sites

heavengrace    0

heavengrace
Posted

Nice job, It is work for Chinese, Great!

#include "EzMySql.au3"
#include <Array.au3>
#cs
http://www.autoitscript.com/forum/topic/116072-ezmysql-udf-use-mysql-databases-with-autoit/?hl=%2Bezmysql#entry1005253
#ce
If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

Local $strHost = "127.0.0.1", $strUID = "root", $strPass = "**********", $strDB = "test"

If Not _EzMySql_Open($strHost, $strUID, $strPass, $strDB, "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS EzMySqlTest") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SelectDB("EzMySqlTest") Then
    MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$sMySqlStatement = "CREATE TABLE IF NOT EXISTS TestTable (" & _
                   "RowID INT NOT NULL AUTO_INCREMENT," & _
                   "Name TEXT NOT NULL ," & _
                   "Age INT NOT NULL ," & _
                   "EyeColour TEXT NOT NULL ," & _
                   "HairColour TEXT NOT NULL ," & _
                   "PRIMARY KEY (`RowID`) ," & _
                   "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"

If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

;Local $aEyeColours[7] = ["Amber","Blue","Brown","Grey","Green","Hazel","Red"]
;Local $aHairColours[6] = ["Brown","Black","Blond","Grey","Green","Pink"]
Local $aEyeColours[7] = ["琥珀","藍","棕","灰","綠","淡褐","紅"]
Local $aHairColours[6] = ["棕","黑","亞麻","灰","綠","桃紅"]

Local $sMySqlStatement = ""
For $i = 1 To 50 Step 1
    $sMySqlStatement &= "INSERT INTO TestTable (Name,Age,EyeColour,HairColour) VALUES (" & _
                        "'Person" & $i & "'," & _
                        "'" & Random(1, 100, 1) & "'," & _
                        "'" & $aEyeColours[Random(0, 6, 1)] & "'," & _
                        "'" & $aHairColours[Random(0, 5, 1)] & "');"
Next

If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

;MsgBox(0, "Last AUTO_INCREMENT columnID2", _EzMySql_InsertID())
ConsoleWrite("!mysql> Last AUTO_INCREMENT columnID2  = " & _EzMySql_InsertID() & @CRLF)

;$aOk = _EzMySql_GetTable2d("SELECT Name,Age,EyeColour,HairColour FROM TestTable WHERE EyeColour = '"& $aEyeColours[Random(0, 6, 1)] & "';")
$aOk = _EzMySql_GetTable2d("SELECT Name,Age,EyeColour,HairColour FROM TestTable WHERE EyeColour = '"& $aEyeColours[Random(0, 6, 1)] & "'" & " AND Age >= '30' AND Age <= '40' " & ";")

$error = @error
If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error)
_ArrayDisplay($aOk, "2d Array Names of certain eyecolour")

MsgBox(0, "", "Following is how to get a row at a time of a query as a 1d array")
If Not _EzMYSql_Query("SELECT * FROM TestTable WHERE HairColour = '"& $aHairColours[Random(0, 5, 1)] & "' LIMIT 5;") Then
MsgBox(0, "Query Error", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

For $i = 1 To _EzMySql_Rows() Step 1
    $a1Row = _EzMySql_FetchData()
    _ArrayDisplay($a1Row, "Result: " & $i)
Next

_EzMySql_Exec("DROP TABLE TestTable")

_EzMySql_Close()
_EzMySql_ShutDown()
Exit

Share this post

Link to post
Share on other sites

ChrisAnderson    0

ChrisAnderson
Posted

I realize this is a very old topic, but whenever I go to parse through my MySQL DB any text because a number - not the text..

 ;****************************************
 ; Get all Non-Active Articles
 ;****************************************
 If Not _EzMYSql_Query('SELECT * FROM rars_articles WHERE IsActive = 1;') Then
 MsgBox(0, 'Query Error', 'Error: ' & @error & @CR & 'Error string: ' & _EzMySql_ErrMsg())
  Exit
 Else
  For $i = 1 To _EzMySql_Rows() Step 1
   $a1Row = _EzMySql_FetchData()
   MsgBox (0, "ID ", $a1Row[0] + 0)
   MsgBox (0, "Date", $a1Row[1] + 0)
   MsgBox (0, "Section", $a1Row[2] + 0)
   MsgBox (0, "Title", $a1Row[3] + 0)
   MsgBox (0, "Author", $a1Row[4] + 0)
   ;_ArrayDisplay($a1Row, "Result: " & $i)
  Next
 EndIf

Where this should return a Text return for "Section", "Title", and "Author" it is returning 0 for each of those rows. Yet _ArrayDisplay displays them correctly:

arraydisplay.png

What am I doing wrong, please?

Share this post

Link to post
Share on other sites

checlever    0

checlever
Posted

I realize this is a very old topic, but whenever I go to parse through my MySQL DB any text because a number - not the text..

 ;****************************************
 ; Get all Non-Active Articles
 ;****************************************
 If Not _EzMYSql_Query('SELECT * FROM rars_articles WHERE IsActive = 1;') Then
 MsgBox(0, 'Query Error', 'Error: ' & @error & @CR & 'Error string: ' & _EzMySql_ErrMsg())
  Exit
 Else
  For $i = 1 To _EzMySql_Rows() Step 1
   $a1Row = _EzMySql_FetchData()
   MsgBox (0, "ID ", $a1Row[0] + 0)
   MsgBox (0, "Date", $a1Row[1] + 0)
   MsgBox (0, "Section", $a1Row[2] + 0)
   MsgBox (0, "Title", $a1Row[3] + 0)
   MsgBox (0, "Author", $a1Row[4] + 0)
   ;_ArrayDisplay($a1Row, "Result: " & $i)
  Next
 EndIf

Where this should return a Text return for "Section", "Title", and "Author" it is returning 0 for each of those rows. Yet _ArrayDisplay displays them correctly:

arraydisplay.png

What am I doing wrong, please?

Have you tried without the "+ 0"? If you want to use that to alter the index you should put it inside the braquets ie MsgBox (0, "ID ", $a1Row[1 + 0]) although I don't know why you would want to do something like that since you already know what index you need.

 

Share this post

Link to post
Share on other sites

jofabian    0

jofabian
Posted (edited)

Maybe you wanna add this to EzMySql.au3

Func _EzMySql_Real_Escape_String($From, $FromLen = Default)
   If Not $hEzMySql_Ptr Then Return SetError(1, 0, 0)

   If $FromLen <= 0 Or $FromLen = Default Then $FromLen = StringLen($From)

   Local $TO = DllStructCreate("char[" & $FromLen * 2 + 1 & "]")
   Local $query = DllCall($hEzMySql_Dll, "int", "mysql_real_escape_string", "ptr", $hEzMySql_Ptr, "ptr", DllStructGetPtr($TO), "str", $From, "ulong", $FromLen)
   If @error Then Return SetError(2, 0, 0)
   Return StringLeft(DllStructGetData($TO, 1), $query[0])
EndFunc

This function allow me to escape some strings before I send them in a query to MySQL.

I did copy this from ">MySQL UDFs (without ODBC)" and adapted it to EzMySql, it works for me but maybe the autor of EzMySql should take a look of it.

Good Luck.

Edited by jofabian

Share this post

Link to post
Share on other sites

jchd    1,514

jchd
Posted

I don't use MySQL but it looks like for security reasons Addcslashes() would be a better shield. Second remark: your code is going to translate native AutoIt Unicode strings into ANSI, which is probably not suitable for many users.

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

Morronic    0

Morronic
Posted (edited)

In response to ChrisAnderson's Question. Try something like this. 

#include "EzMySql.au3"

$ipAddress = @IPAddress1

_EzMySql_Startup("\MYSQL")
$sqlConnect = _EzMySql_Open($ipAddress, "Username", "Password", "Database", 3306, "", 0) ;This opens a connection to the mysql database. Change Username Password and Database to YOUR mysql Database settings.

_EzMySql_Query('SELECT * FROM News') ;News is the table that I am getting data from. Change News to the name of the Table you want to query.
$myRows = _EzMySql_Rows() ;Just counts the rows that have data in them. 
_EzMySql_QueryFinalize()

$i = 1
$row = _EzMySql_GetTable2d('SELECT * FROM News') ;Makes a 2d array of data.
_EzMySql_QueryFinalize()

    Do
        MsgBox(0, "", $row[$i][1] & @CRLF & $row[$i][2])
    $i = $i + 1
     Until $i = $myRows + 1
_EzMySql_Close()

This works for me because I only have 2 fields of data. If you have a bunch of fields of data you would have to go much higher than two in the MsgBox. For example. 

Let's say I had 4 fields of Data. I would have to call the array like this:

MsgBox(0, "", $row[$i][1] & @CRLF & $row[$i][2] & @CRLF & $row[$i][3] & @CRLF & $row[$i][4])

This would display all 4 fields of information. This works because _EzMySql_GetTable2d() function makes a 2d array. You pull out the data from the array by using Column and Row. Column is number up and down. Row is number left and right. Which is why we call the variable $row like $row[1][1]. That says we are displaying data from Column1, Row1. Hopefully this helps anyone else struggling with this UDF as well. 

Edited by Morronic

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
Go To Topic Listing

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...