SQL Extension Enhancements

[ar...@EASTPOINT.COM]

I am sneaking a bit of time to look at the SQL extension enhancements that I
had proposed a while back. I probably will not be able to work on this much,
but what I have so far seems to be working really well. I am trying to make
some of the changes that John had counter-proposed. Basically by default
everything would work as it had previously. The enhancements could be
enabled as features on the connection.

Essentially what I think (and is coded at this point) is similar to what I
had proposed earlier. The only thing that is new since my last communication
with the group is that now I am using a very simple additional element to
hold the output parameters. This element mimics in some ways the row
element, but since most of the column attributes would not apply they have
been removed. Here is an example of what this looks like using a stored
procedure that returns two result sets and output parameters (actually I
have trimmed out extra rows).

The XSLT is:

<xsl:variable name="query" select="'?[integer=return]=CALL testproc
?[integer=groupid], ?[varchar=servername OUT]'"/>
<xsl:variable name="groupid" select="25"/>
<xsl:variable name="queryresult" select="sql:query($products, $query)"/>
<xsl:copy-of select="$queryresult"/>

The <xsl:copy-of> produces:


<sql>
<row-set>
<metadata>
<column-header searchable="true" writable="true" signed="true"
nullable="false" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="int" column-type="4" column-display-size="11"
catalogue-name="Not Supported" column-label="groupid"
column-name="groupid"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="varchar" column-type="12"
column-display-size="50" catalogue-name="Not Supported"
column-label="description" column-name="description"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="false" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="bitflag" column-type="-7"
column-display-size="1" catalogue-name="Not Supported"
column-label="update_flag" column-name="update_flag"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="int" column-type="4" column-display-size="11"
catalogue-name="Not Supported" column-label="last_ordinal"
column-name="last_ordinal"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="tinyint" column-type="-6"
column-display-size="3" catalogue-name="Not Supported"
column-label="primedesc" column-name="primedesc"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="smallint" column-type="5"
column-display-size="6" catalogue-name="Not Supported"
column-label="audit_action" column-name="audit_action"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="false" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="datetime" column-type="93"
column-display-size="25" catalogue-name="Not Supported"
column-label="audit_when" column-name="audit_when"></column-header>
</metadata>
<row>
<col searchable="true" writable="true" signed="true" nullable="false"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0" column-typename="int"
column-type="4" column-display-size="11" catalogue-name="Not Supported"
column-label="groupid" column-name="groupid">25</col>
<col searchable="true" writable="true" signed="false" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="varchar" column-type="12" column-display-size="50"
catalogue-name="Not Supported" column-label="description"
column-name="description">Processing Dates</col>
<col searchable="true" writable="true" signed="false" nullable="false"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="bitflag" column-type="-7" column-display-size="1"
catalogue-name="Not Supported" column-label="update_flag"
column-name="update_flag">true</col>
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0" column-typename="int"
column-type="4" column-display-size="11" catalogue-name="Not Supported"
column-label="last_ordinal" column-name="last_ordinal">8</col>
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="tinyint" column-type="-6" column-display-size="3"
catalogue-name="Not Supported" column-label="primedesc"
column-name="primedesc">4</col>
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="smallint" column-type="5" column-display-size="6"
catalogue-name="Not Supported" column-label="audit_action"
column-name="audit_action">
<col searchable="true" writable="true" signed="false" nullable="false"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="datetime" column-type="93" column-display-size="25"
catalogue-name="Not Supported" column-label="audit_when"
column-name="audit_when">2002-08-22 11:58:56.04</col>
</row>
</row-set>
<row-set>
<metadata>
<column-header searchable="true" writable="true" signed="true"
nullable="false" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="int" column-type="4" column-display-size="11"
catalogue-name="Not Supported" column-label="groupid"
column-name="groupid"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="false" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="int" column-type="4" column-display-size="11"
catalogue-name="Not Supported" column-label="ordinal"
column-name="ordinal"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="int" column-type="4" column-display-size="11"
catalogue-name="Not Supported" column-label="integer"
column-name="integer"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="4"
precision="0" column-typename="money" column-type="3"
column-display-size="21" catalogue-name="Not Supported" column-label="money"
column-name="money"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="rate" column-type="8"
column-display-size="85" catalogue-name="Not Supported" column-label="rate"
column-name="rate"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="smalldatetime" column-type="93"
column-display-size="25" catalogue-name="Not Supported" column-label="date"
column-name="date"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="varchar" column-type="12"
column-display-size="15" catalogue-name="Not Supported"
column-label="short_description"
column-name="short_description"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="varchar" column-type="12"
column-display-size="255" catalogue-name="Not Supported"
column-label="long_description"
column-name="long_description"></column-header>
<column-header searchable="true" writable="true" signed="true"
nullable="true" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="smallint" column-type="5"
column-display-size="6" catalogue-name="Not Supported"
column-label="audit_action" column-name="audit_action"></column-header>
<column-header searchable="true" writable="true" signed="false"
nullable="false" definitley-writable="false" case-sensitive="true"
table-name="Not Supported" schema-name="Not Supported" scale="0"
precision="0" column-typename="datetime" column-type="93"
column-display-size="25" catalogue-name="Not Supported"
column-label="audit_when" column-name="audit_when"></column-header>
</metadata>
<row>
<col searchable="true" writable="true" signed="true" nullable="false"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0" column-typename="int"
column-type="4" column-display-size="11" catalogue-name="Not Supported"
column-label="groupid" column-name="groupid">25</col>
<col searchable="true" writable="true" signed="true" nullable="false"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0" column-typename="int"
column-type="4" column-display-size="11" catalogue-name="Not Supported"
column-label="ordinal" column-name="ordinal">1</col>
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0" column-typename="int"
column-type="4" column-display-size="11" catalogue-name="Not Supported"
column-label="integer" column-name="integer">
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="4" precision="0" column-typename="money"
column-type="3" column-display-size="21" catalogue-name="Not Supported"
column-label="money" column-name="money">
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0" column-typename="rate"
column-type="8" column-display-size="85" catalogue-name="Not Supported"
column-label="rate" column-name="rate">
<col searchable="true" writable="true" signed="false" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="smalldatetime" column-type="93" column-display-size="25"
catalogue-name="Not Supported" column-label="date"
column-name="date">2002-12-12 00:00:00.0</col>
<col searchable="true" writable="true" signed="false" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="varchar" column-type="12" column-display-size="15"
catalogue-name="Not Supported" column-label="short_description"
column-name="short_description">Curr Proc Date</col>
<col searchable="true" writable="true" signed="false" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="varchar" column-type="12" column-display-size="255"
catalogue-name="Not Supported" column-label="long_description"
column-name="long_description">
<col searchable="true" writable="true" signed="true" nullable="true"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="smallint" column-type="5" column-display-size="6"
catalogue-name="Not Supported" column-label="audit_action"
column-name="audit_action">125</col>
<col searchable="true" writable="true" signed="false" nullable="false"
definitley-writable="false" case-sensitive="true" table-name="Not Supported"
schema-name="Not Supported" scale="0" precision="0"
column-typename="datetime" column-type="93" column-display-size="25"
catalogue-name="Not Supported" column-label="audit_when"
column-name="audit_when">2000-03-22 11:20:37.0</col>
</row>
</row-set>
<out-parameters>
<col column-type="4" column-label="return" column-name="return">8</col>
<col column-type="12" column-label="servername"
column-name="servername">pulse</col>
</out-parameters>
</sql>

