Daily Cup of Tech

Whether we realize it or not, we are a civilization that runs on data. It controls every decision we make or don’t make. We spend countless hours reading it, analyzing it, massaging it, duplicating it, verifying it, summarizing it, and protecting it. It gives us power.

So, it is logical that we would come up with many different ways to store data electronically. When most people think of electronic information, they often think of files, or e-mails, or webpages. But, if we are going to look at storing some serious data, someone is very quickly going to utter the word…”database”.

According to Wikipedia, a database is:

…a structured collection of records or data that is stored in a computer so that a program can consult it to answer queries. The records retrieved in answer to queries become information that can be used to make decisions. The computer program used to manage and query a database is known as a database management system (DBMS). The properties and design of database systems are included in the study of information science.

One of the most popular database programs today is MySQL. It is an open source database program that lets you store and analyze data to your heart’s content. In fact, Daily Cup of Tech runs on a MySQL database.

But, what about getting to this data with AutoIt. Is there a way to do it? Especially when many MySQL systems run on Linux and AutoIt is definitely a Windows only application. Can the data residing on the Linux MySQL server be accessed from a Windows AutoIt applications. The answer is…definitely!

Note: If you are just trying some of this on for size and are interested in working with MySQL and AutoIt but you do not have a MySQL server, I would suggest getting XAMPP for Windows. It is probably the quickest and easiest way that you can set up a MySQL environment along with a web server that supports PHP. Plus, it is really easy to uninstall at a later date.

Also, if you are going to run XAMPP, make sure that IIS is not running on your system.

There are two key components to working with MySQL data from AutoIt:

1. The MySQL ODBC driver
2. The MySQL AutoIt UDF

Once you have these two things in place, the whole process becomes a lot easier.

MySQL ODBC Driver

The purpose of the MySQL ODBC driver is to give your Windows system access to a MySQL system. Essentially, it works as an interface between Windows and MySQL, translating the information so that they can talk together. You need to install this driver before your AutoIt applications can talk to MySQL.

To install the driver, simply download it from the MySQL website. I would probably suggest the Windows (x86) MSI Installer as it will be the easiest to install. When you are installing the driver, make sure that you use the Complete option for the installation so that you are certain that you have all of the required components.

Once you have the ODBC driver installed, you are ready to start writing your code.

MySQL AutoIt UDF

The most important part of this whole tutorial is the MySQL UDF (user defined functions). Without this bit of code, you are going to have a significantly more difficult time doing any work in MySQL. This UDF was written by cdkid in the AutoIt forums and all of the credit goes to him.

The UDF can be downloaded from the MySQL UDF post. I would suggest saving it in the same directory as the code you are writing. This way, it is really easy to reference and you do not need to worry about modifying the code if you move the project somewhere else.

Using the MySQL UDF

To use the MySQL UDF all you need to do is add

#include "MySQL.au3"

at the top of your AutoIt script and you are ready to use all of the MySQL functions that are available in the UDF.

There are three steps that need to be followed in order so that your script can properly access the MySQL database. As illustrated on the right, these are:

1. Connect to the database
2. Perform functions on the data
3. Close connection to database

If you do not follow these in order, your script will fail and generate errors.

Connecting to the Database

In order for you to be able to connect to the MySQL database, you will need to have some information first:

1. Username with access to the database
2. Password for username with access to the database
3. Database name of the MySQL server
4. MySQL server host name or IP address

To connect to the database, use code something like this:

$UserName = "autoit"
$Password = "password"
$Database = "dcot"
$MySQLServerName = "localhost"

$SQLInstance = _MySQLConnect($UserName,$Password,$Database,$MySQLServerName)

Make sure that you replace all of the variable values with appropriate values for your own server.

If everything works properly, $SQLInstance will now contain a connection instance to your database. If there was an error $SQLInstance will equal 0 and @Error will equal 1 if there was an error opening the connection or 2 if the MySQL ODBC driver is not installed.

Performing Functions on Data

This is where you get to do all of the work with the data. There are several functions that are available to be used at this point. The ones you use will be determined by the work that you want to do in MySQL. For a complete list of all the functions that are available, please look in the MySQL UDF.

For the sake of this tutorial, we will read the contents of a table & display all of the records in two columns from the table, add a record to the table, and then delete that record. The table that we are going to be using is called partylist and it contains the following data:

