Sample MySQL script

[Uncle Argyle]

I threw this script together to show a few basic functions of the MySQL UDF.

Someone had asked to see an example so I thought I's post it here...maybe someone can find it useful.

You will need to edit the mysql connection string to match your server, password and user. database name is Test (with a capital T)

Also, below the script I have included the exported database file. Copy and paste into a text file, rename wih a .sql extension and import into MySQL.

Mike.

expandcollapse popup

#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.1.1.0
 Author:         myName

 Script Function:
    Template AutoIt script.

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

#include <GUIConstants.au3>
#include <String.au3>
#include <GuiListView.au3>
#include <MySQL.au3>
; #include <File.au3>

Opt("TrayIconHide", 0)
Opt("GUICloseOnESC", 1)
Opt("GUIOnEventMode", 1)

Global $currentid = ""
Global $ListView
Global Const $WM_NOTIFY = 0x004E

;ListView Events
Global Const $NM_FIRST = 0
Global Const $NM_CLICK = ($NM_FIRST - 2)
Global Const $NM_DBLCLK = ($NM_FIRST - 3)

GUIRegisterMsg($WM_NOTIFY, "WM_Notify_Events")

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")
            
Global $dbmo = _MySQLConnect('dbuser','dbpassword','database','ipaddress')

$MainForm = GUICreate("TEST", 287, 237, -1, -1)
    
    $ListView = GUICtrlCreateListView("ID|Name|Pet Name|Favorite Food", 16, 40, 250, 150)
;_GUICtrlListViewSetColumnWidth($ListView, 0, 75)
    
    GUICtrlSendMsg($ListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_GRIDLINES, $LVS_EX_GRIDLINES)
    GUICtrlSendMsg($ListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_FULLROWSELECT, $LVS_EX_FULLROWSELECT)
    
    $Button1 = GUICtrlCreateButton("Add", 16, 200, 75, 25, 0)
    $Button2 = GUICtrlCreateButton("Delete", 104, 200, 75, 25, 0)
    $Button3 = GUICtrlCreateButton("Close", 192, 200, 75, 25, 0)
    
    $Label1 = GUICtrlCreateLabel("Double click on an entry to edit", 16, 16, 183, 17)
    GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
    
    GUISetOnEvent($GUI_EVENT_CLOSE, "close")
    GUICtrlSetOnEvent($Button1, "showaddform")
    GUICtrlSetOnEvent($Button2, "deleteentry")
    GUICtrlSetOnEvent($Button3, "close")
    
    GUISetState(@SW_SHOW)
    
    
$EntryForm = GUICreate("Entry Form", 262, 158, -1, -1)
    $Input1 = GUICtrlCreateInput("", 104, 16, 121, 21)
    $Input2 = GUICtrlCreateInput("", 104, 48, 121, 21)
    $Input3 = GUICtrlCreateInput("", 104, 80, 121, 21)
    $Label2 = GUICtrlCreateLabel("Person Name", 16, 16, 76, 17, $SS_RIGHT)
    $Label3 = GUICtrlCreateLabel("Pet's Name:", 16, 48, 77, 17, $SS_RIGHT)
    $Label4 = GUICtrlCreateLabel("Favorite Food", 16, 80, 77, 17, $SS_RIGHT)
    $Button4 = GUICtrlCreateButton("Add/Update", 32, 120, 75, 25, 0)
    $Button5 = GUICtrlCreateButton("Cancel", 152, 120, 75, 25, 0)
    
    GUISetOnEvent($GUI_EVENT_CLOSE, "closeentry")
    GUICtrlSetOnEvent($Button4, "addupdate")
    GUICtrlSetOnEvent($Button5, "closeentry")
    
    GUISetState(@SW_HIDE)

    RefreshList()
        
    While 1
        Sleep(100)
    WEnd
    
Func ShowAddForm()
    GuiSetState(@SW_SHOW, $EntryForm)
    GuiSetState(@SW_HIDE, $MainForm)
    GUICtrlSetData($Input1,"")
    GUICtrlSetData($Input2,"")
    GUICtrlSetData($Input3,"")
    GUISwitch($EntryForm)
    Return
