Inserting into multiple tables with single query

I am trying insert values into two tables using a single query by wrapping the query in a transation.

I created the following namedQuery

BEGIN;
INSERT INTO  notifications_users (user_id,notification_type,username)
 	VALUES ( :user_id , :notification_type , :username );

INSERT INTO notifications_collections (user_id,hash_id)
 	VALUE ( :user_id , :hash_id );
END;

Here is the error I get.

image

Can someone please point out what I am doing wrong.

You can’t have multiple statements in a single query due to the JDBC driver. Either use a stored procedure in your database and call it from Ignition, or separate these into separate named queries

You can script as a transaction and you can incorporate named queries with it as well though as mentioned, each INSERT will be a separate named query. Check out system.db.beginNamedQueryTransaction - Ignition User Manual 8.0 - Ignition Documentation

to see an example of how it is done.

What you show is not a transaction. It is simply a SQL script code block. A transaction in a SQL script/procedure is more explicit, like so:

BEGIN TRANSACTION;

INSERT INTO  notifications_users (user_id,notification_type,username)
 	VALUES ( :user_id , :notification_type , :username );

INSERT INTO notifications_collections (user_id,hash_id)
 	VALUE ( :user_id , :hash_id );

COMMIT;

But as noted above, JDBC does not support SQL scripts (though some drivers allow it). Use Ignition’s transaction management calls instead.

3 Likes

Just wan’t to point out that you don’t need a transaction in order to run an insert into multiple tables. That isn’t what a transaction is for. That doesn’t mean its a bad idea or that you shouldn’t use it here. Just that wrapping it in a transaction wasn’t going to make this a single operation.

Also, either you didn’t include the entirety of you SQL or your syntax isn’t correct because, just using a
BEGIN - END doesn’t create a transaction you would need BEGIN TRANSACTION

Also, perhaps you haven’t made it that far yet, but why use a Transaction if you have no mechanism for deciding to either COMMIT or ROLLBACK?

Ahhh… @pturmel beat me to it.

1 Like