FairCom Java Stored Procedures

Command-Line Execution Using Interactive SQL - Linux

This section explains the steps to run the Java SQL Stored Procedures and Triggers samples from a Linux shell using FairCom Interactive SQL.

The Java JDK is required for submitting stored procedures as this requires the Java compiler. The Java runtime engine (JRE) is required to execute stored procedures and triggers. In addition, the FairCom SQL stored procedure class is required for accessing the necessary Java classes, and should be a part of your CLASSPATH. For details see the note titled Java Requirements for Stored Procedures.
If the FairCom Database Engine s not already running on your machine, start the server as explained in Starting the FairCom Database Engine.

Open a Linux shell.
In the shell, move to the drivers/java.sql.storedprocs/tutorials/cmdline directory and execute the following command:
make run

The batch file will use the ISQL tool to run the four tutorial files one-at-a-time. A prompt will ask you to press <Enter> before running each tutorial.

If you see a c-tree error, you can look it up in the Error Code Reference. See the Troubleshooting section below for solutions to common problems.

The output should look similar to the image below when running:

INIT

Creating procedure Define...

Creating procedure Add_Records...

Creating procedure Display_Records...

Creating procedure Delete_Records...

DEFINE

0 records returned

MANAGE

Delete records...

0 records returned

Add records...

0 records returned

Display records...

NUMB NAME

---- ----

1000 Bryan Williams

1001 Michael Jordan

1002 Joshua Brown

1003 Keyon Dooling

4 records returned

Delete records...

0 records returned

DONE

Tutorial #3 ends with "Issue a COMMIT WORK to commit changes and release locks". This is because you cannot start or end (begin or commit) a transaction inside a stored procedure. Note that you can, if you want, wrap a stored procedure call inside a transaction, though. For example:

CALL PROCEDURE1();

ROLLBACK; -- This will remove any change made by PROCEDURE1 (if any)

CALL PROCEDURE2();

COMMIT; -- This will commit any change made by PROCEDURE2 (if any)

Tutorial #4 will say “error(-20148): Trigger Execution Failed” four times and should end with "Nr of errors detected = 4". In this tutorial, we are using triggers to validate the referential integrity of the data we are inserting. If the trigger fails, the record is invalid. We are deliberately inserting four invalid records to demonstrate how the trigger can raise an exception and prevent record insertion.

Executing the Tutorials by Hand

You can run the tutorials individually by hand using the ISQL tool and the SQL files located in drivers/java.sql.storedprocs/tutorials. As a short-cut, we first set the ISQL variable to the path of the ISQL tool:

ISQL=../../ctree.drivers/bin/isql

$ISQL -s SPTTutorial1.sql -u admin -a ADMIN ctreeSQL

$ISQL -s SPTTutorial2.sql -u admin -a ADMIN ctreeSQL

$ISQL -s SPTTutorial3.sql -u admin -a ADMIN ctreeSQL

$ISQL -s SPTTutorial4.sql -u admin -a ADMIN ctreeSQL

Note: Refer to the FairCom Interactive SQL user guide for all of the supported command-line switches.

Troubleshooting

This section lists some of the errors that can occur when running these tutorials:

error in tcp bind 111
error(-20212): Error in Network Daemon

The most common cause of this error is that the FairCom Database Engine is not running on your machine. Remember that the c-tree evaluation license times out after 3 hours, so the server might have exited and needs to be re-started. See Starting the FairCom Database Engine.

Creating procedure Define...
error(-20141): error in compiling the stored procedure

The most common cause of this error is the three Java-related “SETENV” lines in your FairCom ctsrvr.cfg file not being set correctly. Refer to the Java Requirements for Stored Procedures note above.

error(-20160): Java SP/T Feature not supported

This can be caused by the path to ctreeSQLSP.jar being incorrect in the “SETENV CLASSPATH” line in your FairCom ctsrvr.cfg file. Refer to the Java Requirements for Stored Procedures note above.