Leaderboard

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

Hello Guys! . I have been busy with my exams... They are finished now, so summer holidays! I am been working very hard to bring back the reputation for AutoIt in IRC, I recently made a unofficial channel for AutoIt at freenode (freenode is a very popular IRC Network for FOSS [Free and Open Source Software]). You can check this topic if you want: Introduction This tutorial will guide you from start to finish covering each and every small step (no matter how small to make it more IRC newbie friendly) so that everyone can enjoy the benefits of IRC and the IRC Community. Please read the whole post for better understanding. Ok, lets begin our IRC adventure! Installing HexChat You might wonder why do you need HexChat, why not use the free and no installation needed online IRC client? Well, most online clients might be quick and easy to setup but don't offer much option and power, moreover they even show your IP in the public! For the above reason, we are using a neat desktop client called HexChat! (Its FOSS too!) Here are the steps you need to follow: 1. As you might have expected, you need to download the installer first. Here is the link to the download page: http://hexchat.github.io/downloads.html. You need to select the best option for you, here are some pointers: 2. After downloading the installer, install it... Here is a small video which I made to help you : Configuring HexChat This is the most important step in our journey, Configuration. After launching HexChat, it will prompt you to configure it, simply follow these steps: 1. This is the initial window, find "freenode" in the "Networks" List: 2. Select it and click "Favour" (optional but recommended). 3. Change the values in the input boxes as you wish (I have set mine in the screenshot). 4. Click edit and you will be prompted by a screen, click the "Autojoin channels" and add "##AutoIt" to it. 5. Click "Close" 6. Click "Connect" in the previous window and wait for it to automatically connect to ##AutoIt . 7. Vola! You are done! You can now chat like blah blah blah..... Some extra optional work 1. Type "/msg NickServ REGISTER <any password> <your email address>" 2. Verify your email 3. Open network manager by doing Ctrl + S 4. Find freenode in the network list and click edit 5. Enter your password and click "Close" 6. Click on the "freenode" tab and enter this command: "/stats p". You will get a list of active staff members: 7. Type "/query <nickname of the staff member> <message>" Replace <message> with a message asking for a "cloak", something like this would do the trick: "Hello, I want a cloak for account" 8. Wait for the staff member to give you the cloak. That's it! You are done! End comments by the author of this tutorial I am glad that you are still reading until the end , You have just made a BIG contribution to freenode ##AutoIt IRC Community! Thank you very much for that IF YOU HAVE ANY PROBLEMS, FEEL FREE TO PM ME HERE OR /msg TheDcoder <message> ON IRC.

The snippet TE posted don't throw's the error. The crash is a result of the code he hasn't posted maybe the real callback func. And nobody can, without seeing code showing the issue, do more then saying helpfile is your friend, all other is wasted time. Reading in tea leaves and analyzing will give better result => a good cup of tea.

The helpfile will explain the 2 formats for If..Else--EndIf statements we have. Jos

Case $nMsg = $OkayButton If GUICtrlRead($ShortcutInput) = "" Or GUICtrlRead($OriginalInput) = "" Then MsgBox($MB_SYSTEMMODAL, "Error", ("Please select a two directories.")) ElseIf FileExists(GUICtrlRead($ShortcutInput)) = False Or FileExists(GUICtrlRead($OriginalInput)) = False Then MsgBox($MB_SYSTEMMODAL, "Error", ("Something went wrong. Please select a valid directory.")) Else MsgBox($MB_SYSTEMMODAL, "Good", ("All is good.")) EndIf

If GUICtrlRead($ShortcutInput) = "" Or GUICtrlRead($OriginalInput) = "" Or FileExists(GUICtrlRead($ShortcutInput)) = False Or FileExists(GUICtrlRead($OriginalInput)) = False Then MsgBox($MB_SYSTEMMODAL, "Error", ("Something went wrong. Please select a valid directory.")) Else MsgBox($MB_SYSTEMMODAL, "Good", ("All is good.")) Endif

Make sure $i is set to the number zero and not the letter o? Also, I'm curious why people use Select...Case? Since autoit supports swtiching on strings (some languages don't) I think Switch ($i) Case 0 Case 1 Case 2 EndSwitch Looks better and cleaner than Select Case $i = 0 Case $i = 1 Case $i = 2 EndSelect And switch is usually faster. Just doing some random tests and it looks like slightly faster when executing on just a few cases, significantly faster (about 1/2 the time) when executing on a lot of cases (12 cases). Both string and number tests. Also, if...then came in last on all of my tests.

