Question

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 can I get their type? Or how do you solve this?

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

SELECT rdb$procedure_name, 
       rdb$procedure_parameters.rdb$parameter_name, 
       rdb$fields.rdb$field_name, 
       rdb$fields.rdb$field_type, 
       CASE rdb$fields.RDB$FIELD_TYPE 
           WHEN 7 THEN  SMALLINT  
           WHEN 8 THEN  integer  
           WHEN 9 THEN  QUAD  
           WHEN 10 THEN  FLOAT  
           WHEN 11 THEN  D_FLOAT  
           WHEN 12 THEN  DATE  
           WHEN 13 THEN  TIME  
           WHEN 14 THEN  CHAR  
           WHEN 16 THEN  INT64  
           WHEN 27 THEN  DOUBLE  
           WHEN 35 THEN  TIMESTAMP  
           WHEN 37 THEN  VARCHAR  
           WHEN 40 THEN  CSTRING  
           WHEN 261 THEN  BLOB  
       END AS FIELD_TYPE 
FROM rdb$procedure_parameters, 
     rdb$fields 
WHERE rdb$fields.rdb$field_name = rdb$procedure_parameters.rdb$field_source

友情链接