Re: can't insert DATEs with JDBC
This is a multi-part message in MIME format.
--------------F3ED3F66EC0A14ED0E082AEF
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I fixed it for you.
The date format in JDBC is unique as I remembered:
eg 1999-08-23
Note that I changed the server && port etcaetera but it works.
About system.currentTimeMillis (don't know) - I suppose that
you have to format with DateFormat the date in order to work.
Anyway if you prepare your statements, you will not be required
to format the date. ....
HTH,
Radu
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
--------------F3ED3F66EC0A14ED0E082AEF
Content-Type: text/plain; charset=us-ascii;
name="InformixTest.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="InformixTest.java"
import java.sql.*;
public class InformixTest {
// no IVs
static {
try {
String driver = "com.informix.jdbc.IfxDriver";
Class.forName(driver);
} catch(Exce