SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

Handling Java Exceptions
If an SPJ method returns an uncaught Java exception or an uncaught chain of java.sql.
SQLException objects, NonStop SQL/MX converts each Java exception object into an SQL/MX
error condition, and the CALL statement fails. Each SQL/MX error condition contains the message
text associated with one Java exception object.
If an SPJ method catches and handles exceptions itself, those exceptions do not affect SQL/MX
processing.
User-Defined Exceptions
The SQLSTATE values 38001 to 38999 are reserved for you to define your own error conditions
that SPJ methods can return. By coding your SPJ method to throw a java.sql.SQLException
object, you cause the CALL statement to fail with a specific user-defined SQLSTATE value and your
own error message text.
If you define the SQLSTATE to be outside the range of 38001 to 38999, NonStop SQL/MX raises
SQLSTATE 39001, external routine invocation exception.
This example uses the throw statement in the SPJ method named numMonthlyOrders() to raise
a user-defined error condition when an invalid argument value is entered for the month:
public static void numMonthlyOrders(int month,
int[] numOrders)
throws java.sql.SQLException
{
if ( month < 1 || month > 12 )
{
throw new java.sql.SQLException (
"Invalid value for month. "
+ "Retry the CALL statement using a number "
+ "from 1 to 12 to represent the month.", "38001" );
}
....
}
For more information about the numMonthlyOrders() method, see the Sales Class (page 107).
For information about specific SQL/MX errors, see the SQL/MX Messages Manual, which lists the
SQLCODE, SQLSTATE, message text, and cause-effect-recovery information for all SQL/MX errors.
Writing Data to a File or Terminal
Within the SPJ environment, data sent to the System.out and System.err output streams goes
nowhere by default because the SQL/MX UDR server runs without a terminal. You can add code
to your SPJ methods to write data or debugging information to an OSS file. You can also map one
or both of the System.out and System.err streams to a file.
Because terminal devices have OSS path names, you can route output from an SPJ method to a
terminal device if you know the OSS name of that device. The tty command in OSS writes the
full path name of your terminal device to standard output.
This example enables an SPJ method to write a message into a file:
public static void adjustSalary(BigDecimal empNum,
double percent,
BigDecimal[] newSalary)
throws SQLException
{
...
String outputFileName = "/usr/mydir/spj.output";
FileOutputStream fileStream =
new FileOutputStream(outputFileName);
PrintStream printStream =
new PrintStream(fileStream, true);
Handling Java Exceptions 57