Installing JSQL

Revised 8-30-00

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 it is used.

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 the Enterprise version of JBuilder, to use jsql.

You install jsql by editing a batch file or shell script JBuilder provides in order to invoke the main[] method in com.borland.sqltools.jsql.

Platform-specific instructions for installing jsql follow:

Information on connecting to databases and using passwords follows the installation instructions.

Installing on Windows

Step 1

The file jsql.exe may work without modification. To find out, change directory to the bin directory under your JBuilder home directory and type jsql. If you see the sql> prompt, go to Step 2.

If not, edit the files jdk.config and jsql.config, which you will find in the JBuilder bin directory. jdk.config must contain an addpath command for your JDK directory, and jsql.config must contain addpath commands for the JDBC driver you want to use. Be sure to keep the dx.jar and jds.jar addpath commands, too.

Another way to configure jsql is to make a batch file, jsql.bat, containing this line:

[path to java] -classpath [your class path] com.borland.sqltools.bsql %1
%2 %3 %4 %5 %6 %7 %8 %9

For example, jsql.bat might contain:

set LIB_HOME=c:\JBuilder4\lib
c:\jdk1.3\bin\java -classpath
%LIB_HOME%\sqltools.jar;%LIB_HOME%\jds.jar%LIB_HOME%\dx.jar;%LIB_HOME%\jdsremote.jar
com.borland.sqltools.bsql %1 %2 %3 %4 %5 %6 %7 %8 %9

Step 2

Next, check that the JDBC driver you need is on the class path. If you are using JDataStore, you may not need to make a change.

To find out, start jsql and enter these commands to create a new JDataStore file:

sql> connection c:\newfile.jds;
   sql> initialize;
   sql> quit
If this works, you have created a database file named newfile.jds.

If you can enter those commands without error, and don't need another JDBC driver, go to Step 3. The initialize command works only with JDataStore; it creates and initializes an empty JDS file at the path you specify.

If you see an error message concerning the JDataStore license file, use the JDataStore Explorer License Manager to create a license file and make sure it can be found on the class path. The license file must be on the class path and cannot be in a JAR file. To use a different JDBC driver, just edit jsql.config and add an addpath command for the JAR file containing the driver you want to use. For example, to use the Interbase driver with the JAR file in c:\drivers\interclient.jar, add a line addpath c:\drivers\interclient.jar

Alternatively, you can start jsql from a batch file that sets the classpath and then invokes the underlying jsql class this way:

 set LIB_HOME=c:\JBuilder4\lib
  c:\jdk1.3\bin\java -classpath
%LIB_HOME%\sqltools.jar;%LIB_HOME%\sqltools.jar;
c:\drivers\interclient.jar;
%LIB_HOME%\datastore.jar%LIB_HOME%\dx.jar;%LIB_HOME%\dsremote.jar
com.borland.sqltools.bsql %1 %2 %3 %4 %5 %6 %7 %8 %9
Note: You can have more than one driver on the class path if you choose.

Step 3

Now refer to "Using jsql" for more information about using jsql. For example, to see a list of tables in the current database, enter:

  sql> connection c:\newfile.jds;
  sql> list;
  sql> quit
Be sure to check the last section, Connecting to databases, for the information you need to work with databases from different vendors.

Installing on Linux and Solaris

Step 1

The file jsql may work without modification. To find out, change directory to the bin directory under your JBuilder home directory and type jsql. If you see the sql> prompt, go to Step 2.

If not, first make sure that jsql is executable. Enter:

 chmod +x jsql
at the command prompt, and try the command jsql again.

If that still doesn't work, edit jsql, and replace its contents with a line for invoking jsql.main[]. It will look like this:

[path to java] -classpath [your class path] com.borland.sqltools.bsql $*

For example, jsql might contain:

  LIB_HOME=c:\JBuilder4\lib
  export LIB_HOME
  c:\jdk1.3\bin\java -classpath
