Sign in to follow this  
Followers 0
Uncle Argyle

Sample MySQL script

5 posts in this topic

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.

#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

-- 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');

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Thank you!

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

Csaba :whistle:

Edited by Meszaros Csaba

Share this post


Link to post
Share on other sites

Does anyone know sql server 2005?


[center]AutoIT + Finger Print Reader/Scanner = COOL STUFF -> Check Out Topic![/center][center][font=Arial Black]Check out ConsultingJoe.com[/font][/center][center]My Scripts~~~~~~~~~~~~~~Web Protocol Managing - Simple WiFi Scanner - AutoTunes - Remote PC Control V2 - Audio SpectrascopePie Chart UDF - At&t's TTS - Custom Progress Bar - Windows Media Player Embed[/center]

Share this post


Link to post
Share on other sites

@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

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