Win a copy of liveProject: Build an ML Recommender System this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • paul wheaton
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Piet Souris
Bartenders:
  • salvin francis
  • Mikalai Zaikin
  • Himai Minh

Inserting date into an MS Access DB

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 67532
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You didn't mention how you know that it's the time field that's causing the ruckus.
 
Ranch Hand
Posts: 1923
Scala Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Sheriff
Posts: 67532
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Awesome - worked a charm

Thank you

 
Tell me how it all turns out. Here is a tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic