MySQl experiences

Problem/Question/Abstract:

Some tips on using MySQl with delphi

Answer:

I’ve used Delphi (versions 4 & 5) with MySql versions 3.23 and higher for a few years and did one project which involved a data import utility reading data into the database and then displaying graphs on website using ISAPI dlls written in Delphi.

First tip- get yourself a good front end; my-manager from ems-tech.com or sqlyog are both excellent and simplify development enormously. Both cost but will repay the effort in next to no time.

Next download the zeos libraries from http://www.zeoslib.net/http://www.zeoslib.net/ - these are superb- though take a little getting used to. Installing is a bit of a pig-with 6 different folders needed that have to be added to the environment library path. The zeos libraries aren’t just for mysql BTW, other databases are supported as well.

Next, I’ve found it simplest to keep the appropriate libmysql.dll in the same folder as the Delphi application. At one point during my import utility development, things started going very strange – every time I tried to connect to a database, I got really odd access violations. A quick search determined I had 4 different libmysqls on the pc and my app was picking up the wrong one. It doesn’t help that utilities like sqlyog or my-manager install their own versions – this makes it easy to get confused. I ended up removing all but the newest libmysql dll and then having to reinstall sqlyog etc but that fixed it, - the website, code and sqlyog etc all worked fine- so if you get funny a/vs check your lib dlls.

I’ve always tended to develop using classes and that’s true with zeos- less hassle than wotrking with components on forms or data modules. The code accompanying this shows how to create a class- I call it TgvDB. This handles all the initialisation of properties etc and lets you create a TGVdb instance dynamically. This creates a Connection and query and simplifies returning data or running queries – if your variable is db1 then

NumRecords := db1.Select('select * from table'); // Return all records
Db1.exec('Update table2 set column1 = 0 ');

In all rows, sets column 1 = 0.

for returned data, use the queryrec property to get at the values.

while not db.queryrec.eof do
begin
value := db.queryrec.fields('column1').asstring;
db.queryrec.next;
end;

Code:

unit mysql;

interface

uses
ZConnection, Db, ZAbstractRODataset, ZAbstractDataset, ZDataset, zdbcIntfs, classes;

type
TGvDb = class
private
FDataBase: TZConnection;
FDB: TZQuery;
FLastError: string;

public
constructor Create; overload;
destructor Destroy; override;
function Select(SQL: string): integer;
function Exec(sql: string): boolean;
function LockTables(tablename: string): boolean;
procedure UnLockTables;
property QueryRec: TzQuery read FDB;
property LastError: string read FLastError write FLastError;
end;

function NewQuery: Tgvdb;

implementation

uses Sysutils;

function NewQuery: Tgvdb;
begin
Result := Tgvdb.Create;
end;

{ TGvDb }

function TGvDb.LockTables(tablename: string): boolean;
begin
fdb.Sql.Text := 'LOCK TABLES ' + Tablename;
try
fdb.ExecSql;
Result := True;
except
Result := False;
end;
end;

procedure TGvDb.UnlockTables;
begin
fdb.Sql.Text := 'UNLOCK TABLES';
fdb.ExecSql;
end;

constructor TGvDb.Create; // Used to create new cities
begin
FDatabase := TZConnection.Create(nil);
FDatabase.HostName := 'localhost';
FDatabase.User := '';
FDatabase.Password := '';
Fdatabase.Protocol := 'mysql';
FDatabase.Database := 'mysql';
FDatabase.Catalog := 'mysql';
FDatabase.Port := 3306;
Fdb := TZQuery.Create(nil);
Fdb.Connection := FDatabase;
end;

destructor TGvDb.Destroy;
begin
FDb.Free;
FDatabase.Free;
end;

function TGvDb.Exec(sql: string): boolean;
begin
Fdb.Active := false;
Fdb.Sql.Text := SQL;
try
Fdb.ExecSql;
FLastError := '';
result := true;
except
on E: Exception do
begin
Result := False;
FLastError := E.Message;
end;
end;
end;

function TGvDb.Select(SQL: string): integer;
begin
Fdb.Active := false;
Fdb.Sql.Text := SQL;
try
Fdb.Open;
FLastError := '';
result := Fdb.RecordCount;
except
on E: Exception do
begin
Result := 0;
FLastError := E.Message;
end;
end;
end;

end.