SQL*Magic : Super*SQL
|SQL*MagicSuper*SQLCase StudiesResourcesTechnology ChoicesSQL StandardLinks

Super*SQL User Manual

Java Runtime Environment:

Before you run Super*SQL you will need to have a Java Runtime Environment installed. Detailed instructions on how to install the JRE can be found at java.sun.com.

Required Software:

You will need SuperSQL.jar and tinySQL.jar as a minimum. For Oracle connections you will also need classes111.zip or classes12.zip. For MySQL connections you will need a current mm*.jar. For other JDBC connections you will need the corresponding jar file.

Launching Super*SQL

There are several different ways to launch the Super*SQL program, depending upon the hardware and OS platform you are using.

Windows 95/98/200/NT/XP: You can launch Super*SQL from a Command Prompt window provided that you have the proper CLASSPATH set up. An example follows:

set CLASSPATH=SuperSQL.jar;tinySQL.jar
java SuperSQL

If you put those two commands into a file called SuperSQL.bat you could then type SuperSQL to launch the program.

The problem with that approach is that you always have to be in a particular directory to launch the program. A better approach is to place the jar files in a directory (ex: C:\SuperSQL), and then create environment variables and a desktop shortcut. Under the Control Panel double click on the System icon, open the Environment tab, and add a new variable called CLASSPATH with the value C:\SuperSQL\SuperSQL.jar;C:\SuperSQL\tinySQL.jar, as shown here. You should also edit the variable PATH and add the directory where your JRE is installed. You can then set up a shortcut as shown here

Unix/Linux/Mac OS X: Super*SQL can be launched from the command line provided that the CLASSPATH has been set up properly and the java executable is in the user's path. An example is shown below (note the separators between jar files are colons, not semi-colons which are used in Windows environments):

CLASSPATH=SuperSQL.jar:tinySQL.jar
export CLASSPATH
java SuperSQL

Mac OS 9.x: Super*SQL is launched by dragging the jar file onto the JBindery icon. This will bring up a screen where you can add any other JDBC jar files to the classpath.

The Registration Screen:

If you are using an unregistered copy of Super*SQL or your registration key has expired, you will first see the registration screen. If you have a new key, enter the exact Registrant name and the key value and press the Register button. Otherwise press the "I will continue using the unregistered copy" button.

The Connection Screen:

You will then see the connection screen. Choose a connection type or specific connection from the drop-down list. You will then be asked to provide all the connection parameters for the particular type of database that you want to connect to. These parameters will be saved as a ConnectionURL so that you will not have to enter them again in subsequent sessions.

Note: If you choose "Provide connection URL and Driver String" youwill have to provide both the complete connection URL, and the corresponding driver string for the connection. The driver string will be something like Org.as220.tinySQL.dbfFileDriver. This option should allow youto connect to databases that are otherwise unknown to Super*SQL. Remember to have the appropriate JDBC jar file in the CLASSPATH.

Once you have entered the required parameters, click on the Connect button. A message in the lower part of the screen will indicate whether or not the connection was successful.

The SQL Command Screen:

You now have a direct connection to your database. You can enter any valid SQL statement in the upper window, terminated by a semi-colon. Pressing the Execute Command button will cause the command to be processed and the results will be shown in the lower part of the screen.

In addition to native SQL commands, Super*SQL supports the following additional commands.

SET HEADING ON<OFF>
SET PAGESIZE <number of lines before headings are repeated>
SET TERMOUT ON<OFF> - controls screen output
SET FORMAT NORMAL<HTML>

SPOOL filename - spools output to the specified file. This will suppress output to the screen unless you also SET TERMOUT ON.

START filename - this command reads SQL statements from a command file. Note that the command file can contain substitution variables which are supplied with the start file name. For example, if the file mycommands.sql contained the following commands:

SET FORMAT HTML;
SPOOL webpage.html;
SELECT &1,&2 FROM &3;
EXIT;

From within Super*SQL, typing in START mycommands.sql COLUMN1 COLUMN2 TABLE1 would result in a report listing the values for the columns COLUMN1 and COLUMN2 selected from TABLE1 in a webpage format.

Running in True "Batch" Mode:

Super*SQL can be run in batch mode, which would allow it to be used in cron scripts or automated procedures to update web pages or generate reports. When you create a connection to a database using the interactive screens, a nickname is stored for each connection. For Oracle connections this is the Oracle SID. For MySQL connections this is the database name. For tinySQL connections the user is asked to provide a nickname. Super*SQL can then be launched with a command such as the following;

java SuperSQL userid/[email protected] mycommands.sql COLUMN1 COLUMN2 TABLE1

The result in this case would be a report written to the file webpage.html