Using XML Servlet

Note: This is a feature of JBuilder Professional and Enterprise.

XmlServlet is a servlet for running queries using a JDBC database driver and generating XML or plain text output. It can be invoked from an HTML FORM in a web page or from Java client programs.

XmlServlet was written to the Servlet 2.2 API and is compatible with Tomcat, the Servlet 2.2 reference implementation.

Installing and running the XmlServlet

XmlServlet is installed by copying its system directory into the Tomcat system directory and changing two configuration files.

To install and start XmlServlet, copy the XmlServlet directory from your JBuilder system directory to the web applications area of your server. When using Tomcat, the default Web server, this is usually TOMCAT_HOME/webapps. Then you edit TOMCAT_HOME/conf/server.xml and add these lines to define a new servlet context:

 <Context path="/XmlServlet" docBase="webapps/XmlServlet" debug="0" reloadable="true" > 
 </Context>

Finally, stop and start your server.

XmlServlet is now installed, and you can access it using URLs of the form:

http://localhost:8080/XmlServlet/servlet/XmlServlet?query=...some query...

An easy way to do this is to open the file XmlForm.html in a browser, build a query, and click the "Submit" button to send it. For example, the following query creates a JDataStore file named sandbox.jds in your root directory, in the query box:

    connection /sandbox.jds;
    initialize;
    create table a (name string);
    insert into a values('Sam');
    select * from a;
	

Please note that XmlServlet can be used with any JDBC driver, but the initialize command shown in this example is a specific command for working with JDataStore. The initialize command creates a new JDataStore file, or attaches a TxManager to an existing JDataStore file.

You can also submit queries in an XML form. The command line tool jsql and XmlServlet support the same set of XML query tags, which you will find described in "Using JSQL to generate XML files".

Advanced installation techniques

XmlServlet complies with the Servlet 2.2 API. You can configure XmlServlet by editing two XML files in your server's configuration area: server.xml in the web server's configuration directory WEB-INF, and web.xml in the XmlServlet's WEB-INF directory.

Using a set of startup parameters specified in the XmlServlet server configuration file, it is possible to limit the use of the servlet to read-only access, limit the servlet to single file access, and specify various defaults. With the exceptions of "noconnection" and "noreadwrite", any of these default parameter values can also be overwritten by a query. XmlServlet parameters are set by a file on the web server where XmlServlet is running, called web.xml.

A single web.xml file can specify multiple servlet names for invoking the XmlServlet, and each name can be associated with a different set of parameter settings. This is useful for publishing multiple JDS files on the web when you want to limit users to a particular set of datastore files.

The following is a complete list of the parameter names that can be set. The first set of parameters specify the JDBC connection, and control whether clients can change the connection or not:

Parameter name Description
noconnection when this parameter is present, the client cannot change the JDBC connection driver
noreadwrite when this parameter is present, the client cannot change the readonly setting
file (string) the JDataStore file, JDS file, to connect to at startup (can be changed by 'connection')
driver (string) the JDBC driver name to connect to at startup (can be changed by 'connection')
url (string) the JDBC driver name to connect to at startup (can be changed by 'connection')
username (string) username for 'file' or 'driver' and 'url'
password (string) password for account 'username'
readonly (true or false) specifies that this this is a readonly connection (or not)

Note that if you do not specify a connection using the 'file' or 'driver' and 'url' properties in web.xml, each query will have to contain a 'connection' statement.

Another set of parameters control the default report type:

Parameter name Description
heading (true or false)
type commas - comma-delimited report

fixedwith - fixed-width columns

plain - no spacing between columns, useful for output like "The average rainfall is 2 inches"

quotes - quote and comma delimited (useful for spreadsheet import)

xml - preferred form for xml output

xmlcompressed - an alternative form for xml output sql generates SQL statements, eg. "create table...", for generating SQL commands that will reproduce a ResultSet

output-type (string) sets the HTTP context-type used for sending output to the client browser. Normally, you will use "text/plain" unless sending XML output to a browser that supports cascading style sheets or XSL, in which case you will use "text/xml".

When the output report type is XML, another set of parameters control additional reporting options:

Parameter name Description
doc-element sets the default document level element name, which can be overwritten by a query
row-element sets the default name used for rows, which can be overwritten by a query
col-element sets the default name for columns
xml-tag sets the default xml tag used at the beginng of the document

Normally, you will not change the default XML output settings using Servlet parameters.

Changing parameter settings

When the XmlServlet loads, it will look for parameter settings marked <init-param> in the Servlet 2.2 configuration file web.xml. If you are using the version of Tomcat shipped with JBuilder, this file will be in TOMCAT_HOME/webapps/XmlServlet/WEB-INF.

