Any good tutorials?
lazarus / 16-Databases

----------------------------------------------------------------------
forlornity · 2008-01-24 11:35

Ok, I've been trying to develop a MySQL application using lazarus and
I've been getting along OK with he information available on the wiki
until I reached the point where I wanted to update or insert into a
database, I know I could figure this out with sufficient time but I
need to do this quickly so my question to you is: Are there any good
tutorials out there on how to use TMySQLxxConnection for all purposes?

If not, what do I have to do differently to run update and insert
queries?

thanks in advance, Forlornity

----------------------------------------------------------------------
piper62 · 2008-01-25 06:03

Hi,
I strongly recommend to use the ZeosLib components. Working with them
is very smooth and there's is also a very active community regarding
especially database questions.
http://zeos.firmos.at/portal.php

Regarding the update or inserts you have mainly two possibilities: use
directly a SQLQuery or ZQuery and use on these datasets the ".Insert"
or the ".Edit" methods. Followed by ."Post" it should work. Or you do
it by "hand" and write all SQL statements on your own.
If you want to work with transactions on InnoDB tables I recommend the
use of an separate ZQuery where you can send the SQL  commands
directly to the database regarding the transactions. CAVE: This works
ONLY with transaction supporting storage engines of MySQL!
update example:
   if not (DataSource1.State in [dsEdit, dsInsert]) then
   begin
      ZQuery2.SQL.Clear;
      ZQuery2.SQL.Add('select * from <table_name> where
table_id='+ZQuery1.FieldByName('table_id').AsString+' for update');
      ZQuery2.ExecSQL;
      ZQuery1.Edit;
// make some changes ...
      ZQuery1.FieldByName('user').AsString:=v_user_id;
      ZQuery1.FieldByName('dept').AsString:=v_dept_id;
      ZQuery1.FieldByName('saved').AsDateTime:=now;
      DBEdit2.SetFocus;
   end; { of if }

commit:
    v_state:=DataSource1.State;
     if DataSource1.State in [dsEdit, dsInsert] then
     begin
        ZQuery1.Post;
        ZQuery2.SQL.Clear;
        ZQuery2.SQL.Add('COMMIT');
        ZQuery2.ExecSQL;
        ZQuery1.Refresh;
     end;

or rollback:

   if DataSource1.State in [dsEdit, dsInsert] then
   begin
      ZQuery2.SQL.Clear;
      ZQuery2.SQL.Add('ROLLBACK');
      ZQuery2.ExecSQL;
      ZQuery1.Cancel;
   end { of if }

hope that helps..
I'm sorry that I have not finished my book about database programming
right now...
Regards,
Tibor

----------------------------------------------------------------------
anderson5420 · 2008-06-28 12:51

So why doesn't the TDBNavigator encapsulate all this stuff?  The MySQL
(using 5.0) connection and query components work great -  the MySQL
data shows up in the TDBGrid, the Edit button on the TDBNavigator lets
me edit the fields of the record in the grid, and the Post button
appears to work. But when I check the actual MySQL table with the
MySQL command line utility, no edits or updates "took."  Not getting
committed back to the actual database tables.  I have tried the
"BeforeAction" event handler code suggested elsewhere - you shouldn't
have to do this with a functional component - but keep getting the
same errors.  "Can't perform this operation on a active dataset." OK,
so we set it to inactive.  Now the error is "Can't perform this
operation on an inactive dataset."  It would be comforting to know,
you know, that it is not just me but that the TDBNavigator is not
quite fully functional yet...and how exactly to get the dang edits and
updates to "stick."

----------------------------------------------------------------------
normanp · 2008-09-24 18:59

Please see the posts in the topic 'MySQL Component ?' - this works
fine though it is only a crude demo. I used Lighty2Go Web Server &
MySQL dbms on XP. There is no need to put the components on the form
for the demo. It shows how to get the post to update the db.