I'm not sure what your Bot is doing but If you want people to download it from a website or the internet in general, you will have to compile it. If it is creating, writing and deleting files on the PC, the person who downloads it will need to have those permissions. Also, if you don't include a digital signature in your exe, windows will throw all kinds of Red Flags to the end user along with the browser the user is using to download it. You may want to check out https://www.comodo.com/ to be able to get a digital Signature along with Nullsoft (NSIS) and use it as a installer. To keep the browser from throwing errors you also may want to look into getting a SSL Certificate if you have your own server or shared hosting server.

An example script of cascading combo box. #include <GuiComboBox.au3> #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> ; Create GUI GUICreate("Example Cascading ComboBox", 400, 296) $Combo1 = GUICtrlCreateCombo("", 2, 2, 396, 296) GUICtrlSetData($Combo1, "123|456|789|0") $Combo2 = GUICtrlCreateCombo("", 2, 30, 396, 296) GUISetState(@SW_SHOW) Global $cb1 = _GUICtrlComboBox_GetEditText($Combo1) Do $Msg = GUIGetMsg() Switch $Msg Case $Combo1 $cb1 = _GUICtrlComboBox_GetEditText($Combo1) _GUICtrlComboBox_ResetContent($Combo2) If $cb1 = "123" Then GUICtrlSetData($Combo2, "ABC|DEF") If $cb1 = "456" Then GUICtrlSetData($Combo2, "GHI|JKL") Case $Combo2 MsgBox($MB_SYSTEMMODAL, 'Information', 'ComboBox Selected Item: ' & _GUICtrlComboBox_GetEditText($Combo2)) EndSwitch Until $Msg = $GUI_EVENT_CLOSE GUIDelete()

I just identified a issue while joining the ##AutoIt channel, fixed it now TD P.S The issue was the channel's mode was set to +i (invite-only)

<Reserved for future use>

