Subject Re: [IB-Architect] SQL Scripts
Author Jim Starkey
At 11:38 AM 12/7/01 +0000, Ian Newby wrote:
>Hi Paul,
> There are two options; either the client library parses the script or
>the DSQL level accepts multiple statements at the same time. The second
>option is preferable to me in that tools which bypass the client library
>(such as FirebirdJDBC) can access the same functionality.

It would be no big deal (probably just a couple of lines) to have
DSQL loop through statements rather than stopping after the first
one. But this is the easiest part of the architectural problem.
Let me suggest a few more.

The first is error reporting and unwinding. If an error occurs,
how does the host program know which statement failed? Does it
have to parse the error diagnostic, determine whether it was syntax
or operational, and if syntax, find the caret and back process the
original string?

What is the meaning of prepare? Does it prepare only the first?
Or does it go ahead and prepare all statements in order to give
timely diagnostics? Are there significant resource issues with
a bulk prepare?

What effect will the change make on the way programers write
code? A performance features desperately needed by Firebird
for web applications is caching of compiled statements. This
is feasible only the parameters are passed as formal parameters
(i.e. with a "?") and not instring literals. But since a script
can have at most a single SQLDA, multiple inserts, for example,
necessarily must be written with instring literals. Breaking
programmers of this bad habit will not be easy.

Multi-statement execution can certainly be implemented. Many
other systems have them, and both ODBC and JDBC have support
for them. I am not at all convinced they are a good idea, but
mostly a compensation for architectural deficiencies in Sybase
institutionalized by Microsoft in ODBC. There is next to
no hope for bona fide interoperability between Firebird and
Sybase derivatives, so standards compliance isn't really an

If you are looking for a checkmark in an evaluation grid (which
is sometimes important), by all means implement mulit-statement
execution. If you're looking for a high performance solution,
however, look at all of the parts, problems, and potential
solutions before making a decision.

da Wolf