<reference id="ref-sql-module"><title>SQL Elements</title>

<refentry id="sx:customJdbcConnectionPool" xreflabel="sx:customJdbcConnectionPool">
<refnamediv>
  <refname>sx:customJdbcConnectionPool</refname>
  <refpurpose>Custom JDBC connection pool</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:jdbcConnectionPool"/></refclass>
</refnamediv>
<indexterm><primary>customJdbcConnectionPool</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
<row>
<entry>class</entry>
<entry>Yes</entry>
<entry>Java class</entry>
<entry>The name of a Java class that implements <code>com.servingxml.components.sql.JdbcConnectionPool</code>.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<itemizedlist>
<listitem>
Optionally, any number of <xref linkend="sx:property"/> elements.
</listitem>
</itemizedlist>
</refsection>
<refsection>
<title>Description</title>
<para>This element allows you to configure a custom JDBC connection pool by providing an implementation of the
interface
<programlisting>
<![CDATA[
public interface JdbcConnectionPool {

  Connection getConnection() throws SQLException;

  void releaseConnection(Connection connection);
}
]]>
</programlisting>
</para>
<para>
The implementation should have a constructor that takes one argument, a <code>java.util.Properties</code> instance.
There you will find the custom properties you specified with the <xref linkend="sx:property"/> elements.
</para>
</refsection>
<refsection>
<title>Example</title>
<example>
<title>Example of a custom JDBC connection pool.</title>
<figure id="custom-pool" xreflabel="custom pool">
  <title>Resources script entry</title>
<programlisting>
<![CDATA[
  <sx:customJdbcConnectionPool id="custom-jdbc-pool" class="MyJdbcConnectionPool">
    <sx:property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
    <sx:property name="databaseUrl" value="jdbc:oracle:thin:@127.0.0.1:1521:dev"/>
    <sx:property name="user" value="scott"/>
    <sx:property name="password" value="spring"/>
  </sx:customJdbcConnectionPool>
]]>
</programlisting>
</figure>
<figure id="custom-pool-java" xreflabel="custom pool java">
  <title>Simple JdbcConnectionPool implementation</title>
<programlisting>
<![CDATA[
public class MyJdbcConnectionPool implements JdbcConnectionPool {

  private final String driver;
  private final String databaseUrl;
  private final String user;
  private final String password;

  public MyJdbcConnectionPool(Properties properties) 
   {
    this.driver = properties.getProperty("driver");
    this.databaseUrl = properties.getProperty("databaseUrl");
    this.user = properties.getProperty("user");
    this.password = properties.getProperty("password");

    if (driver == null) {
      throw new ServingXmlException("driver required");
    }

    try {
      Class.forName(driver);
    } catch (java.lang.ClassNotFoundException e) {
      throw new ServingXmlException("Failed to load jdbc driver class.  " + e.getMessage());
    }

    if (databaseUrl == null) {
      throw new ServingXmlException("databaseUrl required");
    }
    if (user == null) {
      throw new ServingXmlException("user required");
    }
    if (password == null) {
      throw new ServingXmlException("password required");
    }
  }

  public Connection getConnection() throws SQLException {
    Connection connection = DriverManager.getConnection(databaseUrl, user, password);
    return connection;
  }

  public void releaseConnection(Connection connection) {
    try {
      connection.close();
    } catch(Exception e) {
    }
  }
}
]]>
</programlisting>
</figure>
</example>
</refsection>
</refentry>

<refentry id="sx:jdbcConnectionPool" xreflabel="sx:jdbcConnectionPool">
<refnamediv>
  <refname>sx:jdbcConnectionPool</refname>
  <refpurpose>Abstract element standing for a JDBC connection pool</refpurpose>
