Xalan SQL Extension Enhancements (was: funny game)

[ar...@EASTPOINT.COM]

My responses are included below:

-----Original Message-----
From: John Gentilin [mailto:gentijo@eyecatching.com
<ma...@eyecatching.com> ] 
Sent: Monday, January 27, 2003 2:14 PM
To: Art Welch
Subject: Re: A funny game


Art,

Again sorry for the delay, had an unscheduled trip to LA.
Life has been a bit crazy the past month or so....but enough with excuses...
Tag, your it

Here are my notes,

Leave query as it stands, i.e. do not move signature to
oquery (the idea is backwards compatibility).
I would either dual purpose pquery or make a new
method for cquery. i.e. callable statement query. Also
note that query can support both streamable and
non streamable modes, not sure if that is your
comment or an error from before.

Art> Yes, I believe that I just renames the original query() method to
oquery() for EastPoint. When integrated into Xalan I would rename oquery()
back to query(). If it would be acceptable then the enhanced query method
would then be pquery(). This could actually process all types of queries
that return results (one or more sets depending on settings and the query).
Because I dislike duplicating code, I have implemented this as follows:

  public DTM query( ExpressionContext exprContext, String queryString )
  {
		/* The slightly modified contents of the original query()
method. */
  }

  public DTM pquery( ExpressionContext exprContext, String queryString )
  {
		return(pquery(exprContext, queryString, null));
  }

  public DTM pquery( ExpressionContext exprContext, String queryString,
String typeInfo )
  {
		/* The enhanced pquery() method. Determines if
CallableStatement and how to 
                read parameters, etc. */
  }

I don't think we should mark PQuery, addParameter...
as depreciated. These functions should always be
available, just an alternative to inline parameters.

Art> OK.

enable/disable streaming, can be depreciated.

Art> OK

I am OK with the new DTD/Data structure, in the back
of my head it seems as though we should add in another
layer to account for extensibility but its not so clear at
the moment. When testing your code, there are some
points to look out for.

There is only one copy of the meta data and the cols
also point to that metadata. The current code calculates
the pointed based on column #. You code will need to
keep an array of metadata sections which vary in size
based on ResultSet. The mods you had before were
killing the DTM structure as you moved between result
sets.


Art> Actually the result sets must be processed in sequence. At least they
do as far as handling what comes from the database. The getMoreResults()
method is used to advance through the result sets, the API documentation
contains the following:

	Moves to a Statement object's next result. It returns true if this
result is a ResultSet object. This method also implicitly closes any current
ResultSet object obtained with the method getResultSet.

Streaming mode must of course follow this sequence. Cached mode the results
are obtained from the database in seequence, but then may be traversed in
any order.

My implementation creates a new metadata element for each result set (under
the row-set element).

While a result set is being processed the m_ColHeadersIdx is appropriate to
that result set (extractSQLMetaData() creates a new one each time it is
called).

The DTM indices for each column point to the appropriate metadata
attributes.

I think that the current modifications to not kill the DTM structure. At
least it has been working for a while. I must admit that it has been a while
since I did this, and I do not recall a lot of the details.


Handling empty result sets is sometimes tricky. For the
new DTD, <ResultSet><MetaData> will return data while <ResultSet><Row>
needs to return DTM.NULL This will be interesting in a multiple result set
situation, where the middle result set is empty.

Art> I would not think that handling an empty result set would be too
difficult, there are just no rows. A stylesheet may need to account for
this, but a simple XPath node existence test would seem to cover it. As to
if it works, the following SQL produced the attached output:

	SELECT	"One Row, One Col result set."

	SELECT	acctcatg, acctnumb
	FROM acctmast
	WHERE 1 = 2

	SELECT	"Three row, Three col result set", ordinal,
short_description
	FROM	bkparama
	WHERE	groupid = 25 and ordinal < 4

	SELECT	"Two row, Two col result set", ordinal
	FROM	bkparama
	WHERE	groupid = 25 and ordinal < 3

 <<sqlout.xml>> 

Are you adding an ID attribute to the ResultSet Node for identification ??

Art> I had not planned to add an ID attribute. The XPath position seems to
be adequate. For example <output><xsl:value-of
select="$queryresult/sql/row-set[4]/row[1]/col[1]"/></output> gives me
<output>Two row, Two col result set</output>. I do admit that I cheated a
bit to get that. I put the connection in cached mode. Reading ahead in
result sets does not work in streaming mode because of Xalan's propensity to
read more than it should.



How do you plan to recall a cached statement ??

Art> A static Hashtable of connections with cached statements.


A big task here will include building a set of tests
that fully test streaming and non streaming modes.
There is also a bug in the XPath core that kind of messes
with Streaming mode and this pruning project stands in the
way of coming up with a fix.

Art> I have tested many combinations, not sure if I got all of them.
Certainly did not test every possible scenario. But my tests have been done
by manually editing a stylesheet and executing it - no suite of tests.
Yes... Pesky WalkingIteratorSorted!!! 


We also may want to move to a DB system that we can include
in the distribution or at least is still available for download. It seems
that Lutris is either being purchased or went out of business. I spoke to
the owner a few times and he promised that they were coming back strong but
it never seemed to materialized. Here is another DB version, it may not be
complete but we really don't need it to be.
http://www.mckoi.com/database/index.htm
<http://www.mckoi.com/database/index.htm> 
Let me know, if you know of any other Java DB's

Art> I do not know about Java DBs. I have been using Sybase. Personally I do
not think that we need (or should) include a database with Xalan.

Art> Anyhow, I have attached the code to this e-mail. I just did a quick
pass through trying to get it close to Xalan form. Also stripped out some
debugging prints, etc. I am sending this now, because I want to go home now
and I will not be in the office until later tomorrow. I want people to see
this as soon as possible so that I can get comments soon (if possible). I
will be making these changes to the version that EastPoint is using, so if
changes are desired I could get them made before EastPoint has gone too far
changing our XSL to this version.

Art

 <<xalansql.zip>> 

Regards
John G

art_w wrote:

>  This is a special funny game
> This game is my first work.
> You're the first player.
> I expect you would enjoy it.

--
--------------------------------------
John Gentilin
Eye Catching Solutions Inc.
18314 Carlwyn Drive
Castro Valley CA 94546

    Contact Info
gentijo@eyecatching.com
Ca Office 1-510-881-4821
NJ Office 1-732-422-4917