Generic Examples

SQL Interpreter — A small-scale interpreter that sends (to the database server) the queries that the user types, and then retrieves the result set.

The sample example illustrates the simplest use that can be made of DB Link libraries. It is independent of any RDBMS and is built from the files sample.cpp and ildutil.cpp.

It connects to the database server and checks whether the connection is properly established, and then creates an IldRequest object used to execute all SQL statements.

The SQL statements consist of:

Neither the insert nor the select statements use parameters. They are executed immediately using the function IldRequest::execute.

After each call to this function, the error status is checked using the operator “!”, which is applied to the reference to the caller returned by the function.

The disconnection and deletion of the IldRequest object are implicit: the deletion of the IldDbms object takes care of both aspects.

The datasmpl example shows how to handle dates and exact numeric values. It is built from the files datasmpl.cpp and ildutil.cpp. This example is RDBMS-dependent in that it changes the column data types according to the RDBMS name. See the global functions IldGetDateTypeName and IldGetNumericTypeName in the file datasmpl.cpp.

This example creates a table with two columns, the first holding numeric values, the second holding dates. For insertions, it uses the protocol for repeated execution with bound variables.

The insertions are made with the date as string feature turned off. The second time the IldRequest::bindParam function is called, the value IldDateTime is passed as the column type argument.

For the two first insertions, the exact numeric input is bound using the IldStringType column type. This choice allows you to send such values as “9876543210987.654321098” to the database server with no precision loss.

For the following insertions, the numeric as objects feature is turned on. The second parameter is then bound using the IldNumericType value for the column type argument.

For the first selection, the values are retrieved with the numeric as string feature turned on, and the date type values are retrieved using IldDateTime objects.

Before dropping the table, the previous cursor is explicitly closed using the function IldRequest::closeCursor. The drop table statement is executed using the IldDbms::execute function.

The ildsql example is a simplified SQL interpreter that sends to the database server the queries that the user types, and then retrieves the result set (if any). It is built from the files ildsql.cpp and ildutil.cpp. This example is fully RDBMS-independent.

When retrieving a row from a result set, the example first checks that the column is not null for the current row.

Then, it dynamically calls the appropriate data accessors (IldRequest::getCol<data type>Value functions) as determined through the DB Link type contained in the column descriptor (IldRelation::getColType). The code for the IldPrintTuple function is to be found in the file ildutil.cpp. This function makes use of all column type accessors.

This example can also handle more sophisticated statements like commit or rollback, and it even implements a table description facility through the command describe <table name>.

The kernel of the interpreter contains 35 lines of code, including error checking of each call to the RDBMS.

This example also supports the retrieval of multiple result sets because the fetch loop is doubled, that is, a first do-while loop retrieves the first row of a result set while the inner while loop fetches all remaining rows. When the inner loop stops due to a negative result from the call to IldRequest::hasTuple, the outer loop adds one more call to IldRequest::fetch, which starts retrieving the next result set, if any, and getting the result set column descriptions. If this call fails, no error is raised but the outer loop stops as well.

These nested loops are necessary because MS SQL Server, ODBC, and Sybase each have the capability to return several result sets for one execute call, which is the case when the SQL statement is a stored procedure call.

See the code in file ildsql.cpp for the other available options.

The example multidb illustrates the concurrence of connections and cursors. This example is built from the files multidb.cpp and ildutil.cpp.

Three different connections are created as three IldDbms objects. Each of them has three cursors attached in the form of three IldRequest objects that are used to create tables and issue SQL select statements on these tables.

The tables are then fetched using the different cursors from a same connection for each table, the calls to IldRequest::fetch being intertwined.

When the connection objects have been deleted, the cursor array is cleaned up. This is done to avoid keeping references to cursors that have become invalid because they have been deleted as a result of the corresponding connections being destroyed.

The readrel example illustrates the use of the member function IldDbms::getRelation to retrieve a table description from the database schema.

The example is built from the files readrel.cpp and ildutil.cpp.

After connecting to the database server, this code sample tries to retrieve the description of a table that should not exist in the database.

Then, a table with a primary key is created before its description is retrieved and printed.

The table description displays the owner, name, and type of the relation. Its columns show the column name, native SQL type, size (in bytes), and nullability. Finally, the primary key and index descriptions are displayed.

