MySQL UDFs

[cdkid]

Title: MySQL UDF functions

Filename: MySQL.au3

Description: A collection of functions for interacting with MySQL

Author: cdkid <cdkid@team-kan.com>

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's the first release... only a few functions so far, any input would be appriciated

Todo:

_CreateTable()

_CreateColumn()

_DropCol()

_DropTbl()

_CountRecords()

_CountTables()

_GetTblNames()

_GetColCount()

_GetColType()

_GetColNames()

_GetDbNames()

_ChangeCon()

any input would be much appreciated! i hope this helps someone

The MySQL ODBC 3.51 DRIVER IS REQUIRED FOR THIS

it can be downloaded here: http://dev.mysql.com/downloads/connector/odbc/3.51.html

Updates:

1/27/06 2:27 PM - Added _CreateTable

1/27/06 4:43 PM - Added _CreateColumn

1/27/06 5:14 PM - Added _DropCol and _DropTbl

1/28/06 2:01 PM - Added _CountRecords

1/28/06 10:02 PM - Added _CountTables

1/29/06 3:34 PM - Added _GetColNames

1/30/06 11:26 AM - Added _GetTblNames

2/06/06 12:29 PM - Added _GetColVals

2/08/06 8:24 PM - Added _GetColCount and _GetColType

2/21/06 10:17 PM - Added _MySQLTable exists, and am in process of updating these for submitting to the Standard UDF Library.

3/09/06 10:27 AM - Added _GetDBNames

3/09/06 5:29 PM - Added _ChangeCon

3/17/06 12:39 PM - Updated _MySQLConnect to set @Error to 2 if the MySQL ODBC Driver is not installed.

8/30/06 7:56 PM - Fixed a problem with _AddRecord (Thanks burthold)\

7/31/07 1:51 PM - Updated _MySQLConnect and _ChangeCon to allow a user specified port

Downloads:

v1.6 - 1964

v1.5 - 756

v1.4 - 60

v1.3 - 150

Below is a .ZIP called "ODBC_DRIVER_SETUP" it has the au3 for driver setup, read _ReadMe_.txt to see how to make it work.

ODBC_DRIVER_SETUP.zip

mysql.au3

Edited July 31, 2007 by cdkid

[cdkid]

Ok, 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 "SELECT * FROM `testtable` WHERE `tt_id` = 1 then counts the records
_DropTbl($sql,'testtable')
_MySQLEnd($sql)

#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 anyone has any suggestions for more functions i'd love to hear em

Edited March 20, 2006 by cdkid

[blitzkrg]

AWESOME.. i've been waiting for something like that..

great job.. i cant wait to start coding using this UDF

[cdkid]

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

[cdkid]

124 views and only one reply... anyone got some ideas on what else i should add or any other feedback?

[randallc]

Hi,

Looks a good start; I presume you have to have MySQL for this, and I presume not free?. Perhaps only 0.01% of those who read it? Don't be too despondent about replies; just work on if it is of benefit to you or others over time.

[Of interest rather than any utility, I wonder if you can convert the data to a table dump to csv with your UDF queries, and read/ write it to sqlite database with my functions?]

Best, Randall

[cdkid]

to answer your questions:

you need the MySQL driver

and MySQL is free for personal use

and i dunno how to convert to a csv

Edited January 27, 2006 by cdkid

[randallc]

hi again,

OK I'll try it;

1. How do you view the tables; is there a browser?

2. You asked for other suggestions; why not emulate all the ones I have for sqlite; see last link in my signature?... (some are on the base post; most recent changes in functions [not full update yet in comments] in the functions enumerated in "sqliteExe.au3")

Best, Randall

[blitzkrg]

@randallc - As he said yes mysql is free.

also for a "browser" google for phpmyadmin

that should get you started.

I would say it's very possible to read from mysql and write to sqlite using your functions.

i wasnt able to test out his UDF last night, but i'm going to try over the weekend.

I have a site i'm working on for a friend and we are having problems migrating the data over to a new site..

so i'm hoping i can use this to read from those tables and write to the new ones.

