Recent

Author Topic: is there any solution like clentdataset-DataSetProvider-query on delphi  (Read 57886 times)

jianwang

  • New member
  • *
  • Posts: 7
Hello everyone:
 i always develop n-tier application on delphi. and is used to code by ClientDataSet on client side and DataSetProvider on server side. is there any solution like above on lazarus?
  I attach some example code that i worked on delphi:
 
1. get data from database:
1.1server-side code:
   Query.Connection:=DBConnect;  //DBConnection connected to the database  and Query connected to the DBconnection
   Query.SQL.Text:='select * from tablename'; //manully set sql statement
      result:=DataSetProvider .GetRecords(-1,i,MetaDataOption+ResetOption); // DataSetProvider linked to the query and

1.2 return search result by networt transfom like web service

1.3 Client-side code:
               ClientDataSet.data :=search result returned by server-side web service

2. commit clientdataset changed record  to server side database:
2.1 Client-side code:
             send ClientDataSet.Delta to server side by web service

2.2server-side code:
            Query.Connection:=DBConnect;  //DBConnection connected to the database  and Query connected to the DBconnection
            Query.SQL.Text:= 'select * from tablename where 1=2';
            DataSetProvider.ApplyUpdates(Deltadata,0,i); // Deltadata is from ClientDataSet.Delta
           
2.3 on DataSetProviderBeforeUpdateRecord  event  to write code for some sql manully like below:

   procedure TdmSysManage.dspDepartMentBeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet;
  UpdateKind: TUpdateKind; var Applied: Boolean);
begin
  if SourceDS =qryDepartMent then
  begin
     //DeltaDS.FieldByName('ID').ProviderFlags := [];
     case UpdateKind of
     ukInsert:
     begin
        DeltaDS.FieldByName('ID').NewValue:=newID ;
        Applied :=False;
     end;
     ukModify:
     begin
        Applied :=False;
        DeltaDS.FieldByName('ID').ProviderFlags := [pfInKey];
        DeltaDS.FieldByName('Update_Time').NewValue:=now;
     end;
     ukDelete:
     begin
        Applied :=true;
        DBConnect.Execute('delete from tablename where ID = ' + DeltaDS.FieldByName('ID').oldvalue);
     end;
     end;
  end;
end;

this is very convenient way to process changed record, I can save changed record to database automatic or manully if needed.

in lazarus is there some similar way?

thanks a lot!


Thaddy

  • Hero Member
  • *****
  • Posts: 14167
  • Probably until I exterminate Putin.
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #1 on: September 22, 2022, 12:30:20 pm »
It is the exact same on Freepascal/Lazarus as it is in Delphi..
« Last Edit: September 22, 2022, 01:08:07 pm by Thaddy »
Specialize a type, not a var.

jianwang

  • New member
  • *
  • Posts: 7
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #2 on: September 22, 2022, 03:53:52 pm »
Hello, Thaddy
 is it  exact same on Freepascal/Lazarus as it is in Delphi ? can you attch some example code for refrence?
thank you!

Thaddy

  • Hero Member
  • *****
  • Posts: 14167
  • Probably until I exterminate Putin.
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #3 on: September 22, 2022, 05:21:02 pm »
Like your own code? Which is not complete. But Your own code should work if it works in Delphi.
Otherwise report back.
Specialize a type, not a var.

egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #4 on: September 23, 2022, 09:52:43 am »
I couldn't find anything like either TDataSetProvider or TClientDataSet in Lazarus.
Currently I'm using TBufDataSet instead of TClientDataSet.

1. get data from database:

   When there is a request frrom client,

   1) Execute SQL statement in server. This is TSQLQuery. I copy it to TBufDataSet,
       using TBufDataSet.CopyFromDataSet(SQLQuery1), save it as TStream,
       and server returns the TStream to the client. 
   2) Client gets the stream, and a copy of TBufDataSet is made --- using TBufDataSet.LoadFromStream.


2.  Update changes to the server

    As there are no TDataSetProvider, there are no Delta neither.

    There are two options.

    1) For a table with large amount of modifications,
       - First I pack the new table as a TStream, and send it to server.
       - Server first delete all records of the specific condition.
       - Server inserts all records received from the client.

    2)  Small changes :  I try to keep both table (server and client) as the same at record basis ---
         treated in the event handlers of  beforepost, beforeinsert, beforedelete, etc.
         of client's local dataset (TBufDataSet).
         
         When there is a deletion, for example, the client first request the server to delete a record
         with the key fields in the server DB, and if successful then delete client's dataset record
         but calls DataSet.Cancel if not successful.

         Insertions and updates are treated within beforepost event.
         Client sends one records data in the TStrings.Text format (fieldname=value...),
         and server call InsertOrUpdate based on keys.

