Forum > Databases

is there any solution like clentdataset-DataSetProvider-query on delphi

<< < (2/5) > >>

marcov:
Sqlquery is a custombufdataset, so that copying might not be necessary.

jianwang:
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:
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:

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

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:
> 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.


* https://robertocschneiders.wordpress.com/2013/01/09/datasnap-analysis-based-on-speed-stability-tests-part-2/
* https://andremussche.blogspot.com/2013/01/datasnap-ro-rtc-mormot-wcf-node-speed.html
* https://datasnapperformance.wordpress.com/2014/10/16/datasnap-performance-test-en/
> 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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- DeltaDS.FieldByName('ID').NewValue:=newID ;I was buffled.

For Firebird usually you do

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---INSERT INTO TableName(data1,Data2, ...) VALUES ( :param1, :param2, ...)  returning IDAnd 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

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version