</refnamediv>
<indexterm><primary>jdbcConnectionPool</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="1">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry>ref</entry>
<entry>Yes</entry>
<entry><xref linkend="QName"/></entry>
<entry>This attribute is used to reference the id of a JDBC connection pool.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para>None.</para>
</refsection>
<refsection>
<title>Description</title>
<para>This is an abstract element that allows us to refer generically to any specialization of 
<sgmltag>sx:jdbcConnectionPool</sgmltag>, such as <xref linkend="sx:sqlConnectionPool"/>.</para>
</refsection>
<refsection>
<title>Examples</title>
<para>
</para>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlConnectionPool"/>
</listitem>
<listitem>
<xref linkend="sx:customJdbcConnectionPool"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlBatchWriter" xreflabel="sx:sqlBatchWriter">
<refnamediv>
  <refname>sx:sqlBatchWriter</refname>
  <refpurpose>SQL batch writer</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:recordWriter"/></refclass>
</refnamediv>
<indexterm><primary>sqlBatchWriter</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
<row>
<entry>batchSize</entry>
<entry>No</entry>
<entry><xref linkend="QName"/></entry>
<entry>The batch size.  Defaults to 100.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<itemizedlist>
<listitem>Optionally, any number of <xref linkend="sx:parameter"/> elements.</listitem>
<listitem>A <xref linkend="sx:jdbcConnectionPool"/> element.</listitem>
<listitem>One <xref linkend="sx:sqlUpdateDatabase"/> element.</listitem>
</itemizedlist>
</refsection>
<refsection>
  <title>Description</title>
  <para>
For bulk inserts, JDBC batches may be expected to take only 10-20% of the time needed for 
default (autocommit) behavior.  There is a DB-dependent limit on the 
number of records in the batch, which is likely related to the size of the 
SQL statement that gets issued on the executeBatch.  There is, however, a rapidly 
diminishing return to the batch size. In general,  50-100 rows per batch will likely achieve 
most of the benefit.  
  </para>
  <para>
Note that some DBMS's (or JDBC drivers) may 
not support JDBC batches.
  </para>
</refsection>
<refsection>
<title>Examples</title>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:jdbcConnectionPool"/>
</listitem>
<listitem>
<xref linkend="sx:sqlUpdate"/>
</listitem>
<listitem>
<xref linkend="sx:sqlWriter"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlCommand" xreflabel="sx:sqlCommand">
<refnamediv>
  <refname>sx:sqlCommand</refname>
  <refpurpose>Execute a command on the server after opening a connection</refpurpose>
</refnamediv>
<indexterm><primary>sqlCommand</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para>An SQL command.</para>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a SQL command.</title>
<programlisting>
<![CDATA[
<sx:sqlCommand>set forceplan on</sx:sqlCommand>
]]>
</programlisting>
</example>
</refsection>
</refentry>

<refentry id="sx:sqlConnectionPool" xreflabel="sx:sqlConnectionPool">
<refnamediv>
  <refname>sx:sqlConnectionPool</refname>
  <refpurpose>SQL connection pool</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:jdbcConnectionPool"/></refclass>
</refnamediv>
<indexterm><primary>sqlConnectionPool</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
<row>
<entry>driver</entry>
<entry>Yes</entry>
<entry>Java class</entry>
<entry>The class name of the JDBC driver.</entry>
</row>
<row>
<entry>databaseUrl</entry>
<entry>Yes</entry>
<entry>URL</entry>
<entry>The JDBC database URL.</entry>
</row>
<row>
<entry>user</entry>
<entry>No</entry>
<entry>String</entry>
<entry>The database user name.</entry>
</row>
<row>
<entry>password</entry>
<entry>No</entry>
<entry>String</entry>
<entry>The database password.</entry>
</row>
<row>
<entry>minConnections</entry>
<entry>No</entry>
<entry>Number</entry>
<entry>The minimum number of connections to keep in the pool.  The default is zero.</entry>
</row>
<row>
<entry>maxConnections</entry>
<entry>No</entry>
<entry>Number</entry>
<entry>The maximum number of connections to keep in the pool.  The default is any number.</entry>
</row>
<row>
<entry>testStatement</entry>
<entry>No</entry>
<entry>String</entry>
<entry>A statement to test the liveness of the database before obtaining a connection.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para> Optionally, any number of <xref linkend="sx:sqlCommand"/> elements.
</para>
</refsection>
</refentry>

