• About
• News
• Quick Start
• Bugs
• FAQ
• Purchase
• Tutorial
• Top Ten

Super*SQL Tutorial - for Beta Version 1.01 released May 11, 2006

TINYSQL database has 0 tables.

List of tables for TINYSQL database:
MUSIC_COLLECTIONS: 77
MUSIC_COLLECTION_TRACKS: 479
MUSIC_LOCATIONS: 5
MUSIC_COLLECTION_LOCS: 22
MUSIC_EVENTS: 398
MUSIC_TRACKS: 217
MUSIC_ARTISTS: 4
MUSIC_LYRICS: 5585

Using the Find and Replace Functions

The tutorial database contains information on all the tracks and most of the collections produced by the Beatles.  You can test the Super*SQL Find facility by doing  the following searches. Choose the menu item Find/Replace > Find any column = and enter Let It Be when prompted to. The following output will be produced.

2xMUSIC_COLLECTIONS.COLN_NAME Let It Be
MUSIC_TRACKS.TRACK_NAME Let It Be
10xMUSIC_LYRICS.LYRIC Let it be

13 matches found.

Note that Super*SQL found two collections with the exact name "Let It Be".  That is because there was a single as well as an album.

Choose the menu item Find/Replace > Find any column like with the value kite will produce the following output.

MUSIC_TRACKS:TRACK_NAME:Being for the Benefit of Mr. Kite
MUSIC_LYRICS:LYRIC:For the benefit of Mr. Kite
MUSIC_LYRICS:LYRIC:As Mr. Kite flies through the ring don't be late
MUSIC_LYRICS:LYRIC:And tonight Mr. Kite is topping the bill 
4 matches found.

The Super*SQL replace function can be used to generate the SQL statements required to change all occurrences of a particular string in the database. Note: This function will not actually make any changes to the database. It is recommended that you spool the output to a file and review the changes that will be made. Once you have determined that the changes are as you want them, use the menu item Start/Load and browse for the file containing the SQL commands to implement the changes.

For example, choose Find/Replace > Replace any column like and supply the strings Let It Be and Let Sleeping Dogs Lie when prompted to. In the output panel you will see all the SQL statements that are required to implement the change.

Running Interactive Queries

You can now run some SQL queries against the database. To get information about any of the tables, you can use the describe command. For example, the command describe music_artists; will produce the following output.

Name                             Null?    Type
--------------------------------------- -----------------------------
ARTST_ID                                INT(10)                 XOPEN[4]
ARTST_NAME                              CHAR(69)                XOPEN[1]

This is a list of the columns and their types.  The last column lists the XOPEN type that is used internally by JDBC to identify the column type. 

A simple query is shown below.

select COLN_NAME from MUSIC_COLLECTIONS WHERE COLN_ID=12;

The result will be Help. 

Note that successful queries are always pasted into the history pane automatically so that they can be cut and pasted in the future.  Another way to save and retrieve complex scripts is through the use of script files, as described below.

Running Interactive Scripts

You can now run some of the scripts from the TestPak. The music_recording_dates.sql script demonstrates the ability of Super*SQL to generate web formatted reports.  If you open up this file in an editor you will notice the following lines (not every line is reproduced below);

spool music_recording_dates.html;
set format HTML;
column TRACK_NAME HEADING Song Title;
HTML <IMG SRC="beatles.gif">;
HTML <h3>Recording Dates</h3>;
spool off;

The first line indicates that output will be spooled to a file rather than to the screen. As a result, you will not see any of the output while the script is running. When it has completed, you can look at the file by opening it up in a Web browser.

The next line indicates that the output should be HTML, the language used in Web pages.  The next line substitutes the column heading "Song Title" for the column name TRACK_NAME that would be used as the default column heading in the report. The next lines demonstrate that raw HTML commands can be included at any point in your script to enhance the appearance of the output.  Super*SQL also supports Cascading Style Sheets, which are discussed much more extensively in the FAQ. The last line stops spooling to the specified file and closes it.

To actually run the script, select the menu item File > Start/Load and browse for the file music_recording_dates.sql in the testpakdir directory.

The music_recording_dates.sql script can take a minute or so to run, depending upon the type of computer you have.

The music_album_tracks.sql script will produce a listing of all albums and tracks for the first two collections that are in the database (note that using Super*SQL this query can take several minutes depending upon the computer you have). There are several features of Super*SQL demonstrated by this script. If you open up the script file in an editor you see the following command;

COLUMN COLN_NAME HEADING 'Album Name'

As in the music_recording_dates.sql script, this command replaces the default heading for the column COLN_NAME. Another command in this file is the following;

BREAK ON COLN_NAME NODUPS ON COLN_NAME SKIP 1;

This command causes two actions to take place when a "break" occurs on the column COLN_NAME. A "break" occurs when the column changes value. The command specifies that duplicate values for the column will NOT be repeated. This command produces a more visually appealing and more easily understood output. The second action is that an additional blank line is output before the line which has a changed value for COLN_NAME.

Finally, the following command is used to produce subtotals.

COMPUTE ON COLN_NAME SUM TRACK_LEN;

This command causes the sum of the track lengths to be displayed at the end of each album (that is, every time the value for COLN_NAME changes). This will be the total playing time for each album.

The music_albums_with.sql script demonstrates the use of command line parameters. The script accepts the name of a track as a command line argument. Select File Menu > Start/Load to start the script file and enter Get Back when prompted.

