-1

The question is how to check if transaction was committed or not in firebird in the Delphi level. Let me explain it. (Delphi level) I have call sql procedure which inserts some records into table. After this procedure was execute I do not commit it but I do select (other transaction) on table with just added records then the user can modify added by procedure records and can to decide if it should be saved or not. I need it to control active buttons [Save] and [Cancel].

Kazmirus
  • 69
  • 2
  • 8
  • You seem to be talking about two transactions. Are you saying you want the transaction which was called by your Sql procedure to insert the records, to remain active (uncommitted) while the user modifies or not the records inserted? – MartynA Jul 19 '15 at 16:17
  • Yes. One procedure inserts records and not commit. Another read that records and let the user to modified then is the decision if commit it or cancel. Its work very good but I can not control the buttons Save and Cancel (active or not active) – Kazmirus Jul 19 '15 at 16:24
  • Well, good luck! If you've got a server transaction waiting for the user (who might go out to lunch), you may need it ... – MartynA Jul 19 '15 at 16:30
  • I have two SQLquery. The first one insert records into table and after it was execute (not commited yet) the secend SQLquery read not commited records (from firebird buffer) and give the new inserted records to modify by user. Then user have to Save or Cancel. But I can not control the buttons because dataset with execsql do not change the state. (dsinsert,dsedit) – Kazmirus Jul 19 '15 at 16:34
  • I apologize for my obvious comments and questions. As I understand you are talking about [Read uncommitted](https://en.wikipedia.org/wiki/Isolation_(database_systems)#Read_uncommitted) transaction [Isolation level](https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels) type. – Abelisto Jul 19 '15 at 16:34
  • Well, ofcourse I can do that the righit way but this solution do not need that much work. Because I do not have to delete records if user decide to cancel all operation. The problem is more complicated but to discrabe all will get so much time and will be very defecould to understand so I decide to make it simple question. – Kazmirus Jul 19 '15 at 16:40
  • No. I do not talk about the transaction settings. This application will work for one or maybe two users bot is not problem with lock records or transaction. – Kazmirus Jul 19 '15 at 16:42
  • Let go back to (...who might go out to lunch). Do you see any problem with open transaction? Is no differend if you add 1 record and not commit because the user in the half of work has other tasks to do and then finish fillup fields after 30 min. or add 10 records and weit for accept (commit) transaction. – Kazmirus Jul 19 '15 at 16:58
  • 1
    "Do you see any problem with open transaction?" I confess that I'm not intimately familiar with how Firebird transactions work, but leaving a transaction awaiting user activity would be a cardinal sin on some other DBMSs. – MartynA Jul 19 '15 at 17:04
  • 2
    As @MartynA has pointed out, what you're trying to do is a ***very*** bad idea. Depending on exactly what updates User 1 is checking, there could be more locks than you expect, and User 2 could be blocked from working. Believe me, you don't want to go there! A much better approach is to use a disconnected dataset such as `TClientDataSet`. Make your changes in this dataset, let User 1 check them, then simply call `DataSet.ApplyUpdates` or `DataSet.CancelUpdates`. – Disillusioned Jul 19 '15 at 17:04
  • @Kazmirus Open transactions block other people from doing SELECTs UPDATEs, DELETEs, and sometimes even INSERTs. (It all depends on the specifics of the transaction and the statement the other user is trying to do.... If you want your database to perform well, you need to minimise transaction time. I.e. You can't wait for human input, otherwise you program will be slow and unable to scale up. – Disillusioned Jul 19 '15 at 17:09
  • This is good idea to disconnect dataset but this not end the transaction and I still not have the control of active and not active buttons. Two users do different jobs not on the same tables. I tell you more. The first procedure insert 30 records in 3 tables and all of tem is visable in one dbgrid and user modified two other tables which are base for the first 3 tables. Beleve me is vary crazy solution but I have no choise :) – Kazmirus Jul 19 '15 at 17:12
  • I know tah the best is open transacation and insert and post in one secend but not this time. – Kazmirus Jul 19 '15 at 17:16
  • I try to find solution by controling the Tsqltransaction and Tsqlconnection but I do not know what will be the result... and if I find the solution in that components. – Kazmirus Jul 19 '15 at 17:18
  • 2
    **1)** How does it 'not end the transaction'? Where is the transaction started in the first place? **2)** You always have a choice. **3)** I respectfully suggest you need to do a lot of background reading on database transactions and atomicity. The purpose of transactions is to make a unit of work atomic - they're **not** an "undo mechanism". **4)** Whatever your users' needs, your job is to translate their needs into a solution. To do this you need a _deep_ understanding of their needs and the technical options available. Otherwise you'll use the wrong techniques and have a poor solution. – Disillusioned Jul 19 '15 at 17:30
  • OK. STep 1. User choose some records from table "A" to context. – Kazmirus Jul 19 '15 at 17:36
  • 2
    @Kazmirus Take a look at this [question](http://stackoverflow.com/q/3833432/224704) and its answers. It might give you a better idea how to approach a `TClientDataSet` oriented solution. – Disillusioned Jul 19 '15 at 17:43
  • OK. STep 1. User choose some records from table "A" to context. Step 2. Run procedure which complit and calculate data from other tables "X", "Y","Z" etc. (depend which records choosed user in the first step) then inserts records to table "B" which user can modified by changing values in the tables "X", "Y" and "Z" . So modification is not to the table "B" by simple way. That way is Step 3 Read inserted records from table "B" for choose to context. – Kazmirus Jul 19 '15 at 17:50
  • After all that operations should by step 4 Save or Cancel. All that havy job I do on server by sql procedures and if I would like to divaide all job for a 100 forms that will be very complicated for user so I have to do this in max 3 forms and one transaction which start in step 1 and end in step 4. – Kazmirus Jul 19 '15 at 17:50
  • 1
    @CraigYoung is right. I'm afraid you need to completely re-think/re-design your dataset operations. – MartynA Jul 19 '15 at 18:24
  • I know all of that. That is standard solution for client dataset with master and detail datasets. But it can't be solution for my problem but I thing I found the solution. I should not make inserts into table inside sql procedure. I should just build packed records in memory and send them to the client as buffer (can close the transaction after send to client) and then the client dataset has packed of records waiting for modified, and insert and commit. Thanks to all of you. – Kazmirus Jul 19 '15 at 18:54
  • @Craig Young: I disagree with your statement "Open transactions block other people from doing SELECTs UPDATEs, DELETEs, and sometimes even INSERTs" - that may be true for less enlightened DBMS, but it certainly isn't for Firebird. Thanks to its Multi Version Concurrency Control implementation one would have to set a certain combination of transaction flags on purpose to get that behaviour. (Just for the record, I agree with most everything else you wrote regarding the question.) – mghie Jul 20 '15 at 10:56

1 Answers1

1

The solution is to not insert records inside of SQL procedure. Just prepare all records as buffer and send them to the dataset and then close the transaction of procedure. All modification will be done by using buffered records with client dataset which do not have to be connected to database and anything can be modified locally on client. Next steps are standard and easy. Thanks to all.

Jan Doggen
  • 8,799
  • 13
  • 70
  • 144
Kazmirus
  • 69
  • 2
  • 8