Problem with stored procedure and Zeos [SOLVED]

[Andask]

Hi people.

I'm having with problems with SP and Zeos.

I'm receiving the SQL Error 501.

Attempt to reclose a closed cursor. The cursor identified in a FETCH or CLOSE statement is not open.

The stored procedure code is here:

Code: Select all

CREATE OR ALTER PROCEDURE SP_INSMOV ( 
    tip_id integer, 
    pro_id integer, 
    fun_id integer, 
    data date, 
    qtd integer, 
    volumes integer, 
    ins_embal char(1), 
    ins_fisc char(1), 
    ins_inje char(1), 
    ins_ident char(1), 
    ins_oleo char(1), 
    nf varchar(30), 
    lote integer) 
as 
BEGIN 
INSERT INTO MOVIMENTACAO ( 
TIP_ID, 
PRO_ID, 
FUN_ID, 
DATA, QTD, 
VOLUMES, 
INS_EMBAL, 
INS_FISC, 
INS_INJE, 
INS_IDENT, 
INS_OLEO, 
NF, 
LOTE) 
VALUES ( 
:TIP_ID, 
:PRO_ID, 
:FUN_ID, 
:DATA, 
:QTD, 
:VOLUMES, 
:INS_EMBAL, 
:INS_FISC, 
:INS_INJE, 
:INS_IDENT, 
:INS_OLEO, 
:NF, 
:LOTE 
); 
END

And the way I'm calling is here:

Code: Select all

procedure TfrmMovimentacao.btCadastrarClick(Sender: TObject); 
begin 
  with DM.spMovimentacao do 
  begin 
    StoredProcName := 'SP_INSMOV'; 
    ParamByName('TIP_ID').AsInteger := cbTipoMov.ItemIndex; 
    ParamByName('PRO_ID').AsInteger := cbProduto.ItemIndex; 
    ParamByName('FUN_ID').AsInteger := 1; 
    ParamByName('DATA').AsDate := dtpEntrega.Date; 
    ParamByName('QTD').AsInteger := StrToInt(leQuantidade.Text); 
    ParamByName('VOLUMES').AsInteger := StrToInt(leVolumes.Text); 
    ParamByName('INS_EMBAL').AsString := IntToStr(rgEmbalagem.ItemIndex); 
    ParamByName('INS_FISC').AsString := IntToStr(rgFiscal.ItemIndex); 
    ParamByName('INS_INJE').AsString := IntToStr(rgInjecao.ItemIndex); 
    ParamByName('INS_IDENT').AsString := IntToStr(rgIdentificacao.ItemIndex); 
    ParamByName('INS_OLEO').AsString := IntToStr(rgOleo.ItemIndex); 
    ParamByName('NF').AsString := leNotaFiscal.Text; 
    ParamByName('LOTE').AsInteger := 1{leLote}; 
    ExecProc; 
  end; 
end;

I'm tried with ExecSQL instead of ExecProc. Tried to use "Prepare;" too.

Always with the same error (501).

I'm using Delphi 2009, Zeos 7, Firebird 2.1

Thx!

P.S.: Sorry for my poor english, I tried my best.

[andrevanzuydam]

I am having the same problems, for now I recommend using a TZQuery in a select statement, you need to have a suspend in your proc to make this work:

Code: Select all

create procedure SP_INSMOV (...) returning (Error varchar(100))
as
begin
   error = 'None';

   ...   
  
   suspend;
   when any do
   begin
      Error = 'Cannot Insert';
      suspend;
   end
end^

Use almost same code

Query.Close;
Query.ParamByName (...).AsInteger := 10;
etc ....
Query.Open;

Then you can see errors you make in the stored proc:

ShowMessage (frmDatamodule.qryQuery.FieldByName ('Error').AsString);

Let me know if you get stuck.

[Andask]

Nothing dude..

When I click in "Add all fields" in the ZQuery window. I receive an error 501 too! :O

[andrevanzuydam]

Have a look at the example below:
In Test database

Code: Select all

set term^;
create procedure test_proc (id integer) returns (newid integer)
as
begin
  newid = id;
  suspend;
end^

In Delphi App on a Form
DBGrid -> Dataset -> ZQuery -> ZConnection
Button1

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZQuery1.Close;
  ZQuery1.ParamByName('Number').AsInteger := 7;
  ZQuery1.Open;
end;

You should see the number 7 in the grid

Make sure you have suspend in stored proc and you use open not exec in the query

[Andask]

Oh Dude, I think I'm doing something >really< wrong.
Same error.

I save the project and the database in a folder and zipped it, if you wanna take a look.
http://rapidshare.com/files/412491880/ZeosForum.7z

[andrevanzuydam]

Hi There

You almost have it! Parameters in Delphi are prefixed like in stored proc with a : - so your id parameter in the TZQuery must be :id and not 'id', you can check if its correct by clicking on the params options in the TZQuery.

Hope you come right otherwise post again

[Andask]

Hey, it works! xD

But, I can't figure exactly where I fault in my SP...

I'll try to use the SP in the ZQuery and see what happens. But, in my stored procedure I don't have a returning value, I need a suspend in SP too?

[andrevanzuydam]

Yes - use suspend in stored procedures that must return values, I do this all the time now so I can get back error messages, its a good way to do things and it makes coding a bit easier also, I now use only one set of components - namely TZQuery + Dataset

All stored procs can be called using select * from stored_proc - use flamerobin to test it

Glad you came right

[Andask]

Hey, it works!

But, have a bit problem that I can't figure the "why".

I don't remember if I need to execute another command....

In FlameRobin I see the data that I insert by the query, but in Delphi, I don't see.

Here is what I'm doing:

Code: Select all

procedure TfrmMovimentacao.btCadastrarClick(Sender: TObject);
begin
  with DM.qInsMov do
  begin
    Close;
    ParamByName('TIP_ID').AsInteger := cbTipoMov.ItemIndex;
    ParamByName('PRO_ID').AsInteger := cbProduto.ItemIndex;
    ParamByName('FUN_ID').AsInteger := 1;
    ParamByName('DATA').AsDate := dtpEntrega.Date;
    ParamByName('QTD').AsInteger := StrToInt(leQuantidade.Text);
    ParamByName('VOLUMES').AsInteger := StrToInt(leVolumes.Text);
    ParamByName('INS_EMBAL').AsString := IntToStr(rgEmbalagem.ItemIndex);
    ParamByName('INS_FISC').AsString := IntToStr(rgFiscal.ItemIndex);
    ParamByName('INS_INJE').AsString := IntToStr(rgInjecao.ItemIndex);
    ParamByName('INS_IDENT').AsString := IntToStr(rgIdentificacao.ItemIndex);
    ParamByName('INS_OLEO').AsString := IntToStr(rgOleo.ItemIndex);
    ParamByName('NF').AsString := leNotaFiscal.Text;
    ParamByName('LOTE').AsInteger := 1{leLote};
    Open;
  end;
  DM.qMov.Refresh;
end;

[Andask]

Ohhh I found the problem, was my query.

Thank you for the help dude!!

[andrevanzuydam]

Only a pleasure