The IldPrintRelation global function tries to get all possible keys and indexes from the table. It successively calls the IldRelation member functions getPrimaryKey, getForeignKeys, getIndexes, and getSpecialColumns.

Refer to the file ildutil.cpp to see the code for this function.

In the next step, the descriptor is deleted. Then, the IldDbms object is requested to get this descriptor using its index in the cache, hence an error.

An error is generated once more in the next step, which consists of dropping the table, and then trying to retrieve its description.

The relnames example illustrates the use of two IldDbms member functions:

It is built from the files relnames.cpp and ildutil.cpp.

4. Finally, it asks the user for an owner name that is used to query the database for all the names of all the relations belonging to that owner.

Each function returns one or two arrays of strings that are deleted using the function IldDbms::freeNames. Using this function is mandatory when running on a PC that uses DB Link libraries in DLL forms. Otherwise, a memory access error occurs during their deletion.

The smplbnd example illustrates most of the possible combinations of input bindings. It is built from the files smplbnd.cpp and ildutil.cpp.

1. In the first step, rows are inserted, one by one, into a newly created table. This is achieved using immediate execution through the function IldRequest::execute.

2. Then, a select statement with a variable in the where clause is prepared for repeated execution. It is executed twice with different values for the variable.

3. Then, an insert statement, where all inputs are supplied through variables, is prepared for multiple execution. The array bind feature is set so that two rows will be inserted at once for each execution.

One variable per row is set to null by the function IldRequest::setParamNullInd. After that insertion, a select statement is issued to check that the rows were inserted and the null values used despite the values that were actually passed to the parameter.

4. Finally, an update statement with parameters is prepared and executed, but in the call to IldRequest::execute, a second argument is passed. This argument constrains the number of rows to update to 1 despite the bind array size of 2. The last select statement checks that only one row has been updated.

The sbinding example illustrates how to use:

It is built from the files sbinding.cpp and ildutil.cpp.

2. Then, input variables are used to insert rows in the newly created table. The prepared insert statement is used in repeated execution mode. The parameter values are passed to DB Link, which assigns the necessary memory allocations.

3. The inserted rows are then fetched and the returned values are passed as the attributes of a user-defined object by binding the output columns via the function IldRequest::bindCol.

4. Finally, new rows are inserted using user-allocated memory for the parameter bindings. Then, a select statement is executed and the function IldRequest::fetch brings column data in the user object fields.

For both select statements, null indicator buffers are bound but are not checked at fetch time. This is not safe but the returned rows are known not to contain any null values.

The rebindcl example illustrates the use of multiple bindings.

It is built from the files rebindcl.cpp and ildutil.cpp.

If the application needs to keep in memory the data retrieved from the RDBMS, it has to copy the data to its own internal buffers, since the buffer specified by the first IldRequest::bindCol operation will be overwritten by each successive IldRequest::fetch operation to record the newly retrieved data.

So, to avoid the overhead required to copy the data retrieved to another location, the bindCol function may be called between each fetch operation to specify a new memory area.

The ildtext and ildbin examples show how to handle LOBs (Large OBjects) with the use of the IldRequest functions insertLongText, insertBinary, getColLongTextValue, and getLargeObject. They are built from the files ildbin.cpp, ildtext.cpp, and ildutil.cpp.

These two samples take the data from two files whose names are given by the user, process the insertion into an ad-hoc table, and then retrieve the data into two new files.

The clob and blob examples show how to handle new CLOB and BLOB data types (reserved to Informix 9 and Oracle). These new data types are handled the same way as basic LOB types, so there is a common file between ildtext / clob (file lobtext.cpp) and ildbin / blob (file lobbin.cpp).

The async sample shows how the asynchronous feature may be used.

This feature is not implemented by every RDBMS. It may be used only against Mssql, Odbc (depending on driver capabilities), Oracle, and Sybase.

With the other RDBMSs, the sample will print a message to indicate that the feature is not implemented.

The sample will perform the following tasks:

Run several queries simultaneously. This is to demonstrate that in asynchronous mode, when the application sends a request to the RDBMS, it gets the control back immediately, even if the RDBMS did not complete its task. Then, the application is free to do some other task. In this sample, we chose to submit other request to the RDBMS. Then, from time to time, the application has to check each request to ensure that it is completed.

The cancel feature is also used to cancel a request too long to complete (run the sample with the '-c' parameter, (run the sample with no parameters for information on its usage)).