Sign in to follow this  
Followers 0
Logman

Backup MySql databases on localhost

2 posts in this topic

#1 ·  Posted (edited)

I was too lazy to frequent backups my database on localhost via phpMyAdmin so I wrote a very simple script to backup databases via command com. Maybe it will be useful to someone...

It backs up all or selected databases into one or separate sql files, e.g:

single file output:

20130406.022354_drupal,test.sql

separate files output:

20130406.022354_drupal.sql

20130406.022354_test.sql

Recommended php utility to import .sql files into MySql:

BigDump: Staggered MySQL Dump Importer

#include <Array.au3>
#include <Constants.au3>

; ------------------------------------------------------------------------
; BACKUP MYSQL DATABASES ON LOCALHOST
; ------------------------------------------------------------------------
; Definition and meaning:
; $export_defs .....    combine two constants: $cEXPORT_DB + ($cEXPORT_TO_... or $cEXPORT_AS_...)
;    e.g. [ $cEXPORT_DB_ALL_DATABASES + $cEXPORT_TO_SINGLE_FILE ] => export all dbs to one file
; $custom_dbs ...... user-created databases. Use comma as separator, e.g. "drupal, joomla"
; $export_path ..... an export destination folder
; $dbUsr ........... user login credentials, usually 'root'
; $dbPwd ........... passwords for MySQL accounts
; $dbSrv ........... MySQL server, 127.0.0.1 for localhost
; $sMySqlPath ...... full path to MySQL bin directory
; $sSytemDbs ....... list of databases created during installation MySql app

; use this constants in variable $export_defs:
Const $cEXPORT_DB_SYSTEMS_ONLY        = 1        ; export default databases (e.g. XAMPP default databases)
Const $cEXPORT_DB_NON_SYSTEMS        = 2        ; export user-created databases (e.g. all non XAMPP default dbs)
Const $cEXPORT_DB_ALL_DATABASES        = 4        ; export all databases
Const $cEXPORT_DB_CUSTOM_DATABASES    = 8        ; export selected databases (e.g. 'Drupal' database only)

Const $cEXPORT_TO_SINGLE_FILE        = 128    ; export databases as one .sql file
Const $cEXPORT_AS_SEPARATE_FILES    = 256    ; export each stored database as separate .sql file

;=== user definition ===================================================>>
Local $export_defs    = $cEXPORT_DB_CUSTOM_DATABASES + $cEXPORT_AS_SEPARATE_FILES
;Local $export_defs    = $cEXPORT_DB_NON_SYSTEMS + $cEXPORT_TO_SINGLE_FILE
Local $custom_dbs    = "drupal" ; as separator use comma, e.g. "drupal, joomla"
Local $export_path     = "E:\Backup\FullBackup\Aplikace\MySQL"
Local $dbUsr         = "root"
Local $dbPwd         = "123456"
Local $dbSrv         = "127.0.0.1"
Local $sMySqlPath    = "C:\xampp\mysql\bin\"
Local $sSytemDbs     = "cdcol, information_schema, mysql, performance_schema , phpmyadmin, test, webauth"
;=== user definition ====  (Do not change anything below this line) ====<<

$export_path        = StringRegExpReplace($export_path, "[\\/]+\z", "") & "\"
$sMySqlPath            = StringRegExpReplace($sMySqlPath, "[\\/]+\z", "") & "\"
Local $sMySqlExe    = FileGetShortName($sMySqlPath & "mysql.exe")
Local $sMySqlDmpExe    = FileGetShortName($sMySqlPath & "mysqldump.exe")
Local $sFormat         = "%s -u %s -p%s -h%s %s -e ""show databases"" -s -N"
Local $sExtCmd         = StringFormat($sFormat, $sMySqlExe, $dbUsr, $dbPwd, $dbSrv)
Local $aSytemDbs    = StringSplit(StringStripWS($sSytemDbs, 8), ",", 2)
Const $2L             = @LF & @LF

If FileExists($sMySqlExe) <> 1 Then
    MsgBox(8240, "MySql.exe not found", "The mysql.exe not found!" & $2L & _
      "The path '$export_path' is probably not being set properly.")
    Exit
EndIf

; run in cmd
Local $CMD = Run(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD+$STDOUT_CHILD)
ProcessWaitClose($CMD)
Local $sMsg = StdoutRead($CMD)
Local $sErr = StderrRead($CMD)

; if an error in mysql.exe (eg. server is not running)
If StringInStr($sErr, "ERROR") <> 0 Then
    MsgBox(8208, "Error", $sErr)
    Exit
EndIf
If StringLen($sMsg) = 0 Then
    MsgBox(8208, "Error", "Failed to get databases names")
    Exit
EndIf

; read all installed databases to $aAllDbs array
Local $aAllDbs = StringSplit($sMsg, Chr(13), 2)
For $i = UBound($aAllDbs) - 1 To 0 Step -1
    $aAllDbs[$i] = StringStripWS($aAllDbs[$i],3)
    If StringLen($aAllDbs[$i]) = 0 Then
        _ArrayDelete($aAllDbs, $i)
    EndIf
Next