<refentry id="sx:sqlPrepare" xreflabel="sx:sqlPrepare">
<refnamediv>
  <refname>sx:sqlPrepare</refname>
  <refpurpose>Prepare SQL statement</refpurpose>
</refnamediv>
<indexterm><primary>sqlPrepare</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para>
The content is a SQL insert or update statement.  The statement may have embedded parameter
and field names.  The format for an embedded parameter is {$parameterName}, and for an
embedded field name is {fieldName}.  
</para>
<para>
</para>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a SQL prepare statement.</title>
<programlisting>
<![CDATA[
  <sx:sqlUpdate>                                                                                                                        
    <sx:parameter name="employee-no" value="{EMPNO}" type="xs:long"/>
    <sx:parameter name="mgr" value="{MGR}" type="xs:long"/>
    <sx:parameter name="hiredate" value="{HIREDATE}" type="xs:date"/>              
    <sx:parameter name="salary" value="{SAL}" type="xs:decimal"/>              
    <sx:parameter name="commission" value="{COMM}" type="xs:decimal"/>              
    <sx:sqlPrepare>
      INSERT INTO EMP_HISTORY(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) 
      VALUES({$employee-no},{NAME},{JOB}, {$mgr}, {$hiredate},  {$salary}, {$commission})
    </sx:sqlPrepare>
  </sx:sqlUpdate>
]]>
</programlisting>
<para>
Because the SQL insert executes a prepared statement, values must be
associated with appropriate types.  If the data source is a SQL query source, 
they will already be associated with SQL types, 
and no further action is really necessary.  In the case of other data sources, however,
values by default will be associated with string types, and may need
to be recast in parameter assignments, as shown.
</para>
</example>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlWriter"/>
</listitem>
<listitem>
<xref linkend="sx:quoteSymbol"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlQuery" xreflabel="sx:sqlQuery">
<refnamediv>
  <refname>sx:sqlQuery</refname>
  <refpurpose>SQL query</refpurpose>
</refnamediv>
<indexterm><primary>sqlQuery</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
<row>
<entry>recordType</entry>
<entry>No</entry>
<entry><xref linkend="QName"/></entry>
<entry>The record type of the SQL query.  Defaults to "record"</entry>
</row>
<row>
<entry>trim</entry>
<entry>No</entry>
<entry>true|false</entry>
<entry>Indicates whether to trim both leading and trailing whitespace from all returned values.  Defaults to <code>false</code>.
(For backwards compatability, "yes"/"no" is also supported.)
</entry>
</row>
<row>
<entry>trimLeading</entry>
<entry>No</entry>
<entry>true|false</entry>
<entry>Indicates whether to trim leading whitespace from all returned values.  Defaults to <code>false</code>.
(For backwards compatability, "yes"/"no" is also supported.)
</entry>
</row>
<row>
<entry>trimTrailing</entry>
<entry>No</entry>
<entry>true|false</entry>
<entry>Indicates whether to trim trailing whitespace from all returned values.  Defaults to <code>false</code>.
(For backwards compatability, "yes"/"no" is also supported.)
</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para>Optionally, any number of <xref linkend="sx:parameter"/> elements.</para>
<para>Optionally, but deprecated, a <xref linkend="sx:quoteSymbol"/> element,
  to specify escaping of quote symbols in substitution variables.  
The default, if there is no <xref linkend="sx:escapeSubstitutions"/> element in 
scope, is to escape single quote characters, "'", with two single quote 
characters, "''".  If you need to change the default, use the the configuration 
element 
        <xref linkend="sx:escapeSubstitutions"/> instead of <xref linkend="sx:quoteSymbol"/>. 
