Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Database Transactions. Ummm..



Permlink Replies: 4 - Last Post: Feb 18, 2018 11:56 PM Last Post By: Robert Triest
Ian Branch

Posts: 442
Registered: 9/23/99
Database Transactions. Ummm..
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2018 12:32 PM
Hi Team,
Database Transaction processing - Always been aware of it but not had the need.

Having been handed a significant project I feel I should incorporate them. ;-)

I am familiar with the following construct..
	ttable.Open;
	Database.BeginTransaction;
	Try
	   ttable.Append;
	   ttable.FieldByName('DTG').AsDateTime := Now;
	   ttable.FieldByName('Log Event').AsString := 'User ' + msuUserID + ' has logged out.';
	   ttable.Post;
	   Database.Commit;
	 Except
	   Database.Rollback;
	 End;
	 ttable.Close;
	

This is all good when everything is happening within the code per above.

Apparently it is not good form have such a construct when the user is actually editing the data..
	ttable.Open;
	Database.BeginTransaction;
	Try
	   ttable.Append;
	   ....User enters data into DBEdits;
	   ....User enters data into DBEdits;
	   ....
	   ....
	   ttable.Post;
	   Database.Commit;
	  Except
	   Database.Rollback;
	 End;
	 ttable.Close;
	


So, to my questions.
1. If the above is generally poor form, what are techniques to mitigate it?

2. If the data is being edited in the actual DBGrid and the table edit operations, Insert, Edit, Post, Cancel, are
being managed by the associated DBNavigator, how does one integrate Transaction processing into it?
My initial thought is that is would need to be done at the ttable Event level.
i.e.
	procedure TForm5.Table1BeforeInsert(DataSet: TDataSet);
	begin
		Database.BeginTransaction;
	end;
	procedure TForm5.Table1BeforeEdit(DataSet: TDataSet);
	begin
		Database.BeginTransaction;
	end;
	procedure TForm5.Table1AfterPost(DataSet: TDataSet);
	begin
		Database.Commit;
	end;	
	procedure TForm5.Table1AfterCancel(DataSet: TDataSet);
	begin
		Database.RollBack;
	end;
	procedure TForm5.Table1EditError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
	begin
		Database.RollBack;
	end;
	

Is the above a correct/appropriate set of mechanisms or is there a better way? In particular the exception handling
aspect.

Regards & TIA,
Ian
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Database Transactions. Ummm..
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2018 10:19 PM   in response to: Ian Branch in response to: Ian Branch
On 2/18/2018 3:32 PM, Ian Branch wrote:
Hi Team,
Database Transaction processing - Always been aware of it but not had the need.

Having been handed a significant project I feel I should incorporate them. ;-)

I am familiar with the following construct..
	ttable.Open;
	Database.BeginTransaction;
	Try
	   ttable.Append;
	   ttable.FieldByName('DTG').AsDateTime := Now;
	   ttable.FieldByName('Log Event').AsString := 'User ' + msuUserID + ' has logged out.';
	   ttable.Post;
	   Database.Commit;
	 Except
	   Database.Rollback;
	 End;
	 ttable.Close;
	

This is all good when everything is happening within the code per above.

Apparently it is not good form have such a construct when the user is actually editing the data..
	ttable.Open;
	Database.BeginTransaction;
	Try
	   ttable.Append;
	   ....User enters data into DBEdits;
	   ....User enters data into DBEdits;
	   ....
	   ....
	   ttable.Post;
	   Database.Commit;
	  Except
	   Database.Rollback;
	 End;
	 ttable.Close;
	


So, to my questions.
1. If the above is generally poor form, what are techniques to mitigate it?

2. If the data is being edited in the actual DBGrid and the table edit operations, Insert, Edit, Post, Cancel, are
being managed by the associated DBNavigator, how does one integrate Transaction processing into it?
My initial thought is that is would need to be done at the ttable Event level.
i.e.
	procedure TForm5.Table1BeforeInsert(DataSet: TDataSet);
	begin
		Database.BeginTransaction;
	end;
	procedure TForm5.Table1BeforeEdit(DataSet: TDataSet);
	begin
		Database.BeginTransaction;
	end;
	procedure TForm5.Table1AfterPost(DataSet: TDataSet);
	begin
		Database.Commit;
	end;	
	procedure TForm5.Table1AfterCancel(DataSet: TDataSet);
	begin
		Database.RollBack;
	end;
	procedure TForm5.Table1EditError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
	begin
		Database.RollBack;
	end;
	

Is the above a correct/appropriate set of mechanisms or is there a better way? In particular the exception handling
aspect.

Regards & TIA,
Ian

Both of your examples when using the BDE (which you should be getting off of
like a decade ago) you do not need to add explicit transaction handling.

The BDE will do insert/update/deletes in their own transaction and commit if
successful, rollback if not. Since you are only doing one update per
transaction this is already what the BDE is doing for you under the hood.

