ITransaction Zombie Error using Transactions SQL 2000

[Richard Bianchi]

Hello,

I have a program uploading BDE Paradox tables to ADO SQL Server 2000 tables.
I am using Delphi 5.0 with the latest Jet Engine 4.0 updates installed.

If I add explicit transactions the look fails on the second call to Post
with this
error: ITransaction::Commit or ITransaction::Abort was called, and object is
in
a Zombie State.

I cannot understand why transactions would fail.

Has anyone heard of this problem.

Thanks in advance,

Rich Bianchi
rbia...@alexcorp.com

Here is my ADO setup code:

PADOConnection:= TADOConnection.Create(self);
PADOConnection.ConnectionTimeout:= 60;
PADOConnection.LoginPrompt:= false;
PADOConnection.ConnectOptions := coAsyncConnect;
PADOConnection.Attributes := [];

Here is a sample of the code:

// Copy records
while not TabSrc.EOF do
begin
if not ADOConnection.inTransaction then
ADOConnection.BeginTrans;
tabDest.Append;
for fidx:=0 to tabSrc.fieldCount-1 do
begin
case tabDest.fields[fidx].datatype of
ftSmallInt, ftInteger:
tabDest.fields[fidx].asInteger:=
TabSrc.fields[fidx].asInteger;
ftString, ftMemo:
tabDest.fields[fidx].asString:=
TabSrc.fields[fidx].asString;
ftBlob:
begin
tabdest.fields[fidx].asVariant:=
TabSrc.fields[fidx].value;
end
else
tabDest.fields[fidx].value:=
TabSrc.fields[fidx].value;
end; // case
end;
try
if tabDest.isEdit then
tabDest.post;
if ADOConnection.inTransaction then
ADOConnection.CommitTrans;
tabDest.Last;
except
on E: Exception do
begin
raise;
end;
end;
// Next Record
TabSrc.next;
end;

[Steve Mitchell]

Hi Rich,

We were having the same problem and it's due to the default setting of the
connection defaults for transactions: Specifically the "Preserve on Commit"
and "Preserve on Abort". These are set to False which basically means that
the recordset enters a zombie state after a Commit or RollBack. Zombie
state meaning you can't really do anything until you requery the record set.
If you set these properties to True the recordset state is preserved after
commits and rollbacks. We implemented this by modifying the source of the
AdoDb.pas file as shown below (the modification is between the "// PSI
Modification (Zombie state fix)" comment lines.

Hope this helps - works for us.

Steve Mitchell
Profit Systems Inc.

procedure TCustomADODataSet.OpenCursor(InfoQuery: Boolean);
const
AsyncOptions = [eoAsyncExecute, eoAsyncFetch, eoAsyncFetchNonBlocking];
var
ActiveConnection,
Source: OleVariant;

procedure InitializeConnection;
var
UseCommand: Boolean;
begin
{ Async operations require a connection component so we can hook
events }
if not Assigned(Connection) and (ExecuteOptions * AsyncOptions <> [])
then
DatabaseError(SConnectionRequired);
FConnectionChanged := False;
ActiveConnection := EmptyParam;
UseCommand := not (CommandType in RSOnlyCommandTypes);
if UseCommand then
begin
SetConnectionFlag(cfOpen, True);
Command.Initialize;
// PSI Modification (Zombie state fix)
Command.Properties['Preserve On Commit'].Value := True;
Command.Properties['Preserve On Abort'].Value := True;
// PSI Modification (Zombie state fix)
InitializeMasterFields(Self);
Source := Command.CommandObject;
end else
begin
{ Can't use command for cmdFile and cmdTableDirect }
if Assigned(Connection) then
begin
Connection.Open;
ActiveConnection := Connection.ConnectionObject;
end else if ConnectionString <> '' then
ActiveConnection := ConnectionString;
Command.CheckCommandText;
Source := CommandText;
end;
end;

procedure InitializeRecordset;
begin
FRecordsetObject := CreateADOObject(CLASS_Recordset) as _Recordset;
Recordset.CursorLocation := CursorLocationValues[FCursorLocation];
Recordset.CacheSize := FCacheSize;
Recordset.MaxRecords := FMaxRecords;
if FIndexName <> '' then
begin
Recordset.Index := FIndexName;
RefreshIndexFields;
end;
EnableEvents;
end;

var
VarRecsAffected: OleVariant;
begin
if not Assigned(Recordset) then
begin
InitializeConnection;
InitializeRecordset;
Recordset.Open(Source, ActiveConnection,
CursorTypeValues[FCursorType], LockTypeValues[FLockType],
CommandTypeValues[CommandType] + ExecuteOptionsToOrd(ExecuteOptions));
while Recordset.State = adStateClosed do
try
FRecordsetObject := Recordset.NextRecordset(VarRecsAffected);
if Recordset = nil then Abort;
except
DatabaseError(SNoResultSet, Self);
end;
end else
EnableEvents;
if (eoAsyncExecute in ExecuteOptions) and ((Recordset.State and
adStateExecuting) <> 0) then
SetState(dsOpening);
inherited OpenCursor(False);
end;

"Richard Bianchi" <rbia...@alexcorp.com> wrote in message
news:3d010781$1_1@dnews...

[Brian Bushay TeamB]

>If I add explicit transactions the look fails on the second call to Post
>with this
>error: ITransaction::Commit or ITransaction::Abort was called, and object is
>in
>a Zombie State.
>
>I cannot understand why transactions would fail.

You need to Requery the Tadodataset after a commit.
--
Brian Bushay (TeamB)
Bbu...@NMPLS.com

[Paul Endersby]

Or......

We fixed this one by adding the following line to the event OnWillConnect
handler of my connection object:

{See Micro$oft KB Article "Q187942" for explanation}
if RecordSet <> nil then
Recordset.Properties['Preserve on commit'].Value := True;

HTH,

Paul.

"Richard Bianchi" <rbia...@alexcorp.com> wrote in message
news:3d010781$1_1@dnews...