SQLiteEx v0.5.1 Solved some bugs and mistakes List of public functions _SQLiteEx_Open _SQLiteEx_Get _SQLiteEx_Set _SQLiteEx_Insert _SQLiteEx_Delete _SQLiteEx_QuerySingleRow _SQLiteEx_FirstTableEntry _SQLiteEx_LastTableEntry _SQLiteEx_SetTable _SQLiteEx_TableExists _SQLiteEx_ShowTable _SQLiteEx_DropTable _SQLiteEx_DatabaseExists _SQLiteEx_DropDatabase _SQLiteEx_Close Full code #Region Header #cs Title: SQLite Extending Library for AutoIt3 Filename: SQLiteEx.au3 Description: Set of useful SQLite functions Author: 57ar7up Version: 0.5.1 Last Update: 21/01/14 Requirements: AutoIt v3.3 +, Developed/Tested on Windows 7 Notes: With this UDF you can work only with one database simultaneously, for simplicity Available functions: _SQLiteEx_Open _SQLiteEx_Get _SQLiteEx_Set _SQLiteEx_Insert _SQLiteEx_Delete _SQLiteEx_QuerySingleRow _SQLiteEx_FirstTableEntry _SQLiteEx_LastTableEntry _SQLiteEx_SetTable _SQLiteEx_TableExists _SQLiteEx_ShowTable _SQLiteEx_DropTable _SQLiteEx_DatabaseExists _SQLiteEx_DropDatabase _SQLiteEx_Close Examples: see SQLiteEx_Examples.au3 #ce #Include-once #Include 'SQLite.au3' #Include 'SQLite.dll.au3' #EndRegion Header #Region Global Variables and Constants Global $_sDBExtension = '.sqlite3' ;Database extension Global $_sDBsPath = @ScriptDir & '/' ;Path to databases Global $_hDB = FALSE ;Current database handle Global $_sDBName = FALSE ;Current database name Global $_sTable = FALSE ;Current table name Dim $aSQLiteErrors[24][2] = [ _ [0, 'OK'], _ [1, 'SQL error or missing database'], _ [2, 'An internal logic error in SQLite'], _ [3, 'Access permission denied'], _ [4, 'Callback routine requested an abort'], _ [5, 'The database file is locked'], _ [6, 'A table in the database is locked'], _ [7, 'A malloc() failed'], _ [8, 'Attempt to write a readonly database'], _ [9, 'Operation terminated by sqlite_interrupt()'], _ [10, 'Some kind of disk I/O error occurred'], _ [11, 'The database disk image is malformed'], _ [12, '(Internal Only) Table or record not found'], _ [13, 'Insertion failed because database is full'], _ [14, 'Unable to open the database file'], _ [15, 'Database lock protocol error'], _ [16, '(Internal Only) Database table is empty'], _ [17, 'The database schema changed'], _ [18, 'Too much data for one row of a table'], _ [19, 'Abort due to constraint violation'], _ [20, 'Data type mismatch'], _ [21, 'Library used incorrectly'], _ [22, 'Uses OS features not supported on host'], _ [23, 'Authorization denied'] _ ] #EndRegion Global Variables and Constants ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_Open ; Description....: Opens SQLite database ; Syntax.........: _SQLite_Open ($sDBWay [, $sSQL]) ; $sDBWay - Way to name new DB or find DB file, can be file name in database folder with extension or without (must be $_sDBExtension) ; $sSQL - SQL code to execute, if provided ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; Remarks........: If not exists, new DB created ; ====== Func _SQLiteEx_Open($sDBWay = FALSE, $sSQL = FALSE) ;Returns handle to DB _SQLite_Startup() If @error > 0 Then Exit - 1 EndIf If Not $sDBWay = FALSE Then $sDBPath = $_sDBsPath & $sDBWay If Not FileExists($sDBPath) Then Local $sDrive, $sDir, $sFileName, $sExtension Local $aPathSplit = _PathSplit($sDBPath, $sDrive, $sDir, $sFileName, $sExtension) If $sExtension = $_sDBExtension Then _FileCreate($sDBPath) Else $sDBPath &= $_sDBExtension If NOT FileExists($sDBPath) Then _FileCreate($sDBPath) EndIf EndIf Else $sTempFile = _TempFile(@ScriptFullPath, '', $_sDBExtension) Local $sDrive, $sDir, $sFileName, $sExtension Local $aPathSplit = _PathSplit($sTempFile, $sDrive, $sDir, $sFileName, $sExtension) $sDBPath = $sFileName & $sExtension EndIf $_hDB = _SQLite_Open($sDBPath) If $_hDB <> FALSE Then $_sDBName = __FileBasename($sDBPath) If $sSQL <> FALSE Then $iSQLite_Status = _SQLite_Exec($_hDB, $sSQL) If Not $iSQLite_Status = $SQLite_OK Then Return SetError(1, FALSE, _SQLite_ErrMsg());$aSQLiteErrors[$iSQLite_Status][1]) Else Return TRUE EndIf Else Return TRUE EndIf Else Say('Failed to open DB') Sleep(2000) Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_Get ; Description....: Gets value from specified column ; Syntax.........: _SQLiteEx_Get ($sColumn [, $sWhere]) ; $sColumn - Name of the existing column in current working table ; $sWhere - Optional SQL code inserted after 'WHERE ' ; Return values..: Success - Value ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_Get($sColumn, $sWhere = FALSE) If $_sTable = FALSE Then Return FALSE If $sWhere Then $sSQL = "SELECT " & $sColumn & " FROM " & $_sTable & " WHERE " & $sWhere Else $sSQL = "SELECT " & $sColumn & " FROM " & $_sTable EndIf $aRow = _SQLiteEx_QuerySingleRow($sSQL) If Not IsArray($aRow) Then Return FALSE Else Return $aRow[0] EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_Set ; Description....: Sets value for specified column ; Syntax.........: _SQLiteEx_Set ($Field, $Value [, $sWhere]) ; $sField - Name of the existing column in current working table ; $sValue - Data to set ; $sWhere - Optional SQL code inserted after 'WHERE' ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_Set($Field, $Value, $sWhere = Default) If $_sTable = FALSE Then Return FALSE If $sWhere <> Default Then $sWhereIs = ' WHERE ' & $sWhere Else $sWhereIs = '' EndIf $sSQL = 'SELECT * FROM ' & $_sTable & $sWhereIs $aRow = _SQLiteEx_QuerySingleRow($sSQL) If IsArray($aRow) Then If Not __SQLiteEx_Update($Field, $Value, $sWhere) = FALSE Then Return TRUE Else Return FALSE EndIf Else If Not _SQLiteEx_Insert($Field, $Value) = FALSE Then Return TRUE Else Return FALSE EndIf EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_Insert ; Description....: Inserts row with value at specified column ; Syntax.........: _SQLiteEx_Insert ($Field, $Value) ; $sField - Name of the existing column in current working table ; $sValue - Data to insert ; Return values..: Success - Id of inserted row ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_Insert($Field, $Value) ;Returns inserted id if success If IsArray($Field) Then Local $sField, $sValue = '' For $i = 0 To UBound($Field) - 1 $sField &= $Field[$i] $sValue &= $Value[$i] If $i <> UBound($Field) - 1 Then $sField &= "', '" $sValue &= "', '" EndIf Next Else $sField = $Field $sValue = $Value EndIf If $_sTable = FALSE Then Return FALSE $sSQL = "INSERT INTO " & $_sTable & " ('" & $sField & "') VALUES ('" & $sValue & "');" If _SQLite_Exec(-1, $sSQL) = $SQLite_OK Then Return _SQLite_LastInsertRowID() Else Return SetError(_SQLite_ErrMsg(), '', FALSE) EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_Delete ; Description....: Deletes row with value at specified column ; Syntax.........: _SQLiteEx_Delete ( $sField [, $sValue]) ; $sField - Name of the existing column in current working table ; $sValue - When this value at column, row removed ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_Delete($sField, $sValue) If $_sTable = FALSE Then Return FALSE $sSQL = "SELECT * FROM " & $_sTable & " WHERE " & $sField & "='" & $sValue & "'" If _SQLiteEx_QuerySingleRow($sSQL) = FALSE Then Return FALSE $sSQL = "DELETE FROM " & $_sTable & " WHERE " & $sField & "='" & $sValue & "'" If _SQLite_Exec(-1, $sSQL) = $SQLite_OK Then Return TRUE Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_QuerySingleRow ; Description....: Read out the first row of the result from the specified query. ; Syntax.........: _SQLiteEx_QuerySingleRow ($sSQL) ; $sSQL - SQL code ; Return values..: Success - Array with row data ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_QuerySingleRow($sSQL) Local $aRet _SQLite_QuerySingleRow(-1, $sSQL, $aRet) If IsArray($aRet) Then Return $aRet Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_FirstTableEntry ; Description....: Gets entry with minimum RowID ; Syntax.........: _SQLiteEx_FirstTableEntry () ; Return values..: Success - Array with row data ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_FirstTableEntry() If $_sTable = FALSE Then Return FALSE $sSQL = 'SELECT * FROM ' & $_sTable & ' WHERE ROWID=(SELECT MIN(ROWID) FROM ' & $_sTable & ')'; $aRet = _SQLiteEx_QuerySingleRow($sSQL) If IsArray($aRet) Then Return $aRet Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_LastTableEntry ; Description....: Get entry with maximum RowID ; Syntax.........: _SQLiteEx_LastTableEntry () ; Return values..: Success - Array with row data ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_LastTableEntry() Local $aRet If $_sTable = FALSE Then Return FALSE $sSQL = 'SELECT * FROM ' & $_sTable & ' WHERE ROWID=(SELECT MAX(ROWID) FROM ' & $_sTable & ')'; $aRet = _SQLiteEx_QuerySingleRow($sSQL) If IsArray($aRet) Then Return $aRet Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_SetTable ; Description....: Sets current table to work with ; Syntax.........: _SQLiteEx_SetTable ($sTBL) ; $sTBL - Name of the table ; Return values..: none ; Author.........: 57ar7up ; ====== Func _SQLiteEx_SetTable($sTBL) $_sTable = $sTBL EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_TableExists ; Description....: Checks whether table with specified name exists, if not defined checks current table ; Syntax.........: _SQLiteEx_TableExists ([$sTBL]) ; $sTBL - Name of the table ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_TableExists($sTBL = Default) If $sTBL = Default Then If $_sTable = FALSE Then Return FALSE Else $sTable = $_sTable EndIf Else $sTable = $sTBL EndIf $sSQL = "SELECT name FROM SQLite_master WHERE type='table' AND name='" & $_sTable & "';" $aRet = _SQLiteEx_QuerySingleRow($sSQL) If $aRet[0] <> '' Then Return TRUE Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_ShowTable ; Description....: Checks whether table with specified name exists, if not defined checks current table ; Syntax.........: _SQLiteEx_ShowTable ([$sTBL] [, $sColumn [, $sSortType]]) ; $sTBL - Name of the table, if not specified, will take current ; $sColumn - Name of column to sort ; $sSortType - Type of sorting, ASC or DESC ; Return values..: Success - Shows modal window with table data ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_ShowTable($sTBL = Default, $sColumn = 'RowID', $sSortType = 'ASC') ;Displays current table If $sTBL = Default Then If $_sTable = FALSE Then Return FALSE Else $sTable = $_sTable EndIf Else $sTable = $sTBL EndIf If $_sTable = FALSE Then Return FALSE Local $aResult, $iRows, $iColumns, $iRval $sSQL = 'SELECT * FROM ' & $_sTable & ' ORDER BY ' & $sColumn & ' ' & $sSortType $iRval = _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns) If $iRval = $SQLite_OK Then _ArrayDisplay($aResult, $_sTable) Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_DropTable ; Description....: Deletes table ; Syntax.........: _SQLiteEx_DropTable ($sTBL) ; $sTBL - Name of the table to delete, if not specified, current table will be deleted ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_DropTable($sTBL = Default) If $sTBL = Default Then If $_sTable = FALSE Then Return FALSE Else $sTable = $_sTable EndIf Else $sTable = $sTBL EndIf $sSQL = ('DROP TABLE ' & $_sTable) If _SQLite_Exec($_hDB, $sSQL) = $SQLite_OK Then Return TRUE Else Return FALSE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_DatabaseExists ; Description....: Checks whether database exists, name can be file name with extension or not ; Syntax.........: _SQLiteEx_DatabaseExists ( $sDBName ) ; $sDB - Name of database to delete ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_DatabaseExists($sDBName) If Not FileExists($sDBName) Then $sDBName &= $_sDBExtension If Not FileExists($sDBName) Then Return FALSE EndIf Return TRUE EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_DropDatabase ; Description....: Deletes database ; Syntax.........: _SQLiteEx_DropDatabase ( $sDB) ; $sDBN - Name of database to delete, if not specified, current DB will be deleted ; Return values..: Success - TRUE ; Failure - FALSE ; Author.........: 57ar7up ; ====== Func _SQLiteEx_DropDatabase($sDBN = Default) If $sDBN = Default Then If $_sDBName = FALSE Then Return FALSE Else $sDBName = $_sDBName EndIf Else $sDBName = $sDBN EndIf If $_sDBName <> FALSE And $sDBName = $_sDBName Then _SQLite_Close($_hDB) _SQLite_Shutdown() EndIf $sDBPath = $_sDBsPath & $sDBName & $_sDBExtension If FileExists($sDBPath) Then $iDelete = FileDelete($sDBPath) If $iDelete = 1 Then Return TRUE Return FALSE Else Return TRUE EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _SQLiteEx_Close ; Description....: Closes current database ; Syntax.........: _SQLiteEx_Close () ; Return values..: none ; Author.........: 57ar7up ; ====== Func _SQLiteEx_Close() If $_hDB = FALSE Then $_hDB = -1 _SQLite_Close($_hDB) EndFunc #EndRegion Public Functions Func __SQLiteEx_Query($sSQL) Local $hQuery _SQLite_Query(-1, $sSQL, $hQuery) Return $hQuery EndFunc Func __SQLiteEx_Update($Field, $Value, $sWhere = Default) If IsArray($Field) Then Local $sSet For $i = 0 To UBound($Field) - 1 $sSet &= $Field[$i] & " = '" & $Value[$i] & "'" If $i <> UBound($Field) - 1 Then $sSet &= ", " Next Else $sSet = $Field & " = '" & $Value & "'" EndIf If $sWhere <> Default Then $sWhereIs = ' WHERE ' & $sWhere Else $sWhereIs = '' EndIf If $_sTable = FALSE Then Return FALSE $sSQL = "UPDATE " & $_sTable & " SET " & $sSet & $sWhereIs If _SQLite_Exec(-1, $sSQL) = $SQLite_OK Then Return TRUE Else Return SetError(_SQLite_ErrMsg(), '', FALSE) EndIf EndFunc Func __FileBasename($sFile) Return StringRegExpReplace($sFile, "(.*?[\\/])*(.*?)((?:\.\w+\z|\z))", "$2") EndFunc OnAutoitExitRegister('_SQLite_Shutdown') Examples of using ;-------------------------------------------------------------------------------------------------- ;Examples of work with SQLiteEx - simple SQLite interface for AutoIt ;-------------------------------------------------------------------------------------------------- #Include 'SQLiteEx.au3' $bVerbose = TRUE ;----------------------FIRST DATABASE-------------------------------------------------------------- $sDB_Earth = 'earth_2012' $sTableCountries = 'countries' $sSQL = "CREATE TABLE " & $sTableCountries & " (country text, internet_users integer, population integer);" & @CR $sSQL &= "INSERT INTO " & $sTableCountries & " (country, internet_users, population) VALUES " & @CR $sSQL &= "('Brazil', 99357737, 193946886)," & @CR $sSQL &= "('China', 568192066, 1343239923)," & @CR $sSQL &= "('India', 151598994, 1205073612)," & @CR $sSQL &= "('Japan', 100684474, 127368088)," & @CR $sSQL &= "('Russia', 75926004, 142517670)," & @CR $sSQL &= "('United States', 254295536, 313847465)," & @CR $sSQL &= "('You are here', 2405518376, 7017846922)" & @CR Say('Deleting previous ' & $sDB_Earth & ' database: ' & _SQLiteEx_DropDatabase($sDB_Earth)) Say('Opening database ' & $sDB_Earth & ': ' & _SQLiteEx_Open($sDB_Earth, $sSQL)) _SQLiteEx_SetTable($sTableCountries) Say('Set changed value: ' & _SQLiteEx_Set('country', 'World', "country = 'You are here'")) Say('Getting value - All internet users in the World: ' & _SQLiteEx_Get('internet_users', "country = 'World'")) Dim $aColumns[3] = ['country', 'internet_users', 'population'] Dim $aValues[3] = ['Ancient Rome', 0, 0] Say('Inserting row at Row ID: ' & _SQLiteEx_Insert($aColumns, $aValues)) Say('Deleting row: ' & _SQLiteEx_Delete('country', 'Ancient Rome')) Say('Is table still exists: ' & _SQLiteEx_TableExists()) _SQLiteEx_ShowTable(Default, 'internet_users', 'DESC') Say('Dropping table: ' & _SQLiteEx_DropTable($sTableCountries)) Say('Is table still exists: ' & _SQLiteEx_TableExists()) _SQLiteEx_Close() ;----------------------SECOND DATABASE------------------------------------------------------------- $sDB_SolarSystem = 'solar_system' $sTablePlanets = 'planets' $sSQL2 = "CREATE TABLE " & $sTablePlanets & " (planet text, mass real, length_of_day real, mean_temperature real, ring_system integer);" & @CR $sSQL2 &= "INSERT INTO " & $sTablePlanets & " (planet, mass, length_of_day, mean_temperature, ring_system) VALUES " & @CR $sSQL2 &= "('Mercury', 0.330, 4222.6, 167, 0)," & @CR $sSQL2 &= "('Venus', 4.87, 2802.0, 464, 0)," & @CR $sSQL2 &= "('Earth', 5.97, 24.0, 15, 0)," & @CR $sSQL2 &= "('Moon', 0.073, 708.7, -20, 0)," & @CR $sSQL2 &= "('Mars', 0.642, 24.7, -65, 0)," & @CR $sSQL2 &= "('Jupiter', 1898, 9.9, -110, 1)," & @CR $sSQL2 &= "('Saturn', 568, 10.7, -140, 1)," & @CR $sSQL2 &= "('Uranus', 86.8, 17.2, -195, 1)," & @CR $sSQL2 &= "('Neptune', 102, 16.1, -200, 1)," & @CR $sSQL2 &= "('Pluto', 0.0131, 153.3, -225, 0)" & @CR ;-------------------------------------------------------------------------------------------------- Say('Deleting previous ' & $sDB_SolarSystem & ' database: ' & _SQLiteEx_DropDatabase($sDB_SolarSystem)) Say('Opening database ' & $sDB_SolarSystem & ': ' & _SQLiteEx_Open($sDB_SolarSystem, $sSQL2)) _SQLiteEx_SetTable($sTablePlanets) $aRow = _SQLiteEx_QuerySingleRow('SELECT AVG(length_of_day) FROM ' & $sTablePlanets) Say('Result of queue - Average length of the day in our solar system: ' & $aRow[0] & ' hours') $aFirstEntry = _SQLiteEx_FirstTableEntry() Say('First table entry (planet): ' & $aFirstEntry[0]) $aLastEntry = _SQLiteEx_LastTableEntry() Say('Last table entry (planet): ' & $aLastEntry[0]) _SQLiteEx_ShowTable() Say('Is first database ' & $sDB_Earth & ' still exists? ' & _SQLiteEx_DatabaseExists($sDB_Earth)) Say('Dropping database ' & $sDB_Earth & ': ' & _SQLiteEx_DropDatabase($sDB_Earth)) _SQLiteEx_Close() Func Say($sMsg) If $bVerbose Then MsgBox(0, 'SQLiteEx Example Says', $sMsg) Else _FileWriteLog('SQLitex_Example.log', $sMsg) EndIf EndFunc SQLiteEx.au3 SQLiteEx_Examples.au3