Subject RE: [firebird-support] Connecting with ODBC - Need a trigger on SELECT???
Author Helen Borrie
At 04:34 PM 7/04/2005 -0400, you wrote:

>I had hoped that I could write my own sql to supply
>results to the program but it doesn't allow that.
>It tries to do all the work its self. First it shows
>a list of the files and views for you to pick from
>then a list of the fields in the file you chose.
>So there is no way currently to have it pick a
>stored procedure. I was hoping that someone would
>have a trick?

What you've described so far isn't clear. Is it your program that supplies
the list of tables and views, or somebody else's?

The ODBC interface can query the system tables and find the definitions of
tables and views. I guess you are using some utility program that does
this. (There are no "files" in Firebird!!) With stored procedures,
there's nothing in the metadata that could tell you (or the interface)
which SPs were selectable and which were executable.

So, although it would be possible (no "trick" !!) to query the system
tables (several of them) to arrive at a list of SPs with their input and
output arguments, there is no flag anywhere that could filter out the
executable SPs and leave you with a list of selectable ones. (There has
been some discussion around introducing some kind of flagging to enable
this, though, so you are not alone in your desire!)

At best, during development, you could maintain your own tables identifying
selectable SPs by name, along with their input and output arguments and
data types, in the correct order. Then have your program query this table
at the appropriate time and return a list to the application. At run-time,
the client library itself, in concert with the ODBC driver, will, given a
valid SELECT statement for the selectable stored procedure, return the
appropriate structures into which to write values for the input arguments
and output structures to accept the returned set.

For details of how to set this up and handle it, move your question to the
firebird-odbc-devel list (if using Firebird's ODBC driver) or the
appropriate user forum (if using a different ODBC driver). Each data
access interface has its own way of implementing it...