2 posts in this topic
miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject
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
Download: Zip generated from Github
Feel free to open your mind about this
Storing files in a better way
I am dealing with an issue that I'm trying to think of a clever workaround for. The network drives that I have for deployment are painfully slow and when I put EXE files on them, it takes forever to load. That being said, I use the same network directory for handling "attachments" in various parts of my tool. I crypt the files and store the files extension as well as the crypted password in a database and put the crypted files into a subdirectory.
I'm not going to include my code here because really what I'm trying to figure out is if there is a way to deconstruct any file (mainly pdfs, msg files, and word docs) and store it as a string in my database or something of that nature. Sometimes the files are hundreds of MB and I don't know if I can even store something that big in a DB (I know you can with BULK statements, but that needs a path relative to the server which I cannot do). I want to get rid of the network directory dependency to improve performance.
TinyBackupBox v220.127.116.11 Update of 2011 - 12 - 21
TinyBackupBox : Do a Backup of folders you want to a local/external drive/directory by using Robocopy.
A Big Thank to Yashield for TVExplorer.au3 and WinApiEx.au3
Includes needed : >TVExplorer.au3 and >WinApiEx.au3
* Left Treeview :
Check all folders Checkboxes you want to backup.
Hold Shift key when clicking a first child Checkbox will check all other child Checkboxes.
A right click on a parent item will uncheck all his child Checkboxes.
* Right Treeview :
A left click for select Backup Directory.
A right click menu on an item for create a new sub-folder which you can give the name you want.
This new sub-folder will be created and added as child item of the item you have right clicked.
* Start Button :
A Click on Start Button will start backup normally.
Hold Shift key when clicking it will start backup on Idle Mode.
Idle Mode is used for start or resume backup after a period of user inactivity (that you can set by tray menu)
and will pause current backup if not idle.
* Stop Button :
A click on Stop Button will Pause backup, and any changes will affect the backup when resume it.
You can resume a current backup by Re-clicking Start Button.
Hold Shift key when clicking Stop Button will totally Cancel current backup.
In Idle mode Stop Button is inactive until you Hold Shift key and click for Cancel current backup.
* Refresh Button :
A Click will refresh both treeviews and set them to the previous selections you have done.
Hold Shift key when clicking it will refresh both treeview and set them to the root.
* Tray Menu :
By Default Robocopy copy only new files,
so i add the possibility to
_ delete previous existing dir.
_ delete destination files and dirs that no longer exists in source.
_ Preserve all Attributes.
_ Show Robocopy Console window.
_ Set Idle Time Delay.
_ Open Backup Dir.
Update of 2011-12-21
Previous downloads : 103
source and executable are available in the Download Section
minimalist but handy !
Hope you like it !
And do not forget to create backup periodically !
Do a backup of a folders list
WARNING: I never tried this on a root directory, I should probably write a fix for that case. EX: There is no C:\..\
I don't know how many of these there are on this forum and I know Tidy is real nice for making backups.
I have a few dislikes relying on the Tidy backup:
1 I'm not crazy about the backup folder it creates in project folder.
2. If no Tidy edits are performed script is not backed up on Tidy. I can't rely on that when I request a backup.
3. If I want to backup a select group of files without backing up the entire project folder, it is somewhat tedious to select each file and Tidy.
Now this is just a very simple script that I felt I could use to simplify backing up my project.
I'm not asking you to code it for me, but I welcome feedback. If you think you can make it better or know of something better please share.
I know of Github it's overkill for me, I'm not comfortable, and I won't use it as much as I need to.
So here it is:
You place it in the source folder, open it in Scite.
Change: Global $gBackup_dir_path = "..\Backup\" to wherever you want you backup folder Path to be.
in the main() add some backup("file_name") calls remember to remove file extension.
and it should make a new backup file for each backup("file_name") every time you run the script.
Test it out make sure it's working and you're good to go.
#cs ---------------------------------------------------------------------------- AutoIt Version: 18.104.22.168 Author: myName Script Function: To copy files to a backup folder. #ce ---------------------------------------------------------------------------- #include <File.au3> Global $gBackup_dir_path = "..\Backup\" ; Todo ; Add notes, options notes in file name main() func main() ; File list remember to remove extentions from file_name ; or add file extention as second parameter backup("Map_Editor") backup("DW_Server") EndFunc Func backup($spFile_name, $spFile_ext = ".au3") Local $error = 0 Local $iFile_num = 0 ; Make source file path Local $sFile_path_source = $spFile_name & $spFile_ext Local $sFile_path_dest = "" ; Sample the contents of the backup directory before creating a file there Local $aFile = _FileListToArray($gBackup_dir_path, $spFile_name&"*"&$spFile_ext, $FLTA_FILES) $error = @error If $error = 0 Then $iFile_num = $aFile EndIf out("Found: " & $iFile_num & " files named: " & $spFile_name) Do ; Incroment file_num to find available file_name $iFile_num += 1 ; Path to Write File Copy $sFile_path_dest = $gBackup_dir_path & $spFile_name & "_" & $iFile_num & $spFile_ext ; Refuse to Overwrite File If FileExists($sFile_path_dest) = 0 Then ExitLoop EndIf ; Am I right? Sleep(30) Until 0 ; Create the file FileCopy($sFile_path_source, $sFile_path_dest, $FC_CREATEPATH) $error = @error If $error Then out("FileCopy() error: " & $error) out("sFile_path_source: " & $sFile_path_source & " sFile_path_dest: " & $sFile_path_dest) EndFunc ;==>backup Func out($output = "", $user = 0);debug tool ConsoleWrite(@CRLF & $output);to console new line, value of $output EndFunc ;==>out Backup_Script.au3
The script isn't authorized to overwrite files, and tries to create the next number of file_name available.
The 'p' in my variable names stands for parameter.