Structured Types

Data Access User Manual > Data Access and SQL > SQL Tables > Structured Types

Structured Types

Some database systems (for example, Oracle 9i or later, Informix 9.x) support extensible type systems in which the type of a database table column is not limited to the traditional scalar data types. It can also be either a structured type or a collection type.

Data Access represents values of the structured types as IliMemoryTable objects.

For example, a database table called PRODUCT can be defined in an Oracle 8.x database system as follows:

CREATE TYPE PART_T AS OBJECT(

PARTNO INTEGER NOT NULL,

PARTNAME VARCHAR(20) NOT NULL);

CREATE TYPE PART_TABLE_T AS TABLE OF PART_T;

CREATE TABLE PRODUCT(

PRODNO INTEGER NOT NULL PRIMARY KEY,

PRODNAME VARCHAR2(50) NOT NULL,

PARTS PART_TABLE_T);

An SQL data source named PRODUCT_DS based on the PRODUCT table can then be created. All three columns of the PRODUCT table should be included in the PRODUCT_DS data source.

The following code extract shows how the contents of the PARTS column can be used in Rogue Wave Script.

IliSQLDataSource prodDS = ...;

IliSQLTable* prodTable = prodDS->getSQLTable();

for (IlInt prodIdx = 0; prodIdx < prodTable->getRowsCount(); ++prodIdx) {

const IliTable* partsTable = prodTable->at(prodIdx, "PARTS").asTable();

const char* prodName = prodTable->at(prodIdx, "PRODNAME");

if (partsTable != NULL) {

IlvPrint("Product %s parts:", prodName);

IlInt partCount = partsTable->getRowsCount();

for (IlInt partIdx = 0; partIdx < partCount; ++partIdx) {

const char* partName = partsTable->at(partIdx, "PARTNAME");

IlvPrint(" %s", partName);

}

else

IlvPrint("Product %s does not have parts", prodName);

}

The following code extract shows how a new row can be inserted in the PRODUCT table.

IliSQLDataSource prodDS = ...;

IliSQLTable* prodTable = prodDS->getSQLTable();

// Create the nested PARTS table.

IlInt partsColno = prodTable->getColumnIndex("PARTS");

const IliDatatype* type = prodTable->getColumnType(partsColno);

IliTable* partsTable = type->makeTable();

partsTable->lock();

IliTableBuffer* partsBuf = partsTable->getBuffer();

// Insert the first part.

partsBuf->at("PARTNO") = (IlInt)610;

partsBuf->at("PARTNAME") = "Drawer";

partsTable->appendRow(partsBuf);

// Insert the second part.

partsBuf->at("PARTNO") = (IlInt)611;

partsBuf->at("PARTNAME") = "Handle";

partsTable->appendRow(partsBuf);

partsTable->releaseBuffer(partsBuf);

// Insert the new product.

IliTableBuffer* prodBuf = prodTable->getBuffer();

prodBuf->at("PRODNO") = (IlInt)61;

prodBuf->at("PRODNAME") = "Dresser";

prodBuf->at("PRODNAME") = partsTable;

prodTable->appendRow(prodBuf);

prodTable->releaseBuffer(prodBuf);

partsTable->unLock();

In both examples shown above, prodTable designates an IliSQLTable whereas partsTable designates a nested IliMemoryTable. Care must be taken not to modify the nested memory table. Doing so would inevitably lead to inconsistencies between the Data Access application and the database. Instead, a PARTS_DS SQL data source based on the PRODUCTS.PARTS nested table can be created. This data source is defined as follows through the SQL Data Source inspector:

1. Set the data source name to PARTS_DS.

2. Add the PRODUCT database table by selecting Add Tables... from the Query menu of the inspector panel.

3. In the PRODUCT table, open the PARTS column by clicking on the + sign that appears to the left of the column name.

4. Drag the PARTNO and PARTNAME columns and drop them in the SELECT section.

5. To edit the definition of the PRODUCT table, double-click on the PRODUCT table. In the Table definition dialog box that appears, make sure that the following items are defined:

Parent: PRODUCT_DS

Alias: PRODUCT

The PARTS_DS data source can be used in two different settings:

The PRODUCT_DS data source does not include the PARTS column. Instead, the PARTS_DS data source retrieves the contents of the nested PARTS table each time its select method is called. Note that in this case, there are no nested IliMemoryTable objects involved. Instead there are two IliSQLTable objects, one of them belongs to PRODUCT_DS and the second one belongs to PARTS_DS.

The PRODUCT_DS data source does include the PARTS column. Consequently, there are many nested IliMemoryTable available, one for each row in the PRODUCT_DS data source. The PARTS_DS data source can be used in this setting to edit any nested partsTable IliMemoryTable objects contained in the PRODUCT_DS data source as shown in the following code extract:

partsMemoryTable->Lock();

IliSQLDataSource* prodDS = ...;

IliSQLTable* prodTable = prodDS->getSQLTable();

IlInt prodIdx = prodDS->getCurrentRow();

const IliTable* partsMemoryTable = prodTable->at(prodIdx,

"PARTS").asTable();

if (partsMemoryTable == NULL) {

IlInt partsColno = prodTable->getColumnIndex(""PARTS");

const IliDatatype* type = prodTable->getColumnType(partsColno);

partsMemoryTable = type->makeTable();

prodTable->set(prodIdx, "PARTS", IliValue(partsMemoryTable);

partsMemoryTable = prodTable->at(prodIdx, "PARTS").asTable();

}

IliSQLDataSource* partsDS = ...;

IliSQLTable* partsSQLTable = partsDS->getSQLTable();

partsSQLTable->setCache(partsMemoryTable);

partsMemoryTable->unLock();

Once the nested memory table has been assigned to the cache property of the PARTS_DS table, it can be edited as any SQL table through PARTS_DS.

Note that the PARTS_DS data source assumes that the nested table it is editing belongs to the current row of the PRODUCT_DS data source. As a consequence, it is necessary to adjust the value of the cache property when the PRODUCT_DS data source moves to another row.

Version 6.3