Problem with NULL params in TZStoredProc - URGENT!

[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"


Here is the Delphi 7 code and the stored procedure definition:


// Delphi Code
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;


// Firebird 1.5 Stored Proc 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^


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)?

ps. The same code works when Delphi IBX stored procedure component or MDO Stored Procedure component are used instead of ZEOS

Thaks in advance...

[mdaems]

Hi jennesy,

I'm not sure, But are you sure you don't have to do it like this:

Code: Select all

    IF <is it null??> THEN
        DBFIELD.AsVariant := Null
    ELSE
        DBFIELD.AsInteger := Value;
[code]

I've used this method in one of my programs because otherwise I had the same kind of trouble. Sorry, but I didn't have a way to check it before replying.

Good luck,
Mark

[jennecy]

Hi all,

thanks Mark for your reply.

yes i'm sure it should work as it is... it works with Delphi's ADO components, MDO Firebird components, Delphi's IBX and so on.

I could choose to work with MDO, but it works only with Firebird, and I'd like to be working with more than one DB.

Any other idea ? I looks much more like a ZEOS components bug, case it didn't work with the TZQuery with the "execute procedure" statement neither...


Thanks in advance for the help!

Jennecy

[mdaems]

Jennecy,

If you think it's a bug : post it to the buglists at sourceforge.
Did you try my suggestion, anyway? Just in case it works...

Greets,

Mark

[zippo]

Quick and dirty:

Use -1 as a parameter for using NULL (you will have to slightly modify the stored procedure): If the parameter i >=0 then it's a customer, else write NULL in the field.

Hope it helps.

[jennecy]

Hi all,

I did test Mark's suggestion but it didn´t work also.
Passing -1 as a param is not a solution for me cause it won´t work in othe procedures.
So, problem the persist

Thanks...

Jennecy