can't insert DATEs with JDBC
Hi Folks,
I'm trying to get inserts working with DATE literals, and I've failed.
Attached is a small Java file that demonstates the problem. Any help
would be greatly appreciated!
matt
/*
This file demonstrates a problem with inserting DATE literals into
Informix.
Problem: the first set of insert statements in testInsert() are
commented out.
These use the same DATE literal quoting syntax as dbaccess, but throw an
SQLException with error number -1205. The second set of inserts does not
throw
the exception but results in the wrong values being inserted. Here is
what
the program prints for the inserted values:
1899-12-31
1899-12-31
1899-12-31
1899-12-31
1905-05-30
I believe something like the first set of inserts should work, but I
can't
figure out how.
Server: IDS.2000 9.21.UC3-2
OS: RedHat Linux 6.2
PC: Intel Celeron 500MHz 200MB RAM
JDBC Driver: Informix 2.11JC2
*/
import java.sql.*;
public class InformixTest {
// no IVs
static {
try {
String driver = "com.informix.jdbc.IfxDriver";
Class.forName(driver);
} catch(Exception exc) {
System.err.println("error loading driver: " + exc);
}
}
InformixTest() throws SQLException {
String dbName = "testdb";
createDB(dbName);
testInsert(connect(dbName));
System.out.println("Done.");
}
public static void main(String[] args) {
try {
new InformixTest();
} catch(SQLException sqlExc) {
printSQLException("main(): Failed", sqlExc);
sqlExc.printStackTrace();
}
}
Connection connect(String dbName) throws SQLException {
String host = "localhost";
String port = "3000";
String server = "local_tcp";
String url = "jdbc:informix-sqli://" + host + ":" + port +
(dbName == null ? "" : "/" + dbName) + ":INFORMIXSERVER=" +
server + ";";
Connection connection = DriverManager.getConnection(url);
return connection;
}
public void createDB(String dbName) throws SQLException {
createDBProper(dbName); // doesn't create tables
Connection connection = connect(dbName);
createTables(connection);
connection.close();
}
public void createDBProper(String dbName) throws SQLException {
String dbspace = "workdbs";
Connection connection = connect(null); // nb: null dbName -> connect
to server
Statement stmt = connection.createStatement();
String query = "CREATE DATABASE " + dbName + " IN " + dbspace;
stmt.execute(query);
stmt.close();
connection.close();
}
public void createTables(Connection connection) throws SQLException {
StringBuffer sqlSB = new StringBuffer();
sqlSB.append("CREATE TABLE av_4 (\\n");
sqlSB.append(" value DATE NOT NULL);");
Statement stmt = connection.createStatement();
stmt.execute(sqlSB.toString());
stmt.close();
}
public static void printSQLException(String prefix, SQLException
sqlExc) {
String message = prefix + ": SQLState, Severity, Message: " +
sqlExc.getSQLState() + ", " + sqlExc.getErrorCode() + ", " +
sqlExc.getMessage();
System.out.println(message);
if(sqlExc.getNextException() != null)
printSQLException(prefix, sqlExc.getNextException());
}
public void testInsert(Connection connection) throws SQLException {
Statement stmt = connection.createStatement();
/*
// following syntax works in dbaccess, but fails with error -1205:
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('2/23/99')");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('02/23/99')");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('02/23/1999')");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('01/08/1999')");
*/
// following syntax doesn't fail, but inserts wrong values:
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (2/23/99)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/99)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/1999)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/2000)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (" +
new java.sql.Date(System.currentTimeMillis()) + ")");
// print inserted values
ResultSet rs = stmt.executeQuery("SELECT value FROM av_4");
while(rs.next()) {
Date date = rs.getDate(1);
System.out.println("" + date);
}
stmt.close();
}
}
// EOF
You might try setting either DBDATE or GL_DATE environment variables
prior to execution.
DBDATE=MDY4/
HTH
Brett Randall
Matthew Cornell wrote:
>
> Hi Folks,
>
> I'm trying to get inserts working with DATE literals, and I've failed.
> Attached is a small Java file that demonstates the problem. Any help
> would be greatly appreciated!
>
> matt
>
> /*
> This file demonstrates a problem with inserting DATE literals into
> Informix.
>
> Problem: the first set of insert statements in testInsert() are
> commented out.
> These use the same DATE literal quoting syntax as dbaccess, but throw an
> SQLException with error number -1205. The second set of inserts does not
> throw
> the exception but results in the wrong values being inserted. Here is
> what
> the program prints for the inserted values:
>
> 1899-12-31
> 1899-12-31
> 1899-12-31
> 1899-12-31
> 1905-05-30
>
> I believe something like the first set of inserts should work, but I
> can't
> figure out how.
>
> Server: IDS.2000 9.21.UC3-2
>
> OS: RedHat Linux 6.2
>
> PC: Intel Celeron 500MHz 200MB RAM
>
> JDBC Driver: Informix 2.11JC2
>
> */
>
> import java.sql.*;
>
> public class InformixTest {
>
> // no IVs
>
> static {
> try {
> String driver = "com.informix.jdbc.IfxDriver";
> Class.forName(driver);
> } catch(Exception exc) {
> System.err.println("error loading driver: " + exc);
> }
> }
>
> InformixTest() throws SQLException {
> String dbName = "testdb";
> createDB(dbName);
> testInsert(connect(dbName));
> System.out.println("Done.");
> }
>
> public static void main(String[] args) {
> try {
> new InformixTest();
> } catch(SQLException sqlExc) {
> printSQLException("main(): Failed", sqlExc);
> sqlExc.printStackTrace();
> }
> }
>
> Connection connect(String dbName) throws SQLException {
> String host = "localhost";
> String port = "3000";
> String server = "local_tcp";
> String url = "jdbc:informix-sqli://" + host + ":" + port +
> (dbName == null ? "" : "/" + dbName) + ":INFORMIXSERVER=" +
> server + ";";
> Connection connection = DriverManager.getConnection(url);
> return connection;
> }
>
> public void createDB(String dbName) throws SQLException {
> createDBProper(dbName); // doesn't create tables
> Connection connection = connect(dbName);
> createTables(connection);
> connection.close();
> }
>
> public void createDBProper(String dbName) throws SQLException {
> String dbspace = "workdbs";
> Connection connection = connect(null); // nb: null dbName -> connect
> to server
> Statement stmt = connection.createStatement();
> String query = "CREATE DATABASE " + dbName + " IN " + dbspace;
> stmt.execute(query);
> stmt.close();
> connection.close();
> }
>
> public void createTables(Connection connection) throws SQLException {
> StringBuffer sqlSB = new StringBuffer();
> sqlSB.append("CREATE TABLE av_4 (\\n");
> sqlSB.append(" value DATE NOT NULL);");
> Statement stmt = connection.createStatement();
> stmt.execute(sqlSB.toString());
> stmt.close();
> }
>
> public static void printSQLException(String prefix, SQLException
> sqlExc) {
> String message = prefix + ": SQLState, Severity, Message: " +
> sqlExc.getSQLState() + ", " + sqlExc.getErrorCode() + ", " +
> sqlExc.getMessage();
> System.out.println(message);
> if(sqlExc.getNextException() != null)
> printSQLException(prefix, sqlExc.getNextException());
> }
>
> public void testInsert(Connection connection) throws SQLException {
> Statement stmt = connection.createStatement();
> /*
> // following syntax works in dbaccess, but fails with error -1205:
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('2/23/99')");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('02/23/99')");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('02/23/1999')");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('01/08/1999')");
> */
>
> // following syntax doesn't fail, but inserts wrong values:
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (2/23/99)");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/99)");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/1999)");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/2000)");
> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (" +
> new java.sql.Date(System.currentTimeMillis()) + ")");
>
> // print inserted values
> ResultSet rs = stmt.executeQuery("SELECT value FROM av_4");
> while(rs.next()) {
> Date date = rs.getDate(1);
> System.out.println("" + date);
> }
> stmt.close();
> }
>
> }
>
> // EOF
Oh, I forgot: then I think that your first syntax is correct and should
run without error. The second syntax is performing a division and then
adding the result to 31/12/1899.
Life would be so much easier for programmers if dates were always
YYYY/MM/DD or some such format. They sort correctly, etc., yada yada
...
Oh - you might be able to help me understand something. From whence
came the m/d/y format as a date standard? It's like "second most
significant, then least significant, then most significant". I never
could work that one out ... >8-).
Regards
Brett Randall
Brett Randall wrote:
>
> You might try setting either DBDATE or GL_DATE environment variables
> prior to execution.
>
> DBDATE=MDY4/>
> HTH
>
> Brett Randall
>
> Matthew Cornell wrote:
> >
> > Hi Folks,
> >
> > I'm trying to get inserts working with DATE literals, and I've failed.
> > Attached is a small Java file that demonstates the problem. Any help
> > would be greatly appreciated!
> >
> > matt
> >
> > /*
> > This file demonstrates a problem with inserting DATE literals into
> > Informix.
> >
> > Problem: the first set of insert statements in testInsert() are
> > commented out.
> > These use the same DATE literal quoting syntax as dbaccess, but throw an
> > SQLException with error number -1205. The second set of inserts does not
> > throw
> > the exception but results in the wrong values being inserted. Here is
> > what
> > the program prints for the inserted values:
> >
> > 1899-12-31
> > 1899-12-31
> > 1899-12-31
> > 1899-12-31
> > 1905-05-30
> >
> > I believe something like the first set of inserts should work, but I
> > can't
> > figure out how.
> >
> > Server: IDS.2000 9.21.UC3-2
> >
> > OS: RedHat Linux 6.2
> >
> > PC: Intel Celeron 500MHz 200MB RAM
> >
> > JDBC Driver: Informix 2.11JC2
> >
> > */
> >
> > import java.sql.*;
> >
> > public class InformixTest {
> >
> > // no IVs
> >
> > static {
> > try {
> > String driver = "com.informix.jdbc.IfxDriver";
> > Class.forName(driver);
> > } catch(Exception exc) {
> > System.err.println("error loading driver: " + exc);
> > }
> > }
> >
> > InformixTest() throws SQLException {
> > String dbName = "testdb";
> > createDB(dbName);
> > testInsert(connect(dbName));
> > System.out.println("Done.");
> > }
> >
> > public static void main(String[] args) {
> > try {
> > new InformixTest();
> > } catch(SQLException sqlExc) {
> > printSQLException("main(): Failed", sqlExc);
> > sqlExc.printStackTrace();
> > }
> > }
> >
> > Connection connect(String dbName) throws SQLException {
> > String host = "localhost";
> > String port = "3000";
> > String server = "local_tcp";
> > String url = "jdbc:informix-sqli://" + host + ":" + port +
> > (dbName == null ? "" : "/" + dbName) + ":INFORMIXSERVER=" +
> > server + ";";
> > Connection connection = DriverManager.getConnection(url);
> > return connection;
> > }
> >
> > public void createDB(String dbName) throws SQLException {
> > createDBProper(dbName); // doesn't create tables
> > Connection connection = connect(dbName);
> > createTables(connection);
> > connection.close();
> > }
> >
> > public void createDBProper(String dbName) throws SQLException {
> > String dbspace = "workdbs";
> > Connection connection = connect(null); // nb: null dbName -> connect
> > to server
> > Statement stmt = connection.createStatement();
> > String query = "CREATE DATABASE " + dbName + " IN " + dbspace;
> > stmt.execute(query);
> > stmt.close();
> > connection.close();
> > }
> >
> > public void createTables(Connection connection) throws SQLException {
> > StringBuffer sqlSB = new StringBuffer();
> > sqlSB.append("CREATE TABLE av_4 (\\n");
> > sqlSB.append(" value DATE NOT NULL);");
> > Statement stmt = connection.createStatement();
> > stmt.execute(sqlSB.toString());
> > stmt.close();
> > }
> >
> > public static void printSQLException(String prefix, SQLException
> > sqlExc) {
> > String message = prefix + ": SQLState, Severity, Message: " +
> > sqlExc.getSQLState() + ", " + sqlExc.getErrorCode() + ", " +
> > sqlExc.getMessage();
> > System.out.println(message);
> > if(sqlExc.getNextException() != null)
> > printSQLException(prefix, sqlExc.getNextException());
> > }
> >
> > public void testInsert(Connection connection) throws SQLException {
> > Statement stmt = connection.createStatement();
> > /*
> > // following syntax works in dbaccess, but fails with error -1205:
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('2/23/99')");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('02/23/99')");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('02/23/1999')");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ('01/08/1999')");
> > */
> >
> > // following syntax doesn't fail, but inserts wrong values:
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (2/23/99)");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/99)");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/1999)");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (02/23/2000)");
> > stmt.executeUpdate("INSERT INTO av_4 (value) VALUES (" +
> > new java.sql.Date(System.currentTimeMillis()) + ")");
> >
> > // print inserted values
> > ResultSet rs = stmt.executeQuery("SELECT value FROM av_4");
> > while(rs.next()) {
> > Date date = rs.getDate(1);
> > System.out.println("" + date);
> > }
> >
Problem solved - our code wasn't using JDBC-standard DATE syntax. Thanks to all who helped! matt