Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

Progress 9.x
OpenEdge 10.x

SYMPTOM(s):


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

Creating a JAVA stored procedure using the following SQL-92 script:
CREATE PROCEDURE getName( IN from_cust INTEGER)
RESULT (
name CHARACTER(30),
Custnum INTEGER
)
BEGIN
String sname = "";
BigDecimal icustnum = "0";
SQLCursor custcursor = new SQLCursor ("select name,custnum from
pub.customer where custnum = ?");
custcursor.setParam (1, from_cust);
custcursor.open ();
custcursor.fetch ();
while (custcursor.found())
{
sname = (String) custcursor.getValue(1, CHARACTER);
icustnum = (BigDecimal) custcursor.getValue(2, INTEGER);
SQLResultSet.set (1, sname);
SQLResultSet.set (2, icustnum);
SQLResultSet.insert ();
custcursor.fetch(); }
custcursor.close ();
END

CAUSE:

This is a purely JAVA language syntax issue. The error is generated because the java.math.BigDecimal class is referenced without being imported.

FIX:

There are two possible solutions for this specific issue:
1. Import the java.math.BigDecimal class:
DROP PROCEDURE getName1;
CREATE PROCEDURE getName1( IN from_cust INTEGER)
RESULT (
name CHARACTER(30),
Custnum INTEGER
)
IMPORT
import java.math.BigDecimal;
BEGIN
String sname = "";
BigDecimal icustnum = new BigDecimal(0);
SQLCursor custcursor = new SQLCursor ("select name,custnum from
pub.customer where custnum = ?");
custcursor.setParam (1, from_cust);
custcursor.open ();
custcursor.fetch ();
while (custcursor.found())
{
sname = (String) custcursor.getValue(1, CHARACTER);
icustnum = (BigDecimal) custcursor.getValue(2, INTEGER);
SQLResultSet.set (1, sname);
SQLResultSet.set (2, icustnum);
SQLResultSet.insert ();
custcursor.fetch();
}
custcursor.close ();
END

2. Use the java.lang.Integer class instead of the java.math.BigDecimal:
DROP PROCEDURE getName1;
CREATE PROCEDURE getName1( IN from_cust INTEGER)
RESULT (
name CHARACTER(30),
Custnum INTEGER
)
BEGIN
String sname = "";
Integer icustnum = new Integer(0);
SQLCursor custcursor = new SQLCursor ("select name,custnum from
pub.customer where custnum = ?");
custcursor.setParam (1, from_cust);
custcursor.open ();
custcursor.fetch ();
while (custcursor.found())
{
sname = (String) custcursor.getValue(1, CHARACTER);
icustnum = (Integer) custcursor.getValue(2, INTEGER);
SQLResultSet.set (1, sname);
SQLResultSet.set (2, icustnum);
SQLResultSet.insert ();
custcursor.fetch();
}
custcursor.close ();
END