IOPC2

IOPC 2 can be used as a database access library. It is able to send SQL commands to supported database engines and to execute SQL queries and iterate through their results. Transactions are also supported - see further. To use the provided database features we need to include and link either the iopcdb or the iopclib library / configuration.

To send any SQL commands to the database, we need to obtain and open a connection:

Database* db = 
  DriverManager::getDriver("IopcOracle10g").getDatabase("XE");
Connection* conn = 
  db->getConnection("username=iopc;password=iopc");
conn->open();

First the Driver instance is obtained using the DriverManager::getDriver() method. The Driver does not provide much functionality. Usually it is used to obtain the Database object using its Driver::getDatabase() method. Its other responsibilities will be discussed later in this section.

Driver::getDatbase() takes one parameter denoting name of the database instance the returned Database object will represent. For other database systems, the parameter can refer for example to a filename of a database that will be loaded. The Database's only interesting method at the moment is Database::getConnection.

By calling the Database::getConnection() method a physical link to the DBMS is established^[33] and a Connection object representing the link is returned. The string passed to the Database::getConnection() method is called connection string. The connection string syntax may vary between different drivers. For the Oracle10g driver the string is a semicolon delimited list of key-value pairs as shown in the example. Recognised keys are:

When the connection is no longer needed, it should be closed and returned back to the database object by calling db->returnConnection(conn). The same goes for the database and driver:

...
conn->close();
db->returnConnection(conn);
DriverManager::getDriver("IopcOracle10g").returnDatabase(db);
// alternatively:
DriverManager::returnDatabase(db);

Oracle10g driver supports transactions^[34]. First SQL statement executed after the connection is created begins first transaction. Transactions can be ended by calling Connection::commit() or Connection::rollback(). Next transaction starts again when next SQL statement is executed. In the Oracle10g database driver each transaction get committed implicitly whenever a DDL statement is issued. Closing an open connection also commits any running transaction. If autocommit is enabled, transactions are committed immediately everytime after any SQL statement is executed.

Connection::savePoint() and Connection::rollbackToSavePoint() operations are also provided by the Connection interface. They interact with the cache analogously.

Simple commands that do not return any value and that are not parameterised can be executed using the sqlNonQuery method. Let's create a new table and insert some testing values into it.

conn->sqlNonQuery("CREATE TABLE blacklist(id NUMBER PRIMARY KEY, name VARCHAR2(20))");
conn->sqlNonQuery(
  "INSERT INTO blacklist VALUES(1, 'Richard Doe')");
conn->sqlNonQuery(
  "INSERT INTO blacklist VALUES(2, 'Ola Nordmann')");
// we have to call commit() because autocommit is disabled
// for conn
conn->commit();

Now we will load and print the inserted data:

Cursor* cur = conn->sql("select id, name from blacklist");
std::string name;
int id;
cur->addOutParam(1, &id, TypeDesc<int>::getType());
cur->addOutParam(2, &name, TypeDesc<std::string>::getType());
cur->execute();
while (cur->fetchNext()) {
	cout << id << " " << name << endl;
}
cur->close();
conn->returnCursor(cur);

By calling the Connection::sql() method we obtain a Cursor which represents results of the query passed to the method. The query is not yet executed; nothing is sent to the database system until the Cursor::execute() is invoked. Before we execute the query, we may specify input and output parameters (if any). This is done by calling one of the Cursor::addXXParam(), Cursor::addEnhancedXXParam() where XX is "In" or "Out", or Cursor::addNullInParam(). Whether to call the first or the second (enhanced) method depends on if we are going to use enhanced data type or basic C++ data type (plus the STL strings) parameters. The example above uses the basic variant.

First parameter specifies the parameter position in the query^[35] and second parameter the variable to be stored/loaded. Last parameter of the Cursor::addXXParam method tells the database layer of what type the value passed as pointer in the second parameter is. In some situations, the database layer and drivers must know the memory size (sizeof) occupied by the input or output parameters. This information is also obtained from the type descriptions. However, because memory occupied by string parameters (STL or enhanced) is of variable size, a number describing memory size to be allocated for buffers needed by the database layer must be specified. This number is defined as maximum string length and defaults to 2000 characters. It can be overridden by supplying the last argument of the Cursor::addXXParam methods in this way:

MetadataHolder meta;
meta["db.type.length"].setIntValue(100);
cur->addOutParam(1, &id, TypeDesc<int>::getType());
cur->addOutParam(2, &name, TypeDesc<std::string>::getType(), meta);

The db.type.length metadata can be also specified on the type or attribute level as described in the section called “Class metadata”. Type level represents the default value for all parameters of such type (by default 2000 as mentioned above), attribute level is used by the object persistence layer - see later in this guide.

Same example as above, this time using the enhanced data types:

Cursor* cur = conn->sql("select id, name from blacklist");
EString name;
EInt id;
// OUT parameters can be added any time before fetch is invoked
// - even after execute
cur->addEnhancedOutParam(1, id);
cur->addEnhancedOutParam(2, name);
cur->execute();
while (cur->fetchNext()) {
	cout << id << " " << name << endl;
}
cur->close();
conn->returnCursor(cur);

As you see, specifying enhanced data type parameters is even simpler, the additional type description is not needed.

If the cursor is not needed, it should be closed to free blocked database resources. If it is not needed any longer, it should be released by returning it back to its connection which also releases allocated application resources. After the cursor is closed, it can be re-opened and executed again (it even be re-executed without closing and opening). The parameters are not discarded. If a cursor is returned back to its connection in an open state, it is automatically closed.

Input pramaters are used in a similar way:

Cursor* cur = conn->sql(
  "SELECT name from blacklist where id = :id");
EString name;
EInt id = 2;
cur->addEnhancedOutParam(1, name); // first input parameter
cur->addEnhancedInParam(1, id); // first output parameter
cur->execute();
while (cur->fetchNext()) {
	cout << id << " " << name << endl;
}
cur->close();
conn->returnCursor(cur);

This example loads and print only the second row inserted ("Ola Nordmann").

All objects from the iopcdb library are thread safe except for the Cursor class, which should be used always only from one thread.