tarretarretarre

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

2 posts in this topic

#1 ·  Posted (edited)

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
1 person likes this

Share this post


Link to post
Share on other sites



Very nice. Thank you for sharing. :)

 

1 person likes this

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

    • 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
    • genius257
      By genius257
      I've made a library, based on AutoItObject UDF with the goal of implementing getter and setter functionality and make it possible to define new object properties in as few steps as possible.
      Thank you to @trancexx for getting me on the right track, and all users in Hooking into the IDispatch interface for the code to get me going.
      If I've forgotten to add credit, please let me know
      Example:
      #include "AutoItObject_Internal.au3" $myCar = IDispatch() $myCar.make = 'Ford' $myCar.model = 'Mustang' $myCar.year = 1969 $myCar.__defineGetter('DisplayCar', DisplayCar) Func DisplayCar($oThis) Return 'A Beautiful ' & $oThis.parent.year & ' ' & $oThis.parent.make & ' ' & $oThis.parent.model EndFunc MsgBox(0, "", $myCar.DisplayCar) More examples: https://github.com/genius257/AutoIt-projects/tree/master/AutoItObject Internal/Examples
      Version: 1.0.3
      AutoItObject_Internal.au3
      Documentation:
       

      Edit2 (19th March 2017):
      First of all, sorry about the lack of updates on this project. I always start too many projects and end up ignoring old projects, if I run into problems ^^'.
      So I've started moving my AutoIt scripts to GitHub. I will still post the most recent script version here.
    • Fhelipe
      By Fhelipe
      Nothing More
    • rynow
      By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
    • macran
      By macran
      I use mysql.au3 to connect mysql database .  If restart the server ,the client how to reconnect mysql?
      The function prototype is
      _MySQL_Options($MySQL_ptr, $option, $arg = 0, $type = "ptr")
      so,
      _MySQL_Options($MysqlConn,$MYSQL_OPT_RECONNECT, ?, ?)
      pls help me,thanks!