Since all those attributes can make it a bit hard to see what is going on,
here is a short form to give you a flavor:

<sql>
	<row-set>
		<metadata><!-- Same Content--></metadata>
		<row><!-- Same Content--></row>
	</row-set>
	<out-parameters>
		<col column-type="4" column-label="return"
column-name="return">8</col>
		<col column-type="12" column-label="servername"
column-name="servername">pulse</col>
	</out-parameters>
</sql>

What do you think?

As I said earlier, these are all optional features that are off by default.
To set these the new setFeature() function would be used. Currently the
function looks like this:

  /**
   * Set feature options for this XConnection.
   * @param feature The name of the feature being set, currently supports
(streaming, inline-variables, multiple-results, cache-statements,
default-pool-enabled).
   * @param setting The new setting for the specified feature, currently
"true" is true and anything else is false.
   * @return
   */
  public void setFeature(String feature, String setting)
  {
	boolean value = false;

	if ( "true".equalsIgnoreCase(setting) )
		value = true;

	if ( "streaming".equalsIgnoreCase(feature) )
    	m_IsStreamingEnabled = value;
	else if ( "inline-variables".equalsIgnoreCase(feature) )
	{
		if ( ! value && m_ParameterList == null )
	    	m_ParameterList = new Vector();
		else if ( value )
			m_ParameterList = null;
	}
	else if ( "multiple-results".equalsIgnoreCase(feature) )
    	m_IsMultipleResultsEnabled = value;
	else if ( "cache-statements".equalsIgnoreCase(feature) )
    	m_IsCachedStatementsEnabled = value;
	else if ( "default-pool-enabled".equalsIgnoreCase(feature) )
	{
	    m_DefaultPoolingEnabled = value;

	    if (m_ConnectionPool == null) return;
	    if (m_IsDefaultPool) return;

	    m_ConnectionPool.setPoolEnabled(value);
	}
  }

I think that I incorporated all of John's excellent suggestions.

Please comment with any improvements, changes, complaints, that you may have
(just do not mention it to my employer). I would like to make a submission
before anyone here notices. (Note that my employer is OK with submitting to
Xalan - it is just that at the moment I am supposed to be working on a
slightly different area).

Art skonkwerks?