; move requested names of databases to $aDbs array
Select
    Case BitAND($export_defs, $cEXPORT_DB_SYSTEMS_ONLY) <> 0
        $aDbs = $aSytemDbs
        Local $sResult = fncItemsInArray($aDbs, $aAllDbs)
        If @error Then
            MsgBox(8240, "Error", "Defined system database name '" & $sResult & "' not found!")
            Exit
        EndIf
    Case BitAND($export_defs, $cEXPORT_DB_NON_SYSTEMS) <> 0
        $aDbs = fncArrayExclude($aAllDbs, $aSytemDbs)
    Case BitAND($export_defs, $cEXPORT_DB_ALL_DATABASES) <> 0
        $aDbs = $aAllDbs
    Case BitAND($export_defs, $cEXPORT_DB_CUSTOM_DATABASES) <> 0
        $aDbs = StringSplit(StringStripWS($custom_dbs, 8), ",", 2)
        Local $sResult = fncItemsInArray($aDbs, $aAllDbs)
        If @error Then
            MsgBox(8240, "Error", "Defined custom database name '" & $sResult & "' not found!")
            Exit
        EndIf
EndSelect

; export
Local $sOutFile
Local $sFileFirstPart = $export_path & @YEAR & @MON & @MDAY & "." & @HOUR & @MIN & @SEC
$sFormat = "%s --lock-all-tables -u %s -p%s -h%s %s > " & """" & "%s" & """"
Select
    Case BitAND($export_defs, $cEXPORT_TO_SINGLE_FILE) <> 0
        $sOutFile = FileGetShortName($sFileFirstPart & "_" & _ArrayToString($aDbs, ",") & ".sql")
        $sExtCmd  = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, "-B " & _
          _ArrayToString($aDbs, " "), $sOutFile)
        $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
        If FileExists($sOutFile) = 0 Then
            MsgBox(8208, "Error", "An error occurring during the export..." & $2L & "databases: " & _
              _ArrayToString($aDbs, ", ") & @LF & "destination: " & $sOutFile)
            Exit
        EndIf

    Case BitAND($export_defs, $cEXPORT_AS_SEPARATE_FILES) <> 0
        For $x = 0 To UBound($aDbs) - 1
            $sOutFile = FileGetShortName($sFileFirstPart & "_" & $aDbs[$x] & ".sql")
            $sExtCmd  = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, $aDbs[$x], $sOutFile)
            $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
            If FileExists($sOutFile) = 0 Then
                MsgBox(8208, "Error", "An error occurring during the export..." & $2L & "database: " & _
                  $aDbs[$x] & @LF & "destination: " & $sOutFile)
                Exit
            EndIf
        Next
    EndSelect

; final msg
$sFormat = "%s database%s was exported:%s%s%sTo destination:%s%s"
$sMsg = StringFormat($sFormat, UBound($aDbs), _iIf(UBound($aDbs) > 1, "s", ""), $2L, "- " & _
  _ArrayToString($aDbs, @LF & "- "), $2L, $2L, $export_path)
MsgBox(8256, "Done", $sMsg)

Exit
; -------------------------------------------------------------------
; Check if all items from $aSrc are included in $aCmp
; -------------------------------------------------------------------
Func fncItemsInArray($aSrc, $aCmp)
    Local $bFound, $i, $j
    For $i = 0 To UBound($aSrc) - 1
        $bFound = False
        For $j = 0 To UBound($aCmp) - 1
            If $aSrc[$i] = $aCmp[$j] Then
                $bFound = True
                ExitLoop
            EndIf
        Next
        If $bFound = False Then
            SetError(1)
            Return $aSrc[$i]
        EndIf
    Next
    Return 1
EndFunc ;==>> fncItemsInArray

; -------------------------------------------------------------------
; Exclude items from array based on another array
; $iFirstIdx1: ... first index of $aAll
; $iFirstIdx2: ... first index of $aExclude
; -------------------------------------------------------------------
Func fncArrayExclude($aAll, $aExclude, $iFirstIdx1=0, $iFirstIdx2=0)
    Local $bFound, $i, $j, $aResult[1]
    For $i = $iFirstIdx1 To UBound($aAll) - 1
        $bFound = False
        For $j = $iFirstIdx2 To UBound($aExclude) - 1
            If $aAll[$i] = $aExclude[$j] Then
                $bFound = True
                ExitLoop
            EndIf
        Next
        If $bFound = False Then
            If StringLen($aResult[0]) <> 0 Then
                ReDim $aResult[UBound($aResult) + 1]
            EndIf
            $aResult[UBound($aResult)-1] = $aAll[$i]
        EndIf
    Next
    Return $aResult
EndFunc ; ==>> fncArrayExclude

; -------------------------------------------------------------------
; _Iif from MISC
; -------------------------------------------------------------------
Func _Iif($fTest, $vTrueVal, $vFalseVal)
    If $fTest Then
        Return $vTrueVal
    Else
        Return $vFalseVal
    EndIf
EndFunc ;==>_Iif

Export_MySql_Databases_v1.au3

Edited by Logman

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

HI Logman,

Forgot to thank you for sharing

i myself use a modified script of Matt Moeller "Auto MySQL Backup For Windows Servers By Matt Moeller v.1.5"

Yours is more configurable with your $export_defs

Edited by Emiel Wieldraaijer