that's the plan anyways..

[blitzkrg]

@cdkid - once the mysql/odbc driver is installed do i have to configure it for my data source?

or is it enough just to have it installed?

[cdkid]

i believe it's enough just to have it installed.

~cdkid

[blitzkrg]

Is it possible to obtain the disposition of a field?

for example

in the table "users" it has say 10 fields

say field 3 is "location" it's 'text' fields with a max of 15 characters.

is it possible to read the tables and report the field name, type and legnth? (or any of the parameters in that field)

Edited January 27, 2006 by blitzkrg

[cdkid]

hhmm i'll look up those properties on the web site and report back when i find something...

here we go

(got the properties from here

$sql = _MySQLConnect(blahblahblah)
$q = _Query($sql, "SELECT * FROM users")
With $q
While NOT .EOF
FileWriteLine("c:\test.txt",.Fields("location").value & " - " & .Fields("location").ActualSize & " - " & .Fields("location").Value & @CRLF)
.MoveNext
WEnd
Endwith
_MYSQLEnd($sql)

did this help u?

--hope this helps

~cdkid

Edited January 27, 2006 by cdkid

[cdkid]

1/27/06 - Added _CreateTable() and _CreateColumn!

[edit1]

1/27/06 - Added _DropTbl() and _DropCol!

[/edit1]

Edited January 27, 2006 by cdkid

[cdkid]

1/28/06 1:43 PM - Added _CountRecords

[edit1]

1/28/06 10:03 PM - Added _CountTables

[/edit1]

Edited January 29, 2006 by cdkid

[modeler2us]

cdkid - this is great.

[cdkid]

[uPDATE]

Version 1.0 - Have a good handful of functions now. Any comments/critcisms (sorry cant spell ) are still appriciated

[Gene]

I hope to try them out soon, at the moment I have more than a few things I don't want to do, but must.

Gene

[uPDATE]

Version 1.0 - Have a good handful of functions now. Any comments/critcisms (sorry cant spell ) are still appriciated

[randallc]

Hi, I still don't have it working.

1. Do you have an example table/ script, to see it working?

2. Do I only need the odbc, or do I need to install "MySQL"?

3. here is a comparison of our udfs.

Proposed;

SQLite// MySQL

_SQL_DeleteRecord //

_SQL_ReIndex //

_SQL_VacuumDB//

//_GetColumnCount()

//_GetColumnType()

_SQL_TableExists//

_SQL_IndexExists//

_SQL_DeleteBAKtables//

SQLite// MySQL

_SQL_AddColumn //_CreateColumn

n/a //_DropCol

_SQL_CopyTable

_SQL_CountRows// _CountRecords

//_CountTables

_SQL_CreateDbCSV

_SQL_CreateDbCSVSlow

_SQL_CreateDbCSVTable

_SQL_CreateIndex

_SQL_CreateQueryTable

_SQL_CreateRandomDb

_SQL_CreateTable// _CreateTable

//_DeleteRecord

_SQL_DialogueDB

_SQL_DropIndex

_SQL_DropTable //_DropTbl

_SQL_DumpTableToText

_SQL_GetCSVHeader

_SQL_GetCSVHeaderRow

_SQL_GetTableHeader //_GetColNames

_SQL_IndexAllCSV

_SQL_IndexFirstCSV

_SQL_IndexNames

_SQL_InsertAddtoTable //_AddRecord

_SQL_OpenCSV

_SQL_QueryToText

_SQL_RenameTable

_SQL_SaveAsDB

_SQL_TableFromArray2D

_SQL_TableNames

_SQL_UpDateItem

Process functions

_SQLiteExe //n/a

_SQL_PrepareToInsert //n/a

_SQL_BeginAndCommit //n/a

n/a //_MySQLConnect

n/a //_MySQLEnd

n/a //_Query

Best, Randall Edited January 30, 2006 by randallc

[cdkid]

well u do not need MySQL installed on the comp ur runnin the script on but u have to have a MySQL DB to connect to and im still working on more functions... dont have a whole lot of time so im doing 2-3 a day