This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Inserting date into an MS Access DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Inserting date into an MS Access DB" Watch "Inserting date into an MS Access DB" New topic
Author

Inserting date into an MS Access DB

John Gillespie
Greenhorn

Joined: Oct 18, 2004
Posts: 3
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

Joined: Jan 10, 2002
Posts: 60782
    
  65

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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

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.


http://home.arcor.de/hirnstrom/bewerbung
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60782
    
  65

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

Joined: Oct 18, 2004
Posts: 3
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

Joined: Sep 13, 2004
Posts: 7
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

Joined: Oct 18, 2004
Posts: 3
Awesome - worked a charm

Thank you

 
 
subject: Inserting date into an MS Access DB
 
Similar Threads
Help with MS Access & Date
Getting error while inserting data into table
how to increase the number in oracle database automatically
Update Statement Problem
MSAccess and JDBC