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.
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:
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.)
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.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.
There are several configuration variables:
Aliases for database connections can be defined using conn prefix. For each alias, you should define drv and url (usr and pwd are optional).
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.
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> <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.
Command line arguments overrides configuration variables.
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:
By default, current connection, url and user database parameters are shown.
By default, autocommit is set on. It means every sql sentence will modify database data after you write it. You can switch it off using begin command to perform sql statements without data modifications until you use commit or rollback commands.
Run sqljc (sqljc.bat in DOS/WINDOWS).
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:@220.127.116.11: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:
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:
url jdbc:oracle:thin:@18.104.22.168:1521:oracle_sid user userDB passwd passwordDB
After that, you can connect to the data base using connect command.
conn.myconex1.drv=oracle.jdbc.driver.OracleDriver conn.myconex1.url=jdbc:oracle:thin:@22.214.171.124:1521:oracle_sid conn.myconex1.usr=userDB conn.myconex1.pwd=passwordDB
type: sqljc <CR>
type: conn myconex1 <CR>
type: connect <CR>
Write in a file your commands. SQL sentences can be written in different lines. You can write line comments starting with '#':
file name: report.exec
#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.
sqljc is under GNU General Public License:
Copyright (C) 2000 Juan Carlos Segovia Serrato
This software is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.
This software is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
Bug fixes, suggestions and comments should be sent to:
Juan Carlos Segovia (firstname.lastname@example.org)