Extensibility > Using External Databases > Using Structured Query Language > Basic SQL Scenarios

Basic SQL Scenarios

Two basic scenarios of using the Cicode SQL database interface are:

Accessing Data in a CCicode Procedure

The CCicode SQL database interface enables users to create a connection object, connect it to a database, execute SQL queries and step through records in either the default connected recordset, using legacy Cicode functions, or a disconnected recordset, using the new Cicode functions. See examples in SQLCreate and SQLConnect Cicode function descriptions.

In previous versions, this scenario could be problematic because connecting to databases and accessing data might hold SCADA Kernel’s Tasks and temporarily block the entire application. The new implementation delegates connections, disconnections and query executions to separate application threads. The SCADA kernel thread, which initializes one of those actions, is suspended and waits for synchronization while other threads can continue processing their tasks.

Displaying data on a graphics page

Typically, this scenario is used when the result of a query is displayed on a graphic page in the form of a table with the possibility of scrolling forward and backward through the data.

In this case the data from a disconnected SCADA recordset is displayed on a graphic page using the SQLGetField and SQLIsNullField SQL functions.

Data pertaining to the SQL query is copied to the disconnected SCADA recordset just after executing the query. This behavior means that there is no additional data fetching when you iterate through disconnected recordsets later.

Note: When executing queries, there is a direct relation between data size, execution time and memory consumption: the more data fetched the longer execution time and the larger amount of physical memory taken by the SCADA recordset. Take care to design your queries so that they return only amount of data compatible with available resources and performance expectations.

Since recordsets reside in memory while active, it is your responsibility to close unused SCADA recordsets when they are no longer required.

Disconnected SCADA recordsets don’t depend on the existence of either the database connection object or the SQL connection to a database. The recordsets can therefore be seen as disconnected immediately after executing a query. They do not synchronize with the source database after that point. Subsequently, the SQL connection to a database can either stay open or be closed.

Published June 2018