SQLCreate | SQL Functions

Cicode Programming Reference > Cicode Function Categories > SQL Functions > SQLCreate

SQLCreate

Creates an internal DB connection object and returns a handle to the object for use by the other DB functions. The object is in disconnected state and can be connected to a database by executing the SQLOpen function.

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

Each DB connection object created by SQLCreate should be released by calling SQLDispose with the handle to the object. The releasing operation should be performed even when the SQL connection to DB is no longer active; for example, automatically dropped by a remote DB. Memory leaks can occur if the handles are not properly released.

This function can be called in the foreground or background.

Syntax

SQLCreate(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 use of other forms of authentication instead of username/password login as for example Windows Authentication. If not possible, try to limit the database account rights and not use the same username and password as for other vital part of the system as for example for SCADA.

NOTICE

SECURITY BREACH VIA SQL INJECTION

- Validate all textbox entries using validation controls, regular expressions and code
- Use parameterized SQL or stored procedures
- Use a limited access account to connect to the database

Failure to follow these instructions can result in equipment damage.

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 DB. 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 for establishing connections.
SCADA Query Timeout	The timeout for executing queries. This attribute overwrites [SQL]QueryTimeout INI parameter.

Return Value

The handle to the DB connection object if the connection is successful, otherwise -1 is returned. (For details call the SQLErrMsg() function). The handle identifies the DB 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 field
from each record in the employee database. */
FUNCTION
ListNames()
    INT hSQL;
    STRING sName;
    INT Status;
    INT hRec;
    INT nColumns;
    INT nRows;
    INT i;
    hSQL = SQLCreate("DSN=MyDatabase;UID=billw;SRVR=CI1");
    IF hSQL <> -1 THEN
      Status = SQLOpen(hSQL);
      IF Status = 0 THEN
        hRec = SQLGetRecordset(hSQL, "SELECT NAME FROM EMPLOYEE");
        IF hRec <> -1 THEN
          nRows = SQLRowCount(hRec);
          FOR i=0 TO nRows - 1 DO
            sName = SQLGetField(hRec, "NAME", i);
              .. 
          END
          SQLEnd(hRec);
        ELSE
            Message("Information", SQLErrMsg(), 48);
        END
        SQLClose(hSQL);
      ELSE
        Message("Information", SQLErrMsg(), 48);
      END
      SQLDispose(hSQL);
    END
END