Alternatively, you can start it by typing in the command;

start testpakdir\music_albums_with 'Get Back'; Windows
start testpakdir/music_albums_with 'Get Back'; Linux/Unix

The command would produce the following output.

Starting/loading file D:\supersql\testpak\music_albums_with.sql
Get Back                         Let It Be                                                    
Get Back                         The Beatles - 1                                              
Get Back                         Let It Be - Naked                                            
Get Back                         Get Back                                                     

4 rows selected

 
Connecting to other databases (Oracle, MySQL, SQL Server, etc.)

You must close down Super*SQL and start it up again to create a connection to a different database system (this will change in the commercial release). Choose "New Connection" from the drop-down menu, for the type of database that you want to connect to. Supply all the required parameters, including a short nickname (no blanks or special characters) which you can use to refer to the database connection later with.

In order to make the physical connection, Super*SQL will require access to the applicable JDBC Driver files for your particular database system. Super*SQL has been extensively tested with the following drivers;

Super*SQL should work with any later version of these drivers, or with other 3rd party drivers. However, there are differences in the implementation of drivers that can sometimes cause problems. If you run into anything weird, send a report to [email protected].

These JDBC driver files must be in the current CLASSPATH in order for Super*SQL to find them. Check out the quickstart section for tips on setting your classpath on different platforms.

 
Running Batch Scripts (not applicable to Mac OS 9.x)

Once you have established a connection to a particular database using the Super*SQL interactive interface, you can then connect to that database again using only the user ID, password, and nickname (or just the nickname if the database does not require a user ID and password). For example, while connected to your own database, try reconnecting to the TESTPAK database using the command;

CONNECT TESTPAK;

You can confirm that the connection has been changed successfully by using the Show Tables menu command. Connect back to your personal database using the appropriate user ID, password, and nickname with the following syntax (note that a list of nicknames can be displayed using the Show > Nicknames menu);

CONNECT userid/password@NICKNAME;

This is exactly the technique used to run scripts in batch mode. Exit the Super*SQL interactive command interface. From the command line you can then use the following command to execute the music_albums_with script.

java SuperSQL userID/password@NICKNAME testpakdir/music_albums_with.sql "Get Back"

Moving data between databases

Create an unload file containing all of the information in the music database by running the script music_unload_tables.sql from the TestPak. You can do this by typing in the following commands;

spool somedatafile.dat;start testpakdir/music_unload_tables.sql;spool off;

Alternatively, you can use the File menu to set your spool file, start the file music_unload_tables.sql, and set the spool off.

 
The result of the unload should be a datafile containing 6,815 lines and ending with the string ENDOFDATA.

Exit Super*SQL and start the program up again. Create a connection to a new database system (Oracle, MySQL, Access, etc.) by choosing the database type and entering the required parameters. After you click on the Connect to Database button you should see some diagnostic messages, the last being a count of the tables in the database you are connected to.

If you have not included the appropriate JDBC driver file in your classpath you will get an error which should be fairly easy to understand.  For hints on setting your classpath, see the Quick Start guide.

Assuming you have CREATE TABLE privileges on your database, you can now load the data file that you created earlier. Its probably always a good idea to run the music_drop_tables.sql script first to make sure that your database does not already have any of the tables (perhaps from a previous test run). Then issue the following command.

load somedatafile.dat;

To confirm that you have loaded all of the tables in the test dataset, start the music_count_rows.sql script from the TestPak. The output should be as shown below (which is very similar to that produced from the tinySQL database with  the Show > Row Counts menu):

MUSIC_ARTISTS: 4
MUSIC_COLLECTIONS: 77
MUSIC_COLLECTION_TRACKS: 479
MUSIC_TRACKS: 217
MUSIC_LOCATIONS: 5
MUSIC_COLLECTION_LOCS: 22
MUSIC_EVENTS: 318
MUSIC_LYRICS: 5585

At this point it would be useful to check the column types created by the load operation. Use the commands DESCRIBE MUSIC_TRACKS; and DESCRIBE MUSIC_COLLECTIONS; to confirm that the columns TRACK_LEN and US_SALES have a numeric column type capable of storing decimal numbers.

Using the Virtual Clipboard

One of the design goals of Super*SQL is to provide developers with an easy way to combine data from several databases simultaneously. This can be done by writing Java programs but in many cases this is not feasable. Super*SQL will include a procedural scripting language that will allow this type of cross database access in the near future. For the initial release a limited cross database query capability is available through the use of the virtual clipboard.

The virtual clipboard can be populated using a query from one database, then used in queries against a second database. To see how this works, connect to a database that you have CREATE TABLE privileges on (Oracle, MySQL, Access, SQL Serever, etc.) Use the menu item File > Start/Load and browse for the file music_create_favourites.sql in the testpak. This script will create a table with a single column named TRACK_NAME and will insert 4 rows. Select the menu item Cipboard > Insert and enter the statement SELECT TRACK_NAME FROM MUSIC_FAVOURITES when prompted to. You will see a message indicating that 4 elements have been added to the clipboard. You can view the contents of the clipboard at any time by using the menu item Clipboard > List Contents .

Connect to the Music database with the command CONNECT TPAK;. You can now use the contents of the clipboard in a new query. The script music_clipboard.sql provides an example.

This concludes the tutorial. If you have any questions or comments regarding this tutorial or Super*SQL please send a message to [email protected].

top