miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject

[tarretarretarre]

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)

expandcollapse popup

; 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:

expandcollapse popup

#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()

expandcollapse popup

#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

Edited February 5, 2016 by tarretarretarre
Better examples

[Skysnake]

Very nice. Thank you for sharing.