Author: david bolton 

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. 

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


Code:

6   unit mysql;
7   
8   interface
9   
10  uses
11    ZConnection, Db, ZAbstractRODataset, ZAbstractDataset, ZDataset, zdbcIntfs, 
12  classes;
13  
14  type
15    TGvDb = class
16    private
17      FDataBase: TZConnection;
18      FDB: TZQuery;
19      FLastError: string;
20  
21    public
22      constructor Create; overload;
23      destructor Destroy; override;
24      function Select(SQL: string): integer;
25      function Exec(sql: string): boolean;
26      function LockTables(tablename: string): boolean;
27      procedure UnLockTables;
28      property QueryRec: TzQuery read FDB;
29      property LastError: string read FLastError write FLastError;
30    end;
31  
32  function NewQuery: Tgvdb;
33  
34  implementation
35  
36  uses Sysutils;
37  
38  function NewQuery: Tgvdb;
39  begin
40    Result := Tgvdb.Create;
41  end;
42  
43  { TGvDb }
44  
45  function TGvDb.LockTables(tablename: string): boolean;
46  begin
47    fdb.Sql.Text := 'LOCK TABLES ' + Tablename;
48    try
49      fdb.ExecSql;
50      Result := True;
51    except
52      Result := False;
53    end;
54  end;
55  
56  procedure TGvDb.UnlockTables;
57  begin
58    fdb.Sql.Text := 'UNLOCK TABLES';
59    fdb.ExecSql;
60  end;
61  
62  constructor TGvDb.Create; // Used to create new cities
63  begin
64    FDatabase := TZConnection.Create(nil);
65    FDatabase.HostName := 'localhost';
66    FDatabase.User := '';
67    FDatabase.Password := '';
68    Fdatabase.Protocol := 'mysql';
69    FDatabase.Database := 'mysql';
70    FDatabase.Catalog := 'mysql';
71    FDatabase.Port := 3306;
72    Fdb := TZQuery.Create(nil);
73    Fdb.Connection := FDatabase;
74  end;
75  
76  destructor TGvDb.Destroy;
77  begin
78    FDb.Free;
79    FDatabase.Free;
80  end;
81  
82  function TGvDb.Exec(sql: string): boolean;
83  begin
84    Fdb.Active := false;
85    Fdb.Sql.Text := SQL;
86    try
87      Fdb.ExecSql;
88      FLastError := '';
89      result := true;
90    except
91      on E: Exception do
92      begin
93        Result := False;
94        FLastError := E.message;
95      end;
96    end;
97  end;
98  
99  function TGvDb.Select(SQL: string): integer;
100 begin
101   Fdb.Active := false;
102   Fdb.Sql.Text := SQL;
103   try
104     Fdb.Open;
105     FLastError := '';
106     result := Fdb.RecordCount;
107   except
108     on E: Exception do
109     begin
110       Result := 0;
111       FLastError := E.message;
112     end;
113   end;
114 end;
115 
116 end.