Resolving data from multiple tables

You can specify a query on multiple tables in a QueryDataSet and JBuilder can resolve changes to such a DataSet. SQLResolver is able to resolve SQL queries that have more than one table reference. The metadata discovery will detect which table each column belongs to. The ability to resolve data from multiple tables focuses on these properties:

The tableName property may be different on the Columns belonging to one DataSet. The tableName and schemaName properties on a DataSet are null if this is a multiple table query. In this case the table names should be found per column.

The property resolveOrder is a String array that specifies the resolution order for multi-table resolution. INSERT and UPDATE queries use the order of this array, DELETE queries use the reverse order. If a table is removed from the list, the columns from that table will not be resolved.

Considerations for the type of linkage between tables in the query

A multi-table SQL query is usually defines a link between tables in the WHERE clause of the query. Depending on the nature of the link and the structure of the tables, this link may be of four distinct types (given the primary table T1 and a linked table T2):

JBuilder takes a simplified approach to resolving mulitple, linked tables: JBuilder only resolves linkages of type 1:1. However, because it is difficult to detect which type of linkage a given SQL query describes, JBuilder assumes that any multi-table query is of type 1:1. If the multiple, linked tables are not of type 1:1, you handle resolving of other types as follows:

Table and column references (aliases) in a query string

A query string may include table references and column references or aliases.

Controlling the setting of the column properties

The tableName, schemaName, and tableColumnName properties are set by the QueryProvider for QueryDataSets unless the metaDataUpdate property does not include metaDataUpdate.TABLENAME. These properties are added to the Inspector for a Column. These can then be modified in the Inspector when the metaDataUpdate property is turned off.

What if a table is not updatable?

If there is no rowId in a certain table of a query, all the updates to this table are not saved with the saveChanges() call.

How can the user specify that a table should never be updated?

The StorageDataSet property resolveOrder is a String array that specifies the resolution order for multi-table resolution. INSERT and UPDATE queries use the order of this array, DELETE queries use the reverse order. If a table is removed from the list, the columns from that table will not be resolved.