Read stored procedure parameters' type in Firebird

Question

Hi,

I need to read the stored procedures' parameters type in Firebird database.

I'm able to read their name, if they are input or output parameters, but where I can get their type? Or how do you solve this?

Thank you.

Answer 1

You need to use the combination of the RDB$PROCEDURE_PARAMETERS and RDB$FIELDS views, as shown below:

SELECT rdb$procedure_name, rdb$procedure_parameters.rdb$parameter_name, 
    rdb$fields.rdb$field_name, rdb$fields.rdb$field_type 
FROM rdb$procedure_parameters, rdb$fields 
WHERE rdb$fields.rdb$field_name = rdb$procedure_parameters.rdb$field_source

The field type values in RDB$FIELDS are defined as follows:

BLOB - 261
CHAR - 14
CSTRING - 40
D_FLOAT - 11
DOUBLE - 27
FLOAT - 10
INT64 - 16
INTEGER - 8
QUAD - 9
SMALLINT - 7
DATE - 12
TIME - 13
TIMESTAMP - 35
VARCHAR - 37

See the Interbase Language Reference for more info on these type definitions.

Answer 2

Unfortunately, that doesn't answer the question. How does one determine which of the parameters are IN parameters and which are OUT parameters? The rdb$procedure_parameters table does not include a column for this.