Resolving data from multiple tables

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

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, and suggest a resolution order between the tables. The properties set by the metadata discovery are:

The tableName property of the StorageDataSet is not set. The tableName is identified on a per column basis.

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 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 multiple, 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 serverColumnName properties are set by the QueryProvider for a QueryDataSet unless the metaDataUpdate property does not include metaDataUpdate.TABLENAME.

What if a table is not updateable?

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.

Note: The ability to update depends on other things, which are described in more detail in "Querying a database."

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

For a multi-table query, one of the tables can be updateable when the other is not. 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.

For a single table, set the metaDataUpdate property to NONE, and do not set any of the resolving properties (rowID, tableName, etc.).