Stored Procedure parameters Default values

[lam]

Since Firebird 2 it is possible to create stored procedures that have parameters with default values defined. I understand that this feature is supported by IBDAC's stored procedures from the features site: https://www.devart.com/ibdac/features.html but I can't find the way to do this.

What I need to do is run a stored procedure that has a Default Value defined in the database and that this value is used.

Thanks

[ViktorV]

To use the default stored procedure parameter value, you should delete this parameter from the procedure calling query. For example, instead of

Code: Select all

 EXECUTE PROCEDURE DEFAULT_SP(:PARAMETER, :DEF_PARAMETER)

use

Code: Select all

EXECUTE PROCEDURE DEFAULT_SP(:PARAMETER)

[lam]

Hi Viktor, thanks for the response.

Could you explain in detail with the IBDAC StoredProc component how is it supposed to be done?

The docs https://www.devart.com/ibdac/docs/?deva ... edproc.htm says that the correct way of configuring a StoredProc is to only define the StoredProcName property and let the parameters to be added automatically when we call the Prepare method. If I do this, the DEF_PARAMETER will be added automatically (This will pass a null value) and only if I delete the parameter explicitly from the SP object by calling:

Code: Select all

SP_OBJ.ParamByName('DEF_PARAMETER').destroy;

It works, but I need a cleaner way to do this.

I've also tried defining the SQL.Text of the StoredProc manually:

Code: Select all

SP_OBJ.SQL.Text := 'EXECUTE PROCEDURE DEFAULT_SP(:PARAMETER)'

But it doesn't work.

Could you explain what is the correct way of doing this using a TIBCStoredProc?

Thanks!

[ViktorV]

Yes, you are right. When using the TIBCStoredProc.StoredProcName property and the TIBCStoredProc.Prepare method, all the stored procedure parameters are created according to the data queried from Firebird system tables, and are set to NULL. Therefore, to use a default value of a stored procedure parameter, you should delete this parameter.
When using your approach, parameters will be created basing on the query text specified in the TIBCStoredProc.SQL.Text property. Note that in this case, in order to create out parameters, you should call the TIBCStoredProc.Prepare method. Please clarify the issues you encounter on using this approach.
You can use both approaches to work with default values of stored procedure parameters.

[lam]

The issue with the Deleting the queried parameters from the StoredProc approach is we lose one of the main advantages of using default values. Say we are calling a stored procedure from many places in our code, then we need to use that same StoredProc in a different situation and we need 1 more parameter for it. Default values gives us the ability to add parameters without modifying code that previously executed it. Destroying a parameter would involve modifying that code.

About the second approach I've tried to execute this code:

Code: Select all

  IBCStoredProc1.StoredProcName := 'DEFAULT_SP';
  IBCStoredProc1.SQL.Text := 'EXECUTE PROCEDURE DEFAULT_SP(:PARAMETER)';
  IBCStoredProc1.Prepare;
  Memo1.Lines.Add('SP ParamCount = '+IntToStr(IBCStoredProc1.ParamCount));
  // ^^^ See how many parameters where created. Results in 1, Out Parameters are not created

  IBCStoredProc1.ParamByName('PARAMETER').AsInteger := 19850;
  IBCStoredProc1.ExecProc;


  Memo1.Lines.Add('SP OP = '+IntToStr(IBCStoredProc1.ParamByName('OUT_PARAM').AsInteger));
  // ^^^ Throws exception: Parameter 'OUT_PARAM' not found

The StoredProc is not executed successfully, as the comments on the code note, the Out Parameters are not created and trying to read one of them throws an exception. Is there something obvious that I am missing?

[ViktorV]

Please make sure the TIBCStoredProc.Options.DescribeParams property is set to True. If it is set to False, please set it to True before calling TIBCStoredProc.Prepare.

[lam]

Thank you very much that worked fine.

About your documentation, it would be very handy if you specify that turning on the DescribeParams Option allows you to use a StoredProc defining the SQL.Text manually, I think this is a very important point that is not mentioned anywhere in your docs.

Thanks again for all the help!

[ViktorV]

It is good to see that the issue has been solved. We will add this behavior in the next build of IBDAC. Feel free to contact us if you have any further questions about IBDAC.