- Joined Inserts

Joined Inserts

[EE] A joined insert is an insert statement which inserts data into multiple database tables.

For example, the following operation inserts data into two tables, the CUSTOMER and CUSTLOC tables.

NOTE: Field names beginning with "C" are from the CUSTOMER table, and fields beginning with "L" are from the CUSTLOC table

Joined inserts can also occur when a join is used to select multiple rows from the database with a query like:

SELECT * FROM JETEMP.CUSTOMER JOIN JETEMP.CUSTLOC ON CLOCID = LOCID

...and the user clicks the corresponding Insert button.

During a joined insert, the user is shown fields from all tables on the same insert screen.

There is no indication given to the user of which fields belong to which tables.

Additional properties can be used to control join behavior. See the Join property group:

Restrictions

Each field name in a joined insert must apply to a single table. When two or more tables have columns with the same name, those columns cannot be used in a joined insert into those tables.

Transactions

[EE] The SQL standard does not allow for inserting into multiple tables with a single statement, therefore internally WOW splits the insert statement into multiple separate SQL statements and sends them all to the database. It is possible for the one statement to succeed but for the other statements to fail (a dropped network connection or an authorization error are two things that could cause one statement to work and then others to fail). When the first statement fails, WOW abandons the insert and reports the error as usual. However, if a subsequent statement fails after the first statement succeeds, the database could be left in a corrupted state. For this reason you may wish to configure your application not to use joined inserts, or to use database transactions.

A transaction is a way of bundling multiple SQL statements into a single unit of work – that unit of work will either succeed or fail as a whole. If it fails then none of the statements in the transaction will have affected the database. Some databases do not support transactions, and other databases require special configuration before transactions can be used. Check with your database documentation to find out how to configure transactions on your database.

By default, WOW will not use transactions for joined inserts. If you want WOW to issue your joined insert as a single transaction (which is recommended if your database supports transactions), you must use the Join property group to specify this:

Join { transactions: true; }

This property group should be placed in the properties field of the operation which is inserting the joined rows into the database (or the operation which selected the joined rows, depending on which operation is being run). Alternatively you can place this property group in the properties field of the application, where it will apply to all of the operations in that application.