update database with TZQuery  (Firebird 2.1)
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2014-07-16 06:11

Hi All,

I always find it difficult to update or edit the database with
TZConnection, TZQuery components (Zeos 7.0.0). My way of managing the
database update and editing is that: I use only one TZConnection and
TZQuery component set as iterator to modify the tables in the dataset,
switching between the tables in the database is performed that way:

    Code: [Select]ZQuery.Close;
    ZQuery.SQL.Clear;
    ZQuery.sql.add('select * from Eigenvalues order by Eigen_num');
    ZQuery.Open;

    // Write data to table
    ZQuery.FieldByName('Eigenvalues').asInteger := 0.045;
    Zquery.post;
    ZQuery.ApplyUpdates;
  
    ZQuery.Close;
    ZQuery.SQL.Clear;
    ZQuery.sql.add('select * from New_Table');
    ZQuery.Open;

    ZQuery.FieldByName('First_Field_of_New_Table').asInteger := 0.045;
    Zquery.post;
    ZQuery.ApplyUpdates; 
The records are written to database perfectly because I monitor it
with the FlameRobin. The weird thing is visual DBGrid component never
reflects the current situation of database table Eigenvalues. Probably
I have to express that DBGird is not connected to the ZQuery, but has
its own distinct TZZonnection and TZQuery, could that be a source of
the problem ?

Regards,

----------------------------------------------------------------------
mangakissa · 2014-07-16 07:11

There's no communication to different TzConnections. They even create
a new connection to the database. If you use embedded Firebird it will
give you an error.
Why doesn't you use one connection for all your queries.
Same as  for TzQuery. If you use two different TzQueries, you have to
refresh the dataset for visualising new / modified / deleted records.

----------------------------------------------------------------------
Ronan · 2014-07-16 07:30

If I use only one connection(TZConnection) and one query (TZQuery)
then how am I supposed to visualize the all tables in DBGrid which in
turn will require the  DBGrid->Datasource->TZQuery->TZConnection
components chain again?

----------------------------------------------------------------------
mangakissa · 2014-07-16 09:06

I'll try to understand your question.
there are two ways to show your data with one TDBGrid (if you
mentioned that).
1. Use one TzQuery and put all your queries in it with property
TZQuery.SQL.Text.
    With open and close you can see your data.
2. Use different TzQueries and fill them with you required query.
    Switch your datasource whith the created TzQueries.

I think option 2 will work better.

What are you trying to accomplish?

----------------------------------------------------------------------
Ronan · 2014-07-16 11:19

Ok, thanks for helping,

I'm doing close to the thing you describe in #2 option with little
exception. Suppose that I have one TForm, place 4 DBGrid, 4 DataSource
and connect each one to 4 different TZQuery. The exception is,
somewhere in the code I need to refer to each record (for that I use
another general purpose 5th TZQuery) residing in the 4 different
DBGrid and change them, but with 5th TZQuery ;

If that needs to be hard coded :

Code: [Select]var
  val1, val2, val3, val4 :Double;

    // 1st Table modification
    5thQuery.Close;
    5thQuery.SQL.Clear;
    5thQuery.sql.add('select * from Table1');
    5thQuery.Open;

    5thQuery.FieldByName(Field1).As Double := val1;
    5thQuery.POst;


    // 2nd Table modification
    5thQuery.Close;
    5thQuery.SQL.Clear;
    5thQuery.sql.add('select * from Table2');
    5thQuery.Open;

    5thQuery.FieldByName(Field1).As Double := val2;
    5thQuery.POst;

    // 3rd Table modification
    5thQuery.Close;
    5thQuery.SQL.Clear;
    5thQuery.sql.add('select * from Table3');
    5thQuery.Open;

    5thQuery.FieldByName(Field1).As Double := val3;
    5thQuery.POst;

    // 4th Table modification
    5thQuery.Close;
    5thQuery.SQL.Clear;
    5thQuery.sql.add('select * from Table4');
    5thQuery.Open;

    5thQuery.FieldByName(Field1).As Double := val4;
    5thQuery.POst;
   any changes made in Table1,2,3,4 are not visible in the
DBGrid1,2,3,4.

If it is not verbose I 'll try to explain it another way.

----------------------------------------------------------------------
rvk · 2014-07-16 12:08

And you can't do it like this ?
Code: [Select]var
  val1, val2, val3, val4 :Double;
begin
    // 1st Table modification
    1stQuery.FieldByName(Field1).As Double := val1;
    1stQuery.Post;

    // 2nd Table modification
    2ndQuery.FieldByName(Field1).As Double := val2;
    2ndQuery.Post;

    // 3rd Table modification
    3rdQuery.FieldByName(Field1).As Double := val3;
    3rdQuery.Post;

    // 4th Table modification
    4thQuery.FieldByName(Field1).As Double := val4;
    4thQuery.Post;
That way they are directly visible in your DBGrids because you're
using the same TZQuery.

If you do it your way you'll need to refresh the other queries to get
the changes. (or Close and Open them)

----------------------------------------------------------------------
miab3 · 2014-07-16 12:25

@Ronan

It should look like this:
Code: [Select]    ZQuery.Close;
    ZQuery.SQL.Clear;
    ZQuery.sql.add('select* from SALES');
    ZQuery.Open;

    // Write data to table
    Zquery.Edit;  // <--- missing Edit
    ZQuery.FieldByName('SALES_REP').asInteger := 45;  // <--- should
be an integer
    Zquery.Post;
    ZQuery.ApplyUpdates; // Or without ApplyUpdates if CachedUpdates
False
If you want to be refreshed another Query and dbGrid with data that
have been modified add:
Code: [Select]    anotherQuery.Refresh;
Works for me (refreshes dbGrid) without any problem in:
ZEOS 7.2 Revision 3232
http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/  

Michal