Autoit3 Mysql UDF

 mysql.au3.txt    mysql-connector-odbc-3.51.29-win32.part1.rar    mysql-connector-odbc-3.51.29-win32.part2.rar   

Title: MySQL UDF functions
Filename: MySQL.au3
Description: A collection of functions for interacting with MySQL
Author: cdkid
Version: 1.6
Requirements: Autoit V3 with COM support.
Note: Make sure to add _MySQLEnd to the end of any script you use this with or you will have open connections to your DB left open!!!!
Note2: at the end of your script i find that it helps to turn your connection object (in the examples i use $sql) to "" so add $sql = "" to the end
Note3: I think it is because using COM from a non-server connecting to a DB on a server, but these can take an EXTREMELY long time.
---> functions:
_MySQLConnect()
Open a connection to the specified Database
_MySQLEnd()
Close the connection (read notes!)
_AddRecord()
Adds a record to a specified table
_DeleteRecord()
Deletes a record from the specifed table.
_Query()
Execute a query to the database
_CreateTable()
Adds a table to the database
_CreateColumn()
Adds a column to the given table
_DropCol()
Deletes a column from the given table
_DropTbl()
Deletes a table from the given DB
_CountRecords()
Counts the number of records in the given column
_CountTables()
Counts the number of tables in the database
_GetColNames()
Gets the names of all the columns in the given table
_GetTblNames()
Gets the names of all the tables in the database
_GetColvals()
Gets all of the values of the specified column
_GetColType()
Gets the DATA TYPE of the specified column
_GetColCount()
Gets a count of all columns in the specified table
_MySQLTableExists()
Find out whether or not a specified table exists
_GetDBNames()
Get a list & count of databases on the current server.
_ChangeCon()
Change your connection string.

 

here are a few examples
#1 start a connection, do a SELECT statement, write the return value to 'C:\test.txt' then end connection

 

$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")
$var = _Query($sql,"SELECT * FROM mytable WHERE user = 'username'")
With $var
While NOT .EOF
FileWriteLine("c:\test.txt",.Fields("user_name").value & @CRLF)
.MoveNext
WEnd
EndWith
_MySQLEnd($sql)

 

#2 start a connection, add a record, delete a record, end connection

 

dim $values[5]
$values[0] = "col1"
$values[1] = "col2"
$values[2] = "col3"
$values[3] = "col4"
$values[4] = "";make sure u have one extra blank element

$sql = _MySQLConnect("sa","sa","mydb","mywebsite.com")
_AddRecord($sql, 'mytable', $values, $values)
;this executes a query ("INSERT INTO mytable (col1, col2, col3, col4) VALUE (col1, col2, col3, col4)
;which adds a record where col1, col2, col3, and col4 have the value "col1", "col2" etc
_DeleteRecord($sql, "mytable", "username", "user")
;that executes a MySQL query ("DELETE FROM mytable WHERE username = 'user';)
_MySQLEnd($sql)

 

#3 - Connect, create a table, add a record, count records in the table, drop the table, end connection

 

 

$sql = _MySQLConnect('sa','sa','mydb','mywebsite.com')
_CreateTable($sql, 'testtable', 'tt_id')
_AddRecord($sql, 'testtable', 'tt_id', 1)
$count = _CountRecords($sql, 'testtable', 'tt_id', 1)
;this executes the query

 

#4 - Connect, get a list of databases, MessageBox the result

 

$sql = _MySQLConnect('sa','sa','','mywebsite.com')
$dbs = _GetDbNames($sql)
For $i in $dbs
MsgBox(0,'',$i)
Next
_MySQLEnd($sql)

 

if you're getting errors...i've found a good way to track em. this was in the idea lab, written by SvenP
put this at the top:

 

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

and then this anywhere

 

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

 

example

 

#include <MySQL.au3>
#include <Array.au3>

;listDB()
;listTB()
listCOL()

Func listDB()
    $sql = _MySQLConnect("root","autoit","","localhost")
    $dbs = _GetDbNames($sql)
    _arrayDisplay($dbs)
    _MySQLEnd($sql)
EndFunc

Func listTB()
    $sql = _MySQLConnect("root","autoit","autoit","localhost")
    $tbs = _GetTblNames($sql)
    _arrayDisplay($tbs)
    _MySQLEnd($sql)
EndFunc

Func listCOL()
    $sql = _MySQLConnect("root","autoit","autoit","localhost")
    $var = _Query($sql,"SELECT * FROM test")
    With $var
        While NOT .EOF
            MsgBox(0,"listCOL","id=" & .Fields("id").value & " name=" & .Fields("name").value & @CRLF)
            .MoveNext
        WEnd
    EndWith
    _MySQLEnd($sql)
EndFunc