Jump to content
tarretarretarre

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

Recommended Posts

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)

; 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

Edited by tarretarretarre
Better examples
  • Like 1

Share this post


Link to post
Share on other sites
Skysnake

Very nice. Thank you for sharing. :)

 

  • Like 1

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening everyone
      I'm working on a little project of mines, and I was trying to use WMI Object.
      The question which I don't find an answer is: 
      Once I do the query with WMI Object, something like "SELECT * FROM Win32_LogonSession", instead of specify the field of the collection returned, ( i.e. $colItems.Caption ), can I loop though each property and each value of the property, writing so one row of code only?
      Hope my question was clear enough.
      Thanks in advance.

      Best Regards.
    • Case85
      By Case85
      Hi for all!
       
      After a long time I wasted to find the best way to connect to any external MySQL server, I'm ready to hire "C" or "C++" developer to use MySQL connector dll file for create a stable way to connect to a MySQL server.
      I already tried to use the UDF "miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject" which is working perfect (this way what I looking for), but unfortunately after 1-2 hours usage failed and exit the script.
      I plan to use the MySQL connector heavily and continuously on long time.
      The developed connector must be have a small memory footprint.
      My budget is moderated, I need this connector quick as possible, please contact with me in PM for more details.
      Thank you for all.
    • Fenzik
      By Fenzik
       Hello all"
      I have curious problem with com object implementation of Sapi 5.1.
      In some cases }Some Voice engines] the metods for retrieve the voice parameters fails with error :Member not exists:.
      But the Retrieved Voice object can speak the given text, so It exists and work.
      Example of this type of Engine can be this one: http://download.kobavision.be/KobaSpeech3/KobaSpeech 3 With Vocalizer Serena - English (Great Britain).exe (can work as demo)
      So my question is> Is there some way to workaround or solve this issue?
      What i tryed:
      1. Typical use of Sapi.spvoice object:
      $oMyError = ObjEvent("AutoIt.Error","MyErrFunc"); Install a custom error handler
       
        $spvoice = ObjCreate("sapi.spvoice")
      for $voice in $spvoice.getvoices()
        msgbox(0, "Voice", $voice.getdescription())
      next
      Func MyErrFunc()
      $HexNumber = hex($oMyError.number, 8)
      Msgbox(0,"","We intercepted a COM Error !" & @CRLF &"Number is: " & $HexNumber & @CRLF &"Windescription is: " & $oMyError.windescription)
      SetError(1)
      Endfunc

      2. Implement workaround based on Nvda Screen reader sapi5 Library at https://github.com/nvaccess/nvda/blob/master/source/synthDrivers/sapi5.py
      Thys code in Pascal should work, so i tryed to reproduce it in Autoit.
      Pascal code just as example:
                   SOTokens:=SpVoice.GetVoices('','');
                   for i:=0 to SOTokens.Count-1 do
                   try
                        SOToken:=SOTokens.Item(I); s:=SOToken.GetDescription(0);
      end
      In Autoit I tryed it like this:
      $oMyError = ObjEvent("AutoIt.Error","MyErrFunc"); Install a custom error handler
        $spvoice = ObjCreate("sapi.spvoice")
      for $i = 0 to $spvoice.getvoices.count-1
      $name = $spvoice.getvoices.item($i).getdescription
      msgbox(0,"Voice", $name)
      next
      Func MyErrFunc()
      $HexNumber = hex($oMyError.number, 8)
      Msgbox(0,"","We intercepted a COM Error !" & @CRLF &"Number is: " & $HexNumber & @CRLF &"Windescription is: " & $oMyError.windescription)
      SetError(1)
      Endfunc
      Both of this methods returning same Error ("Member not exists.").
      Thanks a lot for help.
      Znefyg
    • SchneiMi
      By SchneiMi
      Hello,
      following my previous question, I have moved all potentially instable object interactions into Executes. But it Looks like "="-assignments to object Attributes cannot be done with Execute, only method calls. Using an "$obj = 1" construct, it compares (Eval) instead of sets (Execute) the value.
      I have tested multiple different combinations, using Execute and Assign, but it seems not to work with object Attributes. :-(
      Gives following Output:
      The assign Action using apply (a3) Fails, while assigning it directly, without Assign() works fine.
      The execute versions compare and do not assign, in both cases. Though, the "Execute" topic in the help file says it executes, not evaluates.
      I have found a similar, old thread, which explains this behavious but does not give a solution. https://www.autoitscript.com/forum/topic/110228-pass-object-property-as-a-variable/  
      Is there a way to assign to a com object's Attribute? Or is there anything new to this unexpected behaviour of Execute (at least compared to the help file description and Python's exec).
       
      Any help is appreciated, and thank you for all the help so far.
      Regards, Michael
       
    • Simpel
      By Simpel
      Hi,
      since some days I become this error message exiting my app:

      Eventviewer shows following data:
      Name der fehlerhaften Anwendung: autoit3.exe, Version: 3.3.14.2, Zeitstempel: 0x55fc1979 Name des fehlerhaften Moduls: ntdll.dll, Version: 6.1.7601.23864, Zeitstempel: 0x595fa490 Ausnahmecode: 0xc000000d Fehleroffset: 0x000987e0 I stripped my code from 1500 lines down to 70:
      #include <GUIConstants.au3> Global $g_sPathToPDF = ; path to some pdf file to show Opt("GUIOnEventMode", 1) ; default ist 0 ; 1 bedeutet, daß bei Klick direkt die darunterbeschriebene Funktion ausgeführt wird Global $g_hGUI_MAIN ; Haupt-GUI Global $g_hDummy_Main ; Dummy um Fokus in der Haupt-GUI unsichtbar zu setzen Global $g_hGUI_Pruefen ; GUI zum Prüfen aller PDF Global $g_hGUI_PDF ; GUI PDF-Ansicht der ausgewählten PDF Global $g_hPDF ; ActiveX control welches das PDF enthält Global $g_oAcrobatReader ; AcrobatReaderObjekt in dem die PDF gezeigt werden _GUI_Main() GUISetOnEvent ($GUI_EVENT_CLOSE, "_Exit_Main" , $g_hGUI_MAIN) While 1 Sleep(1) WEnd Exit Func _GUI_Main() ; GUI-MAIN $g_hGUI_MAIN = GUICreate("MAIN", 390, 390, 763, 372) GUISetFont(12) GUICtrlCreateButton("NEXT", 20, 20, 350, 55, $BS_DEFPUSHBUTTON) ; Default-Knopf GUICtrlSetOnEvent(-1, "_GUI_Pruefen") GUISetState(@SW_SHOW, $g_hGUI_MAIN) ; GUI anzeigen EndFunc Func _GUI_Pruefen() ; GUI zum Prüfen der PDF GUISetState(@SW_HIDE, $g_hGUI_MAIN) ; MAIN-GUI ausblenden Opt("GUIOnEventMode", 0) ; wieder auf Default gesetzt $g_hGUI_Pruefen = GUICreate("RIGHT", 490,950, 1057, 91, -1, $WS_EX_APPWINDOW, $g_hGUI_MAIN) _AcrobatShow($g_sPathToPDF, "", 367, 91, 674, 950, $g_hGUI_Pruefen) ; PDF-GUI erstellen GUISetState(@SW_SHOW, $g_hGUI_Pruefen) ; GUI-Prüfen anzeigen Local $msg While 1 $msg = GuiGetMsg() ; Aktion mit der GUI registrieren Switch $msg ; je nach Aktion mit der GUI Case $GUI_EVENT_CLOSE ; X gedrückt $g_oAcrobatReader = "" ; zerstöre das Objekt AcrobatReader GUIDelete($g_hGUI_PDF) ; lösche die GUI-PDF GUIDelete($g_hGUI_Pruefen) ; lösche die GUI-Prüfen Opt("GUIOnEventMode", 1) ; Default 0 GUISetState(@SW_SHOW, $g_hGUI_MAIN) ; MAIN-GUI wieder zeigen Return EndSwitch WEnd EndFunc Func _Exit_Main() ; ausführen, wenn die MAIN-GUI schließt ConsoleWrite("EXIT" & @CRLF) Exit EndFunc Func _AcrobatShow($sFile, $sTitle = "PDF ", $iLeft = 50, $iTop = 0, $iWidth = 1000, $iHeight = 700, $hWnd = "") ; GUI-PDF erstellen If FileExists($sFile) Then ; wenn das PDF existiert $g_oAcrobatReader = ObjCreate("AcroPDF.PDF.1") $g_oAcrobatReader.src = $sFile ; Quelle ist das File $g_oAcrobatReader.SetLayoutMode("SinglePage") ; default "SinglePage" $g_oAcrobatReader.SetPageMode("none") ; default "none" $g_oAcrobatReader.SetShowToolbar(0) ; Tool-Bar nicht zeigen 0 $g_oAcrobatReader.SetShowScrollbars(0) ; Scroll-Balken nicht zeigen 0 $g_oAcrobatReader.SetView("fit") ; "fit" falls wer eigene Einstellungen im Reader gespeichert hat $g_hGUI_PDF = GUICreate($sTitle, $iWidth, $iHeight, $iLeft, $iTop, -1, -1, $hWnd) ; GUI als Child zu GUI-PRUEFEN erstellen - es soll nicht aktiviert werden $g_hPDF = GUICtrlCreateObj($g_oAcrobatReader, 0, 0, $iWidth, $iHeight) ; Objekt für das PDF erstellen GUICtrlSetStyle($g_hPDF, $WS_VISIBLE) ; PDF anzeigen GUISetState(@SW_SHOW, $g_hGUI_PDF) ; GUI-PDF anzeigen Else MsgBox(0, 'ERROR', "No PDF found.") EndIf EndFunc Do following steps to prove:
      - start app
      - click "next" on main gui
      - wait minimum 5 seconds (until the arrows left and right on "gui left" disappear)
      - close gui left or right
      - close main gui
      - look on console written "EXIT" the last code line before exit
      - now windows error message above appears
      The funny thing is if I don't wait the 5 seconds (before the half transparent arrows disappear) closing the gui then I will get no win error message.
      If I comment _AcrobatShow() out then the error never appears. So it seemed to be an acrobat reader issue. Every week at work there are a lot of updates, but there is no chance to know which one. But since one update this error happens.
      Any solutions? Regards, Conrad
×