Search the Community

Now its my turn to give back to the community ( Better late than never :P).. First i want to thank progAndy for his amazing UDF which this idea came from And the AutoitObject Team (For making autoit fun again) I dont have so much to say more thant to let the project speak for itself, ive had this for a couple of months but it was "integrated" into my own "framework" but today I decided to release it because i have seen some people on the forum search for something like this. What libraries does this use and are they included? Connector/C 6.1.6 ( https://dev.mysql.com/downloads/connector/c/ ) And yes, they are included in the download so nothing has to be installed or anything What are the features: Prepared statements 32 and 64 bit environment Multiline prepared statements Simplicity User-friendly PDO-like Syntax & Methods (http://php.net/pdo) So whats the difference between this and x's Mysql UDF? When you are fetching your data from your database, you use your table-names to display them, like this: with $MySql Local $Vars = ["?", $lastname, "?", $age] .prepare("SELECT id, surname, lastname FROM users WHERE lastname = ? AND AGE > ?") .execute($Vars) Consolewrite(stringformat("Searchresult: %d Hits", .rowCount()) for $row in $oRows consolewrite("Surname: " & $row.surname & @crlf) consolewrite("Lastname: " & $row.lastname & @crlf) next endwith Function-list (Yeah i might improve this when i have time) ; Every parameter is a default value of method _miniSQL_setDllDir(@ScriptDir); returns nothing Local Const $MySql = _miniSQL_LoadLibrary(); Returns object with methods ; Starts the library, connects to the database and returns the object $MySql.Startup($sHost, $sUser, $sPass = "", $sDatabase = "", $iPort = 0, $sUnix_socket = "", $iClient_Flag = 0); Returns TRUE if connection was succeded otherwise FALSE ; Shuts down the library and prevents any methods to be executed $MySql.Shutdown() ; The desired SQL query goes here (SELECT x FROM table) $MySql.prepare($sQuery); Returns nothing ; Used for multi-line prepared statements (See Example3 - newline prepared statements) $MySql.PrepareGlue($sQuery); Returns nothing ; Cleans any previous prepared statement of any kind $MySql.PrepareClean(); Returns nothing ; Executes a prepared statement of any kind, with or without passed arguments $MySql.execute($aVars = Null, $iExecuteStyle = $_miniSQL_ExecuteStoreResult); returns TRUE if success, otherwise FALSE ; Options for $iExecuteStyle: $_miniSQL_ExecuteStoreResult = 0 and $_miniSQL_ExecuteOnly = 1 ; Fetches previous executed prepared statement (If anything was stored "see Options for iExecuteStyle") $MySql.fetchAll($iFetchStyle = $_miniSQL_FetchObject); Returns (Depends on $iFetchStyle) ; Options for $iFetchStyle: $_miniSQL_FetchObject = 0 (Default), $_miniSQL_FetchSingleObject = 1, $_miniSQL_FetchArray = 2, $_miniSQL_FetchSingleValue = 3 ; Gives you the "lastinsertId" (The last id that was affected) $MySql.lastInsertId(); Returns the last affected id ; Counts the affected rows done by any MySQL operation (INSERT\SELECT\UPDATE\DELETE) $MySql.rowCount(); Returns how affected rows ; Use this if want to know why nothing is working (Can be used anywhere after $MySql.Startup()) $MySql.debug(); Returns nothing ; Retrives the last MysqlError set $MySql.SQLerror(); Returns error (If any) Here is some example code: #include <miniSQL\miniSQL.au3> ; Set default dir for our dlls (Only has to be done once) _miniSQL_setDllDir(@ScriptDir & "\miniSQL") ; Declared as CONST since we never want to accidentally change the variables original value Local Const $MySql = _miniSQL_LoadLibrary() ;Connect to database & Init library If Not $MySql.Startup("localhost", "user", "pass", "db", 3306) Then MsgBox(0, "Failed to start library", $MySql.debug()) Exit EndIf With $MySql .prepare("SELECT * FROM members") If Not .execute() Then MsgBox(0, "Failed to execute query", .sqlError()) Local $oRows = .fetchAll() ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Number of rows to display: %s", .rowCount()) & @CRLF) ; we use isObj to check if we got any result. If IsObj($oRows) Then For $row In $oRows ConsoleWrite(StringFormat("Id: %s", $row.id) & @CRLF) ConsoleWrite(StringFormat("Name: %s", $row.name) & @CRLF) ConsoleWrite(StringFormat("Bio: %s", $row.bio) & @CRLF) Next Else ConsoleWrite("No rows to show"&@CRLF) EndIf EndWith ; Use this in your app when you are done using the database $MySql.Shutdown() #include <miniSQL\miniSQL.au3> ; Set default dir for our dlls (Only has to be done once) _miniSQL_setDllDir(@ScriptDir & "\miniSQL") ; Declared as CONST since we never want to accidentally change the variables original value Local Const $MySql = _miniSQL_LoadLibrary() ;Connect to database & Init library If Not $MySql.Startup("localhost", "user", "pass", "db", 3306) Then MsgBox(0, "Failed to start library", $MySql.debug()) Exit EndIf With $MySql ; We use an array to make our query look nicer Local $vars = [":name", @UserName&Random(1,10,1)] ; Prepare our statement .prepare("UPDATE members SET name = :name WHERE 1") If Not .execute($vars) Then MsgBox(0, "Failed to execute query", .sqlError()) ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Example 1 rows affected: %s", .rowCount()) & @CRLF) EndWith ; We can also prepare like this With $MySql Local $vars = ["?", @UserName, "?", 1] ; Prepare our statement .prepare("UPDATE members SET name = ? WHERE id = ?") If Not .execute($vars) Then MsgBox(0, "Failed to execute query", .sqlError()) ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Example 2 rows affected: %s", .rowCount()) & @CRLF) EndWith ; Use this in your app when you are done using the database $MySql.Shutdown() With $MySql ; We use an array to make our query look nicer Local $vars = ["?", 1] ;Line by line prepared statement .prepareClean(); .prepareGlue("SELECT *") .prepareGlue("FROM members") .prepareGlue("WHERE id = ?") If Not .execute($vars) Then MsgBox(0, "Failed to execute query", .sqlError()) ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Example 1 rows affected: %s", .rowCount()) & @CRLF) EndWith ; Use this in your app when you are done using the database $MySql.Shutdown() Some code from one of my applications at work using this UDF With $MySql .prepareClean() .prepareGlue("SELECT") .prepareGlue("cases.cases_dedu_casenumber,") .prepareGlue("cases.cases_created_by_ugid,") .prepareGlue("cases.cases_dedu_ftg,") .prepareGlue("cases.cases_date_created,") .prepareGlue("cases.cases_date_finished,") .prepareGlue("cases.cases_protocol_director,") .prepareGlue("cases.cases_finished_by_ugid,") .prepareGlue("IFNULL(uid1.names_name, 'none') as createdByFullname,") .prepareGlue("IFNULL(uid2.names_name, 'none') as finishedByFullname") .prepareGlue("FROM cases") .prepareGlue("LEFT JOIN names AS uid1") .prepareGlue("ON cases.cases_created_by_ugid = uid1.names_uid") .prepareGlue("LEFT JOIN names AS uid2") .prepareGlue("ON cases.cases_finished_by_ugid = uid2.names_uid") if $_App_Case_SearchFor Then .prepareGlue(StringFormat("WHERE cases_dedu_casenumber LIKE '%s'",$_App_Case_SearchFor)) .prepareGlue("ORDER BY cases.cases_date_created DESC") .prepareGlue("LIMIT 0, 30") if not .execute() then return __ThrowException(.sqlError()) Local $oRows = .fetchAll() EndWith Git: https://gitlab.com/xdtarrexd/MiniSQL.git Download: Zip generated from Github Feel free to open your mind about this