</para>.
<para>
The content is a SQL select statement.  The statement may have embedded parameter
and field names.  The format for an embedded parameter is {$parameterName}, and for an
embedded field name is {fieldName}.  Parameter and field values in the current context
will be substituted by name.  Parameter and field references bracketed by quote symbols
will have their values searched for quote symbols, which if found will be escaped.
</para>
<para>
The SQL statement may also contain a sx:choose element.  This allows for the
construction of ad hoc SQL statements based on passed parameters.
</para>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a parameterized query with double quote escaping.</title>
<programlisting>
<![CDATA[
  <sx:sqlQuery recordType = "employee">
    <sx:escapeSubstitutions character='"' escapeCharacter='"'/>
    SELECT EMPNO, ENAME AS NAME,JOB FROM EMP 
        WHERE JOB = "{$job}"
  </sx:sqlQuery>
]]>
</programlisting>
<para>
In this example, we specify the double quote as the quote symbol, to be escaped with two double quotes.
Since the parameter "job" appears within the specified quote symbols, values of "job" will be searched for double quotes,
which if found will be replaced by two double quotes.
</para>
<para>Note that if the <xref linkend="sx:quoteSymbol"/> element is omitted, the default quote symbol is
the single quote, to be escaped with two single quotes.
</para>
</example>
<example>
<title>Example of a parameterized query that resolves into alternate SQL statements.</title>
<programlisting>
<![CDATA[                                 
    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
      SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1 
      <sx:choose>
        <sx:when test="$jobList">
          AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
      ORDER BY JOB,ENAME
    </sx:sqlQuery>
]]>
</programlisting>
<para>In this example, the parameter "job" may have multiple values.  In the <xref linkend="sx:toString"/> instruction,
the multiple values are concatenated into a string of comma-separated, quoted values.  If the
string is non-empty, the query expression resolves into a select statement with an "IN" clause;
otherwise it resolves into a select statement that selects all rows.
</para>
</example>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlReader"/>
</listitem>
<listitem>
<xref linkend="sx:quoteSymbol"/>
</listitem>
<listitem>
<xref linkend="sx:toString"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlReader" xreflabel="sx:sqlReader">
<refnamediv>
  <refname>sx:sqlReader</refname>
  <refpurpose>SQL record reader</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:recordReader"/></refclass>
</refnamediv>
<indexterm><primary>sqlReader</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<itemizedlist>
<listitem>Optionally, any number of <xref linkend="sx:parameter"/> elements.</listitem>
<listitem>A <xref linkend="sx:jdbcConnectionPool"/> element.</listitem>
<listitem>A <xref linkend="sx:sqlQuery"/> element.</listitem>
</itemizedlist>
</refsection>
<refsection>
  <title>Description</title>
<para>Note that data values resulting from a SQL query are mapped to XML Schema types according to the rules given in <xref linkend="jdbc-datatype-mappings"/>.
</para>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a SQL Record Reader.</title>
<programlisting>
<![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="employees">
    <sx:serialize>
      <sx:content ref="employees"/>
    </sx:serialize>
  </sx:service>

  <sx:sqlConnectionPool id="jdbcPool"
                         driver="oracle.jdbc.driver.OracleDriver"
                         databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                         user="scott"
                         password="spring"
                         minConnections="2"
                         testStatement="SELECT * FROM DUAL"/>

  <sx:recordContent id="employees">
    <sx:sqlReader>
      <sx:sqlConnectionPool ref="jdbcPool"/>
      
      <sx:parameter name="jobList">
        <sx:toString value="{$job}" separator=",">
          <sx:quoteSymbol character="'" escapeCharacter="'"/>
        </sx:toString>
      </sx:parameter>
        
      <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1
        <sx:choose>
          <sx:when test="$jobList">
            AND JOB IN ({$jobList})
          </sx:when>
        </sx:choose>
        ORDER BY JOB,ENAME
      </sx:sqlQuery>
    </sx:sqlReader>
    
    <sx:recordMapping>
      <employees>
        <sx:groupBy fields="JOB">
          <sx:elementMap element="{JOB}">
            <sx:onRecord>
              <employee>
                <sx:fieldAttributeMap field="EMPNO" attribute="employee-no"/>
                <sx:fieldElementMap field="NAME" element="name"/>
              </employee>
            </sx:onRecord>
          </sx:elementMap>
        </sx:groupBy>
      </employees>
    </sx:recordMapping>
    
  </sx:recordContent>

</sx:resources>
]]>
</programlisting>
<para>
Executing the <code>employees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    java -jar dir/servingxml.jar -r resources.xml employees -o output/analysts.txt employees 
        job=ANALYST job=CLERK
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME,JOB 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('ANALYST','CLERK') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
</example>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:jdbcConnectionPool"/>
</listitem>
<listitem>
<xref linkend="sx:sqlQuery"/>
</listitem>
<listitem>
<xref linkend="sx:recordContent"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlUpdate" xreflabel="sx:sqlUpdate">
<refnamediv>
  <refname>sx:sqlUpdate</refname>
  <refpurpose>Update or insert rows</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:sqlUpdateDatabase"/></refclass>
