Firebird 1.5: Null Parameters in Stored Procedures

[jennecy]

Hi all,

I am migrating a system from DELPHI + ADO + SQL Server to
DELPHI + ZEOS + Firebird, and there are Stored Procedures with parameters that may need to be NULL,

e.g. "execute procedure test 1, NULL, 'testing', 2, 9"

The problem is that the aplicatiion is setting parameters values correctly, but, calling the execproc method of tzstoredproc component raises the exception

"SQL Error: Dynamic SQL error parameter mismatch for procedure TESTE"

ps. It works when Delphi IBX stored procedure component is used

[Michael]

Hi jennecy,

could you please attach an example code (with the definition of the StoredProc that causes the exception)? This would be very helpful. Thanks!

[jennecy]

Hi jennecy,

could you please attach an example code (with the definition of the StoredProc that causes the exception)? This would be very helpful. Thanks!

First of all, thanks Michael for the attention and sorry for the long time taken to reply... :-)

this is the part of code you asked for:

procedure TForm1.Button1Click(Sender: TObject);
begin
zconnection1.Connect;
zconnection1.StartTransaction;
ZStoredProc.ParamByName('Nome').Value := edit1.text;
ZStoredProc.ParamByName('Chave').Value := NULL; /// THIS IS WHERE THE PROBLEM IS
ZStoredProc.ParamByName('Autor').Value := VF[CheckBox1.Checked];
ZStoredProc.ParamByName('Compositor').Value := VF[CheckBox2.Checked];
ZStoredProc.ParamByName('Arranjador').Value := VF[CheckBox3.Checked];
ZStoredProc.ParamByName('orquestrador').Value := VF[CheckBox4.Checked];
ZStoredProc.ParamByName('operacao').Value := 2;
ZStoredProc.ExecProc;
edit2.Text := ZSP.ParamByName('Codigo').AsString;
zconnection1.commit;
zconnection1.Disconnect;
end;

There is no error if the line
ZStoredProc.ParamByName('Chave').Value := NULL;

is substituted by
ZStoredProc.ParamByName('Chave').Value := StrToInt(edit2.Text);
for example, and the procedure works.

The Fact is that a do really need to pass some NULL params sometimes.... :-(
could you please help me (or someone else, of course)?

Thanks,

Jennecy

[jennecy]

Hi again this is the Stored Procedure definition:

Create or alter Procedure stoproc_Artista (Operacao int, Chave int, Nome varChar(50), Autor char(3), Compositor char(3), Arranjador char(3), Orquestrador char(3)) returns (result int, Codigo int) As
BEGIN
IF (:Operacao = 0) then
BEGIN
DELETE FROM IAS_Artista WHERE CD_Artista_ID = :Chave;
if (row_count = 0) then
Exception ErrChaveNaoEncontrada;
Codigo = Chave;
END
Else
IF (:Operacao = 2) then
BEGIN
UPDATE IAS_CD_Incremental SET CD_Ultimo=CD_Ultimo+1 WHERE TA_Nome='IAS_Artista';
if (row_count = 0) then
Exception ErrChaveNaoEncontrada;
SELECT CD_Ultimo From IAS_CD_Incremental WHERE TA_Nome='IAS_Artista' into :Codigo;
if (:codigo is null) then
Exception ErrChaveNaoEncontrada;
INSERT INTO IAS_Artista (CD_Artista_ID, NM_Artista, CH_Autor, CH_Compositor, CH_Arranjador, CH_Orquestrador)
VALUES (:Codigo, :Nome, :Autor, :Compositor, :Arranjador, :Orquestrador);
if (row_count = 0) then
Exception ErrInserirRegistro;
END
result = 0;
end^

Thanks

Jennecy