Win a copy of Soft Skills: The software developer's life manual this week in the Jobs Discussion forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inserting date into an MS Access DB

 
John Gillespie
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author and ninkuma
Marshal
Pie
Posts: 64171
83
IntelliJ IDE Java jQuery Mac Mac OS X
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You didn't mention how you know that it's the time field that's causing the ruckus.
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author and ninkuma
Marshal
Pie
Posts: 64171
83
IntelliJ IDE Java jQuery Mac Mac OS X
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Awesome - worked a charm

Thank you

 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic