Insert multiple rows - java prepared statement failing

Question

--SOLVED--Turned out to be a problem with our Shareplow install--Thanks for the tries guys--

Per the answer on Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values I'm trying to use bulk insert prepared statments. The problem is, only the first row is being inserted.

Relevant piece of my code

var sql2 = 'INSERT INTO ' + memtable2 + ' (' + locNameCol2 + ', ' + sectTypeCol + ', '
+ sectPathCol + ') VALUES ';
var cntr = 0;

for (var key in polyObj)//pass sub section paths in an object.
{
  if(cntr){
    sql2 += ',';
  }
  sql2 += '(?,?,?)';
  cntr = 1;
}

var s2 = con2.prepareStatement(sql2);
cntr = 0;
for (var key in polyObj)
{
  indxOffset = 3*cntr;
  s2.setString(indxOffset+1 , fieldNameVal);
  s2.setString(indxOffset+2 , 'Poly');
  s2.setString(indxOffset+3 , polyObj[key]);
  rval += 'index offset ' + indxOffset;
  cntr++;
}
s2.execute();

The first row gets inserted fine but no subsequent rows get inserted. When I dump the sql2 string I see that it's getting built correctly (I think) for the number of properties in polyObj. So if polyObj has 3 properties, the sql would be: Insert into table (col1, col2, col3) values (?,?,?),(?,?,?),(?,?,?) Is there some setting on my database that would prevent multiple row inserts? Am I just doing something wrong? Totally stumped.

Thanks in advance.

Answer 1

Consider using a PreparedStatement with executeBatch() instead.

String sql = "insert into foo (x,y,z) values (?,?,?)
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, "a1");
ps.setObject(2, "b1");
ps.setObject(3, "c1");
ps.addBatch();
ps.setObject(1, "a2");
ps.setObject(2, "b2");
ps.setObject(3, "c2");
ps.addBatch();
ps.setObject(1, "a3");
ps.setObject(2, "b3");
ps.setObject(3, "c3");
ps.addBatch();
ps.executeBatch();