</refnamediv>
<indexterm><primary>sqlUpdate</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
<row>
<entry>recordType</entry>
<entry>No</entry>
<entry><xref linkend="QName"/></entry>
<entry>Apply updates to records of this record type.  Defaults to all 
  records.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para>Optionally, any number of <xref linkend="sx:parameter"/> elements.</para>
<para>
A <sgmltag>sx:sqlUpdate</sgmltag> may contain either text with embedded parameters and fields,
which will be evaluated as a SQL statement, or alternatively an <xref linkend="sx:sqlPrepare"/> element,
which will be compiled into a SQL prepared statement.  
</para> 
  <para>Any number of <xref linkend="sx:sqlUpdateDatabase"/> elements, 
such as an
  <xref linkend="sx:sqlUpdateDetail"/> element.  The child updates will be part
  of the same transaction as the parent.
  </para>
<para>Optionally, but deprecated, a <xref linkend="sx:quoteSymbol"/> element,
  to specify escaping of quote symbols in substitution variables.  
The default, if there is no <xref linkend="sx:escapeSubstitutions"/> element in 
scope, is to escape single quote characters, "'", with two single quote 
characters, "''".  If you need to change the default, use the the configuration 
element 
        <xref linkend="sx:escapeSubstitutions"/> instead of <xref linkend="sx:quoteSymbol"/>. 
</para>.
<para>
A text SQL update or insert statement may have embedded parameter
and field names.  The format for an embedded parameter is {$parameterName}, and for an
embedded field name is {fieldName}.  Parameter and field values in the current context
will be substituted by name.  Parameter and field references bracketed by quote symbols
will have their values searched for quote symbols, which if found will be escaped.
</para>
<para>
A text SQL statement may also contain a sx:choose element. 
</para>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a text SQL update statement.</title>
<programlisting>
<![CDATA[
<sx:sqlUpdate>
  <sx:quoteSymbol character='"' escapeCharacter='"'/>
  UPDATE EMP SET JOB = "{$job}" WHERE EMPNO={$empNo}
</sx:sqlUpdate>
]]>
</programlisting>
<para>
In this example, we specify the double quote as the quote symbol, to be escaped with two double quotes.
Since the parameter "job" appears within the specified quote symbols, values of "job" will be searched for double quotes,
which if found will be replaced by two double quotes. Values of the parameter "empNo", however, will not be
searched for double quotes.
</para>
<para>Note that if the <xref linkend="sx:quoteSymbol"/> element is omitted, the default quote symbol is
the single quote, to be escaped with two single quotes.
</para>
</example>
<example>
<title>Example of a prepared SQL update statement.</title>
<programlisting>
<![CDATA[
  <sx:sqlUpdate>
    <!-- Because the SQL insert executes a prepared statement, values must be
          associated with appropriate types.  
    -->
    <sx:parameter name="employee-no" value="{EMPNO}" type="xs:long"/>
    <sx:parameter name="mgr" value="{MGR}" type="xs:long"/>
    <sx:parameter name="hiredate" value="{HIREDATE}" type="xs:date"/>              
    <sx:parameter name="salary" value="{SAL}" type="xs:decimal"/>              
    <sx:parameter name="commission" value="{COMM}" type="xs:decimal"/>              
    <sx:sqlPrepare>
      INSERT INTO EMP_HISTORY(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) 
      VALUES({$employee-no},{NAME},{JOB}, {$mgr}, {$hiredate},  {$salary}, {$commission})
    </sx:sqlPrepare>
  </sx:sqlUpdate>
]]>
</programlisting>
</example>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlWriter"/>
</listitem>
<listitem>
<xref linkend="sx:quoteSymbol"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlUpdateChoice" xreflabel="sx:sqlUpdateChoice">
<refnamediv>
  <refname>sx:sqlUpdateChoice</refname>
  <refpurpose>Update based on results of query</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:sqlUpdateDatabase"/></refclass>
