I have a Firebird 2.5 database .As an example I have stored procedure with a name
QRESULT which expected return is:
Parameter - DATATYPE
a - date
b - numeric(18,0)
c - integer
d - varchar(50)
and so on....
I use PHP - PDO to query the firebird database using the procedure
QRESULT like this:
SELECT a,b,d from QRESULT() where a = "some value"
I need to run some query before
QRESULT procedure and i need it to return the datatype of all the columns that
QRESULT would return if it was ran. So i can help user to type proper value for my "where" clause.I know i can set that manually in the user interface, but in the real project there are lots of procedures and if there is a way i can make my filter interface generate dynamically i would be happy about that.If this is not possible for a stored procedure i can make it with select statements.I just need some lead.
The information you want is in the
RDB$PROCEDURE_PARAMETERS table, basically what you need is query
SELECT r.RDB$PARAMETER_NAME ParName, F.RDB$FIELD_TYPE ParType FROM RDB$PROCEDURE_PARAMETERS r JOIN RDB$FIELDS F ON(F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE) WHERE r.RDB$PROCEDURE_NAME = 'QRESULT' AND r.RDB$PARAMETER_TYPE = 1 ORDER BY r.RDB$PARAMETER_TYPE, r.RDB$PARAMETER_NUMBER
Note that the SP name should be in upper case as this is how it is stored into system tables (unless you use quoted identifiers). If you want to get both input and output parameters the delete the
r.RDB$PARAMETER_TYPE = 1 predicate from the
WHERE (type 0 is input parameters and 1 is output).
The type returned by this query is integer id for the type, quick googling found this:
14,"TEXT " 7,"SHORT " 8,"LONG " 9,"QUAD " 10,"FLOAT " 27,"DOUBLE " 35,"TIMESTAMP " 37,"VARYING " 261,"BLOB " 40,"CSTRING " 45,"BLOB_ID " 12,"DATE " 13,"TIME " 16,"INT64 "
but if you want to have more precise type then see this SO post.