Best regards,Emiel Wieldraaijer

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
Sign in to follow this  
Followers 0

  • Similar Content

    • S0lidFr0st
      SQL Query Manipulation
      By S0lidFr0st
      Hello! I'm fairly new to using Autoit, I like the language and simplicity, however, there is a bit of a learning curve for me. I'm stuck and need some community help!
      I need to manipulate a query by using GUICtrlCreateDate to select the correct date and pipe the selected date into my actual query in a specific format (yyyymmdd).
      Here is an example:
      _Flag_RecordsetDisplay($sConnectionString, "select * from trips_to_complete_20161122 where trip_type in ('P','C') and trip_status in ('S','PC','DC') and Flagged = 1") Func _Flag_RecordsetDisplay($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Recordset content') EndFunc ;==>_Flag_RecordsetDisplay The part of the query that needs modified is "trips_to_complete_20161122" I need to be able to select a date (via the gui) and that selection pipe into my query.  
       
      Thanks in Advanced!
    • Aphotic
      Simple SQL Query Tool
      By Aphotic
      Hey Guys,
      I've been using AutoIT for about 3 years now, lurking the forums, scouring documentation, and creating things relevant to my job function.
      I work entry level IT and utilizing AutoIT has earned me much respect in the workplace. I made my first application on the level of sharing with the community that has no relevance to my company other than the data that is used with it.
      Our tech's were using development software to make simple pre-written queries. I was made aware of the process in order to assist in automating it. We found that the software they were using was being sunset in our environment so they'd need a replacement; and upon me realizing that they only needed a simple query tool, decided to 'homegrow' a simple app and save some hefty licensing fees.
      I built in some versatility to the SQL database you can connect to but it is only tested working with a Sybase 15 system (note the example connection string).
      I'd love to hear some suggestions and critique. I'm sure there are some editing functionalities that I could implement into the query edit box that I haven't bothered looking at. (other than CTRA+A to select all)
       
      Thanks guys!
      #include <WindowsConstants.au3> #include <GUIConstants.au3> #include <GUIConstantsEx.au3> #include <GuiEdit.au3> #include <GuiListView.au3> #include <File.au3> #include <Crypt.au3> #include <Date.au3> $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") Local $unique = "SQL-Query-Tool" ;TO ALLOW ONLY ONE INSTANCE OF THE TOOL If WinExists($unique) Then MsgBox(0,'Duplicate Process', 'This script is already running....' & @CRLF & "If the window is not visible, terminate the process from Task Manager" & @CRLF & 'This instance will terminate after clicking OK') Exit EndIf AutoItWinSetTitle($unique) Local $fResized = False, $GUIs, $temp, $Qlist[0][3], $x, $selQ[2], $guics[4] Local $conName, $conStr, $conPW, $selC, $selConn, $sqlErr Local $fh Local $readme = "You may delete this entry if desired once other ." & @CRLF & _ "Although it is prefaced with 'zz' for sorting" & @CRLF & _ @CRLF & _ "-Select a Query from the drop-down" & @CRLF & _ "-Note: changing selection will not lose" & @CRLF & _ " changes made during this session" & @CRLF & _ "-Click Run Query to produce results" & @CRLF & _ @CRLF & _ "-Select from 'Query Options' to create new," & @CRLF & _ " edit name, save, and delete queries" & @CRLF & _ @CRLF & _ "-Click 'Connection Settings' to change" & @CRLF & _ " server settings" $conName = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name") If @error = 1 Then DirCreate(@AppDataDir & "\SQL-Query-Tool\") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", "1", "Connection_Name_Placeholder") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", "1", "Connection_String_Placeholder") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", "1", "Connection_Password_Placeholder") EndIf $selConn = IniRead(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", "Placeholder") If $selConn = "Placeholder" Then DirCreate(@AppDataDir & "\SQL-Query-Tool\") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", "1") $selConn = 1 EndIf $temp = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") If @error = 1 Or @error = 4 Then DirCreate(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") FileWrite(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\zz_Readme.txt", $readme) $temp = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") EndIf QueryLoad(False) $selQ[0] = $Qlist[1][0] Local $GUI = GUICreate("Simple SQL Query Tool", 260, 320, -1, -1, BitOR($WS_SIZEBOX,$WS_MINIMIZEBOX));, @DesktopWidth-275, @DesktopHeight-500) GUIRegisterMsg($WM_SIZE, 'MY_WM_SIZE') Local $querysel = GUICtrlCreateCombo("", 5, 5, 155, 25, $CBS_DROPDOWNLIST) GUICtrlSetData($querysel, $Qlist[0][1], $Qlist[1][0]) GUICtrlSetResizing(-1, 552) Local $queryopt = GUICtrlCreateCombo("Query Options", 165, 5, 90, 20, $CBS_DROPDOWNLIST) GUICtrlSetData($queryopt, "Save to File|New|Edit Name|Delete|Open Query Folder") GUICtrlSetResizing(-1, 552) Local $tab = GUICtrlCreateTab(5, 35, 235, 200) $qtab = GUICtrlCreateTabItem(" Query ") Local $query = GuiCtrlCreateEdit("", 10, 60, 240, 170) GUICtrlSetFont(-1, 9, 0, 0, "Lucida Console") GUICtrlSetData($query, $Qlist[1][1]) $rtab = GUICtrlCreateTabItem(" Results ") Local $result = GuiCtrlCreateEdit("", 10, 80, 240, 150) GUICtrlSetFont(-1, 9, 0, 0, "Lucida Console") Local $status = GUICtrlCreateLabel("Last Ran: None", 15, 62, 500) GUICtrlSetResizing(-1, 802) GUICtrlCreateTabItem("") Local $run = GUICtrlCreateButton("Run Query", 5, 270, 130, 25) GUICtrlSetResizing(-1, 584) Local $settings = GUICtrlCreateButton("Connection Settings", 140, 270, 115, 25) GUICtrlSetResizing(-1, 584) Local $consel = GUICtrlCreateCombo("", 5, 5, 390, 25, $CBS_DROPDOWNLIST) GUICtrlSetResizing(-1, 802) Local $newc = GUICtrlCreateButton("New", 400, 5, 40, 20) GUICtrlSetResizing(-1, 802) Local $edic = GUICtrlCreateButton("Edit", 445, 5, 40, 20) GUICtrlSetResizing(-1, 802) Local $delc = GUICtrlCreateButton("Delete", 490, 5, 40, 20) GUICtrlSetResizing(-1, 802) Local $test = GUICtrlCreateButton("Test Connection", 540, 5, 95, 20) GUICtrlSetResizing(-1, 802) Local $return = GUICtrlCreateButton("Return to Query", 640, 5, 95, 20) GUICtrlSetResizing(-1, 802) Local $divider = GUICtrlCreateLabel("",-5,30,760,3,BitOR($SS_SUNKEN,$WS_BORDER)) GUICtrlSetResizing(-1, 802) Local $cnamel = GUICtrlCreateLabel("Connection Name:", 15, 45) GUICtrlSetResizing(-1, 802) Local $cname = GUICtrlCreateInput("", 110, 41, 200, 20) GUICtrlSetResizing(-1, 802) Local $cstringl = GUICtrlCreateLabel(" Connection String" & @CRLF & _ "Replace your password with *PW*. For example:" & @CRLF & _ "Provider=ASEOLEDB;User ID=-USERID-;Password=*PW*;Data Source=-SERVER-:-PORT-;Initial Catalog=-DatabaseName-" & @CRLF & _ "If you need assistance with your specific connection string try using: https://www.connectionstrings.com/", 5, 70, 740, 60) GUICtrlSetResizing(-1, 802) Local $cstring = GUICtrlCreateInput("", 10, 125, 725, 20) GUICtrlSetResizing(-1, 802) Local $pwl = GUICtrlCreateLabel("Password:", 10, 165) GUICtrlSetResizing(-1, 802) Local $pw = GUICtrlCreateInput("", 65, 161, 340, 20, BitOR($ES_PASSWORD, $ES_AUTOHSCROLL)) GUICtrlSetResizing(-1, 802) Local $savc = GUICtrlCreateButton("Save", 480, 161, 100, 20) GUICtrlSetResizing(-1, 802) Local $canc = GUICtrlCreateButton("Cancel", 600, 161, 100, 20) GUICtrlSetResizing(-1, 802) Guimod("SETTINGS", $GUI_HIDE+1) GuiMod('SETTINGS-ENTRY', $GUI_DISABLE) $selC = $selConn GuiMod('RELOAD-CON') Local $hSelAll = GUICtrlCreateDummy() Dim $AccelKeys[1][2] = [["^a", $hSelAll]] GUISetAccelerators($AccelKeys, $GUI) GUISetState(@SW_SHOW) Local $nMsg While 1 $nMsg = GUIGetMsg() Switch $nMsg ;Checks if a button has been pressed Case $querysel $selQ[1] = GUICtrlRead($querysel) If $selQ[0] <> $selQ[1] Then QuerySelect() GUICtrlSetState($qtab, $GUI_SHOW) Case $queryopt Switch GUICtrlRead($queryopt) Case "Save to File" $fh = FileOpen(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt", 2) FileWrite($fh, GuiCtrlRead($query)) $Qlist[_ArraySearch($Qlist, GuiCtrlRead($querysel), 0, 0, 0, 0, 1, 0)][1] = GuiCtrlRead($query) $Qlist[_ArraySearch($Qlist, GuiCtrlRead($querysel), 0, 0, 0, 0, 1, 0)][2] = False GUICtrlSetData($qtab, " Query ") Case "New" $temp = InputBox("New Query Name","Enter a name for your new query." & @CRLF & "Please be aware only letters, numbers, underscores, and spaces are allowed." & @CRLF & "Symbols will be removed.", "", "", Default, Default, Default, Default, 0, $GUI) $temp = StringRegExpReplace($temp, "[^A-Za-z0-9 _]", "") If $temp <> "" And _ArraySearch($Qlist, $temp, 0, 0, 0, 0, 1, 0) = -1 Then FileWriteLine(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $temp & ".txt", "--First Line Placeholder") QueryLoad() GUICtrlSetData($querysel, "") GUICtrlSetData($querysel, $QList[0][1], $temp) $selQ[1] = GUICtrlRead($querysel) If $selQ[0] <> $selQ[1] Then QuerySelect() Else MsgBox(0,'Invalid Input','Invalid name entered. (Symbols and Duplicate Names are not allowed)', 0, $GUI) EndIf Case "Edit Name" $temp = InputBox("Edit Query name","Enter a new name for your query." & @CRLF & "Please be aware only letters, numbers, underscores, and spaces are allowed." & @CRLF & "Symbols will be removed.", GuiCtrlRead($querysel)) $temp = StringRegExpReplace($temp, "[^A-Za-z0-9 _]", "") If $temp <> "" And _ArraySearch($Qlist, $temp, 0, 0, 0, 0, 1, 0) = -1 Then FileMove(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt", @AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $temp & ".txt") $selQ[0] = $temp QueryLoad() GUICtrlSetData($querysel, "") GUICtrlSetData($querysel, $QList[0][1], $temp) $selQ[1] = GUICtrlRead($querysel) If $selQ[0] <> $selQ[1] Then QuerySelect() Else MsgBox(0,'Invalid Input','Invalid name entered. (Symbols and Duplicate Names are not allowed)', 0, $GUI) EndIf Case "Delete" If UBound($Qlist) = 2 Then MsgBox(0,'Deletion Error','You may not delete the only saved query', 0, $GUI) Else If MsgBox(4, 'Confirm Deletion', "Are you sure you want to delete the query named: " & @CRLF & GUICtrlRead($querysel), 0, $GUI) = 6 Then FileDelete(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt") QueryLoad() GUICtrlSetData($querysel, "") GUICtrlSetData($querysel, $QList[0][1], $Qlist[1][0]) $selQ[1] = GUICtrlRead($querysel) $selQ[0] = $selQ[1] GUICtrlSetData($query, $Qlist[1][1]) EndIf EndIf Case "Open Query Folder" ShellExecute(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") EndSwitch GUICtrlSetData($queryopt, 'Query Options', 'Query Options') Case $run GuiMod("RUNNING", $GUI_DISABLE) ToolTip("Executing SQL query, please wait...") $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Mode = 16 ; shared $sqlCon.CursorLocation = 3 ; client side cursor Local $connString = StringReplace(GuiCtrlRead($cstring), "*PW*", GuiCtrlRead($pw)) $sqlCon.Open($connString) If @error Then MsgBox(0, "Fail", "Failed to connect to the database") Else $sqlCon.CommandTimeout = 60 GUICtrlSetData($result, ProcessQuery(GuiCtrlRead($query))) GUICtrlSetState($rtab , $GUI_SHOW) GuiCtrlSetData($status, "Last Ran: " & GuiCtrlRead($querysel) & " - " & StringRight(_NowCalc(), 8)) EndIf ToolTip("") GuiMod("RUNNING", $GUI_ENABLE) Case $settings ToolTip("Loading...") $selC = $selConn GuiMod('MAIN', $GUI_HIDE) GuiMod('RELOAD-CON') GuiMod('SETTINGS', $GUI_SHOW) ToolTip("") Case $consel If GUICtrlRead($consel) <> $selC Then $selC = _ArraySearch($conName, GUICtrlRead($consel), 0, 0, 0, 0, 1, 1) GUICtrlSetData($cname, $conName[$selC][1]) GUICtrlSetData($cString, $conStr[$selC][1]) GUICtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1])) $selC = GUICtrlRead($consel) EndIf Case $newc GuiMod('SETTINGS-SELECT', $GUI_DISABLE) GuiMod('SETTINGS-ENTRY', $GUI_ENABLE) GuiCtrlSetData($consel, '*New-Connection-Entry*', '*New-Connection-Entry*') GuiCtrlSetData($cname, '') GuiCtrlSetData($cString, '') GuiCtrlSetData($pw, '') $selC = -1 Case $edic GuiMod('SETTINGS-SELECT', $GUI_DISABLE) GuiMod('SETTINGS-ENTRY', $GUI_ENABLE) $selC = GUICtrlRead($consel) _ArrayDisplay($conName, "2") $selC = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1) GuiCtrlSetData($cname, $conName[$selC][1]) $conName[$selC][1] &= "*" GuiCtrlSetData($cString, $conStr[$selC][1]) GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1])) Case $delc If UBound($conName) = 2 Then MsgBox(0,'Error','You can not delete the only connection setting.', 0, $GUI) Else $selC = GuiCtrlRead($consel) If MsgBox(4, 'Confirm Deletion', 'Are you sure you want to delete the connection: ' & $selC, 0, $GUI) = 6 Then ToolTip('Loading...') $selC = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1) $conName[$selC][1] = '*!*BLANK*!*' $conStr[$selC][1] = '*!*BLANK*!*' $conPW[$selC][1] = '*!*BLANK*!*' IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", $conName) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", $conStr) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", $conPW) GuiMod('RELOAD-CON') ToolTip('') EndIf EndIf Case $savc If GuiCtrlRead($cname) = "" Or _ArraySearch($conName, GuiCtrlRead($cname), 0, 0, 0, 0, 1, 1) > -1 Then MsgBox(0,'Invalid Input',"You can't have a blank connection name or two connections with the same name", 0, $GUI) Else ToolTip("Loading...") If $selC = -1 Then For $i = 1 To UBound($conName)-1 If $conName[$i][1] = '*!*BLANK*!*' Then $selC = $i $conName[$i][1] = GuiCtrlRead($cname) $conStr[$i][1] = GuiCtrlRead($cstring) $conPW[$i][1] = StringEncrypt(True, GuiCtrlRead($pw)) ExitLoop ElseIf $i = UBound($conName)-1 Then $selC = UBound($conName) _ArrayAdd($conName, UBound($conName) & "|" & GuiCtrlRead($cname)) _ArrayAdd($conStr, UBound($conStr) & "|" & GuiCtrlRead($cstring)) _ArrayAdd($conPW, UBound($conPW) & "|" & StringEncrypt(True, GuiCtrlRead($pw))) EndIf Next Else $conName[$selC][1] = GuiCtrlRead($cname) $conStr[$selC][1] = GuiCtrlRead($cstring) $conPW[$selC][1] = StringEncrypt(True, GuiCtrlRead($pw)) EndIf IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", $conName) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", $conStr) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", $conPW) GuiMod('SETTINGS-SELECT', $GUI_ENABLE) GuiMod('SETTINGS-ENTRY', $GUI_DISABLE) GuiMod('RELOAD-CON') ToolTip('') EndIf Case $canc ToolTip("Loading...") GuiMod('SETTINGS-SELECT', $GUI_ENABLE) GuiMod('SETTINGS-ENTRY', $GUI_DISABLE) GuiMod('RELOAD-CON') ToolTip('') Case $test $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Mode = 16 ; shared $sqlCon.CursorLocation = 3 ; client side cursor Local $connString = StringReplace(GuiCtrlRead($cstring), "*PW*", GuiCtrlRead($pw)) $sqlCon.Open($connString) If @error Then MsgBox(0, "Fail", "Failed to connect to the database") Else MsgBox(0, "Success", "Successfully connected to the database") EndIf Case $return $selConn = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1) IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", $selConn) GuiMod('SETTINGS', $GUI_HIDE) GuiMod('MAIN', $GUI_SHOW) Case $hSelAll _SelectAllTextInEdit() Case $GUI_EVENT_CLOSE Exit EndSwitch If $fResized Then GuiMod('RESIZE') $fResized = False EndIf If $guics[0] = True Then _Input_Check($cname) WEnd Func QuerySelect() Local $oldp = _ArraySearch($Qlist, $selQ[0], 0, 0, 0, 0, 1, 0) Local $old = GuiCtrlRead($query) If $Qlist[$oldp][1] <> $old Then $Qlist[$oldp][1] = $old $Qlist[$oldp][2] = True EndIf Local $newp = _ArraySearch($Qlist, $selQ[1], 0, 0, 0, 0, 1, 0) GUICtrlSetData($query, $Qlist[$newp][1]) $selQ[0] = $selQ[1] If $Qlist[$newp][2] = False Then GUICtrlSetData($qtab, " Query ") Else GUICtrlSetData($qtab, " Query (unsaved) ") EndIf EndFunc Func QueryLoad($reload = True) Local $qFiles = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") Local $tQlist[UBound($qFiles)][3] Local $selT $tQlist[0][0] = $qFiles[0] $tQlist[0][1] = "" For $i = 1 To $tQlist[0][0] $tQlist[$i][0] = StringTrimRight($qFiles[$i], 4) If $reload Then $selT = _ArraySearch($Qlist, $tQlist[$i][0], 0, 0, 0, 0, 1, 0) If $selT > -1 Then $tQlist[$i][1] = $Qlist[$selT][1] $tQlist[$i][2] = $Qlist[$selT][2] Else $tQlist[$i][1] = FileRead(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $qFiles[$i]) $tQlist[$i][2] = False EndIf Else $tQlist[$i][1] = FileRead(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $qFiles[$i]) $tQlist[$i][2] = False EndIf $tQlist[0][1] &= StringTrimRight($qFiles[$i], 4) If $i < $tQlist[0][0] Then $tQlist[0][1] &= "|" Next $Qlist = $tQlist EndFunc Func ProcessQuery($input) Local $resultO = $sqlCon.Execute($input) If Not @error Then Local $resultT = "" Local $recordE = 0 Local $resultA[2][0] Local $i, $z While $recordE = 0 and IsObj($resultO) Redim $resultA[2][0] For $oField In $resultO.Fields Redim $resultA[2][UBound($resultA, 2)+1] $resultA[1][UBound($resultA, 2)-1] = $oField.Name Next ReDim $resultA[UBound($resultA)+1][UBound($resultA, 2)] While Not $resultO.EOF ReDim $resultA[UBound($resultA)+1][UBound($resultA, 2)] $i = 0 For $oField in $resultO.Fields $resultA[UBound($resultA)-1][$i] = StringStripWS(StringStripWS($oField.Value, 1), 2) $i += 1 Next $resultO.MoveNext WEnd For $i = 0 To UBound($resultA, 2)-1 $z = 0 For $x = 1 To UBound($resultA)-1 If StringLen($resultA[$x][$i]) > $z Then $z = StringLen($resultA[$x][$i]) Next $resultA[0][$i] = $z Next For $i = 0 To UBound($resultA, 2)-1 $z = "" For $x = 1 To $resultA[0][$i] + 4 $z &= "-" Next $resultA[2][$i] = $z Next For $x = 1 To UBound($resultA)-1 For $i = 0 To UBound($resultA, 2)-1 $resultT &= $resultA[$x][$i] For $z = StringLen($resultA[$x][$i]) To $resultA[0][$i] + 4 $resultT &= " " Next Next $resultT &= @CRLF Next $resultT &= @CRLF & "Total Lines Returned: " & UBound($resultA)-3 & @CRLF $resultT &= "__________________________________________________________" & @CRLF & @CRLF & @CRLF $resultO = $resultO.NextRecordset $recordE = @error WEnd $resultO.Close Else Local $resultT = $sqlErr EndIf Return $resultT EndFunc Func GuiMod($action, $toggle = 0) Switch $action Case 'RESIZE' If ($GUIs[2] < 266 or $GUIs[3] < 306) And $guics[0] = False Then WinMove($GUI, '', $GUIs[0], $GUIs[1], 276, 336) GUICtrlSetPos($tab, 5, 35, $GUIs[2] - 25, $GUIs[3] - 106) GUICtrlSetPos($query, 10, 60, $GUIs[2] - 36, $GUIs[3] - 136) GUICtrlSetPos($result, 10, 80, $GUIs[2] - 36, $GUIs[3] - 156) GUICtrlSetPos($result, 10, 80) Case 'MAIN' GUICtrlSetState($querysel, $toggle) GUICtrlSetState($queryopt, $toggle) GUICtrlSetState($tab, $toggle) GUICtrlSetState($query, $toggle) GUICtrlSetState($result, $toggle) GUICtrlSetState($status, $toggle) GUICtrlSetState($run, $toggle) GUICtrlSetState($settings, $toggle) Case 'SETTINGS' If $toggle = $GUI_SHOW Then GUISetStyle(BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_SYSMENU)) $GUIs = WinGetPos($GUI) $guics[0] = True $guics[1] = $GUIs[2] $guics[2] = $GUIs[3] $guics[3] = $GUIs[0] WinMove($GUI, '', 25, $GUIs[1], 750, 221) ElseIf $toggle = $GUI_HIDE Then GUISetStyle(BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_SYSMENU, $WS_SIZEBOX)) $guics[0] = False WinMove($GUI, '', $guics[3], Default, $guics[1], $guics[2]) ElseIf $toggle = $GUI_HIDE+1 Then $toggle -= 1 EndIf GUICtrlSetState($consel, $toggle) GUICtrlSetState($newc, $toggle) GUICtrlSetState($edic, $toggle) GUICtrlSetState($delc, $toggle) GUICtrlSetState($test, $toggle) GUICtrlSetState($return, $toggle) GUICtrlSetState($divider, $toggle) GUICtrlSetState($cnamel, $toggle) GUICtrlSetState($cname, $toggle) GUICtrlSetState($cstringl, $toggle) GUICtrlSetState($cstring, $toggle) GUICtrlSetState($pwl, $toggle) GUICtrlSetState($pw, $toggle) GUICtrlSetState($savc, $toggle) GUICtrlSetState($canc, $toggle) Case 'SETTINGS-SELECT' GUICtrlSetState($consel, $toggle) GUICtrlSetState($newc, $toggle) GUICtrlSetState($edic, $toggle) GUICtrlSetState($delc, $toggle) GUICtrlSetState($test, $toggle) GUICtrlSetState($return, $toggle) Case 'SETTINGS-ENTRY' GUICtrlSetState($cnamel, $toggle) GUICtrlSetState($cname, $toggle) GUICtrlSetState($cstringl, $toggle) GUICtrlSetState($cstring, $toggle) GUICtrlSetState($pwl, $toggle) GUICtrlSetState($pw, $toggle) GUICtrlSetState($savc, $toggle) GUICtrlSetState($canc, $toggle) Case 'RUNNING' GUICtrlSetState($querysel, $toggle) GUICtrlSetState($queryopt, $toggle) GUICtrlSetState($query, $toggle) GUICtrlSetState($tab, $toggle) GUICtrlSetState($result, $toggle) GUICtrlSetState($run, $toggle) GUICtrlSetState($settings, $toggle) Case 'RELOAD-CON' $conName = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name") $conStr = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String") $conPW = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password") Local $sC = $conName _ArraySort($sC, 0, 1, 0, 1) GUICtrlSetData($consel, "") If $conName[$selC][1] <> "*!*BLANK*!*" Then GuiCtrlSetData($cname, $conName[$selC][1]) GuiCtrlSetData($cString, $conStr[$selC][1]) GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1])) $selC = $conName[$selC][1] Else For $i = 1 To UBound($sC)-1 If $sC[$i][1] <> '*!*BLANK*!*' Then GuiCtrlSetData($cname, $sC[$i][1]) GuiCtrlSetData($cString, $conStr[$sC[$i][0]][1]) GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$sC[$i][0]][1])) $selC = $sC[$i][1] ExitLoop EndIf Next EndIf For $i = 1 To UBound($sC)-1 If $sC[$i][1] <> '*!*BLANK*!*' Then GUICtrlSetData($consel, $sC[$i][1], $selC) Next EndSwitch EndFunc Func MY_WM_SIZE($hWnd, $Msg, $wParam, $lParam) $GUIs = WinGetPos($GUI) $fResized = True Return $GUI_RUNDEFMSG EndFunc ;==>MY_WM_SIZE Func StringEncrypt($bEncrypt, $sData, $sPassword = 'SQL') If $sData = "" Then Return '' _Crypt_Startup() ; Start the Crypt library. Local $sReturn = '' If $bEncrypt Then ; If the flag is set to True then encrypt, otherwise decrypt. $sReturn = _Crypt_EncryptData($sData, $sPassword, $CALG_RC4) Else $sReturn = BinaryToString(_Crypt_DecryptData($sData, $sPassword, $CALG_RC4)) EndIf _Crypt_Shutdown() ; Shutdown the Crypt library. Return $sReturn EndFunc ;==>StringEncrypt Func _Input_Check($hInput) Local $sText = GUICtrlRead($hInput) If StringRegExp($sText, "[^A-Za-z0-9 _]") Then GUICtrlSetData($hInput, StringRegExpReplace($sText, "[^A-Za-z0-9 _]", "")) EndFunc Func _SelectAllTextInEdit();will make select all text in any focused edit Local $theHandle = _WinAPI_GetFocus() Local $TheClass = _WinAPI_GetClassName($theHandle) If $TheClass = 'Edit' Then _GUICtrlEdit_SetSel($theHandle, 0, -1) EndFunc Func MyErrFunc() $HexNumber=hex($oMyError.number,8) $sqlErr = "We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & $HexNumber & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext SetError(1) ; to check for after this function returns Endfunc  
    • skybax
      Array to Listview
      By skybax
      Good morning all,
      I have a question regarding a situation im in...
       
      I have an array with information from sql query, how can i send this information to a listview ?
       
      $citeste_daune = "SELECT `id_dauna`,`data_incident`,`sala`,`autor`,`suma` FROM `daune` WHERE `status`=1;" $sa_citit_daune = _query($sqlinstance, $citeste_daune) Global $aresult[10001][5] = [[10000, 5]] Global $iindex = 0 With $sa_citit_daune While NOT .eof $aresult[$iindex][0] = .fields("id_dauna").value $aresult[$iindex][1] = .fields("data_incident").value $aresult[$iindex][2] = .fields("sala").value $aresult[$iindex][3] = .fields("autor").value $aresult[$iindex][4] = .fields("suma").value $iindex = $iindex + 1 .movenext WEnd EndWith ReDim $aresult[$iindex][5] $aresult[0][0] = $iindex - 1 _ArrayDisplay($aresult) GUICtrlCreateListViewItem(_ArrayToString($aresult), $lista_daune_active)  
      This is what i see when i execure _ArrayDisplay($aresult)

    • Queener
      Retreiving data from SQL
      By Queener
      Using the MSSQL.udf
      I asked this question once, but haven't had a chance to reply to my old post so posting a new one instead of bouncing the old post.
      I'm trying to retrieve data from my database in Microsoft SQL Server 2014. When I tried the code below; empty message box return and no error approaches.
      $Connection = _MSSQL_Con($ServerAddress, $ServerUsername, $ServerPassword, $ServerDBName) $Testquery = _MSSQL_Query($Connection, "SELECT User_ID From Banker where Manager like '%Jason%'") msgbox(0, "Test", $Testquery) exit
    • sivaramanm
      Unable to retrieve inserted row count in MySQL using ADO from AutoIT
      By sivaramanm
      From AutoIT script (Pretty much same syntax as VBA), Tried connecting to MySQL Server. While i am able to insert a new row successfully, unable to verify the rowcount (# of inserted row - to verify success or failure).
      Have tried two different methods -
      to use the RecordsAffected variable from Connection Execute function to use the RecordSet and retrieve the rowcount But have been missing something and none of these methods return the actual row count.
      Any help would be appreciated.!!!
      Cross-posted in http://stackoverflow.com/questions/27411599/unable-to-retrieve-inserted-row-count-in-mysql-using-ado-from-autoit
      MySQLConnect() $EVENT_TIME= "2014-12-12 12:12:12" $LSMCName='LSMC1' $NEType='MME0001' $OMTarFile='A_MME0011-60MIN-20141212-12-v.tar' $CSVFile='12-00-S1AP.csv' $KPIType='S1AP_HO' $UpdateStatus='NotUpdated' $ReTries='0' If Not (InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries)=1) Then WriteLog("[Error] Record insertion failed for " & $EVENT_TIME & '" ' & $LSMCName & ' ' & $NEType & ' ' & $OMTarFile & ' ' & $CSVFile & ' ' & $KPIType & ' ' & ' NotUpdated 0') EndIf MysqlDisconnect() ;~ ####################### Sub Function Definitions Func MySQLConnect() Local $sDriver="MySQL ODBC 5.3 ANSI Driver" Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $sDriver) If @error or $val = "" Then SetError(2) Return -1 EndIf $constrim="DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost;DATABASE=pmdemo;uid=rootuser;pwd=rootpass;" $oDBConnect = ObjCreate ("ADODB.Connection") ; <== Create SQL connection $oDBConnect.Open ($constrim) ; <== Connect with required credentials if @error Then WriteLog("[Error] Failed to connect to the database") SetError(2) Return -2 Else ;MsgBox(0, "Success!", "Connection to database successful!") Return 1 EndIf EndFunc Func MySQLDisConnect() $oDBConnect.Close ; ==> Close the database EndFunc Func InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries) Local $RowCount = 0 Local $result = ObjCreate("ADODB.Recordset") $sQuery = "INSERT INTO 4gc_fileupdatelog (id,EVENT_TIME,LSMC,NEType,TarFile,CSVFile,KPIFile,UpdateStatus,ReTries) VALUES ('0'," & _ "'" & $EVENT_TIME & "'," & _ "'" & $LSMCName & "'," & _ "'" & $NEType & "'," & _ "'" & $OMTarFile & "'," & _ "'" & $CSVFile & "'," & _ "'" & $KPIType & "'," & _ "'" & $UpdateStatus & "'," & _ "'" & $ReTries & "'" & _ ") ON DUPLICATE KEY UPDATE ReTries=ReTries+1,UpdateStatus='" & $UpdateStatus & "';" $result = $oDBConnect.Execute($sQuery,$RowCount) If @error Then MsgBox(1,1,"Error executing query...") Return -2 EndIf ;# Method-1 : To use records affected from Execute function If $RowCount >= 1 Then MsgBox(1,1,"Success") Else MsgBox(1,1,"Failed, rowcount is:" & $RowCount ) EndIf If Not ($result.bof AND $result.eof) Then WriteLog("[Error] No Rows found") Return 0 EndIf ;# Method-2 : To use recordsset object and retrieve the rows/columns count If IsObj($result) And $result.EOF=False Then $myarray=$result.GetRows() $rows = UBound($myarray,1) $cols = UBound($myarray,2) MsgBox(1,1," rows: " & $rows & " cols: " & $cols) If ($rows = 1) Then WriteLog("[Info] Record inserted successfully") Return 1 ElseIf ($rows = 2) Then WriteLog("[Alert] Record updated successfully. affected row(s) is " & $rows) Return $rows Else > Blockquote WriteLog("[Error] Record insertion failed. affected row(s) is " & $rows) Return 0 EndIf EndIf EndFunc