You specify XmlServlet parameter settings by editing web.xml and adding or changing <init-param> tags, as in this example:

       <servlet> 
       <servlet-name> 
          INVOICEDB 
       </servlet-name> 
       <servlet-class> 
          com.borland.sql.XmlServlet 
       <servlet-class> 
       <init-param> 
          <param-name>file</param-name> 
          <param-value>/home/INVOICEDB.JDS</param-value> 
        </init-param> 
        <init-param> 
           <param-name>report</param-name> 
           <param-value>xml</param-value> 
        </init-param> 
        <init-param> 
           <param-name>noconnection</param-name> 
           <param-value>true</param-value> 
        </init-param> 
        </servlet> 

The above example sets the parameter 'file' equal to "/home/INVOICEDB.JDS" when the XmlServlet is invoked with the servlet name "INVOICEDB". Changing the connection is not allowed, and XML output will be generated as plain text.

Using the standard Servlet 2.2 API tags, it is also possible to set the security contraints associated with a given name for the XmlServlet. You will find more about these constraints in the Servlet 2.2 API document in the section describing the <security-constraint> tag.

Using named parameters

XmlServlet supports parameter substitution for XML input. Here's how this works.

When a string of the form :(name) is encountered in query input, XmlServlet will look for an input control named (name). If one is found, the string :(name) will be replaced by the value supplied by the input control (name).

Using JSQL to generate XML files

JSQL is a command line tool for running SQL scripts and generating reports, including XML reports. You'll find it in the jbuilder4\bin directory.

To generate XML files, you run JSQL like this:

jsql [options] (input file)  > (output file)

For example:

  jsql currentorders.xml > report.xml

Before detailing the structure of JSQL input files, here are two examples and their output. An XML input file containing these lines:

  <?xml version="1.0"?>
  <borland:xmlservlet>
    <connection file="/proj/data/test32.jds"/>
    <report type="xml" heading="true" output-type="text/xml"/>
    <user name="henry" password="rumpelstilskin">
    <query doc-element="TABLE" col-element="COL" row-element="ROW" verbose="false">
    SELECT * from coffees;
    </query>
  </borland:xmlservlet>

generated this XML output:

  <?xml version="1.0"?>
  <CURRENTORDERS > 
  <ORDER>
    <COF_NAME>       Colombian  </COF_NAME>
    <SUP_ID>         101  </SUP_ID>
    <PRICE>          7.99  </PRICE>
    <SALES>          0  </SALES>
    <TOTAL>          0  </TOTAL>
  </ORDER>
  <ORDER>
    <COF_NAME>       French_Roast  </COF_NAME>
    <SUP_ID>         49  </SUP_ID>
    <PRICE>          8.99  </PRICE>
    <SALES>          0  </SALES>
    <TOTAL>          0  </TOTAL>
  </ORDER>
  </CURRENTORDERS>

and another containing these lines:

  <?xml version="1.0"?>
  <borland:xmlservlet>
    <connection file="/proj/data/test32.jds"/>
    <report type="xml" heading="true" output-type="text/xml"/>
    <user name="henry" password="rumpelstilskin"/>
    <query doc-element="TABLE" col-element="COL" row-element="ROW" verbose="false">
      SELECT * from coffees;
    </query>
  </borland:xmlservlet>

generated this XML output:

  <?xml version="1.0"?>
  <TABLE > 
  <ROW>
    <COL>    Colombian  </COL>
    <COL>    101  </COL>
    <COL>    7.99  </COL>
    <COL>    0  </COL>
    <COL>    0  </COL>
  </ROW>
  <ROW>
    <COL>    French_Roast  </COL>
    <COL>    49  </COL>
    <COL>    8.99  </COL>
    <COL>    0  </COL>
    <COL>    0  </COL>
  </ROW>
  </TABLE>

The first line in an XML input file is always the XML version number: <?xml version="1.0"?>.

After this must come the tag "borland:xml":

  <borland:xml>
     ... xml input lines ...
  <borland:xml>

XML input lines may contain these tags:

Tag Description
<connection> Specifies either a JDBC driver and URL or a JDataStore file name as the JDBC connection. It takes these properties:

  • driver=(JDBC driver)
  • url=(JDBC url)
  • file=(JDataStore file)
  • readonly= (true or false)
<report> Specifies the report type. It takes these optional properties:

  • type=(PLAIN | COMMAS | QUOTES | XML | XMLCOMPRESSED | SQL)
  • heading=(true or false)
  • output-type = (string, eg. "plain/text")
<user> Specifies the user name and password:

  • name=(user name)
  • password=(password)
<query> Specifies the SQL query and sets options for XML output. The SQL query should be inserted between <query> and </query> tags, and each SQL command should be terminated by a semicolon.

You use these properties in the "query" tag to control XML output:

  • doc-element=(string) if doc-element is missing, no document level element will be output
  • col-element=(string) if col-element is missing, column names will be used for column elements
  • row-element=(string) if row-element is missing, "row" will be used for row elements.
  • verbose=(true or false) turns debugging output on and off

A single input file may contain multiple <report> and <query> tags so that simple reports can be generated.