Unix Technical Forum

Can't retreive Date it gives me the same exception

This is a discussion on Can't retreive Date it gives me the same exception within the Informix forums, part of the Database Server Software category; --> Hi Guys i am getting the same error can anybody help..... ----------------------------------------------------------------------------------------------------- Hi Folks, I'm trying to get inserts ...


Go Back   Unix Technical Forum > Database Server Software > Informix

Can't retreive Date it gives me the same exception Can't retreive Date it gives me the same exception

FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 04-20-2008, 05:28 PM
kamal.goradia@gmail.com
 
Posts: n/a
Default Can't retreive Date it gives me the same exception
Hi

Guys i am getting the same error can anybody help.....





-----------------------------------------------------------------------------------------------------


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



Reply Reply to author Forward Rate this post:







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Brett Randall View profile
More options Dec 13 2000, 3:47 pm

Newsgroups: comp.databases.informix
From: Brett Randall <brett_s_rREMOVECAPIT...@hotmail.com>
Date: Wed, 13 Dec 2000 21:18:24 +1000
Local: Wed, Dec 13 2000 4:18 pm
Subject: Re: can't insert DATEs with JDBC
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
You might try setting either DBDATE or GL_DATE environment variables
prior to execution.

DBDATE=MDY4/


HTH


Brett Randall



- Hide quoted text -
- Show quoted text -

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





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Brett Randall View profile
More options Dec 13 2000, 3:57 pm

Newsgroups: comp.databases.informix
From: Brett Randall <brett_s_rREMOVECAPIT...@hotmail.com>
Date: Wed, 13 Dec 2000 21:28:09 +1000
Local: Wed, Dec 13 2000 4:28 pm
Subject: Re: can't insert DATEs with JDBC
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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



- Hide quoted text -
- Show quoted text -

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);
> > }
> > stmt.close();
> > }


> > }


> > // EOF





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Radu Dumitriu View profile
More options Dec 14 2000, 4:45 am

Newsgroups: comp.databases.informix
From: Radu Dumitriu <r...@goldenclick.com>
Date: Wed, 13 Dec 2000 16:20:16 -0800
Local: Thurs, Dec 14 2000 5:20 am
Subject: Re: can't insert DATEs with JDBC
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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




- Hide quoted text -
- Show quoted text -

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(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 = "gc.techembassy.local";
String port = "1536";
String server = "gc";
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
{
Connection connection = connect(null);
Statement stmt = connection.createStatement();
String query = "CREATE DATABASE " + dbName;
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
('1999-08-01')");
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();
}



}


--------------F3ED3F66EC0A14ED0E082AEF
Content-Type: text/x-vcard; charset=us-ascii;
name="radu.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Radu Dumitriu
Content-Disposition: attachment;
filename="radu.vcf"

begin:vcard
numitriu;Radu ...

read more »



Reply Reply to author Forward Rate this post:







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Matthew Cornell View profileProblem solved - our code wasn't using
JDBC-standard DATE syntax. Thanks to all who helped! matt
More options Dec 14 2000, 7:33 am

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 04-20-2008, 05:28 PM
Carsten Haese
 
Posts: n/a
Default Re: Can't retreive Date it gives me the same exception
On Sat, 2007-10-13 at 00:49 -0700, kamal.goradia@gmail.com wrote:
> /*
> // 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')");
> */

When you use string literals for dates, they are interpreted according
to the format in the DBDATE environment variable. Check your DBDATE
setting.

>
> // 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)");

Those aren't date literals, they are arithmetic calculations. You are
dividing 2 by 23, and then dividing the result by 99, 1999, and 2000,
respectively. The result is a floating point number very close to 0,
which is rounded down to 0. Informix can bind integers to date columns,
since dates are stored as "number of days since 1899-12-31". The result
of all those operations is the date 1899-12-31.

> stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ("
> +
> new java.sql.Date(System.currentTimeMillis())
> + ")");

I'll assume that currentTimeMillis returns the current time as the
number of seconds since midnight. Informix takes this as the number of
days since 1899-12-31, resulting in, basically, a random date.

Instead of trying to insert literal dates, you should use a
preparedStatement with a question mark placeholder in the SQL string,
and use setDate to bind a Java Date instance to the placeholder.
http://publib.boulder.ibm.com/infoce...doc/jdbc78.htm shows how to execute a prepared statement. Changing the example to bind a date instead of an integer (and changing it from select to insert) is left as an exercise for the reader.

Hope this helps,

--
Carsten Haese
http://informixdb.sourceforge.net


Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« migrating from 7.x/9.x to IDS 10....any risks ? | @IBM IOD event, Las Vegas, »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 08:44 AM.

Contact Us - Unix Technical Forums - Top

Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.2.0
www.UnixAdminTalk.com