EndFunc;=> ShowAddForm
    
Func CloseEntry()
    GuiSetState(@SW_HIDE, $EntryForm)
    GuiSetState(@SW_SHOW, $MainForm)
    GUISwitch($MainForm)
    Return
EndFunc;=> closeentry

Func Close()
    _MySQLEnd($dbmo)
    Exit
EndFunc;=> close

Func deleteentry()
    $getuser = _GUICtrlListViewGetItemText($ListView, -1, 0)
        If $getuser = "" Then
            Msgbox(0,"Attention", "You need to select an entry from the list first")
            Return
        Else
            $deleteprompt = MsgBox(4,"Delete Entry?","Are you sure you wish to delete this entry?")
                If $deleteprompt = 6 Then
                    $deletequery = _Query($dbmo,"DELETE FROM testtable WHERE ID = " & $getuser)
                Else
                    Return
                EndIf
        Endif
        
    RefreshList()
    
    Return
        
EndFunc;=> deleteentry

Func addupdate()
    $adduser = GUICtrlRead($Input1)
    $addpet = GUICtrlRead($Input2)
    $addfood = GUICtrlRead($Input3)
    
    dim $cvalues[4]
            $cvalues[0] = "c1"
            $cvalues[1] = "c2"
            $cvalues[2] = "c3"
            $cvalues[3] = "";make sure u have one extra blank element
                                        
    dim $values[4]
            $values[0] = $adduser
            $values[1] = $addpet
            $values[2] = $addfood
            $values[3] = "";make sure u have one extra blank element
            
        If $currentid = "" Then
        
                If $adduser = "" OR $addpet = "" OR $addfood = "" Then  
                    MsgBox(16,"Error","You have not completed all the required fields!")
                    Return
                Else                                                
                    _AddRecord($dbmo, 'testtable', $cvalues, $values)   
                    MsgBox(0,"Done","Record Added!")
                    $currentid = ""
                    CloseEntry()
                    RefreshList()
                EndIf
        Else
            _query($dbmo, "UPDATE testtable SET c1 = '" & $adduser & "' WHERE ID = " & $currentid)
            _query($dbmo, "UPDATE testtable SET c2 = '" & $addpet & "' WHERE ID = " & $currentid)
            _query($dbmo, "UPDATE testtable SET c3 = '" & $addfood & "' WHERE ID = " & $currentid)
            MsgBox(0,"Done","Record Updated!")
            $currentid = ""
            CloseEntry()
            RefreshList()
        EndIf
        
        Return

EndFunc;=> addupdate

Func RefreshList()
    
    $refreshlist = _Query($dbmo,"SELECT * FROM testtable")
    
            If $refreshlist.EOF Then
                MsgBox(48,"Attention", "The table is empty!")
                $refresh.close
                Return
            Endif
                        
        $rows = _Query($dbmo,"SELECT COUNT(*) num FROM testtable")
            
        $rowcount = $rows.Fields("num").value
        
        _GUICtrlListViewDeleteAllItems($ListView)
            
        For $i = 1 to $rowcount
            GuiCtrlCreateListViewItem($refreshlist.Fields("id").value & "|" & $refreshlist.Fields("c1").value & "|" & $refreshlist.Fields("c2").value & "|" & $refreshlist.Fields("c3").value, $listView)
            $refreshlist.MoveNext
        Next
        
EndFunc;=> refreshlist

Func ListView_Click ()
    
EndFunc  ;==>ListView_Click


Func ListView_DoubleClick ()

    $userselect = _GUICtrlListViewGetItemText($ListView, -1, 0)

    ShowAddForm()
    
    $clickresult = _Query($dbmo,"SELECT * FROM testtable WHERE ID = " & $userselect)
    
    GUICtrlSetData($Input1, $clickresult.Fields("c1").value)
    GUICtrlSetData($Input2, $clickresult.Fields("c2").value)
    GUICtrlSetData($Input3, $clickresult.Fields("c3").value)
    
    $currentid =  $clickresult.Fields("id").value
    
    Return
        
EndFunc  ;==>ListView_DoubleClick


; WM_NOTIFY event handler

