When a complex table is defined to use a SQL Database system connection type, the synchronization components consist of three SQL statements: Reload State Query, Deleted Query, and Data Query.
The reload state query can be enabled or disabled based on preference. When enabled, this query is expected to return the text values “true” or “false.” When the query returns true, the complex table will set to its rebuild state. The condition under which this query returns true is completely dependent on the need of the application or implementation. Its intent is to select from the back end system based on some value or condition that an administrator can easily set when it is desirable to force the complex table to be fully reloaded on the Agentry Client. When this query is disabled, it will not be run by the Agentry Server during synchronization for the complex table.
The data query is always run during synchronization and should include two separate select statements. Both statements are expected to return records from the database to the Agentry Server containing the field values for the complex table records. The columns of this return set must be named to match the names of the complex table fields. The difference between the two statements contained in the data query is the logic related to which records they will select. One statement should be written to select all records to be stored in the complex table on the Agentry Client and under the assumption that the Agentry Client currently contains no records. This statement will then be run for only the rebuild state. The second statement should include logic in support of the exchange data model of synchronization, and should retrieve only new or modified records from the database that will be updated to the records stored on the Agentry Client. To determine if the complex table is in a rebuild state, the SDML data tag <<rebuild>> is used. This tag will return true when the rebuild state is set, and false when it is not. The data query will likely check this data tag using the <<if...>> function tag, which should then return the appropriate statement.
The deleted query is only run when the complex table is not in a rebuild state. This query is expected to return a single column identified as the key field in the complex table. Any values returned by this query will be sent to the Agentry Client so that the Agentry Client will delete the records with the matching key field value from the complex table.