Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC supported SQL functions

 
jam hall
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Im using JDBC to connect to a MS Access database. I've tried to execute a query using the 'MONTHS_BETWEEN' function. An SQL exception was thrown saying it didnt like the function. Anyone know how i might be able to run this query through JDBC. I suspect i might need to change to a oracle DB.

Thanks Ahead
 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24212
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JDBC itself doesn't support any SQL functions, or anything else, for that matter. All such support is offered by the underlying database, or the driver for that database. I'm not familiar with this function you mention, but if it's not supported by MS Access, then you won't be able to use it via JDBC connected to MS Access.
 
Jayesh Lalwani
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Access doesn't support MONTH_BETWEEN. You have to use the dateDiff dunction for Access.

You can refer to this page for differrences between Oracle and Microsoft Access SQL
 
jam hall
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've tried using DATEDIFF, here is my code

System.out.print("enter date");
String date = input.next();
Statement statement = link.createStatement();
results = statement.executeQuery("SELECT refNo ,DATEDIFF('Month','"+date+"','dateOf') FROM tblFault");

I get a datatype mismatch error. Does the date that the user enters need to be in a date object?

Any help would be much apreciated

Thanks ahead
 
Jayesh Lalwani
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's dateOf ? Is that a column in your tblFault table

try this


For the above statement to work, the format of your date should be same as the format of the date in the database. If the format is differrent you will have to use the CONVERT operator to convert string into datetime
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic