USING JSQL                                                      (Revised 7-17-00)


 

'Jsql' is a command line tool for running SQL scripts and generating reports
of types, including XML reports.  You will find it in the 'bin' directory,
under the main JBuilder40 directory.

To use jsql to run a SQL script, start it like this:
  jsql [options] (input file)  >(output file)

Options can be selected from these options:
  -d     (JDBC driver name)      select the JDBC driver
  -u     (JDBC url)              select the JDBC url
  -f     (JDataStore file name)  select the database file (JDataStore files only)
  -q     (query)                 run this query
  -n                             do not prompt (if neither an input file nor the -n
                                 option is specified jsql prompts with "sql>")
  -help                          show help

You must specify a JDBC driver and url or a JDataStore file or you will see
an error message saying "File not found".  From the command line use -d (driver)
and -u (url) to specify a JDBC driver or use -f (filename) to specify a JDataStore
file.  From a script,use the CONNECTION command described below.

To run jsql interactively, start it like this:
  jsql [options]
You can enter SQL commands when you see the "sql>" prompt.  When finished
enter "quit".

To pipe a sql query into jsql, with prompting turned off,start it like this:

  cat foo | jsql -n -f c:\mytable.jds

Finally, to specify a query script at the command line, use the '-q' option:

  jsql -q "connection c:\proj\data\test32.jds; select * from invoices'"



RUNNING QUERIES


Jsql passes SQL commands to a JDBC driver and displays the results.  Each command
should be terminated by a ';'.  In interactive mode, jsql is not very good about
recognizing complete commands that are missing semicolons.  For example entering:
  sql> select * from a
will generate no output.  If you find yourself in this situation, just enter
a semicolon on the next line, like this:
  sql> select * from a
  sql> ;

Jsql passes SQL commands on to the JDBC driver you specify.  It processes these
extended commands without invoking the driver:

   CONNECTION (JDBC driver) (JDBC url);
   CONNECTION (JDataStore file name);
   USER (username) (password);
   HEADING { YES | NO };
   REPORT {PLAIN | COMMAS | FIXEDWIDTH | SQL (table) | XML (element name) |
     XMLCOMPRESSED (element name) | QUOTES]
   QUIT

When using the JDataStore JDBC driver, these commands are also supported:
   INITIALIZE;      (creates and intialize a JDS file)
   IMPORT table FROM [URL] (file or url) USING (schema);


SOME SIMPLE REPORTS

Following are samples of simple report types...

This script:
   report fixedwidth;
   select * from a;

generates this output:
   ID   NAME          AGE
   5    Sam           2
   1    Bartholomew   9

This script:
   report plain;
   heading no;
   select 'The sum of lengths is ', sum(lengths), '.';

generates this output:
   The sum of lengths is 5.

This script:
   report commas;
   select * from a;

generates this output:
   ID, NAME, AGE
   5, Sam, 2
   1, Bartholomew, 9

This script:
   report quotes;
   select * from a;

generates this output:
   "5", "Sam", "2"
   "1", "Bartholomew", "9"


GENERATING XML OUTPUT

To generate XML files you run can 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"?>
  <borlandxml>
    <connection file="/proj/data/test32.jds"/>
    <user name="henry" password="rumpelstilskin">
    <report type="xml" heading="true" output-type="text/xml"/>
    <query doc-element="CURRENTORDERS"  row-element="ORDER" verbose="false">

      SELECT * from coffees;
      </query>
  </borlandxml>

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"?>
  <borlandxml>
    <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>
  </borlandxml>

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 "borlandxml":

  <borlandxml>
     ... XML input lines ...
  <borlandxml>

which informs jsql that this script is uses the XML that is compatible
with the XmlServlet.

The XML input lines may contain these tags:

    <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)
          You must specify a JDBC connection using the connection tag if you do
          not specify it using a command line option

    <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. "text/plain" or "text/xml")
          If you want an XML enabled browser to display output as an XML tree, or to
          apply an XSL stylsheet to XML output use output-type="text/xml".

    <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 insert 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.

           Finally, the "verbose" property turns debuggin output on and off:

             verbose=(true or false)

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