<?xml version="1.0" encoding="iso-8859-1"?>
<?xml-stylesheet href="../make-menu.xsl" type="text/xsl"?><html>
   <head>
      <this-is section="sql-extension" page="example" subpage=""/>
      <!--
           Generated at 2010-10-29T18:20:48.427+01:00--><title>Saxonica: XSLT and XQuery Processing: Example</title>
      <meta name="coverage" content="Worldwide"/>
      <meta name="copyright" content="Copyright Saxonica Ltd"/>
      <meta name="title" content="Saxonica: XSLT and XQuery Processing: Example"/>
      <meta name="robots" content="noindex,nofollow"/>
      <link rel="stylesheet" href="../saxondocs.css" type="text/css"/>
   </head>
   <body class="main">
      <h1>Example</h1>
      <p>A specimen stylesheet that uses these XSL extension is <b>books-sql.xsl</b>. This loads the contents
of the books.xml file into a database table, To use it, you need to create a database
database containing a table "Book" with three character columns,
"Title", "Author", and "Category"</p>
      <p>Here is the stylesheet:</p>
      <div class="codeblock"
           style="border: solid thin; background-color: #B1CCC7; padding: 2px">
         <pre>
            <code>&lt;xsl:stylesheet
	xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory"
 	xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
	xmlns:saxon="http://saxon.sf.net/"
 	extension-element-prefixes="saxon sql"&gt;

&lt;!-- insert your database details here, or supply them in parameters --&gt;
&lt;xsl:param name="driver" select="'sun.jdbc.odbc.JdbcOdbcDriver'"/&gt;
&lt;xsl:param name="database" select="'jdbc:odbc:test'"/&gt;  
&lt;xsl:param name="user"/&gt;
&lt;xsl:param name="password"/&gt;

&lt;!-- This stylesheet writes the book list to a SQL database --&gt;

&lt;xsl:variable name="count" select="0" saxon:assignable="yes"/&gt;

&lt;xsl:output method="xml" indent="yes"/&gt;

&lt;xsl:template match="BOOKLIST"&gt;
    &lt;xsl:if test="not(element-available('sql:connect'))"&gt;
        &lt;xsl:message&gt;sql:connect is not available&lt;/xsl:message&gt;
    &lt;/xsl:if&gt;
    
    &lt;xsl:message&gt;Connecting to &lt;xsl:value-of select="$database"/&gt;...&lt;/xsl:message&gt;

    &lt;xsl:variable name="connection" 
         as="java:java.sql.Connection" xmlns:java="http://saxon.sf.net/java-type"&gt;   
      &lt;sql:connect driver="{$driver}" database="{$database}" 
                 user="{$user}" password="{$password}"&gt;
        &lt;xsl:fallback&gt;
          &lt;xsl:message terminate="yes"&gt;SQL extensions are not installed&lt;/xsl:message&gt;
        &lt;/xsl:fallback&gt;
      &lt;/sql:connect&gt;
    &lt;/xsl:variable&gt;
    
    &lt;xsl:message&gt;Connected...&lt;/xsl:message&gt;
    
    &lt;xsl:apply-templates select="BOOKS"&gt;
      &lt;xsl:with-param name="connection" select="$connection"/&gt;
    &lt;/xsl:apply-templates&gt;
    
    &lt;xsl:message&gt;Inserted &lt;xsl:value-of select="$count"/&gt; records.&lt;/xsl:message&gt;

    &lt;xsl:variable name="book-table"&gt;
      &lt;sql:query connection="$connection" table="Book" column="*" row-tag="book" column-tag="col"/&gt; 
    &lt;/xsl:variable&gt;
    
    &lt;xsl:message&gt;There are now &lt;xsl:value-of select="count($book-table//Book)"/&gt; books.&lt;/xsl:message&gt;
    &lt;new-book-table&gt;
        &lt;xsl:copy-of select="$book-table"/&gt;
    &lt;/new-book-table&gt;
    
    &lt;sql:close connection="$connection"/&gt;
    
&lt;/xsl:template&gt;

