Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Solved : How delete oldest records

 
Darren Wilkinson
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • 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 ]
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Thank you Jan - it all works perfectly now :-)
 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic