Jump to content

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


Recommended Posts

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
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By SEuBo
      Hi there,
      while I created an example script to generate and execute a function during runtime, I stumbled across a neat way to share data between running autoit scripts.
      This is done using the amazing magic of  AutoItObject_Internal . (You'll need at least Version 3.0.0 of AutoItObject_Internal)
      Using this UDF, you can create a shared data storage, basically an empty "AutoitObject_Internal-"Object which you can then use to write / read data Inline. no set/get methods, just
      #include "AutoItSharedData.au3" $oShare = _AutoIt_SharedData_CreateOrAttach("MyCustomID") $oShare.some_data = 'foo' and you're done. any other script accessing this data will have to do:
      #include "AutoItSharedData.au3" $oShare = _AutoIt_SharedData_CreateOrAttach("MyCustomID") ConsoleWrite($oShare.some_data & @LF)  
      Basically it's Larsj's Implementing IRunningObjectTable Interface, but you dont have a Dictionary, but an IDIspatch Object instead.
      There are already a bunch of IPC options available - and this is another one.
      AutoItSharedData.au3
       
      Example Script 1
       
      Example Script 2
      Output: 
      To test: run Example Script 1, Then run example Script 2.. or the other way around.
      Example Script 3
      Example_sharedata3.au3
      Example_sharedata3_Controlsend.au3
      Example_sharedata3_Tooltip.au3
      To test: run Example_sharedata3.au3.
      Output: 
       
       Example SharedData4:
      Output: 
      /Edit: Please note that there's a limitation with the Running object table :
      The Script accessing a variable first, will be the "server" for this variable. This means, access to that variable from other scripts should only be possible, as long the "server" script is running! Use appropriate Object Error handlers in case you don't want the surviving "clients" to crash.
      Feedback and/or improvements appreciated
       
      changelog
      version 2.0
      Removed need for AutoItObject, as AutoItObject_Internal now comes with ROT support Added UDF Header Fixed typo on "#include AutoItObjectInternal.au3" -> "#include AutoItObject_Internal.au3" Added ObjGet() after registering the object fails (in case 2 programs tried to register the same ID simultaneously) Updated Examples & zip archive. Cheers,
       
      AutoItSharedData.zip
    • 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: 3.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.
    • By nacerbaaziz
      hello autoit team
      please
      i tried to create a SPVoice object 
      when i create the object and tried to use this function SetAlertBoundary
      it gave me an error
      please where is the problem?
      here is the code
      local $o_Sapi_OBJ = ObjCreate("SAPI.SpVoice") if IsObj($o_Sapi_OBJ) then $o_Sapi_OBJ.SetAlertBoundary("SPEI_PHONEME") endIf note i got the function name from microsoft web site
       
      https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms719784(v=vs.85)
      i have windows10
    • By Colduction
      Hi AutoIt Programmers!

      I've seen a code that gives Name of Group by writing it's group's SID, but this one response very slower than Windows Command-Line WMIC
       
      Func _GetLocalGroupName($sSID = 'S-1-5-18') $objWMIService = ObjGet ("winmgmts:\\" & @ComputerName & "\root\cimv2") $colItems = $objWMIService.ExecQuery('SELECT Name FROM Win32_Group where SID="' & $sSID & '"') For $GroupNames in $colItems MsgBox (0,"",$GroupNames.Name) ExitLoop Next EndFunc I don't want call and use any third-party programs even CMD, i just want use from Windows API, netapi32.dll or AutoIt Functions (Standalone).
      Do you have any idea to improve speed/performance of this code? I'll happy of your comments
       
       
       
      ====================== SOLOUTION by @Subz ======================
       
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
×
×
  • Create New...