[cdkid]

Quote

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

Quote

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.

Attached File(s)

•  mysql.au3 (18.99K)
  Number of downloads: 6498
•  ODBC_DRIVER_SETUP.zip (771bytes)
  Number of downloads: 4910

This post has been edited by cdkid: 31 July 2007 - 08:01 PM

[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

[ code='text' ]    ( Popup )

$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

[ code='text' ]    ( Popup )

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

[ code='text' ]    ( Popup )

$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

[ code='text' ]    ( Popup )

$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 :)

This post has been edited by cdkid: 20 March 2006 - 06:18 PM

[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:

[ code='text' ]    ( Popup )

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

and then this anywhere

[ code='text' ]    ( Popup )

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 :P

This post has been edited by cdkid: 27 January 2006 - 04:36 PM

[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)

This post has been edited by blitzkrg: 27 January 2006 - 09:37 PM

[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

[ code='text' ]    ( Popup )

$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

This post has been edited by cdkid: 27 January 2006 - 09:50 PM

[cdkid]

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

[edit1]

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

[/edit1]

This post has been edited by cdkid: 28 January 2006 - 12:16 AM

[cdkid]

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

[edit1]
1/28/06 10:03 PM - Added _CountTables
[/edit1]

This post has been edited by cdkid: 29 January 2006 - 05:04 AM

[modeler2us]

cdkid - this is great.

[cdkid]

[UPDATE]
Version 1.0 - Have a good handful of functions now. Any comments/critcisms (sorry cant spell :P ) 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

cdkid, on Jan 29 2006, 04:55 PM, said:

[UPDATE]
Version 1.0 - Have a good handful of functions now. Any comments/critcisms (sorry cant spell :P ) 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.

Quote

Proposed;
SQLite// MySQL
_SQL_DeleteRecord //
_SQL_ReIndex //
_SQL_VacuumDB//
//_GetColumnCount()
//_GetColumnType()
_SQL_TableExists//
_SQL_IndexExists//
_SQL_DeleteBAKtables//

Quote

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

This post has been edited by randallc: 30 January 2006 - 06:24 AM

[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