Actually I think that JDBC specifies "CALL" for the CallableStatement
syntax. So, even on Sybase ASE (which I use) where natively it uses
"EXECUTE", a CallableStatement still uses "CALL". The CallableStatement
documentation states this:
public interface CallableStatement
extends PreparedStatement
The interface used to execute SQL stored procedures. JDBC provides a stored
procedure SQL escape syntax that allows stored procedures to be called in a
standard way for all RDBMSs. This escape syntax has one form that includes a
result parameter and one that does not. If used, the result parameter must
be registered as an OUT parameter. The other parameters can be used for
input, output or both. Parameters are referred to sequentially, by number,
with the first parameter being 1.
{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}
IN parameter values are set using the set methods inherited from
PreparedStatement. The type of all OUT parameters must be registered prior
to executing the stored procedure; their values are retrieved after
execution via the get methods provided here.
A CallableStatement can return one ResultSet or multiple ResultSet objects.
Multiple ResultSet objects are handled using operations inherited from
Statement.
For maximum portability, a call's ResultSet objects and update counts should
be processed prior to getting the values of output parameters.
The syntax that I use just alters this slightly. Whereas the
CallableStatement and PreparedStatement just use a "?" to indicate a
parameter, I follow the "?" with braces containing the type and variable
name. When this processes the type and variable name are used to set the
parameters and then stripped from the string. The
PreparedStatement/CallableStatement is then executed normally.
I think that we may want to have a vote on this. Personally I am concerned
about breaking existing code, but also value clean and consistent syntax. I
also feel that the meta data really belongs under the result set since it is
a property thereof (after all the class for it is ResultSetMetaData). I
still want to think some on an earlier proposal to use separate SQLDocuments
for each result set. My first inclination is that I do not like it, at least
in an na�ve implementation that would result in multiple <sql> nodes.
I will try to think more on this and submit a proposal (or two) that the
Xalan community can debate. I have other things that I must work on, but I
will try to have something soon (hopefully by sometime next week).
Art
-----Original Message-----
From: Phil Friedman [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 16, 2002 3:09 PM
To: [EMAIL PROTECTED]
Subject: Re: SQL Extension enhancement/replacement
Art,
We make extensive use of the SQL extensions, at last count 200+ calls in
dozens of style sheets. I would value consistency among the methods more
than strictly not breaking existing code. Changing the location of the meta
for example, would not be a big deal. A consistent one-time search and
replace is better than keeping track of inconsistencies into the future. I
once wrote a pre-ODBC library that allowed multiple results sets and passed
any output parameters as a special one row result set, which worked fine at
the time. Currently, most of our stored procedures return either interesting
parameters xor a result set, but we would definitely use multiple result
sets if it worked.
I believe Sybase Adaptive Server Enterprise uses EXECUTE not CALL, while
Sybase SQL anywhere uses either, we code EXECUTE, which I believe works on
Microsoft SQL Server as well. So, determining a "callable stamen" may not be
portable.
Regards, Philip Friedman -- Terralink Software Systems -- 207-772-6500 x101
On Tue, 15 Oct 2002 15:59:29 -0700, John Gentilin <[EMAIL PROTECTED]>
may have written:
|Art,
|
|Sounds great. My main concern is that you change to the return data
|will break most implementations in use today, the same goes with query
|and pquery although
|adding a cquery would work just fine. One advantage of query is that it
|uses a
|Statement instead of a Prepared Statement. In MSSQL and other DB's there
|is
|a hugh performance hit with a Prepared Statement.
|
|
|Since an SQLDocument really represents an SQL ResultSet why don't you
|allow the XConnection to return multiple SQLDocuments that way
|query/pquery/cquery
|will return the first Result Set then may add a function,
|getNextResultSet to grab the
|others. That way you support multiple Result Sets without breaking
|previous
|implementations, also I think it will be less complicated with streaming
|mode.
|How do you transition from one result set to another in streaming mode ?
|
|
|
|I am confused about the use of I/O parameters since most of the SQL
|Extension parameters can be passed either XSL variables or elements and
|allow any return
|type unless you are modifying parameters that are passed in which
|probably
|violates the XSLT spec.
|
|
|I personally would not vote in favor of changing the SQL Document
|return format (in a way that breaks previous code) since it would
|affect 100's of my existing SS's.
|
|
|Why don't you post the some example XSL code to the list so we can
|review it and decide on how to integrate the new code. Have you tested
|both Streaming and
|Non Streaming mode with the new code ??
|
|
|If you send me a zip file of the code, I will review the
|implementation.
|
|
|
|Regards
|John G
|
|
|
|[EMAIL PROTECTED] wrote:
|
|
|
|
|I have been working on an enhanced SQL extension for Xalan-J. This adds
|support for callable statements, multiple result sets, and output
|parameters. To do this it makes some changes from the syntax of the
|existing SQL extension. Most significantly to support multiple result
|sets, the metadata element is made a child of the row-set element.
|There are also changes to the call syntax. Parameters (input and
|output) are passed via variables.
|
|
|I would like to contribute this enhanced SQL to the Xalan project. Is
|there interest in this contribution? Since this changes the sql
|extension syntax, should I package this as an alternative function,
|cquery() for example, or just replace query() and pquery() with a new
|query()? Note that the new function and be used to do everything that
|query() and pquery() currently do (with different syntax) as well as
|the new functionality. In short, if the query has parameter definitions
|then they are used, if it starts with "CALL" (perhaps after a return
|parameter definition) then it is assumed to be a CallableStatement.
|
|
|The current status of this is that it has had only limited testing -
|but seems to be working. There are some details that need to be cleaned
|up, some debugging stuff to be deleted, etc. This could be submitted
|fairly soon (unless further testing uncovers something).
|
|
|Thank You,
|Art
|
|
|--
|--------------------------------------
|John Gentilin
|Eye Catching Solutions Inc.
|18314 Carlwyn Drive
|Castro Valley CA 94546
|
|
| Contact Info
|[EMAIL PROTECTED]
|Ca Office 1-510-881-4821
|NJ Office 1-732-422-4917
|
|
