Linking SQL queries with input signals

For SQL queries, analog and digital signals are always linked with the query. The queried values are thus available as signals for visualization and recording.

You can see the signals in the Analog and Digital tabs of the SQL query module.

If the query test has been successful, you can enter the queried values in the signal tables using the button <Add all SQL results>.

Example of the query of a value series (the first) from a table with 8 columns.

If there are not enough signals available in the module, ibaPDA asks you whether you want to increase the number of signals.

The relation of the added signals to the query results is established via the column and row numbers, which are automatically entered in the signal table when you click on the button <Add all SQL results>. If the query provides more than one result, then the result sets are inserted into the signal table one after the other by row number.

An SQL query of the first 3 rows of a table with the following statement would, for example, provide the following result:

Tip

For SQL queries with multi-row results, use the ORDER BY statement to sort the results in a defined manner and therefore to achieve a correct referencing of the signals via the row number.

Signals can also be configured manually by entering the correct column and row numbers according to the query result to be expected.

Note

If you do not want to manually enter the query signals to be expected in the signal table, then the operations <Test statement> and then <Add all SQL results> are required. Otherwise the signals are not created and the module is not shown later in the signal.

For SQL commands, a connection to ibaPDA signals only occurs indirectly via parameters.

Note

If a signal in ibaPDA has the value "NaN" (possible, e.g. when dividing by zero or an incorrect format in the controller) and this value is written in a DB table with an SQL command, then ibaPDA replaces this with "NULL". This takes into consideration the fact that most databases do not support the value "NaN" with parameters. The prerequisite for this is that the corresponding column in the DB table is "NULLable", i.e. the value "NULL" is permitted.

If the value "NULL" is returned when reading via the SQL interface, ibaPDA treats this as "NaN". Alternatively, you can write the SQL query so that "NULL" is converted into other replacement values.