Control-ADO Basics

"CtrlADO" - DLL

To be able to use the interfaces under Windows or Linux, load the DLL library by using the keyword #uses. See chapter To load Control library. Alternatively, you can use the entry:

[ctrl]
CtrlDLL = "CtrlADO"

in the [ctrl] section of the WinCC_OA_Path/config/config.level file: If it is also used in the UI, the entry must also occur in the [ui] section.

You have to install the unixODBC package on the computer, in order to use the ODBC driver under Linux. See www.unixodbc.org.

Data types

The following data types are implemented in CTRL for the access variables:

• dbConnection

• dbCommand

• dbRecordset

Database access

A database access is generally made via a dbConnection. It specifies the data source and other connection parameters (user name, password etc.). Commands (dbCommand objects) can be executed in sequence to a dbConnection (by way of SQL command sequences for manipulating the data source) or record sets may be opened. Transactions are also processed at dbConnection level.

Record sets

Record sets define a tabular data subset of the data source that is usually defined by an SQL SELECT statement. Each record set has a current row (unless it is empty); after the record set is opened, this is the first row returned by the database. You can navigate in a record set (first row, next, previous, last etc.), i.e. the position of the current row can be changed and the data contents of the column of the current row can be read and described by specifying the column number. New rows can be created, rows can be modified and also deleted.

Possible data types

anyType is partly used as the data type for reading and writing column contents (fields), the following data types can be converted:

• Integers (8, 16, 32 and 64 bit, signed/unsigned)

• Floating point numbers (float, double, long, longLong (64 bit), ulong and uLongLong (64 bit))

• Strings

• Date/time (incl. seconds)

• Boolean values

• Blobs (only under Linux)

NULL values in the database (value not known) are returned as an empty string. Unknown data types (dbPutField cannot process DynVars, LangTextVars or BlobVars, RecVars and db... data types) are returned as the string "N/A" ("not available").

Requery

The data of a record set can be buffered internally. A "Requery" method is available to make the changes to the data source made by other users since the record set was opened visible.

Transactions

Transactions allow manipulation of the data as a batch. I.e. all changes made to the data source since a start time to be defined (start of transaction) are buffered internally and at the end are either released by the application ("Commit") or rejected ("Rollback"). The changes are only visible to other users of the data source after the end of the transaction, but immediately, however, for the application making the changes. Transactions include changes to the data source via dbCommand and dbRecordset objects.

Error Query

Each db method (with the exception of the dbEOF method, which has a boolean return value), returns a numeric return value, with 0 indicating success. If the returned values are not 0, the currently pending internal error code, an error text and an SQL error code can be retrieved with the dbGetError method. Under Windows the return error code is ADO specific.

Example

With the following code all the records of the table "PERS" of an Access database are read out and the first two columns of each row are displayed.

#uses "CtrlADO"
main(mapping event)
{
  int rc;
  dbConnection conn;
  dbRecordset rs;
  anytype fld;
  int errCnt, errNo, errNa;
  string errDesc, errState;
  rc = dbOpenConnection("DSN=MyAccessDSN", conn); // use 64bit driver
  if (rc != 0)
  {
    dbGetError(conn, errCnt, errNo, errNa, errDesc, errState);
    DebugN("Error", rc, conn, errCnt, errNo, errNa, errDesc, errState);
  }

  if (!rc)
  {
    rc = dbOpenRecordset (conn, "SELECT * FROM PERS", rs);
    if (!rc)
    {
      while (!rc && !dbEOF (rs))
      {
        rc = dbGetField (rs, 0, fld);
        if (!rc)
          DebugN("Feld 0: " , fld);
        rc = dbGetField (rs, 2, fld);
        if (!rc)
          DebugN("Feld 2: " , fld);
        rc = dbMoveNext (rs);
      }
      dbCloseRecordset (rs);
    }
    else
    {
      dbGetError(conn, errCnt, errNo, errNa, errDesc, errState);
      DebugN("Error", rc, conn, errCnt, errNo, errNa, errDesc, errState);
    }
    dbCloseConnection (conn);
  }
}

For ADO troubleshooting see chapter Trouble-shooting Control-ADO.