MySQL and Delphi

Problem/Question/Abstract:

I've always wanted a better way to interface with my favorite (I would argue the best) database and Delphi - and after much searching I bring you an excellent and sensible way to do it.

Answer:

This is based on the Open source MySQL connector "Objects".

To start with you'll need Delphi of course - I believe this will work with 5 pro and higher although I've only used it with 7. Also I assume you have or have access to a properly configured and working MySQL server. If you don't there are plenty of excellent tutorials available.  I'll also assume you have moderate knowledge of Delphi and can navigate, add buttons and all that basic stuff.

First also need a copy of the actual connector objects. Which can be found at:
http://sourceforge.net/projects/directsql/
http://prdownloads.sourceforge.net/directsql/DirectMysqlObjects.zip?download

If your interested there is also a demo which shows off its capabilities which can be found:
http://prdownloads.sourceforge.net/directsql/DemoObjectsWin.zip?download


To use the MySQL objects - simply unzip the contents of the zip you just downloaded into {Delphi}/lib/ folder.

Now to use them all you need to do is add a couple of things to the uses of your interface:
uMySqlVio, uMysqlCT, uMysqlClient, uMysqlHelpers

Its as easy as that!

I suggest trying to compile your application after adding the "uses" for the first time to make sure Delphi can find them okay. Now I'll run through a quick tutorial on how to use the library to get you started.


Connection Example

First add "MySQLClient: TMySQLClient;" to your main form's public. This will make the actual client that you'll do all the work with.

Also add "MySQLResult: TMysqlResult;" to your main form's public as well. This will create an 'instance' of the MySQL result type for "catching" queries and other stuff that you'll want a result from.

Great, so now the naming is done we'll add some code to actually connect to your database. Add this code to your form's OnCreate procedure (double click on your form):
MySQLClient := TMySQLClient.Create;

Next add the following to the OnDestroy procedure:
MySQLClient.Free;
if MySQLResult <> nil then
MySQLResult.Free;

Okay, now make a new button on your form and give it the caption of "Connect". To get it to actually connect first we'll need to define a few things like the host and user and stuff. You can either "hard code" the values  (or read from your own config files / registry or whatever) or use edit boxes and such. Since this is a simple tutorial I'll leave the reading in values from cfg files up to you and use the easiest which is just a few edit boxes on your form.

Add 5 edit boxes to your form and 3 check boxes. For quick reference label (leave the names the same)  them
Edit1 - Host
Edit2 - Port
Edit3 - User
Edit4 - Password
Edit5 - Db
Check1 - Use named pipes
Check2 - Use SSL
Check3 - Compress

Now add the following code to your OnClick procedure for the connect button you added earlier:
MySQLClient.Host := Edit1.Text;
MySQLClient.port := StrToInt(Edit2.text);
MySQLClient.user := Edit3.text;
MySQLClient.password := Edit4.text;
MySQLClient.Db := Edit5.Text;
MySQLClient.UseNamedPipe := CheckBox1.Checked;
MySQLClient.UseSSL := CheckBox2.Checked;
MySQLClient.Compress := CheckBox3.Checked;

if MySQLClient.Connect then ShowMessage('connected ok!')
else ShowMessage('Somthing went wrong!");

Or instead of the big chunk of text you can use:
if FMysql.Connect(Edit1.Text, Edit3.Text, Edit4.Text, 'db', StrToInt(Edit2.text), '', false, 0) then ShowMessage('connected ok!')
else ShowMessage('Somthing went wrong!");

But its much easier for the second to go wrong, and harder to figure out what went wrong.

Now run your program, fill in the edit boxes and see if it works!

I'm assuming it did - so lets move along, almost there.

Now we come to actually making the query - which is just like a query in any other language or interface. When you make a new query you need to assign the result to MySQLResult and use MySQLClient to run the query. There are 3 parameters, the query, if you want it to save the result, a boolean to store if it executed ok:
MySQLResult := MySQLClient.Query('SELECT * FROM users WHERE username=''username'' and password=''pass''', True, OK);

(just a quick note for the inexperienced - often you'll need to use a ' in a sql query (ie - select * from user where name = 'joe bloggs') - which also signifies to Delphi that the string you are making has ended and will make it "freak out"(TM) - so there thankfully is an easy way around it, which is simply to wherever you need a ' in a string put two together - so select * from user where name = 'joe bloggs' would be 'select * from user where name = ''joe bloggs''')

Now that you have the result of the query there's all sorts of things you can do with it. Have a go at browsing through the list of properties and procedures available. But to get you started - to get a field by using its name:
MySQLResult.FieldValueByName('username');

Hint for a login type script -
if (MySQLResult.FieldValueByName('username') <> 'dummy_username') or (MySQLResult.FieldValueByName('password') <> 'dummy_pass') then ...


So that's it - I hope that all helped - if you have any problems or questions or feedback feel free to e-mail me - ipvariance@hotmail.com.

Special thanks to "Dumbass" who wrote the page where I first found the open source MySQL connector libraries.