Hi John,
I probably should not have said that, I know better. I am kind of up against
the wall on a project that will use SQL with Xalan. I need output parameters
to be working ASAP. I am a bit behind where I should be, so I have been not
to pleasant lately. Anyhow I will try to do whatever I can to facilitate the
enhancements to the SQL library.
My original suggestion on the output format was to change it to something
like:
<sql>
<row-set>
<metadata>
<column-header/>...
</metadata>
<row>
<col>...</col>...
</row>
.
.
.
</row-set>
.
.
.
</sql>
I know very little about how the DTM works. In my ignorance, I assumed that
if I changed the <row-set>s to behave like <row>s it would work. In your
earlier e-mail you implied that this is not the case, something about IDs.
You also mentioned something about the traversal of the <row-set>. I re-read
that e-mail many times, I am still not sure that I "get it". It sounded
reasonable (even if I did not completely understand it). So, I tried doing
just what you had suggested. Leaving SQLDocument alone and adding
getNextResultSet(). In coding this I was concerned about the concurrency
issue that you mention. I am also concerned that it requires the user to
take specific action to retrieve subsequent result sets. In some cases the
number of result sets from a stored procedure may vary. An XSLT could do
multiple getNextResultSet() calls until there are no more, but it would seem
simpler to just do the one query() call and pass the results through an
appropriate transformation, doing something like row-set[#] to get a
specific result set or just letting a template handle the <row-set>s when it
does not matter (for example).
Databases vary, but in general each result set should be processed before
subsequent result sets. Likewise the output parameters should only be
retrieved after all results have been fetched. In the context of Xalan this
probably means that if you want to access something in result set 1 while
your current context is result set 2, you would need to use non-streaming
mode. Essentially the result sets would be like rows.
On parameter passing. It seems to me that extension functions accessing
Xalan variables is a supported technique. It is certainly easy enough to do.
Since this mechanism exists, I am hesitant to add another mechanism
(addParameter()). Also, as I mentioned, in my case often what I want to use
to set a parameter is already in a variable. Other than that it may be
subjective as to which syntax is cleaner. My parsing code (although very
simple) is still smart enough to handle things like stored procedures named
OUT. It also handles embedded strings, etc. What it needs to parse is pretty
simple (?= CALL sp arg OUT,...) so it does not need to be very smart to work
well. I had never thought about column names in "[]". So JDBC does not
abstract this? If not that could be a problem (since parameters '?' can
appear almost anywhere). If we look for [type=name] and pass through
anything that does not match the full pattern would that work? In other
words, can a column name contain '='? Off the top of my head I think that
SQL says no. Anyhow, we can certainly get parameters in with addParameter().
For the output paremeters. It seems that we need to return the parameters
with the DTM from the query(). The obvious choices (to me anyway) are to add
another child under <sql> somewhere. This could either be something that
looks exactly like a <row-set> or perhaps just similar to a <row-set> but
with a different name. Personally I do not know that it matters much,
although since we have this great XML (self describing data format), seems a
shame not to be able to tell if what we have is a result set or output
parameters. Either way I would still prefer it to be another child of <sql>
(as in the example) instead of from another call to getNextResultSet() (or
even getOutputParameters() for that matter, although I am less opposed to a
function like getOutputParameters() than getNextResultSet()).
You mention "addOutputParameterWithType()" as in keeping with the JDBC
design pattern. I would have to disagree here as well. For adding parameters
output parameters CallableStatement just inherits the setXXX method of
PreparedStatement. There is an additional step to register the output
parameters. Being lazy, I try to use existing patterns/syntax/APIs where
ever possible. However I am not sure that the JDBC pattern is that suitable
to Xalan. At least as far as the get/set/register things go. Even in Java I
find myself putting wrappers around the parameter stuff so that I do not
need to deal with it.
Well this e-mail is getting a bit long, so I am going to give it a rest for
a bit. I am not exactly sure how to proceed from here. If there are problems
with doing something like getNextResultSet(), may not want to proceed with
that. I loathe duplicating code (especially code that has complicated
semantics) so subclassing SQLDocument does not appeal to me at the moment.
Also, the more I think about it, the less I like having the output
parameters being indistinguishable from a result set. What about adding a
<output-parameters> element? I keep thinking that I prefer just having one
document (<sql>) with multiple <row-set>s and perhaps <output-parameters>
children. What if we did something like:
<sql>
<result-set>
<metadata/> <!-- content not included -->
<row-set>
<row/> <!-- content not included -->
</row-set>
</result-set>
<result-set/><!-- repeats for multiple result sets, content not
included. -->
<output-parameters/> <!-- content not included -->
</sql>
This way sql/result-set could work about the same as sql did previously, or
maybe sql/result-set[1] would be better.
Well, I said I was going to take a break... so...
I'll be back...
Art
-----Original Message-----
From: John Gentilin [mailto:gentijo@;eyecatching.com]
Sent: Tuesday, October 22, 2002 6:01 PM
To: Art Welch
Cc: [EMAIL PROTECTED]
Subject: Re: SQL Extension enhancement/replacement
Art,
The SQL extension is not my baby, it belongs to the whole community. I just
happen to be the first point of contact in managing it. I am also not
resistant to change, even if breaks existing interfaces, as long as there is
a clear rational that the change will add value and is worth the effort.
I think in general, handling callable statements and multiple result sets
will be a welcome addition to the code. As for the other changes, if you
could make a case for what they may fix that was broken before, or what
functionality they add and how it makes the existing code better, I would be
willing to put it up for a vote. I tried to address some of the issues I saw
with the code in my previous response, I am interested in what you don't
agree in.
To address the other issues of OUT and INOUT parameters. I suggest that we
keep with the design same pattern (the same one that was abstracted from the
JDBC drivers) and add methods such as addOutputParameterWithType()... This
action could create a HashMap (is Xalan allowed to use JAVA2 object yet ??)
holding the name / value. The in cquery, using those parameters as a
mechanism for the registerOutputParameter JDBC calls.
I would stay away from parsing items out of the Query String such as the OUT
keyword because you can't regulate what someone will call their SP. As silly
as it may sound, what if I wanted to call the SP "OUT", it would probably
kill your parse routine. Same with the [name=type] since SQL calls to an
Access DB sometimes require the [ ] be place around the column name. I can't
tell you how many times I have been called in to support a DB that was hack
together with column names that include spaces and quotes. :-(
As for building up ResultSets to house the output parameters, that sounds
like it may be the long way around. Why not sub class SQLDocument and
override the constructor, extractSQLMetaData, addRowToDTMFromResultSet.
In the constructor, add the HashMap that will be used to pull the output
parameters from the last of the result set.
In extractSQLMetaData & addRowToDTMFromResultSet, use the HashMap as a
pseudo ResultSet.
On a side note, adding a function to XConnection to get the next result set
may have a design caveat. The XConnection can be used to produce more than
one query. Its probably the odd case where the XSL designer notices that 2
XConnections would give you separate contexts.
If you perform a query that has multiple results sets then while processing
that query, perform another query your unprocessed result sets will probably
be lost. To fix this, we could move the getNextResultSet() to the
SQLDocument itself since it always maintains the current query context. The
notion would be that SQLDocuments are chained.
You never did answer my previous question. Can you concurrently process
result sets on a multi result set query without loosing the context ??
Thank you for all the effort you are putting into the SQL Extensions.
Regards
John G
--------------------------------------
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