RecordID	FirstName	LastName	InviteSent	Attending
1	Jim	Nasium	1	1
2	Bill	Fold	0	0
3	Jean	Poole	1	0
4	Jen	Tull	1	0
5	Justin	Time	0	0

If you would like to duplicate this table in your MySQL server, you can import this file. It will create the proper database and table.

Reading & Displaying The Table Contents

First, we are going to read the contents of the partylist table into an object. We do this by using the _Query function. Here is the code:

$SQLCode = "SELECT * FROM partylist"
$TableContents = _Query($SQLInstance,$SQLCode)

The entire partylist table is now in an array contained by the $TableContents variable.

If you are familiar with the SQL language, the first line of the code will look familiar to you. In fact, the _Query function accepts most SQL language queries.

Next, we will want to display the results. We only want to display the first and last names of everyone on the list. We will display this list in a message box. Here is the code:

With $TableContents
While Not .EOF
$NameList &= .Fields ("FirstName").value & " " & .Fields ("LastName").value & @CRLF
.MoveNext
WEnd
EndWith
MsgBox(0,"Guest List",$NameList)

Remember that this code needs to be used in conjunction with the code that connected you to the database. This code essentially goes through the entire contents of $TableContents and spits out the value of FirstName and LastName for each record. It then stores that in a string called $NameList. The contents of $NameList is then displayed.

Add A Record to the Table

The next thing that we want to do is add a new guest to the list. Our new guest is Paige Turner. She was sent an invitation and she has indicated that she will be coming to the party.

The best way to do this is to create two arrays, one for the table definition and one for the data. Then, use the _AddRecord function to add it to the correct table in the database. Here is the code:

Dim $ColumnNames[6]
$ColumnNames[0]= "RecordID"
$ColumnNames[1]= "FirstName"
$ColumnNames[2]= "LastName"
$ColumnNames[3]= "InviteSent"
$ColumnNames[4]= "Attending"
$ColumnNames[5]= ""

Dim $NewGuest[6]
$NewGuest[0]=""
$NewGuest[1]="Paige"
$NewGuest[2]="Turner"
$NewGuest[3]=1
$NewGuest[4]=1
$NewGuest[5]=""

_AddRecord($SQLInstance,"partylist",$ColumnNames,$NewGuest)

You may have noticed that both arrays end with an empty value. This is so the function knows that it has reached the end of the array. The $ColumnNames array is a direct reflection of the partylist table column names. The $NewGuest array contains all of the information about the new guest.

After you run this code, your new table in MySQL should look something like this:

RecordID	FirstName	LastName	InviteSent	Attending
1	Jim	Nasium	1	1
2	Bill	Fold	0	0
3	Jean	Poole	1	0
4	Jen	Tull	1	0
5	Justin	Time	0	0
6	Paige	Turner	1	1

Delete A Record from the Table

The final thing that we want to do with this table is delete a record. We have discovered that Jean Poole will be out of town the night of the party and we would like to delete her record completely. Also, we know that Jean’s unique record ID is 3. To delete a record, we use the _DeleteRecord (Duh!) function. This is how we code it:

$TableName = "partylist"
$Column = "RecordID"
$RecordID = "3"

_DeleteRecord($SQLInstance,$TableName,$Column,$RecordID)

If this executes correctly, your partylist table contents should look like this:

RecordID	FirstName	LastName	InviteSent	Attending
1	Jim	Nasium	1	1
2	Bill	Fold	0	0
4	Jen	Tull	1	0
5	Justin	Time	0	0
6	Paige	Turner	1	1

Closing the Connection

Once you have completed all of the data changes that you would like on the database, you now need to close the connection between your AutoIt application and the MySQL database. This is performed using the following line of code:

_MySQLEnd($SQLInstance)

This will ensure that the application closes cleanly. You are now finished with the MySQL portion of your application.

Other Functions

As mentioned earlier, there are other functions that can be used from the UDF on a MySQL database. While a complete tutorial on each of these functions is beyond the scope of this post, here is a quick rundown on each of them and their function.

• _CreateTable - create a new table in the database.
• _CreateColumn - create a new column in a table in the database.
  
