Recent

Author Topic: [SOLVED] tsqlquery refresh one current row after post  (Read 9758 times)

zonafets

  • New member
  • *
  • Posts: 9
[SOLVED] tsqlquery refresh one current row after post
« on: December 06, 2014, 02:46:15 am »
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

« Last Edit: December 11, 2014, 01:20:28 am by zonafets »

mangakissa

  • Hero Member
  • *****
  • Posts: 1090
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
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

LacaK

  • Hero Member
  • *****
  • Posts: 600
Re: tsqlquery refresh one current row after post
« Reply #2 on: December 06, 2014, 07:18:26 pm »
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

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: tsqlquery refresh one current row after post
« Reply #3 on: December 06, 2014, 07:25:27 pm »
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

  • New member
  • *
  • Posts: 9
Re: tsqlquery refresh one current row after post
« Reply #4 on: December 09, 2014, 01:46:20 am »
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

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: tsqlquery refresh one current row after post
« Reply #5 on: December 09, 2014, 08:16:33 am »
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

  • New member
  • *
  • Posts: 9
Re: tsqlquery refresh one current row after post
« Reply #6 on: December 09, 2014, 03:29:08 pm »
[...] 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

  • Hero Member
  • *****
  • Posts: 1090
Re: tsqlquery refresh one current row after post
« Reply #7 on: December 09, 2014, 03:48:34 pm »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Chema.Ortega

  • Newbie
  • Posts: 4
Re: tsqlquery refresh one current row after post
« Reply #8 on: December 09, 2014, 03:49:21 pm »
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

  • Sr. Member
  • ****
  • Posts: 286
Re: tsqlquery refresh one current row after post
« Reply #9 on: December 09, 2014, 04:08:53 pm »
[...] 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. ;-)

parcel

  • Full Member
  • ***
  • Posts: 135
Re: tsqlquery refresh one current row after post
« Reply #10 on: December 10, 2014, 01:58:26 am »
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

  • New member
  • *
  • Posts: 9
[solved] tsqlquery refresh one current row after post (with returning clause)
« Reply #11 on: December 11, 2014, 01:18:27 am »
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

  • New member
  • *
  • Posts: 9
[SOLVED] tsqlquery refresh one current row after post: another way
« Reply #12 on: December 11, 2014, 01:02:58 pm »
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;

« Last Edit: December 11, 2014, 01:21:42 pm by zonafets »

 

TinyPortal © 2005-2018