Title: Transaction processing in ADO+MS SQL Server/Oracle 9i+
Question: A DK3 member sent me an emai with a questionl: "I was wondering if you could point me to some articles that would help me learn how to properly handle record locking and transaction processing in SQL Server?"
My answer might be useful for other members.
Answer:
My method works well with ADO+MS ACCESS, ADO + MSSQLSERVER and ADO+ORACLE9i. Moreover it provides sufficient debug information. I start with the functions and I explain the the working in two examples.
No rocket science but nevertheless....
==============================================================================
THE CODE
==============================================================================
Function AutoExec1(cs: string; tp: integer = 2): boolean;
begin
// tp = 1: Begintrans + execute
// tp = 2: execute
// tp = 3: execute + Committrans
// tp = 4: Begintrans + Execute + Committrans
// debug mode before executing sql statement
if mainunit.sqllogmode then logsql('Comm 1: Standard', cs);
// tp = 1,4 = Begintrans
result := true;
if (tp = 1) or (tp = 4) then
begin
if dataform.ado_conn1.intransaction then dataform.ado_conn1.rollbacktrans;
dataform.ado_conn1.begintrans;
end;
// tp = 2 = test Intransaction
if (tp = 2) and (not dataform.ado_conn1.intransaction) then
begin
dataform.ado_conn1.begintrans;
end;
// tp = 1,2,3,4 = Execute
dataform.ado_comm1.commandtext := cs;
try
dataform.ado_comm1.execute;
except
on e: exception do
begin
// debug mode during executing sql statement
logsql('Comm 1: Exception', cs);
result := false;
if dataform.ado_conn1.intransaction then
dataform.ado_conn1.rollbacktrans;
exit;
end;
end;
// tp = 3,4 = Committrans
if tp 2 and dataform.ado_conn1.intransaction then
dataform.ado_conn1.committrans;
end;
Remarks
- mainunit.sqllogmode is a global boolean variable.
When True all sql statements will be recorded before execution (debugtool)
- dataform.ado_conn1 is a ADOConnection component
- dataform.ado_comm1 is a ADOCommand component
------------------------------------------------------------------------------
Procedure TransProc1(job: char; sendmess: boolean = false);
begin
with dataform.ado_conn1 do
case job of
'B': begin
dataunit.TransProc1('T');
begintrans;
end;
'C': if intransaction then committrans
else if sendmess then
mainunit.bad('Nothing to commit');
'R': if intransaction then rollbacktrans
else if sendmess then
mainunit.bad('Nothing to rollback');
'T': if intransaction then
begin
rollbacktrans;
if sendmess then
mainunit.bad('Had to rollback active transaction');
end;
else
MessageDlg('Invalid transaction request', mtError, [mbOK], 0);
end;
end;
Remarks:
- Mainunit.bad and Mainunit.good are messages
to the user at a 'no wait' basis
------------------------------------------------------------------------------
procedure logsql(mess, cs: string);
var
filename: string;
begin
filename := mainunit.log_dir + formatdatetime('yymmddhhmmss', now) + '.log';
with mainform.sqllog do
begin
clear;
lines.add(filename);
lines.add('');
lines.add(mess);
lines.add('');
lines.Add(cs);
lines.SaveToFile(filename);
if (pos('Exception', mess) 0) and
(MessageDlg('An SQL Exception occured!' +
#13#13'Details written to:' + #13'' + filename +
#13#13'Print the errordetails on your (default) printer?',
mtError, [mbYes, mbNo], 0) = mrYes) then
mainform.sqllog.Print(filename);
end;
end;
Remarks:
- Mainunit holds the mainform
- mainunit.log_dir is a global variable indicating
an user depended log-directory on disk
- mainform.sqllog is a not visible richedit object on the mainform
used for all kind of internal tasks
==============================================================================
EXPLANATION BY EXAMPLES
==============================================================================
I recognize two approaches, which I call the manual and the automatic
approach. The manual approach is necesarry when using Delphi's Edit and
Post commands. The AUTOEXEC-function handles (successive) SQL-statements
via ADOCommand.
Important: with ORACLE as backend, the SQL-commands via ADOcommand cannot handle a string 2000/4000 char, whatever the data type can handle You should in that case use the Delphi Edit/Post method (= the manual methode). Symptom:Error ORA-01704: String literal too long. See Oracle Documentation.
In short: never use AOCommand for datatypes capable of more then 2000/4000 chars if you want your app also to run with Oracle as backend.
------------------------------------------------------------------------------
Example 1 - manual method
------------------------------------------------------------------------------
......
TransProc1('B', true);
try
Dataset1.edit;
Dataset1.fieldbyname('Name').asstring := 'John';
Dataset1.post;
Dataset2.edit;
Dataset2.fieldbyname('Code').asstring := 'JFS';
Dataset2.post;
except
TransProc1('R', true);
end;
TransProc1('C', false);
.......
------------------------------------------------------------------------------
Example 2 - automatic methode
------------------------------------------------------------------------------
This piece of code adds two times a record to table CTRMAIN and
table CTRDATA.. (The example as such is not meaningful)
.......
keymain1 := genkey();
keymain2 := genkey();
keydata1 := genkey();
keydata2 := genkey();
if AutoExec1(
'INSERT INTO ctrmain (ctrmain_key) VALUES (' + +quotedstr(keymain1), 1)
and aq_ctrmain.Locate('ctrmain_key', keymain1, [])
and AutoExec1(
'INSERT INTO ctrdata (ctrdata_key , ctrmain_key ) VALUES (' +
quotedstr(keydata1) + ',' + quotedstr(aq_ctrmain.fieldbyname
('ctrmain_key).asstring) +')',2);
and AutoExec1(
'INSERT INTO ctrmain (ctrmain_key) VALUES (' + +quotedstr(keymain2), 2)
and aq_ctrmain.Locate('ctrmain_key', keymain2, [])
and AutoExec1(
'INSERT INTO ctrdata (ctrdata_key , ctrmain_key ) VALUES (' +
quotedstr(keydata2) + ',' + quotedstr(aq_ctrmain.fieldbyname
('ctrmain_key).asstring) +')',3)
then mainunit.good('Job Done...') else mainunit.bad('Job Failed);
........
Pay attention to the of the last parameter of AutoExec1(p1,p2)
Whatever happens, the result will always be 'Job Done' with all changes Commited or 'Job failed' with all changes Rolled Back.
You can combine the manual and automatic method by using the TransProc1('B',true or false) and the TransProc1('C',true or false) and in between your code with on or more AutoExec1('.....',2) calls.
==============================================================================
REMARKS
==============================================================================
Remember: transactionprocessing happens on connection-level.
The above functions TransProc1 and AutoExec1 worl with ADOConnect1
and ADOCommand1.
Very often 'in the middle of a transaction ' you have to write something in a table, what has nothing to do with the transaction and should not be rolled back when the transaction fails.
In that case I use my second set of ADOConnect2, ADOCommand2, Transproc2, autoexec2 for all those activities not 'related' to the activities on
ADOConnect1.
==============================================================================
RECORDLOCKING IN MS-SQL-SERVER
==============================================================================
I know somebody who tells me that even with ADO old fashioned recordlocking - like in foxpro - is possible in MS SQL Server. When I ask him how, I get a complete uncomprhensible story. When I ask him to write it down and send me a mail, he says 'yep' but the mail never arrives. For short: it might be possible but I don't know how. Anybodey else?