602SQL Documentation Index  

Selecting Records for Output to XML Using Variables

A DAD can be used to export records from tables. Various methods can be used to select the records for XML output, without modifying the DAD. These methods are described below.

The method of record selection depends on the DAD type.

Variable References in DAD

You may reference variables that exist outside of the DAD and whose values will be used for selecting records for export (both in synthetic and analytic DADs). These variables may be used in a stored query (synthetic DAD) or in element conditions that have tables linked to them.

NOTE: Client and server variables may be bound to XML attributes or text and support transfer in both directions.

Client Variables

You may refer to client variables (host variables) in the DAD if the export is called from a client. These variable exist in the client program that executed the export. When calling from an external language, the description of these variables will be passed to the function for export to XML.

A colon must be used in the DAD before each client variable name

When using client variables in the DAD do the following:

  1. Choose the names for the variables and use them in the DAD.
  2. Declare the hostvars array that will describe the names and types of all client variables.
  3. Set the variable values before export.
  4. Pass the hostvars array and the count of variables in this array to the export and import functions.
  5. You may modify the variable values and export different records.

When using Delphi (CDK component TSQL602XML) or PHP, the described method is somewhat easier.

Server Variables

If the export is called on the server from a stored procedure, you may refer to variables declared in this procedure and to parameters in the DAD. A description of these variables does not need to be explicitly passed.

These variables are designated by the identifier in the DAD (without a colon).

When using variables in the DAD, do the following:

  1. Declare variables in the procedure calling the export.
  2. Use the variables in the DAD.
  3. Set the variable values before the export.
  4. You may modify variable values and export different records.

NOTE: A correctly designed DAD that contains a server variable cannot be modified using the 602SQL Client (an error will occur). The DAD must be designed with the variable prefixed with a colon (for compilation and testing). Afterwards, delete the colon in the text editor (and test it by using the API from the procedure).

Synthetic DAD

A synthetic DAD contains a SQL query that is used for selecting records. Conditions that filter the records from the database tables according to the column values may be part of this query.

Example query:


Using Client Variables in a Synthetic DAD

Variables are used directly in the query, usually in the WHERE clause. The query above can be made more precise by client variables param_val and name_val, as follows:

SELECT * FROM T1, T2 WHERE T1.a=T2.b AND T1.param<:param_val AND T2.name=:name_val

Record selection will depend on values in both variables.

Redirect a synthetic DAD to another cursor

A synthetic DAD may also export data from a query that is different than the one specified in the definition. Do the following to use a different query:

  1. Prepare a query that has the same column structure as the query stored in the DAD.
  2. Open a cursor for this query.
  3. Pass this cursor to the function for data export to XML.
  4. Close the cursor.

You may open the following query and pass it to the export function:

SELECT * FROM T1, T2 WHERE T1.a=T2.b AND T2.name='xyz'

Conditions in the DAD

Record selection in the DAD that contains bound tables are governed by conditions specified in description of those elements.

For example: The node connecting the table T1 may have the condition param<:param_val and the node connecting the table T2 may have the condition name=:name_val. The hostvars array will have two fields for variables param_val and name_val. If you assign values to these variables before the export to XML, only records that fulfill the specified condition will be exported.

Conditions specified in the DAD may have any form usable in an SQL query. Each condition may contain any number of client variables and refer to any column of any table usable in the DAD.

Temporary Variables in the DAD Designer

Client variables are defined in external applications working with a 602SQL programming interface. In order to use and test the DAD using client variables, the DAD designer allows you to describe the names, types and values of variables (see the Interactive DAD designer).

Client variables described in the DAD may only be used when working in the interactive designer. They are ignored when using the DAD from an external interface.