Consultor Eletrônico

SUMMARY:

This Solution provides the necessary information to IMPORT JAVA classes generated with proxyGen in a JAVA stored procedure. Attempting to generate a JAVA stored procedure via a SQL-92 server may result in the following exception:

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20141
[JDBC Progress Driver]: error in compiling the stored procedure

EXPLANATION:

A stored procedure is a snippet of JAVA code embedded in a CREATE PROCEDURE statement. The JAVA snippet can use all standard JAVA features as well as use Progress SQL-supplied JAVA classes for processing any number of SQL statements.

When you create a stored procedure, the SQL server processes the JAVA code, submits it to the JAVA compiler, receives the results, and stores the results in the database.

The Progress SQL-supplied JAVA class can be generated using proxyGen.

The following code will generate a JAVA stored procedure using the IMPORT statement to import JAVA proxy .class files:

CREATE PROCEDURE testprocedure()
RESULT (COL1 DATE)
IMPORT
import java.util.*;
import testApp.storedProcedure;

BEGIN
Calendar rightNow = Calendar.getInstance();
SQLResultSet.set( 1, new java.sql.Date(rightNow.getTime().getTime()));
SQLResultSet.insert();
END

In the above coding example, 'import testApp.storedProcedure' is the JAVA proxy being imported.
testApp = package name.
storedProcedure = AppObject name corresponding to the JAVA proxy .class file.

SOLUTION:

In order to get past the above SQL exception being generated with this CREATE PROCEDURE code, you must check to ensure that the SQL server processing the JAVA code has the correct CLASSPATH settings.
Using the Progress SQL Explorer these settings would correspond to the PROGRESSCP environmental variable.

What's required is the path to the directory above the package name (testApp) and the Progress .zip files (o4glrt.zip and o4glrt2.zip).


References to Written Documentation:

Progress SQL-92 Guide and Reference - "JAVA Stored Procedures and Triggers"

Progress Open Client Developers Guide

Progress Knowledge Base Solutions:
20578, "How to find compilation errors in a Stored Procedure"
20575, "Error in Compiling the Stored Procedure"