Cicode Programming Reference > Cicode Function Categories > SQL Functions Introduction > SQLConnect

SQLConnect

Makes a connection to a database system, and returns a handle to the connection for use by the other SQL functions. Through this connection, you can execute SQL statements in the specified database. You need to call this function before any other SQL function.

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

It is strongly 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 essential (for example, recipes or reports). If you try to use SQL to store real time data, CitectSCADA's performance could be greatly decreased.

Syntax

SQLConnect(sConnect)

sConnect:

The connection string, in the format:

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

The following attributes can be used in a connection string:

DSN

Data Source Name. The name of the data source defined with the ODBC utility in the Windows Control Panel. You need to use the DSN attribute, unless you are using CitectSCADA v2.01 or earlier.

DLG

Dialog box. Set DLG to 1 to display a dialog box that allows the user to input their user ID, password, and connection string. DLG is an optional attribute.

UID

User name or Authorization/Login ID. Check the documentation for your ODBC driver and database to see if you need to use the UID attribute.

PWD

Password. Check the documentation for your ODBC driver and database to see if you need to use the PWD attribute.

MODIFY
SQL

The ability of CitectSCADA to understand and accept native SQL depends on the database driver being used. Set MODIFYSQL to 1 (the default) for an ODBC-compliant SQL. Set MODIFYSQL to 0 to use the native SQL syntax of the database system, as well as for any CitectSCADA databases you created with versions 2.01 or earlier, that employ database-specific SQL statements. The Q+E ODBC database drivers are backward compatible with those supplied with earlier versions of CitectSCADA.

REREAD AFTER UPDATE

Set to 1 to reread records from the database after updating them. Use this attribute to get the correct value of automatically updated columns, such as time and date stamps.

REREAD AFTER INSERT

Set to 1 to reread records from the database after inserting into it. Use this attribute to get the correct value of automatically-updated columns, such as time and date stamps.

DRV

Use the DRV attribute for compatibility with CitectSCADA v2.01 and earlier. Use the DRV instead of the data source name (DSN) in the connection string. It is strongly recommended not to use DRV in new CitectSCADA applications.

CitectSCADA recognizes the above attributes for all the database systems in the table below, but not all these attributes are provided for all databases. The asterisks (*) beside each database indicate the attributes you need to use to connect to that database. The acceptable values for each attribute also vary according to the database system, so select from the list to see the attributes and values:

DATABASE SYSTEM

DSN

UID

PWD

DRV

Btrieve Files

*

    

QEBTR

dBASE Files

*

    

QEDBF

EXCEL Files

*

    

QEXLS

IBM DB2

X

X

X

QEDB2

Informix

        

INGRES

*

*

  

QEING

Netware SQL

*

    

QEXQL

Oracle

*

*

*

QEORA

OS/2 and DB2/2

*

*

*

QEEE

Paradox

*

*

  

QEPDX

SQLBase/ (Gupta)

*

*

*

QEGUP

SQL Server

*

*

*

QESS

Text Files

*

*

  

QETXT

XDB Databases

*

*

*

QEXDB

DRV:

DRV names are included only for maintaining CitectSCADA applications built using v2.01 or earlier. For these early version, use DRV instead of the data source name (DSN).

X:

No longer supported directly. See information on the OS/2 and DB2/2 database drivers and the "Q+E Database Drivers Reference Manual".

Return Value

The SQL connection handle if the connection is successful, otherwise -1 is returned. (For details of the 307 error code, call the SQLErrMsg() function). The SQL connection handle identifies the table where details of the associated SQL connection are stored.

Related Functions

SQLBeginTran, SQLCommit, SQLDisconnect, SQLEnd, SQLErrMsg, SQLExec, SQLFieldInfo, SQLGetField, SQLInfo, SQLNext, SQLNoFields, SQLNumChange, SQLRollBack, SQLTraceOff, SQLTraceOn

Example

/* Make a connection to an SQL server and select the name field
from 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("Error", SQLErrMsg(), 48);

		END

		SQLDisconnect(hSQL);

	ELSE

		Message("Error", SQLErrMsg(), 48);

	END

END

See Also

SQL Functions