Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

EzMySql UDF - Use MySql Databases with autoit


  • Please log in to reply
19 replies to this topic

#1 Yoriz

Yoriz

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 356 posts

Posted 19 June 2010 - 04:32 PM

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
AutoIt         
#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
AutoIt         
#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, 22 June 2010 - 05:44 PM.

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.







#2 UnknownWarrior

UnknownWarrior

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 360 posts

Posted 05 October 2010 - 09:01 PM

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

#3 dmob

dmob

    Polymath

  • Active Members
  • PipPipPipPip
  • 237 posts

Posted 06 October 2010 - 01:21 PM

Gives me (crazy) idea to create app that uses SQLite for local (single user) access
and MySQL for network / multi-user access.... hmmmm

#4 rosmild

rosmild

    Seeker

  • New Members
  • 4 posts

Posted 03 December 2010 - 02:36 PM

Hi Yoriz,
It seems to be a very nice job, but I'm not able to donwload your files.
matteo

#5 Yoriz

Yoriz

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 356 posts

Posted 07 January 2011 - 08:35 PM

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

Edited by Yoriz, 07 January 2011 - 08:40 PM.

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.

#6 Reekod

Reekod

    Seeker

  • Active Members
  • 33 posts

Posted 08 April 2011 - 09:23 PM

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, 08 April 2011 - 09:24 PM.


#7 Reekod

Reekod

    Seeker

  • Active Members
  • 33 posts

Posted 08 April 2011 - 09:43 PM

ok i find in the include the opensql function
connection is ok now

great job

#8 Manic

Manic

    Seeker

  • Active Members
  • 25 posts

Posted 30 June 2011 - 07:14 PM

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

#9 dixonpete

dixonpete

    Seeker

  • Active Members
  • 27 posts

Posted 29 September 2011 - 04:38 PM

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

#10 xtehbx

xtehbx

    Seeker

  • New Members
  • 3 posts

Posted 10 October 2011 - 12:49 PM

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, 10 October 2011 - 12:51 PM.


#11 shx

shx

    Wayfarer

  • Active Members
  • Pip
  • 58 posts

Posted 21 March 2012 - 04:54 PM

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

#12 kpuk

kpuk

    Seeker

  • Normal Members
  • 9 posts

Posted 13 July 2012 - 11:50 AM

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

#13 shlasi

shlasi

    Seeker

  • Normal Members
  • 8 posts

Posted 20 July 2012 - 07:28 AM

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?

#14 kpuk

kpuk

    Seeker

  • Normal Members
  • 9 posts

Posted 27 July 2012 - 10:57 AM

may be you write your code (or it part) here? It help undersatnd where you wrong.

#15 heavengrace

heavengrace

    Seeker

  • Normal Members
  • 2 posts

Posted 14 March 2014 - 04:00 AM

Nice job, It is work for Chinese, Great!

