Getting MySQL and Lazarus to work together.
lazarus / 16-Databases

----------------------------------------------------------------------
thaimann · 2008-11-08 17:16

Getting MySQL and Lazarus to work together, My recent learning curve.
Some of this really needs added to Lazarus documentation.

After upgrading to Lazarus 0.9.26,, it appears to mostly work.

1.You can now use a separate form for all of your TSQLQuery's (I
highly recommend doing so.)
2.Drop onto your database form a TmySQL50Conn, TSQLTransaction
3.Give The Tmysql50Conn a meaningful name, and enter the database
info, user info and password.  Link it to the TSQLTransaction and then
make it active.
4.Drop a TSQlQuery onto your Database Form for each table in your
database.  Enter a select statement in the SQL property.
5.Important: change Upda]"]>Blockedde to UpWhereChanged.
6.Give the TmySQLQuery a meaningful name and then change it to active.
7.Drop a Tdatasource for each TmySQLQuery and then point the
Datasource to the query, not the other way around.  Give it a
meaningful name.

Now on your edit screen  drop a Dbnavigator component and what ever
edit components you intend to use (TDBEDIT, TDBCombobox, ..., etc.)
Change each of these so they point to the correct Datasource.

I know most of you know this, but on the edit unit a Uses DatabaseUnit
(or what ever you saved it as) needs added right under Implementation
noun.  Delphi requires the same thing, but is smart enough to add it
itself.

Now back to the database form.

Add a AfterPost Event on each query.  If you double click on
ObjectInspector's AfterPost Method, it will create the procedure for
you and then just add an ApplyUpdates to it:

procedure TDatabaseForm.LocQueryAfterPost(DataSet: TDataSet);
begin
   LocQuery.ApplyUpdates;
end;

Now, Inserts and Updates should work for the Edit Form, but not
deletes.  What I had to do to get deletes to work was the following:

1.Add a string variable to the Database Units public declarations.
2.On the Edit Form I created a Before Action procedure for the
DBNavigator:

procedure TLocForm.DBNavigator1BeforeAction(Sender: TObject;
  Button: TDBNavButtonType);
begin
  DatabaseForm.LIdx :=
DatabaseForm.LocQuery.FieldByName('LocIdx').AsString;
end;

3.Created a seperate TSQLQuery to be used for all of my deletes and
then on each of my regular TSQLQuery's I added an After Delete Event
with the following code:

procedure TDatabaseForm.LocQueryAfterDelete(DataSet: TDataSet);
Var
   SQLCmd:                                             String;
begin
  SQLCmd := 'Delete from location where LocIdx = ' +
                       '''' + LIdx + '''';
  DeleteQuery.SQL.Add(SQLcmd);
  DeleteQuery.ExecSQl;
end;

Yes it is crude and it works and it doesn't take much work to set it
up.  All & all, it isn't a whole lot more work then doing it with
Delphi using DACforMySQL (which I think is the gold standard Lazarus
should be aiming for.)

Current problems I am still having, getting the locate method (or some
other search method) to work.  Let me know if anyone has it working.

Oh, when testing your app, avoid the debugger.  It seems to lockup the
app where if you run it native it won't.

Terry

----------------------------------------------------------------------
researcher · 2009-05-22 22:16

Tried SQLdb, and ran into the same problem with the locate.
FIBL does locate without any problems.
But I can't see how to refresh the data after insert or update.

----------------------------------------------------------------------
kcs · 2009-05-30 09:30

Just find out about Lazarus a day ago. I am also going to try
combination MySql - Lazarus for applications. In Delphi I use
something like this for locate:

dbgrid1.DataSource.DataSet.Locate(dbgrid1.SelectedField.FieldName,
edit1.Text,[locaseinsensitive, lopartialkey]);

It is independet for whatever Dataset is used (TTable, TQuery, etc..)
My question is: Is it possible something like that with Lazarus ?
I just install Lazarus, and have to learn a lot about what objects is
for what, so I can't try this for myself.

----------------------------------------------------------------------
Loesje · 2009-05-30 16:06

It's exactly the same

----------------------------------------------------------------------
gviger · 2009-06-03 23:04

I have problem with UTF not showing in DataGrid. I can see only
?????????.

MySQL connection is working ok, I can see Latin characters.

----------------------------------------------------------------------
Loesje · 2009-06-07 13:43

That can be anything. And there's written a lot about that on the wiki
and this forum.

Lazarus uses UTF-8 internally on all platforms since version 0.9.26.
So MySQL has to return the data in UTF-8 format. Then it should work.

----------------------------------------------------------------------
rokemaster · 2009-09-15 14:59

Hi i new with lazarus, but if need delete and insert is easy i'm
traying with the properties of TDBNavigator and i copy the
"query.applyupdate;" for each SQL sentence and works without other
SQLquery

----------------------------------------------------------------------
gviger · 2009-10-21 01:45

Finally I have managed to connect to MySQL and show table in DBGrid in
UTF8 format.


The main problem is MYSQLConnection is not reporting to MySQL server
that connection should be UTF8.

To corect that I used AfterConnect Event:

procedure TForm1.MySQL50Connection1AfterConnect(Sender: TObject);
begin
  SQLQueryAction.SQL.Text := 'SET CHARACTER SET `utf8`';
  SQLQueryAction.ExecSQL;
end;


and everything is working ok when you compile project.

I still can not see utf characters in design view.
Is there some other solution for MYSQLConnection to connect to mysql
server in utf8 connection. Setting character set parameter of
MySQLConnection it seems is not working.

----------------------------------------------------------------------
xqtr · 2009-10-21 15:09

I use the Zoes connection component in a mysql database with Greek
letters and it is fine.

When you create your database and your tables, you have to declare
them to use utf8, character set and utf8_unicode_ci for the collation.
No need, to make other changes or add functions and sql commands.

If it is working with greek letters then you have to be allright.