Inserting date into an MS Access DB

[John Gillespie]

I am trying to insert a row into an MS Access database and it works fine for every other field apart from time.


My code is

public boolean insertLogEntry(LogEntry lg){

PreparedStatement ps = null;

String sql = "INSERT INTO LogEntry ( " +
"IPAddress, " +
"Date, " +
"Request, " +
"FileName, " +
"Status, " +
"Size, " +
"Referrer, " +
"UserAgent, " +
"Website " +
") " +
"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)";

try{
ps = connection.prepareStatement(sql);

ps.setString(1, lg.getFromIP().getHostAddress());
ps.setTime(2, new java.sql.Time(lg.getDate().getTime()));
ps.setString(3, lg.getRequest());
ps.setString(4, lg.getFile());
ps.setInt(5, lg.getStatus());
ps.setInt(6, lg.getSize());
ps.setString(7, lg.getReferrer());
ps.setString(8 , lg.getUserAgent());
ps.setString(9 , lg.getWebsite());

ps.executeUpdate();

}catch (SQLException e) {
System.err.println(e.getSQLState());
e.printStackTrace();

}


Java throws the following error -

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Any suggestions?
Thanks

[Bear Bibeault]

You didn't mention how you know that it's the time field that's causing the ruckus.

[Stefan Wagner]

perhaps he tried it without the date?

But what is lg.getDate().getTime() returning?
I guess the time.
And LogEntry.Date expects what?
I guess a date.

[Bear Bibeault]

perhaps he tried it without the date?

Perhaps. But knowing that may be a key to solving the problem.

It'd be weird if a syntax error was reported on a PreparedStatement data mismatch, but then again, the ODBC-JDBC driver isn't the smartest kid on the block.
[ October 18, 2004: Message edited by: Bear Bibeault ]

[John Gillespie]

Hi, thanks for the help.

I did indeed comment out the setTime (and made appropriate changes to the values and setxxx code) and it worked great. 3000 log entries inserted just dandy.

The lg.getDate() returns a java.util.Date. I then get the time using getTime() and pass to the java.sql.Date constructor.

I have checked the output of the lg.getDate() and lg.getTime() methods and they look fine.

The LogEntry.Date database object is an Access Date/Time column.

So I am wondering if there is an issue with the Access/JDBC/ODBC combination. Has anyone seem some code that successfully inserts into an Access DB using the setTime() method?

Might be time to get Cloudscape out?

Thanks
John

[Roberto Spier]

Originally posted by John Gillespie:

String sql = "INSERT INTO LogEntry ( " +
"IPAddress, " +
"Date, " +

Any suggestions?
Thanks

Just a though, but Date is a reserved word in access that causes some wierd bugs. Try changing this field name.

hth,
Roberto

[John Gillespie]

Awesome - worked a charm

Thank you