[SOLVED] tsqlquery refresh one current row after post

[zonafets]

Hi,

this is the same problem of autoincrement/serial field.

But in my case an insert of new record or update, can change others fields (default values, triggers, etc.).

So I need to refresh the row just updated.

The problems are two:
1. I can manually read the row from db but need to update the cached/mem buffer bypassing qry.fieldbyname('fld') that is allowed only in edit mode
2. the step into of the debugger do not work on applyupdate, so I cannot trace the code to find the instructions of my interest

Hope somebody can help because this problem is frustratring me for many days.

Thanks

[mangakissa]

Create persistentfields and set your ID readonly. After a insert a InsertQuery will be created without id. The database creates a unique id and put the record in the database. After a refresh your id will be read and can be used for updating your record.

http://wiki.freepascal.org/Working_With_TSQLQuery

[LacaK]

in TRUNK version of FPC exists new property of TSQLQuery named RefreshSQL (plus other new stuff related to refreshin values of fields in record).
You can set SQL in it and it will refresh values in record, when record is Applyied to database.

[exdatis]

in TRUNK version of FPC exists new property of TSQLQuery named RefreshSQL (plus other new stuff related to refreshin values of fields in record).
You can set SQL in it and it will refresh values in record, when record is Applyied to database.

Great news!

[zonafets]

in TRUNK version of FPC exists new property of TSQLQuery named RefreshSQL (plus other new stuff related to refreshin values of fields in record).
You can set SQL in it and it will refresh values in record, when record is Applyied to database.

Great news!

It looks like is not possible copy&paste the fcl-db and recompile. So I'll try to set Lazarus to use fpc 2.7, hoping to not incurring in others problems.

Anyway, my intention was to hack the ApplyUpdates to use rdbms RETURNING clause and manually set the dataset internal buffer of current record.

[exdatis]

Instead "Returning" in Firebird you can use sequence, there is an easy way to find new ID(eg. select next value for seq_name from rdb$database) or create view(if you use a generator) as
select gen_id(generator_name, increment_number) from rdb$database. If you use postgresql there is option: select nextval('seq_name'). I use these options already.

[zonafets]

[...] If you use postgresql there is option: select nextval('seq_name'). I use these options already.

Yes, I know. This is actually the problem with sqldb or zeosdb. They don't let me free to use the maximum power of db engine. They want avoid to the programmer to write code and know sql.
Even if I get the id from seq, I cannot update the dbgrid.cell or I must set the dataset.field from onBeforePost, but can loose an ID if user wrong.

Another problem I have is that the a DELETE don't really remove the line from db but set only disabled field of the record and that I use a field of the query as message carrier for the front end.
Sqldb is very well done but too complex and too automatized without be really complete. Zeosdb looks more complete but suffer of same problems.

It seems like I need only a subset like:

tsqlquery = class
   onInsert    // where write and run my sql code and manage errors
   onUpdate  // where ...
   onDelete   // ...
   onSelect    // ...
   Refresh
   RefreshRow(TFields)
   end;

that let me to be responsbile for my own.

[mangakissa]

I don't think its a problem of any DBComponent tool. It's just the way you want to use it.

Resently I created a small application with SQLdb and Embedded Firebird. It took a time get it right, but it's works like a charm. I did a lot of readings and with help on the forum and internet and the result is great.

[Chema.Ortega]

There's another alternative.


If in the sql command you add "RETURNING field" and that insert sql command is "exec" not "open" (tsqlquery.open no no and no... use tsqlquery.execsql)... and read the fields[0] value... there is your "returning" value...

[mse]

[...] If you use postgresql there is option: select nextval('seq_name'). I use these options already.

Yes, I know. This is actually the problem with sqldb or zeosdb. They don't let me free to use the maximum power of db engine.

Maybe MSEgui is an alternative for you. It has tsequencelink, tmsebufdataset.refreshrecord(), tmsesqlquery.onapplyrecupdate and many other goodies. ;-)

[rasberryrabbit]

Another way on Firebird with ZEOSDBO,

Code: [Select]

@ stored procedure
               CREATE PROCEDURE "GETVALID_ID"
               (
                 "ID" BIGINT
               )
               RETURNS
               (
                 "RETID" BIGINT,
                 "NAME" VARCHAR(200)
               )
               AS
               begin
                 if (ID is null) then
                   RETID=GEN_ID(MYTEST_GEN,0);
                   else
                     RETID=ID;
                 select ID,NAME from SAMPLE where ID=::RETID
                 into ::RETID, ::NAME;
                 suspend;
               end;
// @ refresh sql
select * from getvalid_id(:id)


[zonafets]

Hi solved problem in a simple way.
I had balked on ApplyUpdates in Afterpost, but is not required.

So.

In TSQLQuery.BeforePost event can write my own INSERT/UPDATE ... RETURNING and updates the fields.
I'll never call ApplyUpdates.

Thanks for any inputs.

[zonafets]

Here is a 2nd solution the maybe avoid to rewrite INSERT/UPDATE instructions but require only to write a select * from .. where pkey.
(ofcourse this is less performant that use RETURNING clause but is more practical and multi-dbengine compatible).

// fcl-db ver 2.6.4
procedure ...users_qryBeforePost(DataSet: TDataSet);
begin

  if posting then exit;    // here is a break point that don't stop after [2]

  posting := true;

  users_qry.ApplyUpdates;
  TSQLTransaction(users_qry.Transaction).CommitRetaining;

  // local qry to read the just inserted/updated record  [1]

  // check for changes (don't know is already exists a method) 

  if changed then
    begin
    users_qry.Edit;  // this do not trigger a second Post [2]
                              // but the record will be involved in the next ApplyUpdates
    users_qry.fieldbyname('fullname').asString:= (* just readed value in [1] *);
    end;

  posting := false;

end;