Issue with Date insertion

[K Kiran Kumar]

Hi,

I am trying to insert a date field to MS Access 2002 *.mdb file.
Below is the code I am using and my table structure is very simple
TEST12345(ff) -- the ff column is of type Date/Time

 		Connection con=null;
		Statement s = null;
		PreparedStatement ps=null;
		System.out.println("hi");
		try 
		{
		 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	        String filename = "d:/ssc.mdb";
	        String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
	        database+= filename.trim() + ";DriverID=22;READONLY=true}";  
	        con = DriverManager.getConnection(database,"","");
	        java.util.Date dt=new java.util.Date();
		
//	        s=con.createStatement();
//	        s.execute("insert into TEST12345 values ("+new Date(dt.getTime())+")");
	        
	        String sol= "INSERT into TEST12345(ff) VALUES(?)";
	        ps=con.prepareStatement(sol);
	        ps.setDate(1,new java.sql.Date(dt.getTime()));
	        ps.executeUpdate();
		}
	    catch (Exception e) 
	    {
	       	e.printStackTrace();
		}
	    finally
	    {
	    	try
	    	{
	    		con.close();
	    		ps.close();
//			s.close();
	    		System.out.println("Connection closed successfully");
	    	}
	    	catch(Exception e)
	    	{
	    		e.printStackTrace(); 
	    	}
	    }   

Whether I use createStatement or PrepareStatement, I get the below exception

java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at com.smart.corp.dao.UpdateDAO.main(UpdateDAO.java:158)

Not sure where I am wrong. There is no problem with the connection object since I have created tables with it successfully. Any help on my issue would be highly appreciated.

Regards,
Kiran.

[Paul Clapham]

Seems to me that if the field's type is "Date/Time" then you should be using a Java class which matches that type. That would be java.sql.Timestamp and not java.sql.Date (which doesn't have a Time component).

[K Kiran Kumar]

Thanks Paul. Timestamp worked for me only with PreparedStatement but not with Statement.
Am I missing anything in Statement? Please let me know. Below is mycode.

java.util.Date dt=new java.util.Date();	
Timestamp ts=new Timestamp(dt.getTime());

s = con.createStatement(); //This one didn't work out. Got the below exception.
s.execute("insert into TEST12345(ff) values ("+ts+")"); 
	        
String sol= "INSERT into TEST12345(ff) VALUES(?)";//This one is fine
ps=con.prepareStatement(sol);
ps.setTimestamp(1,ts);
ps.execute();

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '2009-10-08 11:41:38.984'.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at com.smart.corp.dao.UpdateDAO.main(UpdateDAO.java:201)
java.lang.NullPointerException
at com.smart.corp.dao.UpdateDAO.main(UpdateDAO.java:217)



Regards,
Kiran.

[Jan Cumps]

How would you insert this record if you would use an sql query tool?
How would you format the timestamp then?

Compare that with how your statement looks like in your javacode (Do System.out.println("insert into TEST12345(ff) values ("+ts+")");

[K Kiran Kumar]

Hi,
How would you insert this record if you would use an sql query tool?
I use TO_DATE('12-08-2009','DD-MM-YYYY') function.
How would you format the timestamp then?
In java there is no TO_DATE type method and hence trying to insert the Object.

The output for "insert into TEST12345(ff) values ("+ts+")"); is
insert into TEST12345(ff) values (2009-10-08 11:41:38.984)

Thanks for the suggestion.

Regards,
Kiran.