FairCom Java Stored Procedures

Command Line Execution Using Interactive SQL - Windows

This section explains the steps to run the Java SQL Stored Procedures and Triggers samples from the command line on Microsoft Windows using FairCom DB SQL 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 DB SQL stored procedure class is required for accessing the necessary FairCom DB SQL 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 is not already running on your machine, start the server as explained in Starting the FairCom Database Engine.

Open a command prompt window. This can be done by opening the Start menu, typing CMD, and clicking the icon for Command Prompt.
In the command prompt window, move to the drivers\java.sql.storedprocs\tutorials\cmdline directory and execute the BuildTutorials.bat file.
The batch file will use the Interactive SQL tool, isql.exe, 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 first tutorial 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.exe tool and the SQL files located in drivers\java.sql.storedprocs\tutorials. As a short-cut, we first set the ISQL environment variable to the path of the isql.exe tool:

set ISQL=..\..\ctree.drivers\bin\isql.exe

%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: Please refer to the Interactive SQL user guide for the supported command-line switches.

Troubleshooting

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

error in tcp bind 10060
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. Please 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. Please refer to the Java Requirements for Stored Procedures note above.