With the BDE the main use of a transaction is when you have several operations
all of which need to succeed or all fail which gives you the added control to
only commit if everything worked, rollback otherwise.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Ian Branch

Posts: 442
Registered: 9/23/99
Re: Database Transactions. Ummm..
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2018 10:56 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:

Both of your examples when using the BDE (which you should be getting off of
like a decade ago) you do not need to add explicit transaction handling.

The BDE will do insert/update/deletes in their own transaction and commit if
successful, rollback if not. Since you are only doing one update per
transaction this is already what the BDE is doing for you under the hood.

With the BDE the main use of a transaction is when you have several operations
all of which need to succeed or all fail which gives you the added control to
only commit if everything worked, rollback otherwise.

Hi Jeff,

I was just using the basics to illustrate and query techniques. I am in fact using Advantage Database Server. The
App will have some 20 simultaneous users.

Regards,
Ian
Bernd Maierhofer

Posts: 161
Registered: 9/27/99
Re: Database Transactions. Ummm..
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2018 11:11 PM   in response to: Ian Branch in response to: Ian Branch
Ian Branch wrote:

Hi Team,
Database Transaction processing - Always been aware of it but not
had the need.

Having been handed a significant project I feel I should incorporate
them. ;-)

I am familiar with the following construct..
	ttable.Open;
	Database.BeginTransaction;
	Try
	   ttable.Append;
	   ttable.FieldByName('DTG').AsDateTime := Now;
	   ttable.FieldByName('Log Event').AsString := 'User ' + msuUserID +
' has logged out.'; 	   ttable.Post;
	   Database.Commit;
	 Except
	   Database.Rollback;
	 End;
	 ttable.Close;
	

This is all good when everything is happening within the code per
above.

Apparently it is not good form have such a construct when the user
is actually editing the data..
	ttable.Open;
	Database.BeginTransaction;
	Try
	   ttable.Append;
	   ....User enters data into DBEdits;
	   ....User enters data into DBEdits;
	   ....
	   ....
	   ttable.Post;
	   Database.Commit;
	  Except
	   Database.Rollback;
	 End;
	 ttable.Close;
	


So, to my questions.
1. If the above is generally poor form, what are techniques to
mitigate it?

2. If the data is being edited in the actual DBGrid and the table
edit operations, Insert, Edit, Post, Cancel, are being managed by the
associated DBNavigator, how does one integrate Transaction processing
into it? My initial thought is that is would need to be done at the
ttable Event level. i.e.
	procedure TForm5.Table1BeforeInsert(DataSet: TDataSet);
	begin
		Database.BeginTransaction;
	end;
	procedure TForm5.Table1BeforeEdit(DataSet: TDataSet);
	begin
		Database.BeginTransaction;
	end;
	procedure TForm5.Table1AfterPost(DataSet: TDataSet);
	begin
		Database.Commit;
	end;	
	procedure TForm5.Table1AfterCancel(DataSet: TDataSet);
	begin
		Database.RollBack;
	end;
	procedure TForm5.Table1EditError(DataSet: TDataSet; E:
EDatabaseError; var Action: TDataAction); 	begin
		Database.RollBack;
	end;
	

Is the above a correct/appropriate set of mechanisms or is there a
better way? In particular the exception handling aspect.

Regards & TIA,
Ian

Hi Ian,

the use of transactions is to make sure, that operations on the DB
either fail all or succeed all.

Rules:
- Transactions are a must when it comes to concurrency
- Never leave the commit to the user (as in your 2nd example)
To allow editing without touching the DB you need some kind of cached
updates, either by using an ORM or client datasets or in-memory
datasets.
- Keep transaction size as small as possible, as transactions lead to
lockings in the DB, either row, page or even table locking. Depending
on user count this might escalate.
- concurrency needs understanding of isolation and locking mechanisms

HTH as a starter.

Bernd

Robert Triest

Posts: 687
Registered: 3/24/05
Re: Database Transactions. Ummm..
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2018 11:53 PM   in response to: Ian Branch in response to: Ian Branch
Apart from how to handle commit, rollback, I would say.. Get rid of the TTable to handle your queries.
Step over to TQuery (TADOQuery, TFDQuery,TSQLQuery) and do your Database access with SQL language.
In your example you use a database insert:

var
  SQLText: String;
  FieldList, ValueList: String;
begin
  FieldList:='DTG';
  FieldList:=FieldList+',Log Event';
  
  ValueList:=':DTG';
  ValueList:=ValueList+',:Log Event';
  
  SQLText:='insert into "mytable" ('+FieldList+') values ('+ValueList+')';
 
  myQuery.Active:=False;
  myQuery.SQL.Clear;
  myQuery.SQL.Add(SQLText);
 
  myQuery.ParamByName('DTG').AsDateTime :=now;
  myQuery.ParamByName('Log Event').AsString:='User ' + msuUserID + ' has logged out.';
 
  myQuery.ExecSQL;
end;
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02