Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Solved : How delete oldest records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Solved : How delete oldest records" Watch "Solved : How delete oldest records" New topic
Author

Solved : How delete oldest records

Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
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

Joined: Dec 20, 2006
Posts: 2477
    
    7

This does not work. You can use PreparedStatement to do parameter binding. But table binding is not supported.


OCUP UML fundamental and ITIL foundation
youtube channel
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Thank you Jan - it all works perfectly now :-)
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

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 ]

My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

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

Joined: Dec 20, 2006
Posts: 2477
    
    7

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Solved : How delete oldest records
 
Similar Threads
Deletion of record from more than one table
delete records from database
db2 SQL
Deleting bulk records from some history tables using hibernate/JDBC?
DB2 Insert Problem - Invalid Table State