execute batch is not executing all the queries.

[Jigar Naik]

Hi I am trying to perform the batch execution of 3 delete statement which perform delete operation for three different table.

but data is getting deleted for only one table.

   
 String jobKey = request.getParameter("jobkey");
 
                        ConnectionBean connectionBean = new ConnectionBean();
                        Connection connection = null;
                        PreparedStatement ps = null;
                        try{
                        connection = connectionBean.getDBConnection();
                        ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_SCHEDULER_JOB);
                            ps.setString(1, jobKey);
                            ps.addBatch();
                            
                            ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_SCHEDULED_CAMPAIGN);
                            ps.setString(1, jobKey);
                            ps.addBatch();
                        
                            ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_JOB_SCHEDULE_TIME);
                    ps.setString(1, jobKey);
                    ps.addBatch();
                            
                    //Execute Batch Works like LIFO.
                    ps.executeBatch();
                        }catch(Exception e){
                        e.printStackTrace();
                        }finally{
                        ps.close();
                            ps = null;
                            connection.close();
                            connection = null;
 

Data is getting deleted for only bellow query.

   
                            ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_JOB_SCHEDULE_TIME);
                    ps.setString(1, jobKey);
                    ps.addBatch();
 

Thanks & Regards,
[ November 10, 2008: Message edited by: Jigar Naik ]

[Jeanne Boyarsky]

Jigar,
Let's walk though what is happening here:

// create a new prepared statement (let's call it ps1) and store a reference to it in ps
ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_SCHEDULER_JOB);
// set parameter on ps1
ps.setString(1, jobKey);
// store this SQL statement in ps1
ps.addBatch();

// create a new prepared statement (let's call it ps2) and store a reference to it in ps
ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_SCHEDULED_CAMPAIGN);
// set parameter on ps2
ps.setString(1, jobKey);
// store this SQL statement in ps2
ps.addBatch();

// create a new prepared statement (let's call it ps3) and store a reference to it in ps
ps = connection.prepareStatement(SchedulerQueryConstants.DELETE_FROM_JOB_SCHEDULE_TIME);
// set parameter on ps3
ps.setString(1, jobKey);
// store this SQL statement in ps3
ps.addBatch();

// execute all the stored SQL statements on ps3 (because that is what ps is pointing to now)
ps.executeBatch();


ps1 and ps2 are now "orphaned". They will be garbage collected, but never run.

[Jigar Naik]

Thanks Jeanne,

I got your point. So Does that means what i am trying to achieve using prepared statement is not possible?

I did it using Statement.

    
connection = connectionBean.getDBConnection();
stmt = connection.createStatement();
System.out.println("DELETE FROM JobScheduleRecurrence WHERE jobscheduletimekey = (SELECT jobscheduletimekey FROM JobScheduleTime WHERE pkey = " + jobKey + ")");
stmt.addBatch("DELETE FROM JobScheduleRecurrence WHERE jobscheduletimekey = (SELECT jobscheduletimekey FROM JobScheduleTime WHERE pkey = " + jobKey + ")");
stmt.addBatch("DELETE FROM JobScheduleTime WHERE  SchedulerJobKey = " + jobKey);
stmt.addBatch("DELETE FROM ScheduledCampaign WHERE SchedulerJobKey = " + jobKey);
stmt.addBatch("DELETE FROM SchedulerJob WHERE pkey = " + jobKey);
stmt.executeBatch();

[Jeanne Boyarsky]

Originally posted by Jigar Naik:
So Does that means what i am trying to achieve using prepared statement is not possible?

Right. You can add the same statement with multiple parameters via a prepared statement. But not different statements.

[Jigar Naik]

ok.. thanks a lot jeanne