How to batch insert to two tables using anorm with foreign key

Question

Im having trouble doing this batch operations like this.

val params = Seq(Seq[NamedParameter]("valueA" -> 1, "valueB" -> 2))

BatchSql(SQL(
  """
     INSERT INTO tableA ( valueA ) VALUES ( {valueA} );
     INSERT INTO tableB ( tableAId, valueB ) VALUES ( LAST_INSERT_ID(), {valueB});
  """.stripMargin), params).execute()

With tables like...

CREATE TABLE tableA
(
  id int AUTO_INCREMENT PRIMARY KEY,
  valueA int
); 

CREATE TABLE tableB
(
  id int AUTO_INCREMENT PRIMARY KEY
  tableAId int,
  valueA int,
  FOREIGN KEY (tableAId) REFERENCES tableA (id),
);

I get this exception, just complaining about line 2.

BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO tableB  ( tableAid, valueA' at line 1]]

Thanks

As JDBC prepared statement is used, only one SQL statement is expected. So the second statement in the SQL string is in this way a syntax error. Batch update (with Anorm or with direct JDBC) is about executing a list of list of paramaters (batch parameters) with the same single statement (prepared for each list of parameter in the batch). So I would say such JDBC batch update should be impossible whatever is your JDBC driver (underlying DB). — cchantep, Mar 26 '15 at 08:36
Thank you, that makes sense. Maybe this is where stored procedures are useful. — Stephen, Mar 26 '15 at 09:49

score 0 · Answer 1 · edited Jan 26 '16 at 15:25

You need two tables, the first generates the primary key, the second uses the newly created primary key as foreign key ..

create table TABLE_1 columns ID_TABLE_1 PK, A, B (UNIC), C;
create table TABLE_2 columns ID_TABLE_1 FK, D, E;

DECLARE @unicValue int = 987;
INSERT INTO TABLE_1 (A,B,C) VALUES ('A', @unicValue, 'C');

INSERT INTO TABLE_2 (ID_TABLE_1, D, E) VALUES (
 (SELECT ID_TABLE_1 FROM TABLE_1 WHERE B = @unicValue), 'VALUE', 'C');

Add multiple "batch"..
Execute batch !

How to batch insert to two tables using anorm with foreign key

1 Answers1