StoredProc and default parameter values on Firebird

[upscene]

Hi,

I've read the older topic on this issue ( viewtopic.php?f=24&t=33341&hilit=default+parameter ), but it seems something has changed.

I have this routine:

Code: Select all

CREATE OR ALTER PROCEDURE DEFAULT_TEST (
  I1 Integer, 
  I2 Integer, 
  I3 Integer DEFAULT 4)
 returns (
  O1 Integer, 
  O2 Integer, 
  O3 Integer)
AS
begin
  o1 = i1 + i2 + i3;
  o2 = i1 + i2;    
  o3 = i3;
end

In version 6.0.1 (on Delphi XE7), I put a TIBCStoredProc on a form, enter a procedure name and 6 parameters get created. I want the 3rd input parameter to use the default value. According to the older post, I should remove the parameter.

Code:

Code: Select all

var n: Integer;
begin
  IBCStoredProc1.Params.Delete(2);

  IBCStoredProc1.ExecProc;
  for n := 0 to Pred(IBCStoredProc1.ParamCount)
  do if IBCStoredProc1.Params[n].ParamType in [ptOutput]
     then Memo1.Lines.Add(Format('%s - %s', [IBCStoredProc1.Params[n].DisplayName,
                                             IfThen(IBCStoredProc1.Params[n].IsNull, 'NULL', IBCStoredProc1.Params[n].AsString)]));

Executing returns this error:

Dynamic SQL ErrorSQL error code = -804SQLDA error.

The exact same code works fine in version 5.7.32 (Delphi 2009).

Heck, this - in my opinion far more elegant solution - works to:

Code: Select all

IBCStoredProc1.Params[2].Bound := False;

It executes just fine and returns '4' for 'o3'.

What has changed, what should be used?

With regards,

Martijn Tonies

[ViktorV]

You should remove the I3 parameter only from the procedure calling query, which was mentioned in the post you specified. That is, to solve your problem, you can use the following code:

Code: Select all

var n: Integer;
begin
  IBCStoredProc1.Options.DescribeParams := True;
  IBCStoredProc1.SQL.Text := 'EXECUTE PROCEDURE DEFAULT_TEST(:I1, :I2)';
  IBCStoredProc1.Prepare;
  IBCStoredProc1.ExecProc;
  for n := 0 to Pred(IBCStoredProc1.ParamCount)
  do if IBCStoredProc1.Params[n].ParamType in [ptOutput]
     then Memo1.Lines.Add(Format('%s - %s', [IBCStoredProc1.Params[n].DisplayName,
                                             IfThen(IBCStoredProc1.Params[n].IsNull, 'NULL', IBCStoredProc1.Params[n].AsString)]));

end;

[upscene]

What use is using a TIBCStoredProc with the StoredProcName property if you have to modify the SQL yourself?

And you don't have to specify out parameters?

[ViktorV]

When using the TIBCStoredProc.StoredProcName property and the TIBCStoredProc.Prepare method, the SP execution query will automatically be constructed, which will contain all incoming parameters, and all the stored procedure parameters will be also automatically created according to the data queried from the Firebird system tables, and the values ​​of these parameters will be set to NULL. Unfortunately, in this case, since the parameter will be set to NULL, it will not use the default value. Therefore, to work with default values, you have to set the TIBCStoredProc.SQL.Text property manually.
If you set TIBCStoredProc.Options.DescribeParams to True, then when you call the TIBCStoredProc.Prepare method, the OUT parameters will be created automatically. If the TIBCStoredProc.Options.DescribeParams parameter is set to False, you will have to create OUT parameters yourself before calling the TIBCStoredProc.ExecProc method.