you have to prepare a stored procedure (and query and Cursor AND DSQL
and ...) BEFORE setting parameters - not afterwards.

Luc.

Honza SUSTR schrieb:

>
> in
> TIB_Transation
> LockWait:= true;
>
> For SP I use this schema:
>
> sp.UnPrepare;
> sp.StoredProcName:= 'DATAEXIST';
> sp.ParamByName('PAR1').AsINteger := dummy;
> sp.Prepare;
> sp.ExecProc;
> result:= sp.ParamByName('RESULT_0').AsINteger;
>
> I use Prepare after change parameters ...
>
> H.
>
> ----- Original Message -----
> From: "Paulo Henrique Albanez" <pha@...>
> To: <IBObjects@egroups.com>
> Sent: Thursday, January 18, 2001 2:18 PM
> Subject: [IBO] Urgent - Problem with StoredProc and prepare
>
> I have the following:
>
> DatBas = TDataModule
>
> BaseData = TIB_Connection
> DefaultTransaction = TransData
> FieldEntryTypes = [fetDomainName]
> DefaultNoCase = True
> LoginDBReadOnly = True
> DatabaseName = 'BaseData'
>
> TransData = TIB_Transation
> IB_Connection = BaseData
> AutoCommit = True
> Isolation = tiCommitted
>
> DatSis: TDataModule
>
> TransDatSis: TIBTransactionPha
> IB_Connection = DatBas.BaseData
> Isolation = tiCommitted
>
> ProcGenChave: TIBStoredProcPha
> DatabaseName = 'BaseData'
> IB_Connection = DatBas.BaseData
> IB_Transaction = TransDatSis
> StoredProcName = 'PR_GENCHAVE'
>
> ProcParSis: TIBStoredProcPha
> DatabaseName = 'BaseData'
> IB_Connection = DatBas.BaseData
> StoredProcName = 'PR_PARSIS'
> end
>
> function TDatSis.GenChave(const iCodEmp, iFilial: Integer; const sChave,
> sSubChave: string;
> const iOldValue: Integer): Integer;
> begin
> if not ProcGenChave.Prepared then ProcGenChave.Prepare;
> ProcGenChave.Params[0].AsInteger := iCodEmp;
> ProcGenChave.Params[1].AsInteger := iFilial;
> ProcGenChave.Params[2].AsString := sChave;
> ProcGenChave.Params[3].AsString := sSubChave;
> ProcGenChave.Params[4].AsInteger := iOldValue;
> ProcGenChave.Execute;
> Result := ProcGenChave.Fields[0].AsInteger;
> ProcGenChave.Active := False;
> // ProcGenChave.Unprepare;
> end;
>
> function TDatSis.ParSisGen(const iCodEmp, iFilial: Integer; const sChave,
> sSubChave: string;
> const sNewValue: string = #0): string;
> begin
> if not ProcParSis.Prepared then ProcParSis.Prepare;
> ProcParSis.Params[0].AsInteger := iCodEmp;
> ProcParSis.Params[1].AsInteger := iFilial;
> ProcParSis.Params[2].AsString := sChave;
> ProcParSis.Params[3].AsString := sSubChave;
> if sNewValue = #0 then ProcParSis.Params[4].Clear
> else ProcParSis.Params[4].AsString := sNewValue;
>
> ProcParSis.ExecSQL;
> Result := ProcParSis.Fields[0].AsString;
> ProcParSis.Active := False;
> // ProcParSis.Unprepare;
> end;
>
> DatCad01 = TDataModule
>
> procedure TDatCad01.DatEmpCreate(Sender: TObject);
> begin
> try
> inherited;
> DatSis := TDatSis.Create(Self);
>
> except;
> ExceptionOnCreate := True;
> raise;
> end;
> end;
>
> procedure TDatCad01.CadPFJBeforePost(IB_Dataset: TIB_Dataset);
> begin
> inherited;
> iCodPFJ := CadPFJCodPFJ.AsInteger;
> if (CadPFJ.State = dssInsert) and (iCodPFJ = 0) then begin
> DatSis.TransDatSis.StartTransaction;
> CadPFJCodPFJ.AsInteger := DatSis.GenChave(Sistema.Empresa, 0, 'CadPFJ',
> '', -1);
> end;
> end;
>
> procedure TDatCad01.CadPFJAfterPost(IB_Dataset: TIB_Dataset);
> begin
> inherited;
> if DatSis.TransDatSis.InTransaction then DatSis.TransDatSis.Commit;
> end;
>
> procedure TDatCad01.CadPFJPostException(IB_Dataset: TIB_Dataset);
> begin
> inherited;
> if DatSis.TransDatSis.InTransaction then DatSis.TransDatSis.Rollback;
> if (CadPFJ.State = dssInsert) and (iCodPFJ = 0) then CadPFJCodPFJ.Clear;
> end;
>
> DatEst01 = TDataModule
> procedure TDatEst01.DataModuleCreate(Sender: TObject);
> begin
> try
> inherited;
> DatSis := TDatSis.Create(Self);
> sGruEstFormat := DatSis.ParSisGen(0, 0, 'GruEst', 'GruEst_Format');
>
> except;
> ExceptionOnCreate := True;
> raise;
> end;
> end;
>
> procedure TDatEst01.GruEstBeforePost(IB_Dataset: TIB_Dataset);
> var
> GruEstGruEst: TIB_Column;
> GruEstGrupoPai: TIB_Column;
> begin
> inherited;
>
> GruEstGruEst := IB_Dataset.FieldByName('GruEst');
> sGrupo := GruEstGruEst.AsString;
>
> if (IB_Dataset.State = dssInsert) then begin
> GruEstGrupoPai := IB_Dataset.FieldByName('GrupoPai');
> if IB_Dataset = GruEst then
> IB_Dataset.FieldByName('CodEmp').AsInteger := Sistema.Empresa;
>
> if (sGrupo = '') then begin
> DatSis.TransDatSis.StartTransaction;
> GruEstGruEst.AsString := GruEstGrupoPai.AsString + IntToStr(
> DatSis.GenChave(Sistema.Empresa, 0, 'GruEst',
> GruEstGrupoPai.AsString, -5));
> end;
> if GruEstGrupoPai.AsString = '' then GruEstGrupoPai.Clear;
> end;
> end;
>
> procedure TDatEst01.GruEstAfterPost(IB_Dataset: TIB_Dataset);
> begin
> inherited;
> if DatSis.TransDatSis.InTransaction then DatSis.TransDatSis.Commit;
> end;
>
> procedure TDatEst01.GruEstPostException(IB_Dataset: TIB_Dataset);
> begin
> inherited;
> if DatSis.TransDatSis.InTransaction then DatSis.TransDatSis.Rollback;
> if (IB_Dataset.State = dssInsert) and (sGrupo = '') then
> IB_Dataset.FieldByName('GruEst').Clear;
> end;
>
> The problem is:
>
> - I open the form FormCad01
> - I insert the record. Ok.
> - I insert other record. Ok.
>
> - but if I open another form (Ex.FormEst01):
> - and come back to the form FormCad01
> - and insert a new record, show one of the erros below:
>
> - Invalid Statement handle
> - Invalid transation handle
> - Access violation in the address 40561CBC the module 'GDS32.DLL'.
> - Invalid request handle
> The most strange is that when I access the base locally, the program works
> perfectly.
>
> If I use the UnPrepare method at procedures, the problem also disappears.
>
> Does anybody have any ideas about the reason of this problem?
>
> Would it be a problem with the InterBase´s Super Server version?
>
> Would it be a problem with IBO?
>
> I´m using:
>
> Interbase 6.01 Super Server
> Servidor Linux Conectiva 5.2
>
> Windows 98 SE
> Delphi 5.0
> IBO 3.6Cd
>
> PHA
>
> [Non-text portions of this message have been removed]