Title: MySQL without the commonly required components
Question: 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?
Answer:
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 doesnt 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 dont have to use it but it will save you some time and effort if it doesnt 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 doesnt 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 dont 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