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

    • 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/AutoItObject-Internal/tree/master/Examples
      Version: 2.0.0
      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.
    • xiantez
      By xiantez
      This script used to work on an older version of AutoIT. Currently I am running AutoIT v3.3.14.5 and it's failing.
      Func PublicIP() ;Post public facing IP address Local $url = 'https://www.google.com/search?client=opera&q=what+is+my+ip&sourceid=opera&ie=UTF-8&oe=UTF-8' Local $getIPaddress = BinaryToString(InetRead($url)) Local $sStart = 'clamp:2">' Local $sEnd = '</div>' Local $ipaddress = _StringBetween($getIPaddress, $sStart, $sEnd For $i In $ipaddress MsgBox(0, 'External IP', "Your public IP address is " & $i) Next EndFunc ;==>PublicIP The console output shows:
      "C:\Users\user\Documents\AutoIT\Scripts\WSI Tools.au3" (197) : ==> Variable must be of type "Object".: For $i In $ipaddress For $i In $ipaddress^ ERROR ->14:12:16 AutoIt3.exe ended.rc:1 +>14:12:16 AutoIt3Wrapper Finished. >Exit code: 1 Time: 9.811
    • tuffgong
      By tuffgong
      Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:
      #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) barcodePrint() Func barcodePrint() $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) FileOpen($sFilename, $FO_OVERWRITE) ;Global $oWorkbook = _Excel_BookAttach($oExcel) Global $sSTCArray = _Excel_RangeRead($Read) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i]) Next _Excel_Close($oExcel) FileMove($sFilename, "***file path***\print.bch") EndFunc However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:
        
      Global $sPrefix Global $oPath Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) Global $aArray Global $bExcel _Excel_BookNew($bExcel) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm") $sPrefix = $aArray[1][1] $oPath = $aArray[2][1] barcodePrint() EndSwitch WEnd Func barcodePrint() $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx") $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@" Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) $oCopy = _Excel_RangeRead($sWorkbook) _Excel_RangeWrite($oWorkbook, Default, $oCopy) FileOpen($sFilename, $FO_OVERWRITE) $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3) ;_ArrayDisplay($sSTCArray) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i]) Next _Excel_Close($oExcel) ;FileMove($sFilename, $oPath) Exit EndFunc This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!
    • marcoauto
      By marcoauto
      Ciao
      I would like to control an ATEM Video Mixer from autoit. I downloaded his SDK which is written in c ++ and I found the sequences to interface, but I was not able to convert the script to self.
      The instructions say to follow this sequence:
      and to connectTo with C++ is:
      string address = "192.168.1.240"; _BMDSwitcherConnectToFailure failureReason = 0; IBMDSwitcher switcher = null; var discovery = new CBMDSwitcherDiscovery(); discovery.ConnectTo(address, out switcher, out failureReason); From Blackmagic SDK:
      IBMDSwitcherDiscovery::ConnectTo method
      The ConnectTo method connects to the specified switcher and returns an IBMDSwitcher object interface for the switcher.
      Syntax HRESULT ConnectTo (string deviceAddress, IBMDSwitcher** switcherDevice, BMDSwitcherConnectToFailure* failReason); Parameters: deviceAddress in Network hostname or IP address of switcher to connect to. switcherDevice out IBMDSwitcher object interface for the connected switcher. failReason out Reason for connection failure as a BMDSwitcherConnectToFailure value. So, I have I tried these solutions but with non success:
      $DllName =@ScriptDir&"\BMDSwitcherAPI.dll" $result = DllCall($DllName, "none", "IBMDSwitcherDiscovery::ConnectTo" & @CRLF) ConsoleWrite("DLLCall Result: " & $result & @CRLF) and I have tried also create an Object (That I think is the best way solution):
      #include <MsgBoxConstants.au3> $oSwitcher=ObjCreate("IBMDSwitcher") If IsObj($oSwitcher) Then MsgBox(64, "", "Object $oSwitcher created successfully") EndIf $oAtem=ObjCreate("IBMDSwitcherDiscovery") If IsObj($oAtem) Then MsgBox(64, "", "Object $oAtem created successfully") EndIf $failureReason =ObjCreate("_BMDSwitcherConnectToFailure") If IsObj($failureReason) Then MsgBox(64, "", "Object $failureReason created successfully") EndIf $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ; Install a custom error handler Global $errore,$oSwitcher1 $oAtem.ConnectTo("192.168.1.36",$oSwitcher,$failureReason); ; This is the custom error handler Func MyErrFunc() $HexNumber = Hex($oMyError.number, 8) MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ "Number is: " & $HexNumber & @CRLF & _ "Windescription is: " & $oMyError.windescription & @CRLF & _ "Source is: " & $oMyError.source & @CRLF & _ "Description is: " & $oMyError.description & @CRLF & _ "Helpfile is: " & $oMyError.helpfile & @CRLF & _ "Helpcontext is: " & $oMyError.helpcontext & @CRLF & _ "Lastdllerror is: " & $oMyError.lastdllerror & @CRLF & _ "Scriptline is: " & $oMyError.scriptline) EndFunc ;==>MyErrFunc But the result is:
      We intercepted a COM Error !
      Number is: 000000A9
      Windescription is: Variable must be of type 'Object'.
      Source is: 
      Description is: 
      Helpfile is: 
      Helpcontext is: 
      Lastdllerror is: 0
      Scriptline is: 17
      The BMDSwitcherAPI.dll is registered in system. Can someone help me?
      Grazie
      Marco
    • 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.
×