The classes implementing jsql are in the jar file 'sqltools.jar'. You
will also need a JDBC database, such as the JDataStore database
that ships with some versions of JBuilder, to use jsql. 'Installing
Jsql' and 'Using Jsql' have more
information.
There is a shell script, or Windows batch file, in this samples area
named "go" or "go.bat". When run, it will invoke jsql. For
example,
go labels.sql
invokes
jsql -f c:\JBuilder4\samples\JDataStore\datastores\EMPLOYEE.JDS
labels.sql;
(You can avoid the long -f option string string by using a CONNECTION statement in the SQL file)
To print address labels, just turn headings off and use the 'block'
report type. For example, these lines are in the file 'labels.sql':
REPORT BLOCK;
HEADINGS NO;
select FIRST_NAME + ' ' + LAST_NAME, ADDRESS, CITY +',
'+STATE+ZIP FROM addresses;
If you enter the command:
go labels.sql
on the command line, you should see output similar to the following:
John Smith
123 Anywhere St.
Santa Cruz, CA 12345
Mary Smith
123 Anywhere St.
Santa Cruz, CA 12345
To shift the address labels to the right, eg. to align them on sticky
labels, insert leading blanks like this:
select ' '+FIRST_NAME + ' ' + LAST_NAME, '
'+ADDRESS, ' '+CITY +', '+STATE+ZIP FROM addresses;
You can double space or introduce other blank lines with constant strings,
like this:
select FIRST_NAME + ' ' + LAST_NAME, ' ',ADDRESS, ' ',CITY
+', '+STATE+ZIP FROM addresses;
If you are on a Linux system you can pipe output to the printer like
this:
% jsql labels.sql | lpr
or into a text processing application like this:
% jsql labels.sql | pr -4
To generate XML output, eg. for processing with an XSL stylesheet processing
engine, use the report type 'xml'. For example, these lines
are in the file 'xml.sql':
CONNECTION /home/yourhome/addressbook.jds;
REPORT XML;
select * from addresses;
and this command:
go xml.sql
will produce XML output similar to the following:
<?xml version="1.0">
<row>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
<ADDRESS>123 Anywhere St.</ADDRESS>
<CITY>Santa Cruz</CITY>
<STATE>CA</STATE>
<ZIP>12345</ZIP>
</row>
<row>
<FIRST_NAME>Mary</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
<ADDRESS>123 Anywhere St.</ADDRESS>
<CITY>Santa Cruz</CITY>
<STATE>CA</STATE>
<ZIP>12345</ZIP>
</row>
(If you need greater control over the XML output produced, read "GENERATING
XML OUTPUT WITH JSQL OR XML")
To generate "normal" reports of database tables, use the report type
"fixedwidth". For example, these lines are in the file 'report.sql':
CONNECTION /home/yourhome/addressbook.jds;
REPORT FIXEDWIDTH;
select * from addresses;
and "go report.sql" will produce this output:
FIRST_NAME LAST_NAME
ADDRESS CITY
STATE ZIP
John
Smith 123 Anywhere
St. Santa Cruz CA
12345
Mary
Smith 123 Anywhere
St. Santa Cruz CA
12345
If you need to export data, for example to place it in a spreadsheet,
use the report type "quotes", which generates quote and comma delimited
output. Most spreadsheets and databases support this as an import
format. For example, 'export.sql' contains
these lines:
CONNECTION /home/yourhome/addressbook.jds;
REPORT QUOTES;
select * from addresses;
then this command:
% jsql report.sql
generates this output:
"FIRST_NAME", "LAST_NAME", "ADDRESS", "CITY", "STATE", "ZIP"
"John", "Smith", "123 Anywhere St.", "Santa Cruz", "CA", "12345"
"Mary", "Smith", "123 Anywhere St.", "Santa Cruz", "CA", "12345"
If you need output without column names, you can turn headings off,
as in this example:
CONNECTION /home/yourhome/addressbook.jds;
REPORT QUOTES;
HEADING NO;
select * from addresses;
which generates:
"John", "Smith", "123 Anywhere St.", "Santa Cruz", "CA", "12345"
"Mary", "Smith", "123 Anywhere St.", "Santa Cruz", "CA", "12345"
To copy a table structure without copying its data, use the "sql" report
type.
For example, if 'clone.sql' contains these lines
CONNECTION /home/yourhome/addressbook.jds;
REPORT SQL mycopy;
select * from addresses where false;
the SQL command for creating the table will be generated:
create table mycopy (
FIRST_NAME string,
LAST_NAME string,
ADDRESS string,
CITY string,
STATE string,
ZIP string);
If you omit the "where false" clause you'll get the data too:
create table mycopy (
FIRST_NAME string,
LAST_NAME string,
ADDRESS string,
CITY string,
STATE string,
ZIP string);
insert into mycopy values('John', 'Smith', '123 Anywhere St.',
'Santa Cruz', 'CA', '12345');
insert into mycopy values('Mary', 'Smith', '123 Anywhere St.',
'Santa Cruz', 'CA', '12345');
If you need to merge database values with text, use report type 'plain'.
For example, if 'plain.sql' contains these lines:
CONNECTION /home/yourhome/addressbook.jds;
REPORT plain;
HEADING no;
select 'The number of people in your address book is ',
count(*) from addresses;
you will see this output:
The number of people in your address book is 2
A single file can contain multiple queries, and they can use different
report formats. for example, if 'queries.sql' contains these lines:
CONNECTION /home/yourhome/addressbook.jds;
REPORT plain;
HEADING no;
select 'The number of people in your address book is ',
count(*) from addresses;
REPORT fixedwidth;
select * from addresses;
this report will be produced:
The number of people in your address book is 2
FIRST_NAME LAST_NAME
ADDRESS CITY
STATE ZIP
John
Smith 123 Anywhere
St. Santa Cruz CA
12345
Mary
Smith 123 Anywhere
St. Santa Cruz CA
12345
Sometimes you may want to insert a string or the result of database
function into a report. You can use dummy table to do this.
Make a table with one record:
create table dummy (id int);
insert into dummy values(0);
then select from it when you want to insert the result of a function call, or a constant string, into your reports:
report plain;
heading no;
select 'Hello, this is the daily sales recap for ', current_date
from dummy;
generates this output:
Hello, this is the daily sales recap for 2000-08-02