SQLJC

• 1. What is sqljc?
• 2. Requeriments.
• 3. Install.
• 4. Configuration.
• 5. Start.

1. WHAT IS SQLJC ?

sqljc is a java program to connect through JDBC to any database engine (with JDBC support) to make simple queries and to execute sql files.

sqljc can execute commands defined by user. These commands are aliases to sqljc internal commands. User can define these aliases in the configuration file. An internal parser provides a simple mechanism to substitute variables in the command; those variables are passed at command invocation time (at execution time).

sqljc can execute SQL-ANSI sql files.

sqljc can load JDBC drivers dynamically.

Users can define aliases for database connection parameters (driver, url, user and password) and set those parameters at the same time by selecting the connection alias. Those aliases/parameters are defined in the configuration file.

sqljc provides a debugger tool to log any user request and result into a plain text file. File and debugger level are set in the configuration file.

2. REQUERIMENTS

• JDK 1.2.2 or higher.
• Suitables JDBCs drivers.
• sqljc.jar jar file.
• sqljc.conf file.
• commands.conf file.

3. INSTALL

You can find sqljc package at http://sourceforge.net/projects/sqljc/. Last version is 1.0.10. (Go to download section.)

1. Unpack sqljcx-y-z.tgz (or sqljcx-y-z.zip)

2. Modify sqljc (sqljc.bat for DOS/WINDOWS) batch file:

• Add to classpath argument any JDBC driver to use.
• Modify path to sqljc.jar file

Modify sqljc.conf file (to add aliases to database connections).

Modify commands.conf file (you shouldn't do that if you don't know what are you doing.)

4. CONFIGURATION

Configuration is based on two files: sqljc.conf y commands.conf.

These names are by default. You can choose any name you want.

sqljc program will search for sqljc.conf file at the same place you launch the program. If you want to specify a new configuration file, you must use the -f option:

sqljc -fpath_to_configuration_file.

sqljc.bat and sqljc are shell scripts to launch java VM with the current class path and directory. It appends sqljc.jar file to CLASSPATH. It looks like:

(UNIX: sqljc file)
java -classpath .:./sqljc.jar:$CLASSPATH com.jcss.sql.sqljc

(WINDOWS: sqljc.bat file)
java -classpath .;.\sqljc.jar;%CLASSPATH% com.jcss.sql.sqljc

commands.conf file is referenced at sqljc.conf file by commands configuration variable.

4.1. Modifications at sqljc.conf file.

There are several configuration variables:

• version: File configuration version. It must be the same as the program version. (You should use a suitable configuration file.)
• commands: Absolute path to commands.conf file.
• deplevel: Depuration level (0 minimun, 1 by default).
• logfile: Absolute path to log file.

Aliases for database connections can be defined using conn prefix. For each alias, you should define drv and url (usr and pwd are optional).

• conn.alias_name.drv: Database connection driver.
• conn.alias_name.url: Database connection url.
• conn.alias_name.usr: Database connection user login.
• conn.alias_name.pwd: Database connection user password.

You can define another aliases, for example, myConn1:

conn.myConn1.drv=oracle.jdbc.driver.OracleDriver
conn.myConn1.url=jdbc:oracle:thin:@host:port:sid
conn.myConn1.usr=user1
conn.myConn1.pwd=pass1

When sqljc program starts, Default alias is searched.

4.2. Modifications at commands.conf file.

You don't need to modify this file. And you shouldn't do that unless you know what are you doing.

(See commands.conf file comments.)

This file creates aliases for program actions. A program action is, for example, EXEC_FILE (open SQL file and execute sentences). An alias for this action is file. These aliases are called commands. User types commands (aliases). There are two kind of commands: commands to the program (open, close, file, ...) and SQL statements (select, insert, ...).

Tipically, an alias is defined by:

<alias_name>
<exec>(execution description)</exec>
<action>(a valid identifier for the program)</action>
<description>(description)</description>
</alias_name>

For example, for alias file:

<file>
<exec>[path to sql file]</exec>
<action>EXEC_FILE</action>
<description>Execute a sql file.</description>
</file>

Current actions are specified in CommandManager class documentation.

Finally, you can define an alias for an alias:

Example: ? is an alias for help

<?>
<alias>help</alias>
</?>

help is:

<help>
<exec>[command]</exec>
<description>Describe a command if supplied. If not, the command list
page will be shown.</description>
<action>SHOW_COMMAND</action>
</help>

You can define aliases at any point in configuration file.

4.3. Command line Arguments.

• -l: path to log file
• -n: depuration level
• -m: path to commands file
• -f: path to configuration file
• -e: path to commands file to execute (don't exit)
• -x: path to commands file to execute (exit after execution)
• -c: database connection alias (specify a database alias)

Command line arguments overrides configuration variables.

4.4. Prompt configuration.

Prompt line is configurable at sqljc.conf file and on-line by means of prompt command. There are several keys you can use to define your prompt, when you use an alias, sqljc will show to you the current value for it:

• $conn: shows current connection database alias.
• $url: shows current database url.
• $user: shows current database user.
• $CR: shows a carriage return.

By default, current connection, url and user database parameters are shown.

4.5. Autocommit on/off

5. START

Run sqljc (sqljc.bat in DOS/WINDOWS).

5.1. To make a data base connection.

There are two ways to set all the data base connection parameters.

First one, you can select an alias from sqljc.conf file. An alias define a driver, an url and, not mandatory, an user and password.

For example, these lines define a connection alias "Default":

conn.Default.drv=oracle.jdbc.driver.OracleDriver
conn.Default.url=jdbc:oracle:thin:@192.16.16.16:1521:oracle_sid
conn.Default.usr=userDB
conn.Default.pwd=passwordDB

When "Default" alias is selected (typing conn Default), it means that driver, url, user and passwd program variables are set to:

• driver = oracle.jdbc.driver.OracleDriver
• url = jdbc:oracle:thin:@192.16.16.16:1521:oracle_sid
• user = userDB
• passwd = passwordDB

It's just a way to set parameters at the same time.

The other way is to set driver, url, user and passwd program variables manually. You have to type:

driver oracle.jdbc.driver.OracleDriver 
url jdbc:oracle:thin:@192.16.16.16:1521:oracle_sid 
user userDB 
passwd passwordDB 

It assigns:

• driver = oracle.jdbc.driver.OracleDriver
• url = jdbc:oracle:thin:@192.16.16.16:1521:oracle_sid
• user = userDB
• passwd = passwordDB

After that, you can connect to the data base using connect command.

5.2. Just to connect!!!

5.2.1. Modify sqljc.conf file, adding:

conn.myconex1.drv=oracle.jdbc.driver.OracleDriver
conn.myconex1.url=jdbc:oracle:thin:@192.16.16.16:1521:oracle_sid
conn.myconex1.usr=userDB
conn.myconex1.pwd=passwordDB

5.2.2. Run sqljc

type: sqljc <CR>

5.2.3. Select connection alias

type: conn myconex1 <CR>

5.2.4. Connect

type: connect <CR>

5.3. Execute commands from a file

Write in a file your commands. SQL sentences can be written in different lines. You can write line comments starting with '#':

Example:

file name: report.exec

It contains:

#select connection alias
conn myConn

#connect
connect

#make query
select
count(*)
from xx;

####end

To execute this file, run sqljc with -e or -x option argument.

sqljc -ereport.exec (sqljc will remain running)

sqljc -xreport.exec (sqljc will end)

Note: Execution of files cannot be recursive.