Func WM_Notify_Events ($hWndGUI, $MsgID, $wParam, $lParam)
    #forceref $hWndGUI, $MsgID, $wParam
    Local $tagNMHDR, $event, $hwndFrom, $code
    $tagNMHDR = DllStructCreate("int;int;int", $lParam);NMHDR (hwndFrom, idFrom, code)
    If @error Then Return
    $event = DllStructGetData($tagNMHDR, 3)
    Select
        Case $wParam = $ListView
            Select
                Case $event = $NM_CLICK
                    ListView_Click ()
                Case $event = $NM_DBLCLK
                    ListView_DoubleClick ()
            EndSelect
    EndSelect
    $tagNMHDR = 0
    $event = 0
    $lParam = 0
EndFunc  ;==>WM_Notify_Events

Func MyErrFunc()

$hexnum=hex($objErr.number,8)

Msgbox(0,"","We intercepted a COM Error!!"      & @CRLF                & @CRLF & _
             "err.description is: " & $objErr.description   & @CRLF & _
             "err.windescription is: " & $objErr.windescription & @CRLF & _
             "err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & $objErr.scriptline    & @CRLF & _
             "err.number is: "       & $hexnum               & @CRLF & _
             "err.source is: "       & $objErr.source        & @CRLF & _
             "err.helpfile is: "       & $objErr.helpfile      & @CRLF & _
             "err.helpcontext is: " & $objErr.helpcontext _
            )
;exit
EndFunc;=> MyErrFunc

expandcollapse popup

-- phpMyAdmin SQL Dump
-- version 2.8.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Nov 15, 2006 at 09:58 AM
-- Server version: 5.0.21
-- PHP Version: 5.1.2
-- 
-- Database: `Test`
-- 
CREATE DATABASE `Test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `Test`;

-- --------------------------------------------------------

-- 
-- Table structure for table `testtable`
-- 

CREATE TABLE `testtable` (
  `ID` int(4) NOT NULL auto_increment,
  `c1` varchar(20) NOT NULL,
  `c2` varchar(20) NOT NULL,
  `c3` varchar(20) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7;

-- 
-- Dumping data for table `testtable`
-- 

INSERT INTO `testtable` (`ID`, `c1`, `c2`, `c3`) VALUES (1, 'john', 'cat', 'cheese'),
(2, 'rolf', 'fish', 'cocoa'),
(3, 'dave', 'buffy', 'pears'),
(6, 'Phil', 'Buffy', 'Apple'),
(5, 'Matt', 'Scratchy', 'Steak');

[Meszaros Csaba]

Thank you!

That is more than useful! It spares me from writing difficult array- manipulating formulas. MySQL rules! A Nobel-prize!

Csaba

Edited November 15, 2006 by Meszaros Csaba

[ConsultingJoe]

Does anyone know sql server 2005?

[ptrex]

@zerocool60544

Yes

[ptrex]

@zerocool60544

Scheduling backups and store it in different folders :

In case you need to store it in DIFFERENT folders, you can use the SQLCMD command line util.

Example of running a SQLCMD script :

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

In AutoIT use

_RunDos("sqlcmd -S .\SQLExpress -i c:\expressmaint.sql")oÝ÷ ÙD4eZ®&ëÅ©©ä½ëay¶êlr¸©¶Ú-ç4÷gk÷ÓÝÆÞ²É{lªU¢¶ayú%"(ק¶«¨µç±yDZ¦·¬²f¢×Zµ¦Ú±oÝ÷ ٩ݲêi+ax­ Õh¶¬r¸©µÉjëh×6
$dir = "C:\backup1"

_RunDos("sqlcmd -S .\SQLExpress -i"c:\backup scripts\expressmaint.sql" -v DB="ALL_USER" _
 -v BACKUPFOLDER=$dir -v DBRETAINUNIT="days" -v DBRETAINVAL="1")

I hope this explains a bit how it works.

More information on command and parameters see here :

Backup Script with parameters

Please read this site before starting with it. It shows all possibilities.

Once the script is tested and works OK.

You can compile the AutoIT script and schedule it in XP, using scheduled tasks.

regards,

ptrex