Insert of Date Values Using a Java Program and Dg4Ifmx 11.1.0.6 Causes Intermittent Ora-28500 Error Invalid Datetime Format

(Doc ID 1336878.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Database Gateway for Informix - Version 11.1.0.6 to 11.2.0.1 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms



Inserting date values into Informix using a Java program and Database Gateway for Informix (Dg4Ifmx) fails with error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC Informix Wire Protocol driver]Invalid datetime format. Error in parameter 5.
ORA-02063: preceding 2 lines from DG4IFMX


The Java program inserts into a view created using the 11.1.0.6 Dg4Ifmx -

import java.io.*;
import java.util.*;
import java.sql.*;

public class A221122 {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@hostname:11106:v111";
String username = "dg4ifmx";
String password = "mypwd";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
java.sql.Timestamp df = java.sql.Timestamp.valueOf("2011-02-23 08:15:23");
java.sql.Date tmsd = new java.sql.Date(0);
String cadena = df.toString();
Connection con=null;
PreparedStatement estament = null;
try {
con=getConnection();
con.setAutoCommit(false);
String sql1 = "insert into prova_data values(?,?,?,?,?,?,?)";
for (int idx=100;idx<110;idx++) {
try {
estament = con.prepareStatement(sql1);
estament.setInt(1,idx);
estament.setDate(2,tmsd.valueOf("2011-02-15"));
estament.setInt(3,5000);
estament.setInt(4,100);
estament.setTimestamp(5,df);
estament.setString(6,"ipons");
estament.setString(7,cadena);
estament.executeUpdate();
con.commit();
estament.close();
estament = null;
System.out.println("Iteracio: "+idx+" -> OK");
} catch (SQLException ex){
System.out.println("iteracio: "+idx+" -> KO ->"+ex.getMessage());
con.rollback();
estament = null;
}
}
con.close();
} catch(Exception e){
e.printStackTrace();
}
}
}

and this fails when executed  -

$ $ORACLE_HOME/jdk/bin/java A22
Iteracio: 100 -> OK
Iteracio: 101 -> OK
iteracio: 102 -> KO ->ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC Informix Wire Protocol driver]Invalid datetime format. Error in parameter 5.
ORA-02063: preceding 2 lines from DG4IFMX

Iteracio: 103 -> OK
Iteracio: 104 -> OK
Iteracio: 105 -> OK
Iteracio: 106 -> OK
iteracio: 107 -> KO ->ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC Informix Wire Protocol driver]Invalid datetime format. Error in parameter 5.
ORA-02063: preceding 2 lines from DG4IFMX

Iteracio: 108 -> OK
Iteracio: 109 -> OK

If the view is replaced by one created using the 11.2.0.2 Dg4Ifmx then the error does not happen -

String sql1 = "insert into pro_isidrems_1122 values(?,?,?,?,?,?,?)";

$ $ORACLE_HOME/jdk/bin/java A221122
Iteracio: 100 -> OK
Iteracio: 101 -> OK
Iteracio: 102 -> OK
Iteracio: 103 -> OK
Iteracio: 104 -> OK
Iteracio: 105 -> OK
Iteracio: 106 -> OK
Iteracio: 107 -> OK
Iteracio: 108 -> OK
Iteracio: 109 -> OK
$

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms