FairCom Java & .NET Stored Procedures

Debugging Stored Procedures

Compiling Errors

If there’s a Java compilation error, FairCom DB SQL returns the error at create time and does not create the procedure.

Note: If the compilation of the procedure fails, FairCom DB SQL returns only the first error to the calling application.

The following example invokes an ISQL script that attempts to create a procedure that fails and generates a Java compilation error.

Example

C:\example_scripts>type type_mismatch.sql

CREATE PROCEDURE error_proc()

BEGIN

Double dbl_val;

Integer int_val = new Integer(1234);

dbl_val = int_val;

END

C:\example_scripts>isql -s type_mismatch.sql testdb

error(-20141): error in compiling the stored procedure

:03: Incompatible type for =. Can't convert java.lang.Integer to java.lang.Double.

At run time, FairCom DB SQL creates a log file, sql_server.log.

Run Time

Custom message logging can be added with the log() and err() methods which write a character-string message to the SQL log file, sql_server.log.

Note: The log() method is not logged to sql_server.log by default and is intended for development use. Calling log() includes internal details useful in procedure design and should be disabled in production. Enable Java debugging with the FairCom DB SQL debugging flags configuration option:

SETENV TPESQLDBG=NNNNNNNNNNYN

The SQL Explorer tool has an -adv command-line switch to enable setting specific debug flags and will be found on the File menu option as Debug Flags. Check the Java checkbox and click Set.

You can include the values of variables or return values of methods in the string using the standard Java concatenation operator (+) as shown in the following example.

Example

SQLCursor select_syscalctable = new SQLCursor (

"SELECT fld FROM ADMIN.syscalctable ");

select_syscalctable.open();

select_syscalctable.fetch();

err ("Any records? Found returned " + select_syscalctable.found());

The err() invocation in the previous example writes the following line to the sql_server.log file:

Any records? Found returned true

V11 Changes to Stored Procedure Server-side Debugging

In V11 and later, server-side stored procedure execution logging can be turned on and off using the TPESQLDBG environment variable. TPESQLDBG is an array of 'Y'/'N' characters that determine which debug options are enabled.

Prior to this revision, this was achieved by setting the 11th element (offset 10, java_debug) in the TPESQLDBG environment variable. The logging produced in this way is quite verbose and included information that was useful only to FairCom Technicians.

The interpretation of TPESQLDBG (and other methods setting the same debug features) has been modified as follows:

TPESQLDBG (which was an array of 12 'Y'/'N' characters) has been expanded to 13 elements.
The element at offset 12 is a new element named stp_logging that controls the log() method used in stored procedures to generate log messages.

Note that activating element #11 (java_debug) activates internal logging and log() method for ANY stored procedure language, not just Java.