Selecting Multiple Rows

iFIX ODBC can be used to access more than one row with a SELECT command and write the values to multiple sets of tags or to multiple offsets of register blocks. This subsection describes how you can use iFIX ODBC to select multiple rows of SQL data.

The selection mode is determined by the SQL Trigger block configuration. Use the following table to configure the SQL Trigger block:

The SQT block's Column field is used to determine the number of columns to be used. This applies only when using Multiple Rows and Array Mode Select Parameters.

NOTE: When querying iFIX database tags, you cannot retrieve the A_CV value from a tag or group of tags when the tag or tags are off scan. If you try to do so, an error message appears.

Single Row

If a SELECT command returns multiple rows, you can use the SQT block's Single Row mode and the Rows field to determine which row is written to the SQD block. When the SQT block is configured for Single Row mode, the SQD block accepts only one row of data regardless of the number of rows the SELECT command returns.

The SQT block's Rows field determines the row number within the result set to use. The Rows field normally defaults to zero when you select Single Row mode in the SQT block. If zero is the value in Single Row mode, and more than one row is returned, an error results and no data is written to iFIX tags. If a number other than zero is used, the corresponding row number returned from the selected is used.

For example, if the Row field is set to one, the first row of the selected data is used. If it is set to two as shown in the following figure, the second row is used. In this manner you can identify the row to use.

Single Row Mode Example

The Columns field is ignored in this mode. The number of columns is defined by the number of tags in the SQD block that have a direction of IN. If the number of IN tags does not match the number of columns returned, an error results and no data is written.

Multiple Row

When the SQT block is configured for Multiple Row mode, the SQD block accepts more than one row. A set of iFIX tags is used for each row returned. Returned values are written column by column, row by row.

The Columns field must be configured with the correct number of columns in the SELECT command. This information must be defined before the command is executed.

For example, consider the following SELECT statement:

Select col1, col2, col3 from table1

This command returns three columns from the database. The SQD block must contain a multiple of three iFIX tags. If the SELECT command returns two rows of data, six tags should be specified in the SQD block. If more rows are returned by the SELECT command than tags defined in the SQD block, the additional rows are discarded. If less rows are returned, the extra tags in the SQD block are not written to.

The Rows field determines the starting row to use in the selected data. For example, the Rows field can be defined as 3. If the SELECT command returns 10 rows, the tags in the SQD block receive the values starting with the third row. If the number of IN tags in the SQD block is not an even multiple of the number of columns returned, an error results and no data is written to the tags.

Multiple Row Mode Example

You can also use the SELECT command to include parameter markers. For example:

Select col1, col2, col3 from table1 where col4 = ?

In this case, three columns are returned. The command requires one tag with a direction of OUT for the parameter marker.

Array mode

Array mode is used with register blocks. One register block is specified for each column returned from a SELECT statement. Each register block receives multiple rows from a column. The Rows field is used to set a limit on how many rows to write.

In the following example, the SELECT statement returns three columns and requires one parameter marker:

Select col1, col2, col3 from table1 where col4 = ?

In array mode, the SQD block contains one IN register block for each column returned. A tag with an OUT direction is specified in the SQD block for each parameter marker.

Each Register block receives one row of data starting with the register offset specified in the SQL Data block. Data is written until either the Rows limit is reached (specified by the Rows field) or until the data is exhausted.

The F_n field is used for the register block in the SQD block, where n is the offset from the base address that the block references. For example, AR1.F_10, AR2.F_0, and AR3.F_0 are shown in the following figure. Values from the selected data are written to the blocks starting at the offset specified, 10,0 and 0 respectively.

Array Mode Example

The Columns field is ignored in this mode since the number of columns is the same as the number of IN SQD tags defined.