dcsimg
PreparedStatement batch delete
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   faiz_nc
Posted On:   Friday, April 23, 2004 09:51 AM

Hi all I want to delete all the data from more than 10 tables at a strech ie, i would like to use batch update to do this. I have table names in a string array. String tables [] = {"tab1","tab2",........}; And i did something like PreparedStatement pst = con.prepareStatement("delete from ? where user_id = ?"); for( int ctr = 0; ctr < tables.length; ctr++) { pst.setString( 1, tables[ctr]); pst.setString( 2, "userid"); pst.addBatch(); } pst.executeBatch(); when i try to execute the above sql, I am getting SQLException, because the query is interpreted as    More>>

Hi all

			
I want to delete all the data from more than 10 tables at a strech ie, i would like to use batch update to do this. I have table names in a string array.


String tables [] = {"tab1","tab2",........};


And i did something like


PreparedStatement pst = con.prepareStatement("delete from ? where user_id = ?");
for( int ctr = 0; ctr < tables.length; ctr++)
{
pst.setString( 1, tables[ctr]);
pst.setString( 2, "userid");
pst.addBatch();
}
pst.executeBatch();


when i try to execute the above sql, I am getting SQLException, because the query is interpreted as



delete from 'tab1' where user_id = '11';


tablename is the problem , ie the single quotes. with Statement i succesfully executed the above sql but it performs poorly when compared to PreparedStatement .

Could anyone tell how to solve the above problem.



I appreciate ur help    <<Less

Re: PreparedStatement batch delete

Posted By:   David_Thornton  
Posted On:   Monday, April 26, 2004 04:53 PM

You must used a separate prepared statement for each table. A prepared statement is pre-compiled by the DBMS and this can not be done if the table(s) is not known.


Using the setString() method to set the value for a parameter marker in a prepared statement will always enclose the value in single quotes as string values in SQL statements must be enclosed in single quotes.


You need to do something along the lines of;



for( int ctr = 0; ctr < tables.length; ctr++) {
PreparedStatement pst = con.prepareStatement("delete from " + tables[ctr] + " where user_id = ?");
pst.setString(2,"userid");
About | Sitemap | Contact