</refnamediv>
<indexterm><primary>sqlUpdateChoice</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<itemizedlist>
<listitem><para>Optionally, any number of <xref linkend="sx:parameter"/> elements.
</para></listitem>
<listitem><para>A <xref linkend="sx:sqlQuery"/> element.
</para></listitem>
<listitem><para>Optionally, a <sgmltag>sx:recordFound</sgmltag> element, which contains 
<xref linkend="sx:sqlUpdateDatabase"/> elements that will be executed if the 
  query returns rows.
</para></listitem>
<listitem><para>Optionally, a <sgmltag>sx:recordNotFound</sgmltag> element, which contains 
<xref linkend="sx:sqlUpdateDatabase"/> elements that will be executed if the 
  query does not returns rows.
</para></listitem>
</itemizedlist>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a SQL update statement.</title>
<programlisting>
<![CDATA[
  <sx:sqlUpdateChoice>
    <sx:sqlQuery>
      SELECT EMPNO FROM EMP_HISTORY WHERE EMPNO = {EMPNO}
    </sx:sqlQuery>
    <sx:recordNotFound>
      <sx:sqlUpdate>
        INSERT INTO EMP_HISTORY(EMPNO, ENAME) VALUES({EMPNO},'{NAME}')
      </sx:sqlUpdate>
    </sx:recordNotFound>
    <sx:recordFound>
      <sx:sqlUpdate>
        UPDATE EMP_HISTORY SET ENAME = '{NAME}' WHERE EMPNO = {EMPNO}
      </sx:sqlUpdate>
    </sx:recordFound>
  </sx:sqlUpdateChoice>
]]>
</programlisting>
</example>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlWriter"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlUpdateDatabase" xreflabel="sx:sqlUpdateDatabase">
<refnamediv>
  <refname>sx:sqlUpdateDatabase</refname>
  <refpurpose>Abstract element standing for a SQL update</refpurpose>
</refnamediv>
<indexterm><primary>sqlUpdateDatabase</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="1">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry>ref</entry>
<entry>Yes</entry>
<entry><xref linkend="QName"/></entry>
<entry>This attribute is used to reference an id.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<para>None.</para>
</refsection>
<refsection>
<title>Description</title>
<para>This is an abstract element that allows you to refer generically to any 
  element supporting add, change, and delete in a SQL database, including 
        <xref linkend="sx:sqlUpdate"/>,
<xref linkend="sx:sqlUpdateChoice"/>, and <xref linkend="sx:sqlUpdateDetail"/>. 
</para>
</refsection>
<refsection>
<title>Examples</title>
<para>
</para>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlUpdate"/>
</listitem>
<listitem>
<xref linkend="sx:sqlUpdateChoice"/>
</listitem>
<listitem>
<xref linkend="sx:sqlUpdateDetail"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlUpdateDetail" xreflabel="sx:sqlUpdateDetail">
<refnamediv>
  <refname>sx:sqlUpdateDetail</refname>
  <refpurpose>Update detail rows in one transaction</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:sqlUpdateDatabase"/></refclass>