Hope anybody suggests better idea.

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11352
  • FPC developer.
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #5 on: September 23, 2022, 09:59:59 am »
Sqlquery is a custombufdataset, so that copying might not be necessary.

jianwang

  • New member
  • *
  • Posts: 7
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #6 on: September 23, 2022, 06:20:50 pm »
Hello egsuh,
thanks for your reply.

to be honest,I don't think the two options you metioned is good.

for option 1:
 if table have auto increase column, first delete original record and then inserts modified records received from the client will cause generating new auto increase value for same record, when another table linked this table by the auto increase column, this will cause  inconsistent data.
for option 2:
actually in most case, we need delete record,modify record and insert record to be one transaction. these changed record need to be send to server-side at same time,then server-side process these record in one transaction. by option 2, server-side only process one changed record one time.

whatever thank you again!

any body can give better suggestion ?



Arioch

  • Sr. Member
  • ****
  • Posts: 421
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #7 on: September 23, 2022, 07:00:25 pm »
Don't delete records that you want to update.

You decided you want to delete data, then you try to fight the consequence of it.

Don't delete data.
Or, at very least, insert the data with the same ID like it was deleted.

However, if you use SQL, then you probably enshrine business rules on data as database constraints. See https://en.wikipedia.org/wiki/Foreign_key for example. And then, there will be no deleting without consistences for dependent tbles, even if you later insert new rows with the same ID.

So, the idea of deleting records instead of updating is really flawed. On multiversion (MVCC, MGA, etc) database engines this might trigger massive index rebuilding and garbage colleciton, creating unexpected long delays.

If you don't want to delete - don't delete.

For example, load your data packet into a GLOBAL TEMPORARY TABLE then use SQL MERGE command to pump it into the persistent table.

egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #8 on: September 24, 2022, 07:03:23 am »
Quote
to be honest,I don't think the two options you metioned is good.

That's right. I hope there are better ways like Delphi's MIDAS or DataSnap, which are not.

It's simple if I connect to database (Firebird in my case) directly, like HTTP connection. But it does not seem good idea to open database on the http. So I have to write the transmission codes myself.

The methods must be adjusted depending on the structure of tables. For example, if the table has primary key field like auto-increment field, two steps is sufficient:  1) first delete records of to-be-deleted ids, and 2) UpdateOrInsert based on the key. These actions can be done within one transaction. In my case values of primary key itself may change (without autoincrement field), so this way is meaningless.

The second example is when those operations do not have to be done within one transaction. Deletion is deletion, insertion is insertion, and update is update. Not many records in a dataset.

Arioch

  • Sr. Member
  • ****
  • Posts: 421
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #9 on: September 24, 2022, 03:32:03 pm »
> That's right. I hope there are better ways like Delphi's MIDAS or DataSnap, which are not.

Yes and no. There is no drop-in replacement, but there are different means to achive multi-tier architecture.


> It's simple if I connect to database (Firebird in my case) directly, like HTTP connection. But it does not seem good idea to open database on the http. So I have to write the transmission codes myself.

The classic way to connect to IB/FB over global net was ZeeBeeDee which later wast incorporated into OpenVPN.
Given good configuration, this should fence off crackers and relax temporary connection drops (but not totalyl hide them).
Firebird low level protocol, though, is chatty, having a lot of roundtrips. Which is perfectly okay for LAN envionment, provides easy early detection of errors, etc. But for global, internet-like connection the accumulated "ping penality" becomes big.
So, direct far-away conneciton to database is usually considered workable but not best of solutions.

That said, while mORMot has FB3 interface unit, i don't know if its support is anywhere but nominal. mORMot chose FPC as their neext (after Delphi 7) base target, which is good for you, but they also chose SQLite as their primary DB.

However...

With Firebird you definitely always have a choice to populate ID columns or to let server auto-populate them. When i saw
Code: Pascal  [Select][+][-]
  1.  DeltaDS.FieldByName('ID').NewValue:=newID ;
I was buffled.

For Firebird usually you do
Code: SQL  [Select][+][-]
  1. INSERT INTO TableName(data1,Data2, ...) VALUES ( :param1, :param2, ...)  returning ID
And then you read into your program the real ID created by Firebird. This, BTW, saves you one netwrok roundtrip, comparing to an extra query to GEN_ID

Try reading more, on the stack overflow for example, about GTT and Merge.

https://stackoverflow.com/questions/43997516
https://stackoverflow.com/questions/36887591

Those exampels are not immediately your problem - but hopefully would be a starter for you to get idea of those tools. There were other questions, more close to your case, you would probably find them eventually.

