'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'"
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);
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"
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.