• _DropCol - delete a column and its contents from a table in the database.
• _DropTbl - delete a table and its contents from the database.
• _CountRecords - returns the number of records in a column that match a specific search parameter.
• _CountTables - returns the number of tables that are in the database.
• _GetColNames - returns an array with the number of columns in the first array element and all of the column names in the specified table from the database.
• _GetTblNames -returns an array with the number of tables in the first array element and all of the table names in the specified database.
• _GetColVals - gets all of the values in a specified column in a specified table from the database.
• _GetColCount - returns the number of columns in a specified table in the database.
• _GetColType - returns the data type of a column specified in a table from the database.
• _GetDBNames - returns an array with the number of databases in the first array element and all of the database names in the specified server.
• _ChangeCon - changes the connection object to allow for a different username, password, server, or database.

Final Code

Here is the final AutoIt code along with the SQL code to create the database and table used in these examples. Feel free to download it and use it so that you can learn from it.

#include "MySQL.au3"
 
Dim $NameList
Dim $UserName = "autoit"
Dim $Password = "password"
Dim $Database = "dcot"
Dim $MySQLServerName = "localhost"
Dim $TableName = "partylist"
Dim $ColumnNames[6]
Dim $NewGuest[6]
Dim $Column = "RecordID"
Dim $RecordID = "3"
 
$ColumnNames[0]= "RecordID"
$ColumnNames[1]= "FirstName"
$ColumnNames[2]= "LastName"
$ColumnNames[3]= "InviteSent"
$ColumnNames[4]= "Attending"
$ColumnNames[5]= ""
 
$NewGuest[0]=""
$NewGuest[1]="Paige"
$NewGuest[2]="Turner"
$NewGuest[3]=1
$NewGuest[4]=1
$NewGuest[5]=""
 
$SQLInstance = _MySQLConnect ($UserName, $Password, $Database, $MySQLServerName)
 
DisplayTable()
 
_AddRecord($SQLInstance,$TableName,$ColumnNames,$NewGuest)
 
DisplayTable()
 
_DeleteRecord($SQLInstance,$TableName,$Column,$RecordID)
 
DisplayTable()
 
_MySQLEnd ($SQLInstance)
 
#region Functions
Func DisplayTable()
	$NameList = ""
	$SQLCode = "SELECT * FROM partylist"
	$TableContents = _Query ($SQLInstance, $SQLCode)
	With $TableContents
		While Not .EOF
			$NameList &= .Fields ("FirstName").value & " " & .Fields ("LastName").value & @CRLF
			.MoveNext
		WEnd
	EndWith
	MsgBox(0,"Guest List",$NameList)
EndFunc
#endregion

Download this code: MySQLSamples.au3

CREATE DATABASE `dcot` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
USE `dcot`;
 
CREATE TABLE `partylist` (
  `RecordID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique Record ID',
  `FirstName` varchar(30) collate latin1_general_ci NOT NULL COMMENT 'First Name',
  `LastName` varchar(30) collate latin1_general_ci NOT NULL COMMENT 'Last Name',
  `InviteSent` tinyint(1) NOT NULL COMMENT 'Has the invitation been sent yet?',
  `Attending` tinyint(1) NOT NULL COMMENT 'Is this person coming to the party?',
  PRIMARY KEY  (`RecordID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;
 
INSERT INTO `partylist` VALUES (1, 'Jim', 'Nasium', 1, 1);
INSERT INTO `partylist` VALUES (2, 'Bill', 'Fold', 0, 0);
INSERT INTO `partylist` VALUES (3, 'Jean', 'Poole', 1, 0);
INSERT INTO `partylist` VALUES (4, 'Jen', 'Tull', 1, 0);
INSERT INTO `partylist` VALUES (5, 'Justin', 'Time', 0, 0);

Download this code: dcot.sql

Further Reading and Tools

If you want to dig further into this topic, here is some reading that you may find useful along with some tools to make the development easier.

• MySQL UDF on AutoIt Forums
• MySQL Website
• MySQL GUI Tools
• MySQL Reference Manual
• Apache Friends XAMPP System

Summary

I have been a huge fan of both AutoIt and MySQL since I discovered them. With the ability to now work together, these two tools can create some very powerful systems.

Similar Posts:

• MySQL in AutoIt
• Remove Duplicate Records in Access
• Will Oracle Kill MySQL?
• Microsoft Source Code Found On Internet
• Open Source DCoT Application - Word Counter

If you found this post useful, why don't you buy me a cup of coffee to show your gratitude?