Page 1 of 1
Problem with stored procedure and Zeos [SOLVED]
Posted: 11.08.2010, 16:32
by 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.
Posted: 11.08.2010, 19:27
by 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.
Posted: 11.08.2010, 19:50
by Andask
Nothing dude..
When I click in "Add all fields" in the ZQuery window. I receive an error 501 too! :O
Posted: 11.08.2010, 22:24
by 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
Posted: 12.08.2010, 12:03
by 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
Posted: 12.08.2010, 12:39
by 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
Posted: 12.08.2010, 13:22
by 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?
Posted: 12.08.2010, 13:24
by 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
Posted: 12.08.2010, 15:28
by 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;
Posted: 12.08.2010, 16:14
by Andask
Ohhh I found the problem, was my query.
Thank you for the help dude!!
Posted: 12.08.2010, 22:45
by andrevanzuydam
Only a pleasure