> 1) first delete records of to-be-deleted ids,

Really bad idea.

On any server: you would have all the Foreign-key linked tables to loose their data. In ISAM term when you delete a row from Master table - the rows in Details tables have to be deleted too.

Specifically on Firebird/Interbase (and also Postress, Oracle, and any other MGA/MVCC server) - you make unneededly big changes in the database storage, creati a lot of garbage, invaluidating huge parts of indexes. IOW you create a lot of garbage in the storage.

Then, Posgress does store record version in the indexes, so it makes updates slower. Interbase/Firebird does not an do updates are faster - but it means lot of leafs in the indexs are dangling pointers having no rows they point to, which slows down reading operations.  Eventually Firebird gives up and starts Garbage Collector and this slows down the database for quite a while [is SuperServer arch]. In [Super]Classic Server architectures it also means your query is stopped and waits until GC completes, before it is executed.

FB SuperServer means forking threads, and before FB3 it means "one CPU core per connection" (maybe still is, not sure).
FB Classic and SuperClassic means forking processes not threads for connecitons.


Frankly, massive delete is on the list of "don't do"s for IB/FB - it is in common wisdom domain, or you can look for articles explaining how GC works in FB/IB family

Also see http://www.ibase.ru/dontdoit/
Some articles, but probably not that one, were translated on https://ib-aid.com/en/articles/

> 2) UpdateOrInsert based on the key.

Bad idea. This operation can only work one one single row. Meaning if you have 100 rows you would have to issue 100 separate queries. Each would have several network roundtrips, paying ping penalty again and again.

Either go multi-tier, making a FPC/Delphi-written intermediate server, and your clients worinf with him never having direct conneciton to the DB.
Or make it GTT+Merge.

> These actions can be done within one transaction.

per-transaciton GTT + merge

> In my case values of primary key itself may change (without autoincrement field),

Why??? Make them non-changing. What purpose ios for them to change? How can you solve the problem different ways?

When you have several clients working with the same DB - how would Client A inform CLient B that he just changes the PK for the row Client B is processing and so the Client B should drop hios work ad start processing anew with different PK value ???

Or, even make them changing but make Firebird UPDATE PK values and tack the changes into all the detail tables (Foreign Keys in the "on update cascade" mode), though it is still creating unnedded proiblem just to then be looking for ways to escape it. Better be not creeating the problem.

BTW, you can have a column with Unique Constraint with Unique Index different than PK

« Last Edit: September 24, 2022, 03:37:03 pm by Arioch »

egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #10 on: September 26, 2022, 03:12:34 am »
@ Arioch

Really thank you for your comments.

Quote
This operation can only work one one single row. Meaning if you have 100 rows you would have to issue 100 separate queries.

Is there any way to insert or update multiple rows at once? Both at SQL level and Lazarus application level.

Quote
When you have several clients working with the same DB - how would Client A inform CLient B that he just changes the PK for the row Client B is processing and so the Client B should drop hios work ad start processing anew with different PK value ???

Primary key is Pid + Qid, both are integer values. Only one client is allowed to operate on a group of records --- i.e. PID --- once any PID is used by any one client, editing records with that PID is not allowed.


Anyway your comments are very helpful again.

Arioch

  • Sr. Member
  • ****
  • Posts: 421
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #11 on: September 26, 2022, 06:57:28 pm »
Maybe i got you wrong...

You said

Quote from: egsuh
    1) For a table with large amount of modifications,
       - First I pack the new table as a TStream, and send it to server.
       - Server first delete all records of the specific condition.
       - Server inserts all records received from the client.

There was no modification/updating in the list. Do you just forget it?

Or do you really do delete+insert instead of modifications? Because that is how it looked for everyone reading. And that is what everyone argue against, but maybe it was wrong idea.

update multiple rows at once? Both at SQL level

Insert - not a lot of options. Yes, you can send in some XML or JSON and then parse it using PSQL stored procedure. Or you can send binary "file of record" and use SYSDBA to attach it as EXTERNAL TABLE, but those are rather exotic tools. If you fo that way - you

Anyway, individual INSERT operations are not much of problem, as long as they do not interfere with other connections/transactions
Your app-server can populate a GTT with them with no hurry. OR even clients could, but remote INSERTs would work slower due to roundtrips penalty.

It is modifying transaction that better be short, to minimize collision chances.

After you populated a per-connection GTT you can run a MERGE or a stored procedure in one command, then read the results. You would have to clear GTT then.
Or you might do a per-transaciton GTT, then do MERGE/SP, if the transaciton was READ COMMITTED it would be probably mostly the same, as the data from modified tables was not read before you run MERGE.

