SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Writing SPJ Methods
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
3-11
User-Defined Exceptions
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
on page A-3.
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