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.

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)"

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

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

See Also

SQL Functions