Subject | Re: [IBO] SQL transaction handle |
---|---|
Author | B.D.S. Bell |
Post date | 2001-06-19T07:57Z |
Helen Borrie wrote:
arrived, just got in.
I set up the SQL each time as:-
procedure TDMIBObject.SQLPrep( SQLProc: TIB_StoredProc;
ProcName: string );
begin
with SQLProc do
begin
IB_Connection := Messagecn ;
IB_Transaction := MessageTrn;
StoredProcName := ProcName;
AutoDefineParams := false;
StoredProcForSelect := false; // no suspend in SQL
proc.
StoredProcHasDML := false; // no DML here.
ParamNames.Clear;
ParamNames.Add('pID') ; // Tstrings
end;
end;
In the particular update procedure SQLPrep is called in the
sequence(simplified) shown below.
with TGWInBoxStatus(PDObject), SQLInsert do
begin
with MessageTrn do
if not InTransaction then StartTransaction;
SQLPrep( SQLInsert, 'GWInsertInBoxStatus' );
AutoFetchAll := false; // fetch all rows of
record
{ Set up the input params }
{ Get the field names from the Tag list }
for fi := 0 to FFieldList.Count-1 do
begin
Fd := TGWField( FFieldList[ fi]);
if( Fd.FDataSrc = tsDItab)
then ParamNames.Add('p' +Fd.GetTag + 'ID' );
if( (Fd.FDataSrc = tsDatab) or (Fd.FDataSrc =
tsDItab) )
then ParamNames.Add('p' +Fd.GetTag );
end; // for each tag
// EXCEPTION HERE ON PREPARE, I've tried both of
these, no difference.
if not prepared then prepare;
//Prepared := true; // Prepare before
access to Params or Fields.
{ Update the parameters }
..........
ParamByName( 'p'+GetTag ).ASString := GetValue( tmpstr
) ;
etc etc...
ExecProc;
MessageTrn.Commit;
This has been the same for months with no problems. I'm
suspecting db corruption but no problems reported by
IBConsole Validate.
Bryan
[Non-text portions of this message have been removed]
>Thanks for your prompt reply, I had gone home when it
> At 07:59 PM 18-06-01 +0100, you wrote:
>
> >Hi everyone,
> >
> >Can anybody help please, I have a peculiar problem?
> >
> >I have been using a TIB_StoredProc for some time with no
> >problems.
> >I've not made any explicit changes to the way the
> >transaction works,
> >set as Isolation := tiCommitted.
> >
> >The input parameters named for the SQL look correct but I
> >get the error, on Prepared := true; as...
> >
> >Invalid Transaction Handle, (Expecting explicit transaction
> >start)
> >isc error 335544332.
> >
> >Before the prepare I have,
> >
> > with MessageTrn do
> > if not InTransaction then StartTransaction;
> >
> >and after the ExecProc, ( which it doesn't reach ),
> >
> >MessageTrn.Commit;
>
> What is the stored proc's IB_Transaction property set to?
>
> Helen
>
arrived, just got in.
I set up the SQL each time as:-
procedure TDMIBObject.SQLPrep( SQLProc: TIB_StoredProc;
ProcName: string );
begin
with SQLProc do
begin
IB_Connection := Messagecn ;
IB_Transaction := MessageTrn;
StoredProcName := ProcName;
AutoDefineParams := false;
StoredProcForSelect := false; // no suspend in SQL
proc.
StoredProcHasDML := false; // no DML here.
ParamNames.Clear;
ParamNames.Add('pID') ; // Tstrings
end;
end;
In the particular update procedure SQLPrep is called in the
sequence(simplified) shown below.
with TGWInBoxStatus(PDObject), SQLInsert do
begin
with MessageTrn do
if not InTransaction then StartTransaction;
SQLPrep( SQLInsert, 'GWInsertInBoxStatus' );
AutoFetchAll := false; // fetch all rows of
record
{ Set up the input params }
{ Get the field names from the Tag list }
for fi := 0 to FFieldList.Count-1 do
begin
Fd := TGWField( FFieldList[ fi]);
if( Fd.FDataSrc = tsDItab)
then ParamNames.Add('p' +Fd.GetTag + 'ID' );
if( (Fd.FDataSrc = tsDatab) or (Fd.FDataSrc =
tsDItab) )
then ParamNames.Add('p' +Fd.GetTag );
end; // for each tag
// EXCEPTION HERE ON PREPARE, I've tried both of
these, no difference.
if not prepared then prepare;
//Prepared := true; // Prepare before
access to Params or Fields.
{ Update the parameters }
..........
ParamByName( 'p'+GetTag ).ASString := GetValue( tmpstr
) ;
etc etc...
ExecProc;
MessageTrn.Commit;
This has been the same for months with no problems. I'm
suspecting db corruption but no problems reported by
IBConsole Validate.
Bryan
[Non-text portions of this message have been removed]