Subject RE: [IB-Architect] Select procedures
Author Jim Starkey
At 04:36 PM 6/7/00 -0700, David Schnepper wrote:
>(Not knowing the ins & outs of ODBC...)
>Is "{ execute procedure foo }" legal ODBC?
>Is "{ select * from foo(params) }" legal ODBC?
>If one of those forms is legal, and the other not, then "{ call foo }"
>should go to the "illegal" form.
>If both form are legal, then from an application programmer viewpoint *I*
>would expect more
>the "execute procedure foo" type of behavior.

The ODBC (and JDBC, actually) rule is that a SQL statement should
work if a) It conforms to the official grammar, and b) the data
source understands it. Got it, wink wink nudge nudge?

The {} thing is an ODBC specific escape sequence to specify platform
independent things that the decode ring set have officially blessed.
The procedure call escape "{call foo ('yada','yada',?)} is an instance
of an official ODBC blessed procedure call escape sequence.

If you don't know who you are talking to but want to call a given
procedure foo (boy, is this an unlikely scenario), {call foo} is
the way to go. If you know InterBase is at the other end of the
pipe, "execute procedure foo" maybe OK, maybe not, depending on
the ODBC driver.

The OdbcJdbc driver I'm doing, unless somebody provides midcourse
correction, "{call foo}" (because the standard requires it) and
"execute..." will be recognized as stored procedure invocations.
The former will be passed the the Jdbc provider; the IscCalledStatement
class (InterBase instantiation of Jdbc CalledStatement) will turn
"{call foo}" into "execute procedure foo" and leave "execute..."

Incidentally, ODBC and JDBC diverge slightly on their respective
handling of stored procedures due to differing object models.
In JDBC, results are normally fetched after an obligatory
next() from ResultSet. This can't work for scalar stored procedures
because there is no result set. So JDBC subclassed PreparedStatement
to make CalledStatement to support retrieval of scalar values. ODBC,
having an extraordinary primitive object model, can make do by
relaxing the rules on SQLFetch on the statement object.

Getting back to the question at hand, it is my philosophy, subject
to rational argument and petty bribery, to pass all "SQL" string
straight through unless an official ODBC escape sequence is

Jim Starkey