Perhaps you would need two "input" GTTs, for insert/update and for delete.

or GTT + Stored Procedure

Quote
Primary key is Pid + Qid, both are integer values. Only one client is allowed to operate on a group of records --- i.e. PID --- once any PID is used by any one client, editing records with that PID is not allowed.

Sounds you're trying to create something like a blockchain (git, etc) - a set of immutable data frames, that can be deleted and replaced with newer frames.

Do you delete old PID groups or are they left there forever?
Should new PID groups hide old PID groups, like yesterday values of account ususally not matter, only today values do.
The old values are lost or copied to archive table.

Well, maybe you'd rather add VersionID column and make the server increase it in "before update" trigger.
The PK is not changed, but a separate PID/QID/VID unique index is created.

You can also create some 'Last_Save' generator/sequence and make your clients mark their rows with it. Then every client could know the last ID it read from the database last time and whould no to re-load all the newer IDs. As in every replicaiton, though, there would be a problem how to inform about deleted rows. Inserted and updated are easier...


Quote
DeltaDS.FieldByName('Update_Time').NewValue:=now;

Fragile, if it serves any purpose than purely looking at it. We had cases when client computers had different time set. There is also "daylight saving time" in many countries.
But you can also ask server to set that column to the value to CURRENT_TIMESTAMP when your applicaiton doe not send it.
https://www.firebirdsql.org/refdocs/langrefupd20-alter-table.html#langrefupd20-at-ac-setdefault

egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #12 on: September 27, 2022, 07:04:13 am »
The subject of this thread is implementing a kind of briefcase model via web. That's the point, and how to design database structure is not a main concern. The requirement is to implement following flow: first download a group of records from server, second there are modifications at the client side, and finally the modifications must be applied to the server database table.

For this purpose, I devised myself two approaches:
   1) updating the whole dataset which has been downloaded to the client and modified (inc. insertion and deletion) there
   2) updating at record level via Primary key or other keys.

The second approach is nearer to generic solution. When you delete a record at client, a record with the same key is deleted from server table. Before changes in the client dataset is posted, record of server table is modified. But every update to server file require calling webserver via HTTP(which is quite cumbersome. Anyway I use Ararat Synapse for this).

But I also need to edit bulk amount of records.  Downloaded dataset is made with following SQL.

       select * from qdftable where pid=3655;

which may be from several tens of records to hundreds of records. They have order, and users may add, delete, or change the order within the dataset. Changing one record's order will affect other records' data as well. I found it is impractical to update the dataset at record level because I have to track all records that are changed. The impracticality led me to devise the first approach, which is to upload the whole dataset of the client.

This is basically two step --- delete records withpid=3655 and insert all the records from client. But in practice it is done in three steps:

          delete from qdftable where pid = -3655;
          update qdftable set pid=-3655 where pid=3655;
          insert all records from client to qdftable; 


You asked:
Quote
There was no modification/updating in the list. Do you just forget it?

Modification/update is not necessary because there are no records with pid=3655 to modify/update. 

Regarding version / logging etc., I appreciate your advises. I'm using them as necessary.

Again the point is how to implement a briefcase model via web, because MIDAS/DATASNAP is not available for now. I'm not saying my methods are good or optimal or generic solution at all. Just I devised them for my own application.

My codes were written several years ago. I did not and still do now have much knowledge on SQL/FreePascal. I have collected information for my solution and if feasible, I implemented. I did not search for other options much. 

Now I'm reviewing bit by bit of my application. For the first approach (bulk update), I'm wondering whether this is possible:  0) run Select SQL, 1) packing whole SQLQuery from server, 2) downloading it to client, replacate it and do the editing, 3) packing whole client dataset, 4) upload it to server, 5) replacate it in server, 6) and calling ApplyUpdate.  I think I have tested a few times, but as the whole stream had to be sent over internet, I had some difficulties and did not delve into it much.

I hope Lazarus provide a generic solution like MIDAS/DataSnap so that I only have to do complain when something does not follow my intention  :D
« Last Edit: September 27, 2022, 07:11:10 am by egsuh »

jianwang

  • New member
  • *
  • Posts: 7
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #13 on: September 29, 2022, 08:56:38 am »
Actually,the key point is how to store all changed records on client-side in one package just like clientdataset.delta, and then send these data to server-side, and then server-side how to handle these changed-records ?

egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: is there any solution like clentdataset-DataSetProvider-query on delphi
« Reply #14 on: September 29, 2022, 09:47:01 am »
Yes. I have a separate thread on this. Please see following. Hope you can test this approach and share the result.

https://forum.lazarus.freepascal.org/index.php/topic,60741.0.html

 

TinyPortal © 2005-2018