&lt;xsl:template match="BOOKS"&gt;
    &lt;xsl:param name="connection"/&gt;
    &lt;xsl:for-each select="ITEM"&gt;
    	&lt;sql:insert connection="$connection" table="Book"&gt;
    	    &lt;sql:column name="title" select="TITLE"/&gt;
            &lt;sql:column name="author" select="AUTHOR"/&gt;
            &lt;sql:column name="category" select="@CAT"/&gt;
    	&lt;/sql:insert&gt;
    	&lt;saxon:assign name="count" select="$count+1"/&gt;
    &lt;/xsl:for-each&gt;
&lt;/xsl:template&gt;

&lt;/xsl:stylesheet&gt;	
</code>
         </pre>
      </div>
      <p class="subhead">Running the example using Microsoft Access</p>
      <p>To run this stylesheet you will need to do the following:</p>
      <ol>
         <li content="para">
            <p>Create a database (e.g. Microsoft Access) containing a table "Book" with three character columns,
"Title", "Author", and "Category".</p>
         </li>
         <li content="para">
            <p>Register this database as a JDBC data source. (If you use Microsoft Access, register it as an
ODBC data source called, say, Books, and then it will automatically be available under JDBC as
"jdbc:odbc:Books".</p>
         </li>
         <li content="para">
            <p>Modify the <code>&lt;sql:connect&gt;</code> element in the stylesheet to specify the correct JDBC connection
name for the database, and if necessary to supply a username and password. Alternatively you can
supply the driver class, database name, username, and password as parameters on the command line.</p>
         </li>
         <li>
            <p>Execute the stylesheet from the command line, as follows:</p>
            <p class="command">
java   net.sf.saxon.Transform  data\books.xml   style\books-sql.xsl <br/>
            </p>
         </li>
      </ol>
      <p>The database will be populated with data from the <code>books.xml</code> document.</p>
      <p class="subhead">Running the example using MySQL</p>
      <p>The following instructions illustrates how to run the stylesheet using MySQL, under a UNIX platform:</p>
      <ol>
         <li content="para">
            <p>Create the database, using MySQL.</p>
         </li>
         <li>
            <p>Register the database as a JDBC data source (same as 2. above). However, change the <code>xsl:param</code> elements with attribute names 
                "driver" and "database" as follows (we assume machine is localhost):</p>
            <div class="codeblock"
                 style="border: solid thin; background-color: #B1CCC7; padding: 2px">
               <pre>
                  <code>&lt;xsl:stylesheet
               &lt;xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/&gt;
               &lt;xsl:param name="database" select="'jdbc:mysql://localhost:3306/Book'"/&gt;   
                </code>
               </pre>
            </div>
         </li>
         <li content="para">
            <p>Download the MySQL connector jar file, if missing.</p>
         </li>
         <li>
            <p>Execute the stylesheet from the command line (same as 4. above). The kind of problems that might occur are as follows:</p>
            <ul>
               <li>
                  <p>"JDBC Connection Failure: com.mysql.jdbc.Driver": Ensure the MySQL connector jar file is downloaded and in the classpath</p>
               </li>
               <li>
                  <p>"SQL extensions are not installed": The binding of the namespace for the SQL extension must be present in the <code>Configuration</code> object and must 
               match the URI in the stylesheet. For execution of the stylesheet from the command line, the following is required:</p>
                  <p class="command">
                  java   net.sf.saxon.Transform   -config:data/config.xml   data/books.xml   style/books-mysql.xsl <br/>
               </p>
                  <p>where the configuration file <code>data/config.xml</code> includes the entry</p>
                  <div class="codeblock"
                       style="border: solid thin; background-color: #B1CCC7; padding: 2px">
                     <pre>
                        <code>
&lt;configuration xmlns="http://saxon.sf.net/ns/configuration"
               edition="EE"&gt;                
  &lt;xslt&gt;
    &lt;extensionElement namespace="http://saxon.sf.net/sql" 
                      factory="net.sf.saxon.option.sql.SQLElementFactory"/&gt;
  &lt;/xslt&gt;
&lt;/configuration&gt;
</code>
                     </pre>
                  </div>
               </li>
            </ul>
         </li>
      </ol>
      <table width="100%">
         <tr>
            <td>
               <p align="right"><a class="nav" href="execute.xml">Next</a></p>
            </td>
         </tr>
      </table>
   </body>
</html>