$LIB_HOME/sqltools.jar:$LIB_HOME/datastore.jar$LIB_HOME/dx.jar;$LIB_HOME/dsremote.jar
com.borland.sqltools.bsql %1 %2 %3 %4 %5 %6 %7 %8 %9

Step 2

Next, check that the JDBC driver you need is on the class path. If you are using JDataStore, you may not need to make a change.

To find out, start jsql and enter these commands to create a new JDataStore file:

 sql> connection c:\newfile.jds;
   sql> initialize;
   sql> quit
If you can enter those commands without error, and don't need another JDBC driver, go to Step 3. The initialize command works only with JDataStore; it creates and initializes an empty JDS file at the path you specify.

If you see an error message concerning the JDataStore license file, use the JDataStore Explorer License Manager to create a license file and make sure it can be found on the class path. The license file must be on the class path and cannot be in a JAR file.

To use a different JDBC driver, just edit jsql and add the JAR file containing the driver you want to use to the command line. For example, to use the Interbase driver with the JAR file in c:\drivers\interclient.jar, change jsql.bat to:

LIB_HOME=c:\JBuilder4\lib
  export LIB_HOME
  c:\jdk1.3\bin\java -classpath
$LIB_HOME/sqltools.jar:$LIB_HOME/drivers/interclient.jar:$LIB_HOME/datastore.jar$LIB_HOME/dx.jar;$LIB_HOME/dsremote.jar
com.borland.sqltools.bsql %1 %2 %3 %4 %5 %6 %7 %8 %9
Note: You can have more than one driver on the class path if you choose.

Step 3

Now refer to "Using jsql" for more information about using jsql. For example, to see a list of tables in JDataStore file /databases/newfile.jds, enter:
  sql> connection /databases/newfile.jds;
  sql> list;
  sql> quit
Be sure to check the next section, "Connecting to databases", for the information you need to work with databases from different vendors.

Connecting to databases

Following are the CONNECTION commands you will need to connect to some common databases. See the next section for information about specifying your user name and password.

Connecting to JDataStore

Local file

Put datastore.jar and dx.jar on the class path and use this command to connect:
 CONNECTION  [JDataStore file name]
for example:
CONNECTION c:\mydata.jds;
Note that you should not use quotations marks in this release. Also note that paths with blanks are not permitted.

Remote connection to datastore server

Put datastore.jar, dx.jar, and dsremote.jar on the classpath and use this command to connect:
CONNECTION  jdbc:borland:dsremote://[host]/path
Remote connection to XML servlet

Start jsql with this command line option:
jsql -s [url for XmlServlet]
then use CONNECTION as though you were running jsql on the remote machine.

Interbase

Put interclient.jar on the class path, then use these driver and URL strings:
 CONNECTION interbase.interclient.Driver
jdbc:interbase://localhost/interbase.interclient.Driver
jdbc:interbase://[host]/[path to gdb file on host]
For example:
CONNECTION interbase.interclient.Driver
jdbc:interbase://localhost/D:\InterBaseCorp\InterBase\examples\database\employee.gdb;

ODBC

Create an ODBC datasource and put the Oracle driver jar on the class path in jsql.bat Then use driver and URL strings of this form:
CONNECTION sun.jdbc.odbc.JdbcOdbcDriver  jdbc:odbc:[odbc data source]

Oracle

Put the Oracle driver JAR on the class path in jsql.bat or jsql, then use driver and URL strings of this form:
 CONNECTION oracle.jdbc.driver.OracleDriver [Oracle url]
For example:
CONNECTION oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@hostname:1521:ORCL

Other vendors

Consult your documentation for the driver and URL strings you would use to create a JDBC connection. Then use a CONNECTION command of the form:
CONNECTION [driver string] [url string];

User name and password

If you are connecting to a password protected database, you will need to specify a user name and password. The USER command permits you to do this. It takes the form:
 USER [name] [password];
For example:
CONNECTION c:\mydb.jds;
  USER george mypassword;
  SELECT * FROM presidents;
  quit