Batch consisting of multiple PreparedStatements

[Sol Mayer-Orn]

Hi,

Is there a way to create a single batch, with each line being generated by a PrepareStatememt, rather than manual string concatenation ?
I don't think it would do to use PreparedStatement.addBatch(), because this would enforce a uniform structure on all lines...

For example, suppose I'd like the following 4 lines to execute as a single batch. Do I have to concatenate them manually, or is there a way to use PreparedStatements (one PreparedStatement for "update", and one for "insert):

--------- start batch --------
- update TAB set salary=1000 where id=1
- update TAB set salary=2000 where id=2
- insert into TAB values(3,3000)
- insert into TAB values(4,4000)
--------- end batch ---------

Thanks

[Jeanne Boyarsky]

Sol,
What do you mean by "manual string concatenation" ?

[Avi Abrami]

Sol,
Perhaps the following will be of help (assuming you haven't already seen it):

http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html

Good Luck,
Avi.

[Sol Mayer-Orn]

Thanks for replying.

What I meant was, I'd like to create a batch that consists of PreparedStatemtns.

It's *easy* to create a batch if you represent each line as a *stirng*:
batchStmt.addBatch("update TAB set salary=1000 where id=1");
batchStmt.addBatch("update TAB set salary=2000 where id=2");
batchStmt.addBatch("insert into TAB values(3000)");
batchStmt.addBatch("insert into TAB values(4000)");

But i'd like each line to be a preparedStatemet, which would look something like:

// -- That's not real java code !
 
// Prepared statements, with "?":
PreparedStatement updateStmt=con.prepareStatement("update tab set salary=? where id=?"));
PreparedStatement updateStmt=con.prepareStatement("insert into tab values(?"));
// Create batch (4 lines):
updateStmt.setString(1, "1"); updateStmt.setDouble(2, 1000);
batchStmt.addBatch(updateStmet);
updateStmt.setString(1, "2"); updateStmt.setDouble(2, 2000);
batchStmt.addBatch(updateStmet);
insertStmt.setDouble(1,3000);
batchStmt.addBatch(insertStmt);
insertStmt.setDouble(1,4000);
batchStmt.addBatch(insertStmt);

I know it can be done if all 4 lines have the exact same structure (e.g. if all 4 lines where "insert"), but I couldn't find a way to do it when there's mixed structure (e.g. 2 "inserts" and 2 "updates")...

Thanks

[Avi Abrami]

Sol,
You said:

I couldn't find a way to do it when there's mixed structure

That's because it doesn't exist! You can only associate precisely one SQL statement with a single instance of "PreparedStatement". Guess you'll have to find a workaround -- like maybe implementing this functionality yourself (probably by creating some "wrapper" class for "PreparedStatement").

Good Luck,
Avi.

[Sol Mayer-Orn]

Thank you very much.