Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

SQL: 'A server crash is likely' error with SUM() and DECODE() in query.

[DataDirect][OpenEdge JDBC Driver]A server crash is likely.

A ''server crash is likely'' error is generated when an SQL Query invoking the SUM() and DECODE() functions is executed as a JDBC PreparedStatement object.

SQL-92 trace file contents:
: PANIC - DTM sql_fldinfo_t::get_default_precision unknown dhtype: 101 PID 9233

Error is generated executing the following JAVA class object:
import java.sql.*;
public class W707130079 {
private static final String DRIVER = "com.ddtek.jdbc.openedge.OpenEdgeDriver";
private static final String DB_URL =
"jdbc:datadirect:openedge://localhost:23456;databaseName=W705180085;" +
"SpyAttributes=(log=(file)spyTestStatement.log;logTName=yes;timestamp=yes)";
private static final String DB_USER = "yshanshi";
private static final String DB_PASS = "progress";
private String sql = "SELECT \n" +
"SUM( \n" +
"DECODE(pro_element(oa_tax_sw,1,1),0,CAST(pro_element(oa_tax_amt,1,1) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,2,2),0,CAST(pro_element(oa_tax_amt,2,2) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,3,3),0,CAST(pro_element(oa_tax_amt,3,3) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,4,4),0,CAST(pro_element(oa_tax_amt,4,4) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,5,5),0,CAST(pro_element(oa_tax_amt,5,5) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,6,6),0,CAST(pro_element(oa_tax_amt,6,6) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,7,7),0,CAST(pro_element(oa_tax_amt,7,7) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,8,8),0,CAST(pro_element(oa_tax_amt,8,8) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,9,9),0,CAST(pro_element(oa_tax_amt,9,9) AS DECIMAL(9,2)),0.00) + \n" +
"DECODE(pro_element(oa_tax_sw,10,10),0,CAST(pro_element( oa_tax_amt,10,10) AS DECIMAL(9,2)),0.00) \n" +
") sum_tax_amt \n" +
"FROM v_oaei inv \n" +
"WHERE inv.sa_company_nbr = ? \n" +
"AND inv.oa_acct_nbr = ? \n" +
"AND inv.oa_inv_stat = 'O' \n" +
"AND inv.oa_tax_amt <> '0;0;0;0;0;0;0;0;0;0' \n" +
"AND inv.sa_jrl_period <= ? ";
private Connection connection = null;
private Connection getConnection() {
if (connection == null) {
try {
// Load the driver
Class.forName(DRIVER);
connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
DatabaseMetaData meta = connection.getMetaData();
// Gets driver info:
System.out.println("\n=============\nDatabase Product Name is ... " + meta.getDatabaseProductName());
System.out.println("\nDatabase Product Version is " + meta.getDatabaseProductVersion());
System.out.println("\n=============\nJDBC Driver Name is ........ " + meta.getDriverName());
&nbsp.; System.out.println("\nJDBC Driver Version is ..... " + meta.getDriverVersion());
System.out.println("\nJDBC Driver Major Version is " + meta.getDriverMajorVersion());
System.out.println("\nJDBC Driver Minor Version is " + meta.getDriverMinorVersion());
System.out.println("\n=============");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
return connection;
}
public void runTests() {
try {
runTest1();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null)
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void runTest1() throws SQLException {
Connection con = getConnection();
PreparedStatement ps = null;
Integer cono = new Integer(36533);
Long account = new Long(218);
Integer jrlPeriod = new Integer(200705);
try {
// Critical to have auto commit false since we want the deletes scoped to a transaction.
con.setAutoCommit(false);
ps = con.prepareStatement(sql);
ps.setInt(1, cono.intValue());
ps.setLong(2, account.longValue());
ps.setInt(3, jrlPeriod.intValue());
ResultSet rs = null;
rs = ps.executeQuery();
System.out.println("Rolling back after pausing 10 seconds." + rs.getRow());
&nb.sp; Thread.sleep(10000);
// roll back the transaction so we can test it again and again.
con.rollback();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
/* ignored */
}
}
}
public static void main(String[] args) {
W707130079 o = new W707130079();
o.runTests();
}
}
.

FACT(s) (Environment):

OpenEdge 10.1B01 Service Pack
Linux Intel

CAUSE:

Bug# OE00153907

CAUSE:

Problem is caused by the Describe Param method on the complex SUM of a complex DECODE involving PRO_ELEMENT() expressions. Specifically, the issue is caused by scalar function DECODE, which fails to return a type and length for the result in some circumstances.

FIX:

Upgrade to OpenEdge 10.1B03 or later