• Announcements

Sign in to follow this  
Followers 0

MySQL UDFs

376 posts in this topic

Posted (edited) · Report post

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 :lmao:
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. Edited by cdkid

Share this post


Link to post
Share on other sites



Posted (edited) · Report post

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 :lmao: Edited by cdkid

Share this post


Link to post
Share on other sites

Posted · Report post

AWESOME.. i've been waiting for something like that..
great job.. i cant wait to start coding using this UDF

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

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 :lmao: Edited by cdkid

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted · Report post

@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.. :lmao:

Share this post


Link to post
Share on other sites

Posted · Report post

@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?

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

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 by blitzkrg

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

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 by cdkid

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

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

[edit1]

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

[/edit1]
Edited by cdkid

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

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

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

Share this post


Link to post
Share on other sites

Posted · Report post

cdkid - this is great.

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted · Report post

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 :lmao: ) are still appriciated

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

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 by randallc

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted · Report post

[UPDATE]
Version 1.1
Added _GetTblNames()
1/30/06 11:27

Share this post


Link to post
Share on other sites

Posted · Report post

Hey guys got a Question. I am interested in mysql but don't know what it is. Is it basicly a online excel spreadsheet??? if so I could definetly use this. Please explain, anyone :lmao:


by the way when you look
for a smiley do you ever
make the face yourself then
look for that face. lol...

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

Hey guys got a Question. I am interested in mysql but don't know what it is. Is it basicly a online excel spreadsheet??? if so I could definetly use this. Please explain, anyone ;)

Well what it is is a database program. You create a DB then add in tables, then columns. umm... i cant really think of a way to explain this. It's very useful for logging into a website because u could have a database called i dunno 'login' and in there u have a table called 'logindata' and in there u have columns 'username' and 'password' when people register they fill out a form then it sends the info in the register form to the database then when they log in u check the username and password against it... i hope this helps, and if u do anything that might require a DB of any sort i definitly recommend MySQL
if u have any other questions or if i didnt explain this very well just ask.


by the way when you look
for a smiley do you ever
make the face yourself then
look for that face. lol...

yes :lmao: hehe
~cdkid

oh and the website is www.mysql.com Edited by cdkid

Share this post


Link to post
Share on other sites

Posted (edited) · Report post

Well what it is is a database program. You create a DB then add in tables, then columns. umm... i cant really think of a way to explain this. It's very useful for logging into a website because u could have a database called i dunno 'login' and in there u have a table called 'logindata' and in there u have columns 'username' and 'password' when people register they fill out a form then it sends the info in the register form to the database then when they log in u check the username and password against it... i hope this helps, and if u do anything that might require a DB of any sort i definitly recommend MySQL
if u have any other questions or if i didnt explain this very well just ask.
yes :lmao: hehe
~cdkid

oh and the website is www.mysql.com

Thanks so much, I know MySql. I love it now. I would check out this tutorial it you don't know how to use MySql. The only hard part was finding a good host. i use www.freesql.org and use my www.t35.com account to access my database. I can't wait to use your UDFs. I want to try it on my ControlMe.au3 script
Thanks Again
-zero

I have tested the UDFs and I get an error, I have a feeling it is the driver. I don't know eactly about that but, do I download a driver from ]http://dev.mysql.com/downloads/ I'm not sure please help
this is the http://zerocool60544.t35.com/error.bmp Edited by zerocool60544

Share this post


Link to post
Share on other sites

Posted · Report post

just do a google search for "MYSQL ODBC DRIVER DOWNLOADS" and uhhh u can host ur own mysql db if u have World Wide Web publishing enabled u can put it on your localhost account

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

  • Recently Browsing   0 members

    No registered users viewing this page.