[SOLVED] tsqlquery refresh one current row after post
lazarus / 16-Databases

----------------------------------------------------------------------
zonafets · 2014-12-06 01:46

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 · 2014-12-06 11:34

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 · 2014-12-06 18:18

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 · 2014-12-06 18:25

Quote from: LacaK on December 06, 2014, 07:18:26 pmin 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 · 2014-12-09 00:46

Quote from: exdatis on December 06, 2014, 07:25:27 pmQuote from: LacaK
on December 06, 2014, 07:18:26 pmin 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 · 2014-12-09 07:16

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 · 2014-12-09 14:29

Quote from: exdatis on December 09, 2014, 08:16:33 am[...] 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 · 2014-12-09 14:48

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 · 2014-12-09 14:49

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 · 2014-12-09 15:08

Quote from: zonafets on December 09, 2014, 03:29:08 pmQuote from:
exdatis on December 09, 2014, 08:16:33 am[...] 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 · 2014-12-10 00:58

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 · 2014-12-11 00:18

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 · 2014-12-11 12:02

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;