</refnamediv>
<indexterm><primary>sqlUpdateDetail</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
<row>
<entry>field</entry>
<entry>Yes</entry>
<entry><xref linkend="QName"/></entry>
<entry>The name of a repeating group field whose subrecords are to be written to 
  the database.
</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
  <itemizedlist> 
    <listitem><para>Optionally, any number of <xref
  linkend="sx:parameter"/> elements.</para></listitem>
  <listitem><para>Any number of <xref linkend="sx:sqlUpdateDatabase"/> 
                                  elements.</para></listitem>
  </itemizedlist>
</refsection>
<refsection>
<title>Examples</title>
<example>
<title>Example of a SQL update detail statement.</title>
  <para>
    Suppose you have a stream of composite records of type "master" that contain 
    a repeating group field named "details" having subrecords of type "detail".
    The XML representation of one of these records might look like this:
  </para>
<programlisting>
<![CDATA[
<master>
  <masterId>1</masterId>
  <name>aName</name>
  <details>
    <detail>
      <detailId>11</detailId>
      <date>2008-09-03</date>
      <type>aType</type>
    </detail>
    <detail>
      <detailId>12</detailId>
      <date>2008-09-06</date>
      <type>aType</type>
    </detail>
    <detail>
      <detailId>13</detailId>
      <date>2008-09-10</date>
      <type>aType</type>
    </detail>
  </details>
</master>
]]>
</programlisting>
  <para>
    The SQL writer below inserts the master/detail records into a master table
    and a detail table as a single transaction.
  </para>
<programlisting>
<![CDATA[
  <sx:sqlWriter id="masterDetailWriter">                          
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>
        INSERT INTO master(master_id,name) VALUES({masterId},'{name}')
      <sx:sqlUpdateDetail field="details">
        <sx:sqlUpdate>
          INSERT INTO detail(detail_id,detail_date,detail_type) 
          VALUES({detailId},to_date('{date}','yyyy-mm-dd'),'{type}')
        </sx:sqlUpdate>
      </sx:sqlUpdateDetail>
    </sx:sqlUpdate>
  </sx:sqlWriter>
]]>
</programlisting>
</example>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:sqlWriter"/>
</listitem>
<listitem>
<xref linkend="sx:sqlUpdate"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>

<refentry id="sx:sqlWriter" xreflabel="sx:sqlWriter">
<refnamediv>
  <refname>sx:sqlWriter</refname>
  <refpurpose>SQL record writer</refpurpose>
  <refclass>&nbsp;&nbsp;&boxur;&nbsp;<xref linkend="sx:recordWriter"/></refclass>
</refnamediv>
<indexterm><primary>sqlWriter</primary></indexterm>
<refsection>
<title>Attributes</title>
<informaltable colsep="0">
<tgroup cols="4" align="left" colsep="0" rowsep="0">
<thead>
<row>
<entry>Name</entry>
<entry>Required</entry>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><xref linkend="idref-attributes"/></entry>
<entry>No</entry>
<entry>&nbsp;</entry>
<entry>Common id ref attributes.</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</refsection>
<refsection>
<title>Content</title>
<itemizedlist>
<listitem>Optionally, any number of <xref linkend="sx:parameter"/> elements.</listitem>
<listitem>A <xref linkend="sx:jdbcConnectionPool"/> element.</listitem>
<listitem>One or more <xref linkend="sx:sqlUpdate"/> or <xref linkend="sx:sqlUpdateChoice"/> elements.  
The updates are wrapped in a transaction and will either all succeed or all fail.</listitem>
</itemizedlist>
</refsection>
<refsection>
<title>Examples</title>
</refsection>
<refsection>
<title>See also</title>
<itemizedlist>
<listitem>
<xref linkend="sx:jdbcConnectionPool"/>
</listitem>
<listitem>
<xref linkend="sx:sqlUpdate"/>
</listitem>
<listitem>
<xref linkend="sx:recordWriter"/>
</listitem>
</itemizedlist>
</refsection>
</refentry>
</reference>

