This week's giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Production-Ready Serverless (Operational Best Practices) and have Yan Cui on-line!
See this thread for details.
Win a copy of Production-Ready Serverless (Operational Best Practices) this week in the Cloud/Virtualization 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Ron McLeod
  • Tim Moores
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Vijitha Kumara

PreparedStatement Question  RSS feed

 
Bartender
Posts: 1656
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

Is there a way to write a PreparedStatement when you want the variable to be the keyword "INTERVAL" in a MySQL date INTERVAL?

For example, if you had a date comparison in your SQL like this: "> DATE_SUB(CURDATE() , INTERVAL 10 DAY)"

But, you want to pass in different "INTERVALs".

I tried just doing something like this:

(SQL) where ..... > ?

And then tried to set the first PreparedStatement index equal to the string: " DATE_SUB(CURDATE() , INTERVAL 10 DAY)"

But that didn't work.

Suggestions?

Thanks.

- mike
 
Rancher
Posts: 3983
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try:
DATE_SUB(CURDATE() , INTERVAL ? DAY)

You can only bind values, not chunks of SQL.
 
Bartender
Posts: 20562
120
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're missing several important bits of information.

First, I'm assuming that you're using MySQL. Date manipulation is generally pretty non-portable between different brands of DBMS, so it's important to tell us what DBMS you're using.

Secondly, your sample SQL seems to be extremely incomplete.

Have you tried this:


Where you'd set the parameter to the number of days desired

or this:



Where you'd set the parameter to be the string "INTERVAL 10 DAY"?


 
Mike London
Bartender
Posts: 1656
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:You're missing several important bits of information.

First, I'm assuming that you're using MySQL. Date manipulation is generally pretty non-portable between different brands of DBMS, so it's important to tell us what DBMS you're using.

Secondly, your sample SQL seems to be extremely incomplete.

Have you tried this:


Where you'd set the parameter to the number of days desired

or this:



Where you'd set the parameter to be the string "INTERVAL 10 DAY"?




Solved!

I thought I had tried this suggestion, but for some reason it worked today! Perhaps I didn't leave the "?" inside the quoted SQL string. Dunno.

Yes, MySQL.

Thanks very much as always for the excellent help!

- mike
 
Dave Tolls
Rancher
Posts: 3983
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:
or this:



Where you'd set the parameter to be the string "INTERVAL 10 DAY"?



Does that work?
I don't think you can bind that sort of thing, can you?
Or does DATE_SUB allow a string as a parameter?
 
Mike London
Bartender
Posts: 1656
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:

Tim Holloway wrote:
or this:



Where you'd set the parameter to be the string "INTERVAL 10 DAY"?



Does that work?
I don't think you can bind that sort of thing, can you?
Or does DATE_SUB allow a string as a parameter?



What worked for me was "INTERVAL ? DAY" all inside the quote.

I thought I had tried that last week before resorting to separate SQL statements with "days" read from a local variable (not a good practice, I know).

I've now refactored the code to create a PreparedStatement up front and then ps.setInt(...) for each value.

Works great. Faster too, of course using PS.

Thanks again.

- mike
 
Because those who mind don't matter and those who matter don't mind - Seuss. Tiny ad:
Create Edit Print & Convert PDF Using Free API with Java
https://coderanch.com/wiki/703735/Create-Convert-PDF-Free-Spire
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!