• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Solved : How delete oldest records

 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I need to delete all records older than a given timestamp. I am trying to use the following code;



I am getting the following error: SEVERE: null java.sql.SQLException: Must declare the variable '@P0'.

targetTable and insertDate are both fine (they hold the correct values) and the database side of things is all fine.

Can anyone help solve this for me.
[ August 27, 2008: Message edited by: Darren Wilkinson ]
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This does not work. You can use PreparedStatement to do parameter binding. But table binding is not supported.
 
Darren Wilkinson
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Jan - it all works perfectly now :-)
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jan Cumps:
This does not work. You can use PreparedStatement to do parameter binding. But table binding is not supported.



That's not exactly true. You make the mistake of assuming PreparedStatements fully understand and parse the SQL query, which they do not. In this case, the Prepared statement will replace the table name as it would a column name and it is up to the database if this represents a viable query. For this query, you'll get something like "DELETE FROM 'mytable' WHERE ..." and if the database accepts the apostrophes around mytable, then it will work.

Darren- I'd still file this under a "wow it works but probably shouldn't" category. Any solution that replaces a table name as part of the PreparedStatement query is incorrect to me, even if it happens to work for some databases.
[ August 27, 2008: Message edited by: Scott Selikoff ]
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
BTW I believe the original problem for the post was that targetTable or insertDate was null. And Darren, its not very helpful to just mark a post as solved without explaining why. You can help other people who have the same problem as you.
[ August 27, 2008: Message edited by: Scott Selikoff ]
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

In this case, the Prepared statement will replace the table name as it would a column name and it is up to the database if this represents a viable query. For this query, you'll get something like "DELETE FROM 'mytable' WHERE ..."

Would it? And would you advise someone to rely on it?
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jan Cumps:
Would it? And would you advise someone to rely on it?



No, and I stated as much in an earlier post. When this works its extremely unstable and could break even with the change/upgrade of a driver.

But to play devil's advocate for a minute, referring to the link you sent notice the error message wasn't "Cannot bind SQL table name". The error was "invalid table name". This means that the table was successfully binded, and a query was sent to the database... it just turned out the database rejected as being invalid. The key thing to keep in mind is that JDBC drivers know nothing about the query you are submitting, it's just doing simple find/replace on Strings.
 
Hoo hoo hoo! Looks like we got a live one! Here, wave this tiny ad at it:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic