SQLConnect

Creates an internal database connection object and tries to connect it to a database specified by the connection string. Returns a handle to the database connection object for use by the other database functions.

You only require one database connection object for each database system to be accessed (for example, Oracle, dBASE, Excel, etc.).

It is recommended not to use an SQL database for storage of real-time data (such as alarms), because SQL databases do not provide real-time performance when accessing database data. Only use an SQL database where data transfer is not a priority (for example, recipes or reports). If you try to use SQL to store real time data, Citect SCADA's performance could be greatly decreased.

Each database connection object created by SQLConnect should be released by calling SQLDisconnect with handle to the object. The releasing operation should be performed even when the SQL connection to database is no longer active; for example automatically dropped by a remote database or manually closed by SQLClose. Memory leaks can occur if the handles are not properly released.

Note: Currently there are certain configurations of providers, connection strings and database systems which can automatically close connections in order to save the database systems’ resources; for example MS SQL Server dedicated provider with activated pooling closes the connection to MS SQL Server when there is no data exchange between the client and the database server for some predefined time. If such behaviour is observed and is not desired from the system design point of view, it can be either:
- switched off by finding and using respective attributes in the connection string (if existing and supported by database) or
- by actively checking error codes returned from SQL CiCode functions and reconnecting by using SQLOpen if the connection is dropped.
The actual state of connections can be checked by SQLInfo with type 5.

This function is a blocking function and should not be called from a foreground task.

Syntax

SQLConnect(sConnect)

sConnect:

The connection string, in the format:

<attribute>=<value>[;<attribute>=<value>. . .]

Acceptable attributes and their values vary accordingly to the provider and/or the database system, so please refer to your database documentation. For example, connecting to a SQL Server via ODBC usually requires giving the computer name and the database name which can be done by defining "Server" and "Database" attributes. The same connection via OleDB requires defining the computer as "Data Source" and the database as "Initial Catalog".

Providing username and password as a plain text in the connection string may lead to a security breach on the database side. Please consider using other forms of authentication instead of username/password login, for example Windows Authentication. If this is not possible, try to limit the database account rights and not use the same username and password for other vital parts of the system such as for SCADA.

Some simple examples are shown below:

ODBC provider

"Driver={SQL Server};Server=(local);Trusted_Connection=Yes; Database=MyDatabase;"
"Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=Yes"
"Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\doc\MyDatabase.mdb"
"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\doc\MySheet.xls"
"SCADA Data Provider=Odbc;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\doc"
"DSN=MyDSNname"

OleDB provider

"SCADA Data Provider=OleDb;Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=Yes"
"SCADA Data Provider=OleDb;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb"
"SCADA Data Provider=OleDb;Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI"

SQLClient Provider

"SCADA Data Provider=SQLClient;Persist Security Info=False;Integrated Security=true;Initial Catalog=MyCatalog;server=(local)"


        
 SCADA Data Provider
The provider to be used to communicate to a database. Allowed values are as follows:
ODBC – ODBC provider, default one if no provider specified,
OLEDB – OLEDB provider,
SQLClient – MS SQL Server dedicated provider
 SCADA Connection Timeout
The timeout in seconds for establishing connections.
 SCADA Query Timeout
The timeout in seconds for executing queries. This attribute overwrites [SQL]QueryTimeout INI parameter.
Return Value
The handle to the database connection object if the connection is successful, otherwise -1 is returned. (For details call the SQLErrMsg() function). The handle identifies the database connection object where details of the associated SQL connection to a DB are stored.
Related Functions
SQLCreate, SQLOpen, SQLClose, SQLDispose, SQLConnect, SQLDisconnect, SQLInfo
Example
/* Make a connection to an SQL server and select the name fieldfrom each record in the employee database. */
FUNCTION
ListNames()
    INT hSQL;
    STRING sName;
    INT Status;
    hSQL = SQLConnect("DSN=MyDatabase;UID=billw;SRVR=CI1");
    IF hSQL <> -1 THEN
        Status = SQLExec(hSQL, "SELECT NAME FROM EMPLOYEE");
        IF Status = 0 THEN
            WHILE SQLNext(hSQL) = 0 DO
                sName = SQLGetField(hSQL, "NAME");
                .. 
            END
            SQLEnd(hSQL);
        ELSE
            Message("Information", SQLErrMsg(), 48);
        END
        SQLDisconnect(hSQL);
    ELSE
        Message("Information", SQLErrMsg(), 48);
    END
END
See Also 
SQL Functions

Published June 2018