BorlandTalk.com

Rolando · Guest

I am trying to debug a simple master detail form which uses TADOQuery and TADOConnection to display and update data store in a AS400/DB2 v. 5.x. I would like to use a client cursor and to perform a Batch Update (lock type ilBatchOptimistic). The form is set up using data aware components and there is a save button which does something like this:

DBConn.BeginTrans;
HeaderQry.UpdateBatch
DetailQry.UpdateBatch
DBConn.CommitTrans

The problem I have is that the changes to the detail query are dropped regarless of the order I call their respective UpdateBatch methods. I think the problem has to do with the driver included with client access but I am not sure how to fix it. Can someone give any ideas? I have written similar code for Oracle 8i, 9i and 10g and I had no problems. Thanks in advance for your help.

Régis CLUSEAU · Guest

Transaction controls cannot be used through TADOConnection with IBM driver for DB2/400. I have experienced it for 3 years.
I solved the problem in creating 4 fonctions :

FUNCTION StartTransaction( AConn: TADOConnection ) : Boolean;
FUNCTION Commit ( AConn: TADOConnection ) : Boolean;
FUNCTION RollBack ( AConn: TADOConnection ) : Boolean;
FUNCTION Execute_SQL(AConn:TADOConnection; SqlCmd: String): Boolean;

Const
_SetTransaction = 'SET TRANSACTION ISOLATION LEVEL CS ';
_CommitTrans = 'COMMIT';
_RollBackTrans = 'ROLLBACK';

FUNCTION StartTransaction( AConn: TADOConnection ) : Boolean;
Begin
Result := Execute_SQL( AConnection, _SetTransaction );
End;
FUNCTION Commit( AConn: TADOConnection ) : Boolean;
Begin
Result := Execute_SQL( AConnection, _CommitTrans );
End;
FUNCTION RollBack( AConn: TADOConnection ) : Boolean;
Begin
Result := Execute_SQL( AConnection, __RollBackTrans );
End;
FUNCTION Execute_SQL(AConn:TADOConnection; SqlCmd: String): Boolean;
begin
Result := False;
If Not Assigned(AConn) Then Exit;
Try
AConn.Execute( SqlCmd, cmdText, [] );
Result := True;
Except
Display_ConnectionErrors(AConnection, SqlCmd);
Result := False;
End;
end;

I hope it will help

Régis CLUSEAU

"Rolando " <rmarinoc71 (AT) hotmail (DOT) com> wrote:

Régis CLUSEAU · Guest

Hi,

There is a small mistake in my previous answer. "AConn" means "AConnection" of course.

Régis

"Régis CLUSEAU" <rcluseau@seac-guiraud.fr> wrote: