Tom O'Brien Tom O'Brien - 3 months ago 59
MySQL Question

Bulk Insert statements MYSQL Stored Procedure

I have a stored procedure where I need, amongst other things, to be able to do multiple inserts to a particular table.

The number of inserts to this table can vary.

The stored procedure is being called via JDBC.

Passing parameters for a single insert statement to a stored procedure is easy. Is there a way I can pass an array of values to the stored procedure, and then loop through the array to perform the insert statements?

I'm pretty new to stored procedures, so thanks in advance for all your help....

Answer

This was achieved by including everything in a transaction and it will automatically rollback if anything goes haywire. THis is done in JDBC by setting the connection autocommit to be false, and then autocommiting

conn.setAutoCommit(false);
conn.commit();