RE: SQL Extension enhancement/replacement

[art_w]

Title: Message

Breaking existing code was also my concern. I did not know how heavily used the SQL extension is and if people using it would be amenable to a syntax change. Interesting that you mention that other DBs have a "huge performance hit" with Prepared Statement. In my environment, Sybase, PreparedStatement actually has better performance than Statement. But as stated, this could be another argument in favor of adding a separate "cquery".

 

I will have to think about using SQLDocument for a result set... and look at the code. The transition between result sets is actually quite simple. Here is a code excerpt that shows how this is done:

 

  private boolean addRowToDTMFromResultSet( )
  {
    try
    {
      // If we have not started the RowSet yet, then add it to the
      // tree.
System.out.println("cSQLDocument.addRowToDTMFromResultSet() m_FirstRowIdx=" + m_FirstRowIdx);
      if (m_FirstRowIdx == DTM.NULL)
      {
        m_RowSetIdx = addElement(1, m_RowSet_TypeID,  m_SQLIdx, m_RowSetIdx);
       extractSQLMetaData(m_ResultSet.getMetaData());
      }

 

      // Check to see if all the data has been read from the Query.
      // If we are at the end the signal that event
      if ( ! m_ResultSet.next())
      {
        // In Streaming mode, the current ROW will always point back
        // to itself until all the data was read. Once the Query is
        // empty then point the next row to DTM.NULL so that the stream
        // ends. Only do this if we have statted the loop to begin with.

 

        if (m_StreamingMode && (m_LastRowIdx != DTM.NULL))
        {
          // We are at the end, so let's untie the mark
          m_nextsib.setElementAt(DTM.NULL, m_LastRowIdx);
        }

 

  m_ResultSet.close();
  while ( !m_Statement.getMoreResults() && m_Statement.getUpdateCount() >= 0 ) ;
  m_ResultSet = m_Statement.getResultSet();

 

  if ( m_ResultSet != null )
  {
   m_FirstRowIdx = DTM.NULL;
   addRowToDTMFromResultSet();
  }
  else
  {
   // Get output parameters.

.

.

.

 

 

I am not sure if I understand your question about the I/O parameters. However you are correct that using variables to return output parameters is probably a minor infringement of XSLT variable handling. Is this acceptable for an extension function? If not, it could certainly be handled differently. My original thinking was to return them as another result set of sorts. I tried the variable method, to investigate all options and because it was similar to the way that the application I had borrowed some of the code from for this handled this (this application is one that I developed for EastPoint). It worked, and I liked the way that it functioned, so I stayed with it. FWIW, I very much prefer using variables to pass input parameters vs. the method that pquery uses. As much as I also like the way that variables also work for output parameters, if the authorities that be say NO, I do not have a problem with another technique - as long as it is not too obtuse.

 

Sounds like maintaining backwards compatibility is a given... especially if there are other people that also have 100s of SS's using the SQL extension (or 100s of people with a few SQL SSs) that would be affected.

 

Here is a very simple sample stylesheet using the new SQL extension:

 

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns:sql="com.eastpoint.chrysalis.SQLDocumentHandler"
                extension-element-prefixes="sql">
  <xsl:output method="html" indent="yes"/>
  <xsl:param name="query" select="'SELECT * FROM bkparama WHERE groupid = 25'"/>
 
  <xsl:template match="/">
 <!-- 1. Make the connection -->
 <xsl:variable name="products"
           select="sql:new('com.sybase.jdbc2.jdbc.SybDriver',
                         'jdbc:sybase:Tds:dev1:1234/teller', 'teller', 'password')"/>

 

 <!--2. Execute the query -->
 <xsl:variable name="groupid" select="25"/>
 <xsl:variable name="l_result" select="0"/>
 <xsl:variable name="l_server" select="''"/>
 <xsl:variable name="table" select="sql:query($products, '?[integer=l_result] = CALL testproc ?[integer=groupid], ?[varchar=l_server OUT]')"/>

 

 <xsl:copy-of select="$table"/>
 l_result=<xsl:value-of select="$l_result"/>
 l_server=<xsl:value-of select="$l_server"/>

 

 <!-- 3. Close the connection -->
 <xsl:value-of select="sql:close($products)"/>
  </xsl:template>

 

</xsl:stylesheet>

 

The testproc stored procedure returns a couple of different result sets. For this example I am using <xsl:copy-of> (so that I can see everything), it also works just fine using XSLT to work through the results as in the sample that came with Xalan.

 

So far I have only tested streamed results, hopefully I will test cached results soon. The non-streaming mode could be especially important with output parameters, since it is suggested that all result sets be fetched before getting the output parameters, and some situations may want to see the output parameters first (the return value is an output parameter).

 

Unfortunately the code is still mixed with other application code, so I can not send you something functional at the moment. If you just want to read it, maybe I can send you some.

 

The SQL code that I am borrowing from also has more options than the current SQL library. I will have to investigate which if these may be useful in an Xalan extension as well as which ones are implementable. One option that I am thinking may be useful to add would be to return the results as simple named columns instead of generic col elements with all the attributes like "<column_name>column value</column_name>" or something like that. I have not thought this through or looked into implementation, it just seems like it could be useful. As I work more with an application using SQL in Xalan other things may come to light. Obviously you have quite a bit of experience with the current SQL extension library. Has it proven to be excellent in it's current form, or are there shortcomings that you would like to see addressed? Since I am proposing changes anyway...

 

Thank You,

Art

-----Original Message-----
From: John Gentilin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 15, 2002 6:59 PM
To: [EMAIL PROTECTED]
Cc: Scott Boag; Art Welch
Subject: Re: SQL Extension enhancement/replacement

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