JSQL SAMPLES


'Jsql' is a command line tool for running SQL queries and generating reports of various types, including address labels and XML for further processing.  'USING JSQL' tells more about how jsql works.  This directory contains sample scripts that can be used with 'jsql' to produce various kinds of output.  Any of these scripts work with XmlServlet, too.  The following report types are described further below:
  Address Labels    Xml   Columnar Tables    Exporting Data  Sql Statements As Output  Merged Text   A Useful Trick

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)


ADDRESS LABELS

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
 


XML OUTPUT

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")


DATABASE TABLES

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



EXPORTING DATA

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"


GENERATING SQL STATEMENTS

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


MERGING TEXT

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


A USEFUL TRICK

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