How can I connect to an SQL server if I have delphi personal edition 5 or another version that doesn't have Db components that are required for other methods?

TmySQL Tutorial

Before I start I would just like to say if you have any questions about anything at all simply come to #delphi on irc.quakenet.org

This is a tutorial on using the TmySQL component for Delphi. This allows access to an SQL server without any OBDC or BDE needed. It doesn’t require any database components but simply requires the dll bundled with it to be distributed with the application. To Start off with simply download the component from here:

http://www.productivity.org/projects/tmysql/download/tmysql-21b.zip

Then install the components and start up Delphi. Now with this component comes a very simple password form that you can use to enter the username and password for the server on the client side. You don’t have to use it but it will save you some time and effort if it doesn’t bother you. After you have installed the component start up Delphi and a new Visual component should have appeared. Create a new application and place a tMySQL client on the form and look at its properties. Change the “threaded” property to true and put the dll path as root so enter “libmysql.dll” .For Now we are going to keep it very simple and set the Username and password of the server at this stage, it doesn’t really take much to enter them at runtime but for this we are keeping it simple.  So enter your mySQL username and password into the properties and save the whole form to a directory, remembering to put the Dll at the path where you specified, which for us is in the root directory of where you saved it.
Now we must set up the database / tables / data manually because setting it all up is another story that I will save for you for another day. I recommend you use something like phpmyadmin for this because its simple. First create a database called dtestdb and then run the following queries:


CREATE TABLE test (
  id int(11) NOT NULL auto_increment,
  name text,
  PRIMARY KEY (id)
) TYPE=MyISAM;

INSERT INTO test VALUES (1, 'Mr Test 1 ');
INSERT INTO test VALUES (2, 'Mr Test 2 ');
INSERT INTO test VALUES (3, 'Mr Test 3 ');
INSERT INTO test VALUES (4, 'Mr Test 4 ');


Once you have the database and  the server is running then you go back to your project and enter “dtestdb” as the database on the mySQL component.
Now Comes the fun parts, Add 2 memos 2 buttons and an edit box. For Simplicity I am going to leave my components as the standard names (ie Memo1 etc). One Button is going to connect to the database and the other is going to query the test table and the name field with the text from the editbox. Next Change the hostname property of the TMySQL component to the address of your mySQL server. This is much easier to coordinate if the machine is running on a LAN or on your computer however this also works over the internet aswell. You may need to add MySQLCommon to your uses list. Goto the Onclick procedure of the Connect button and put:

MySQLClient1.Connect;

However you may also want to goto the procedure list of the MySQLClient and put under OnConnect

Memo2.Lines.Add(‘Succesfully Connected’);

The Idea is the Memo2 will serve as an output as the status so it would probably be useful to fill in a few more of the propertys particularly the mySQLClient1Status which can often tell you a lot about whats happening.  Next try to connect using the processlist of your server and the Memo2 output as your output.  Next goto the Query Button OnClick Event and put :


procedure TForm1.Button2Click(Sender: TObject);
var
  pQueryCondition : array[0..258] of char;
begin
Memo1.Lines.Clear;
StrPCopy(pQueryCondition,'');
StrCopy(pQueryCondition,PChar(' where name like '+QuoteString('%'+Edit1.Text+'%',[])));
MySQLClient1.Query.PrepareTask(
    PChar('select id, name, address, telephone from test'+pQueryCondition),
    'Query from Edit',
    QueryFinish,
    AnyThreadedError);
MySQLClient1.Execute;
end;


The mySQL client works on a queing basis, so you que up all commands in order and then do MySQLClient1.Execute to actually run them in the order you sent them. ‘Query from Edit’ is simply a name that you give to the procedure that appears on the Status command. QueryFinish is the procedure that processes the data, we will add that next and AnyThreadedError is a procedure that deals with any errors that we may encounter in the Query Process.  Next we must add the QueryFinish Process which you must declare first.  This is the QueryFinish
Code:


procedure TForm1.QueryFinish(Sender: TObject);
var
i:integer;
begin
  with MySQLClient1 do begin
    for i:=0 to Query.DataCount-1 do begin
      Memo1.lines.Add(Query.Data(i,0)+': '+Query.Data(i,1);
    end;
   end;
end;


This procedure will take the data returned and simply add it to Memo1.Lines. You next need to put the error handling in as the following code:


procedure TForm1.AnyThreadedError(Sender: TObject;
  Msg: String);
begin
  memo2.Lines.Add('SQL Client - ERROR '+Msg);
  memo2.Lines.Add('ERROR! '+Msg);
end;


This procedure simply takes any Error and adds the Message it returns (Msg) to Memo2. Now assuming you have followed all my instructions correctly then you should with any luck have a working program. This Should run correctly and do a search for any name or partial name in the editbox. If you don’t enter anything if should list everything in the table. You can download this project at

http://www.qd-uk.com/MySQLTutorial.zip

Thank you for reading please be gentle its my first article in a while