Request for Comment, SQL Connection Management
Inside the SQL Extension database connections are
opened when a query or pquery is performed and held
until a corresponding close statement is called.
Now once you close a connection, assuming that streaming
mode is off, the data is still available to use in a transformation.
All SQL documents are incremental, meaning that data is
not transferred from the ResultSet to the DTM until the Row
is traversed. So if you close a connection prior to reading the
complete data set, you can traverse the data already read but
the unread data is lost.
What I am proposing is add a new function called closeWithCopy().
This function will release the connection set after the complete
ResultSet is read but still retains the data.
Where I see this as a benefit is a stylesheet that is reading several
tables that specify choices in drop down boxes. i.e. a list of states
and their codes. Each one of these connections consumes a DB
connection which may be troublesome, most database servers
typically allow < 100 simulations connections as a default install.
The following code;
<xsl:variable name="slist" select='sql:query($db, "select * from
State")'/>
<xsl:value-of select='sql:closeWithCopy($slist)'/>
<xsl:variable name="availcolors" select='sql:query($db, "select * from
paint_colors")'/>
<xsl:value-of select='sql:closeWithCopy($availcolors)'/>
Would only hold open the connections for a brief amount of time
but still allow $slist and $availcolors to be used inside the
transformation.
Note: New XConnection behavior allows the XConnection to produce
many different SQLDocuments. Previous behavior 1 connection, 1 document.
Although each document produces from an XConnection will consume a
separate
JDBC Connection.
Feedback please
JohnG