AutoIt         
  1. #include "EzMySql.au3"
  2. #include <Array.au3>
  3. #cs
  4. http://www.autoitscript.com/forum/topic/116072-ezmysql-udf-use-mysql-databases-with-autoit/?hl=%2Bezmysql#entry1005253
  5. #ce
  6. If Not _EzMySql_Startup() Then
  7.     MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  8.     Exit
  9.  
  10. Local $strHost = "127.0.0.1", $strUID = "root", $strPass = "**********", $strDB = "test"
  11.  
  12. If Not _EzMySql_Open($strHost, $strUID, $strPass, $strDB, "3306") Then
  13.     MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  14.     Exit
  15.  
  16. If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS EzMySqlTest") Then
  17.     MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  18.     Exit
  19.  
  20. If Not _EzMySql_SelectDB("EzMySqlTest") Then
  21.     MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  22.     Exit
  23.  
  24. $sMySqlStatement = "CREATE TABLE IF NOT EXISTS TestTable (" & _
  25.                    "RowID INT NOT NULL AUTO_INCREMENT," & _
  26.                    "Name TEXT NOT NULL ," & _
  27.                    "Age INT NOT NULL ," & _
  28.                    "EyeColour TEXT NOT NULL ," & _
  29.                    "HairColour TEXT NOT NULL ," & _
  30.                    "PRIMARY KEY (`RowID`) ," & _
  31.                    "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"
  32.  
  33. If Not _EzMySql_Exec($sMySqlStatement) Then
  34.     MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  35.     Exit
  36.  
  37. ;Local $aEyeColours[7] = ["Amber","Blue","Brown","Grey","Green","Hazel","Red"]
  38. ;Local $aHairColours[6] = ["Brown","Black","Blond","Grey","Green","Pink"]
  39. Local $aEyeColours[7] = ["琥珀","藍","棕","灰","綠","淡褐","紅"]
  40. Local $aHairColours[6] = ["棕","黑","亞麻","灰","綠","桃紅"]
  41.  
  42. Local $sMySqlStatement = ""
  43. For $i = 1 To 50 Step 1
  44.     $sMySqlStatement &= "INSERT INTO TestTable (Name,Age,EyeColour,HairColour) VALUES (" & _
  45.                         "'Person" & $i & "'," & _
  46.                         "'" & Random(1, 100, 1) & "'," & _
  47.                         "'" & $aEyeColours[Random(0, 6, 1)] & "'," & _
  48.                         "'" & $aHairColours[Random(0, 5, 1)] & "');"
  49.  
  50. If Not _EzMySql_Exec($sMySqlStatement) Then
  51.     MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  52.     Exit
  53.  
  54. ;MsgBox(0, "Last AUTO_INCREMENT columnID2", _EzMySql_InsertID())
  55. ConsoleWrite("!mysql> Last AUTO_INCREMENT columnID2  = " & _EzMySql_InsertID() & @CRLF)
  56.  
  57. ;$aOk = _EzMySql_GetTable2d("SELECT Name,Age,EyeColour,HairColour FROM TestTable WHERE EyeColour = '"& $aEyeColours[Random(0, 6, 1)] & "';")
  58. $aOk = _EzMySql_GetTable2d("SELECT Name,Age,EyeColour,HairColour FROM TestTable WHERE EyeColour = '"& $aEyeColours[Random(0, 6, 1)] & "'" & " AND Age >= '30' AND Age <= '40' " & ";")
  59.  
  60. $error = @error
  61. If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error)
  62. _ArrayDisplay($aOk, "2d Array Names of certain eyecolour")
  63.  
  64. MsgBox(0, "", "Following is how to get a row at a time of a query as a 1d array")
  65. If Not _EzMYSql_Query("SELECT * FROM TestTable WHERE HairColour = '"& $aHairColours[Random(0, 5, 1)] & "' LIMIT 5;") Then
  66. MsgBox(0, "Query Error", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
  67.     Exit
  68.  
  69. For $i = 1 To _EzMySql_Rows() Step 1
  70.     $a1Row = _EzMySql_FetchData()
  71.     _ArrayDisplay($a1Row, "Result: " & $i)
  72.  
  73. _EzMySql_Exec("DROP TABLE TestTable")
  74.  
  75. _EzMySql_Close()
  76. _EzMySql_ShutDown()


#16 ChrisAnderson

ChrisAnderson

    Seeker

  • New Members
  • 1 posts

Posted 18 March 2014 - 12:02 AM

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?



#17 checlever

checlever

    Seeker

  • Active Members
  • 19 posts

Posted 01 April 2014 - 10:47 PM

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.
 



#18 jofabian

jofabian

    Seeker

  • New Members
  • 1 posts

Posted 02 July 2014 - 09:06 PM

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, 02 July 2014 - 09:07 PM.


#19 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 5,321 posts

Posted 02 July 2014 - 09:42 PM

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.


SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#20 Morronic

Morronic

    Seeker

  • Active Members
  • 12 posts

Posted 05 September 2014 - 06:47 AM

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, 05 September 2014 - 06:52 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users