MySQL Instant Data Refresh?
lazarus / 16-Databases

----------------------------------------------------------------------
oldcoder · 2015-08-21 20:59

Hello everyone.

I am trying to write a business type program for my own use using
Lazarus 1.42 and MySQL 5.5.32. I have no previous experience of MySQL
but cut my programming teeth on Delphi v. 4 and Paradox many years
ago.

I am trying to write a routine to save changes to an existing invoice
in the database, and both of the routines below (cobbled together from
tutorials and forum posts I have read) do save the updated invoice to
the database BUT after I have saved the updates, if I look at another
invoice and then come back to the updated invoice and display it
again, the updates aren't shown UNTIL I stop the program and restart
it OR reconnect to the database on the database connection form.

I would like the saved updates to be immediately shown if I look at
the updated invoice again but no matter what I've tried from the
various tutorials and forum posts it either just doesn't do anything,
or I get an error message. I'm sure that there is a very simple line
or two of code that will refresh the database instantly, but I just
can't seem to find it.

Can anyone help please?

       Method 1
       *************************************************
       sqlmemo.clear;
       sqlmemo.lines.add('select * from invoices where docnum =
'+CHAR(39)+ docnum.text+CHAR(39));
       DM1.SQLQuery2.Close;
       DM1.SQLQuery2.SQL.Text := sqlmemo.Text;
       DM1.SQLQuery2.Open;
       DM1.SQLQuery2.Edit;

       DM1.SQLQuery2.FieldByName('CUSCODE').AsString := code.text;
       DM1.SQLQuery2.FieldByName('CUSADD').AsString := cusadd.text;
       DM1.SQLQuery2.FieldByName('PC').AsString := pc.text;
       DM1.SQLQuery2.FieldByName('CUSDELADD').AsString :=
cusdeladd.text;
       DM1.SQLQuery2.FieldByName('DPC').AsString := dpc.text;
       .
       .
       .
       .         
       DM1.SQLQuery2.Post;
       //DM1.SQLQuery2.Upda]"]>Blockedde := upWhereAll;
       DM1.SQLQuery2.ApplyUpdates;
       DM1.SQLTransaction2.Commit;


       Method 2
       *********************************************
       sqlmemo.clear;
       sqlmemo.lines.add('UPDATE `invoices` SET
`DOCNUM`='+CHAR(39)+docnum.text+CHAR(39)+',`CUSCODE`='+
CHAR(39)+code.text+CHAr(39)+',');
       sqlmemo.lines.add('`CUSADD`='+CHAR(39)+cusadd.text+CHAR(39)+',`
PC`='+CHAR(39)+pc.text+CHAR(39)+',`TEL`='+CHAR(39)+tel.text+CHAR(39)+'
,`CUSDELADD`='+CHAR(39)+cusdeladd.text+CHAR(39)+',');
       .
       .
       .
       .  
       sqlmemo.lines.add('where `DOCNUM` =
'+CHAR(39)+docnum.text+CHAR(39)+';');

       DM1.SQLQuery2.Close;
       DM1.SQLQuery2.SQL.Text := sqlmemo.Text;
       DM1.SQLQuery2.ExecSQL;
       DM1.SQLTransaction2.Commit;  

       ************************************************    

In method 1 when I tried to add the line
'DM1.SQLQuery2.Upda]"]>Blockedde := upWhereAll;' I got an error
message 'Error - identifier not found "upWhereAll" '. I had to set the
Upda]"]>Blockedde value directly in the SQLQuery component for it to
work. My first thought was that a unit needed to be added to the Uses
clause, but I can't seem to find what unit applies.

Thanks for reading, and any help received.

----------------------------------------------------------------------
bylaardt · 2015-08-21 21:30

[MyViewDataset].refresh  after commit

----------------------------------------------------------------------
oldcoder · 2015-08-22 20:51

Thanks for your reply bylaardt, but if I try

      DM1.SQLTransaction2.Commit;
      DM1.SQLQuery2.refresh; 
     
I get 'EDatabase error. Operation cannot be performed on an inactive
dataset.'

I can't find a single post or example on Google showing me how to use
'refresh' with Lazarus and MySql.

I've also just read a post saying 'Views do not need to be refreshed
when the data changes. When you query them they will fetch the newest
data.'.

I am very confused.

----------------------------------------------------------------------
oldcoder · 2015-08-22 21:56

Ignore my last message. I've found out what I was doing wrong. Saving
the invoice with one dataset and recalling it with another dataset.
I'm not quite sure how that makes a difference, but it obviously does.
It's fine now. Well, I am new to MySQL. That's my excuse....