Accessing the Database Schema

A schema entity is any autonomous structure in a schema: this includes tables, views, stored procedures, user-defined data types, and synonyms. However, indexes or primary keys are not schema entities because they cannot be described independently of a table.

Most entities belonging to a database schema can be described by means of special descriptors. All schema entity descriptors are instances of classes derived from the class IldSchemaEntity:

IldADTDescriptor for user-defined data types (supported only when connected to Object-Relational Data Base Management Systems).

It is also possible to get only the entity names and owner names. Names are returned as arrays of character strings by the following member functions of the class IldDbms:

All these functions take the owner name as an optional argument to restrict the returned names to the entities that belong to that owner. These items are described in the following order:

Any schema entity for which a descriptor class exists has an identifier in the enumeration type IldEntityType declared in the file ild.h.

For any descriptor derived from IldSchemaEntity, the actual type of the descriptor can be retrieved by calling the function IldSchemaEntity::getEntityType.

These descriptors have only one possible identifier, except for the table or view descriptor IldRelation, which can be identified by IldTableEntity or IldViewEntity, as summarized in Table 3.3.

Schema Entity Descriptors
Descriptors	Identifiers
Table	IldTableEntity
View	IldViewEntity
Procedure or function	IldCallableEntity
Synonym	IldSynonymEntity
User-defined type (abstract data type)	IldADTEntity
Error	IldUnknownEntity

The names of all owners of any entity in the schema can be retrieved by calling the function IldDbms::readOwners.

The names and owners of any entity type in the schema can be retrieved as a cursor using the function IldDbms::readEntityNames. This function takes the entity type as its first argument and, optionally, an owner’s name as its second argument, and returns a result set in form of a fetch-ready IldRequest object.

If you are interested only in table names, you can use the member function IldDbms::readRelationNames. It returns an array of all table and view names found in the schema. It is your responsibility to delete the array returned and the strings it contains, preferably using the function IldDbms::freeNames:

Some database management systems, such as Oracle, allow different users to create different tables with the same name. In this case, the array returned contains the same name several times. The second argument is optional and changes the behavior of the function:

If the user argument is specified, only the names of the tables that belong to the given user name are returned.

If you want to know all table names and their owner names, the overloaded member function IldDbms::readRelationNames returns an array of the table names and sets its parameter to an array of the owner names. It is your responsibility to delete both arrays and the strings they contain.

All procedure and function names are returned by the function IldDbms::readProcedureNames. If the optional user argument is specified, this function returns only the names of the procedures and functions that belong to that user.

All synonym names are returned by the function IldDbms::readSynonymNames. If the optional user argument is specified, this function returns only the names of the synonyms that belong to that user.


Note: This function is not supported for DB2, MS SQL Server, ODBC, and Sybase, which do not have the notion of synonyms.

All abstract data type names are returned by the function IldDbms::readAbstractTypeNames. If the optional user argument is specified, this function returns only the names of the abstract data types that belong to that user.


Note: This function is only supported for ORDBMS (Object-Relational Data Base Management Systems).

Within Rogue Wave DB Link, a database table or view is described as an object of the class IldRelation. Such objects can be created by calling one of the following functions:

IldDbms::readRelation: This function takes the table or view name as its first argument, and the owner name as its optional second argument.

This function does not cache the returned object, which, therefore, must be deleted by the application.

An overloaded version of this function takes only one argument, namely the numerical identifier of the table or view. This second version is not supported by all RDBMS.


Warning: When a table description is returned by this function, it is not possible to get its keys and indexes. If the application needs to hold a table description that is not attached to a connection, it should get it from the next function, query the keys and indexes, and then ask the description to be detached from the connection using the function IldDbms::removeRelation.

IldDbms::getRelation. This function takes the table name as its first argument and an owner name as its optional second argument. This function adds the created object to the cache managed by the caller. Therefore, the object can be accessed later without querying the server and can be deleted automatically when the caller is destroyed.

An overloaded version of this function takes only one argument, namely the numerical identifier of the table or view. This second version is not supported by all RDBMSs.


Warning: Some database systems, such as Oracle, allow different users to own different tables with the same name. With such systems, it is important to supply the user parameter. Otherwise, Rogue Wave DB Link builds the IldRelation object based on the first row returned from the database server and ignores the others.

Rogue Wave DB Link returns different types of relations, depending on the RDBMS you are connected to. The available types are IldTableEntity and IldViewEntity.

The meaning of these symbols is straightforward. Depending on the target RDBMS, loading the schema may create 0 or n IldRelation objects of type IldView Entity

Objects of the class IldRelation can be accessed to get the table characteristics, as shown in Table 3.4:

Table Descriptors
Use this function...	To get the following descriptor
IldSchemaEntity::getEntityType	Type of the table
getCount	Number of columns
IldSchemaEntity::getName	Table name
IldSchemaEntity::getOwner	Table owner
IldSchemaEntity::getDbms	Related IldDbms object
getPrimaryKey	Primary key, if any
getForeignKeys	Foreign keys, if any
getIndexes	Indexes, if any
getSpecialColumns	Special columns (—that is, the columns that uniquely identify one row in the table)

The global function IldPrintRelation in the ildutil.cpp sample file shows how to use these member functions. See Relation Searching for details.

With the exception of the IldDbms object, no value returned by these functions can be modified.

From an IldRelation object, you can reach its column descriptions. Rogue Wave DB Link preserves the column order, except with ODBC, where the column order is not specified.

A column description includes its name, size, Rogue Wave DB Link type, and native type name, as well as the flag indicating whether it accepts null values. Use the following member functions to get these attributes:

Size: getColSize(IlUShort). The column size is always in bytes. It is the actual size (the maximum size for CHAR and VARCHAR database types) used by Rogue Wave DB Link to store or send the data values.


Note: The LOB-type columns do not follow this rule. Thus, the value returned by the function getColSize is not meaningful for such columns.

DB Link type: getColType(IlUShort). This function returns a value from the enumeration IldColumnType.

Native SQL type name: getColSQLType(IlUShort). This function returns the name of the native SQL data type on the server.

The primary key, foreign keys, indexes, and special column descriptors are not created at the same time as the table description. They are retrieved from the database only the first time they are accessed, using the IldRelation functions getPrimaryKey, getForeignKeys, getIndexes, and getSpecialColumns.

If no keys of that type exist, the server is not queried again on the next call to one of these functions.

Within Rogue Wave DB Link, a database procedure or function is described as an object of the class IldCallable. Such objects can be created by calling one of the following member functions:

IldDbms::readProcedure: This function takes the procedure or function name as its first argument and an owner name as the optional second argument. The returned object is not cached. Therefore, it is the application’s responsibility to delete it.

An overloaded version of this function takes only one argument, namely the numerical identifier of the procedure or function. This second version is not supported by all RDBMSs.

IldDbms::getProcedure: This function takes the procedure or function name as its first argument and an owner name as the optional second argument. The created object is added to the cache managed by the caller. Therefore, the object can be accessed later without querying the server and can be deleted automatically when the caller is destroyed.

An overloaded version of this function takes only one argument, namely, the numerical identifier of the procedure or function. This second version is not supported by all RDBMSs.

The IldCallable object returned can represent either a stored procedure or a stored function. To differentiate between the two, a call to IldCallable::isProcedure returns IlTrue if it is a procedure description, or IlFalse otherwise.

The formal arguments to the procedure or function are represented by objects of the class IldArgument, which is derived from the class IldDescriptor. The number of arguments is returned by a call to IldCallable::getArgumentsCount.

Its input or output mode: One of the three functions IldArgument::isInArgument, IldArgument::isOutArgument, or IldArgument::isInOutArgument returns IlFalse while the other two return IlTrue.

A default value: The member function IldArgument::hasDefault tells whether the argument has a default value or not.

The return values of a function are described by instances of the class IldDescriptor. The number of returned values is given by IldCallable::getResultsCount.

Within Rogue Wave DB Link, a synonym is described as an object of the class IldSynonym. Such objects can be created by calling one of the following functions:

IldDbms::readSynonym: This function takes the synonym name as its first argument and an owner name as the optional second argument. The returned object is not cached. Therefore, it is the application’s responsibility to delete it.

An overloaded version of this function takes only one argument, namely, the numerical identifier of the synonym. This second version is not supported by all RDBMSs.

IldDbms::getSynonym: This function takes the synonym name as its first argument and an owner name as the optional second argument. The created object is added to the cache managed by the caller. Therefore, the object can be accessed later without querying the server and can be deleted automatically when the caller is destroyed.

An overloaded version of this function takes only one argument, namely, the numerical identifier of the synonym. This second version is not supported by all RDBMSs.


Note: None of these functions are supported with DB2, MS SQL Server, ODBC, and Sybase because this concept does not exist in these database systems.

Within Rogue Wave DB Link, an abstract data type is described as an object of the class IldADTDescriptor. Such objects can be created by calling one of the following functions:

IldDbms::readAbstractType: This function takes the abstract data type name as its first argument and an owner name as the optional second argument. The returned object is not cached. Therefore, it is the application’s responsibility to delete it.

An overloaded version of this function takes only one argument, namely, the numerical identifier of the abstract data type.

IldDbms::getAbstractType: This function takes the abstract data type name as its first argument and an owner name as the optional second argument. The created object is added to the cache managed by the caller. Therefore, the object can be accessed later without querying the server and can be deleted automatically when the caller is destroyed.

An overloaded version of this function takes only one argument, namely, the numerical identifier of the abstract data type.


Note: Both functions are only supported for ORDBMSs.

The privileges given to a specific table / view can be accessed by calling the function IldDbms::readTablePrivileges.

The first argument is used only by DBMSs that support three-part naming for tables (qualifier.owner.name). In particular, this is not supported by Oracle, which uses only schema.name.

Make sure that the table, schema, and catalog parameters are spelled with the correct case. The RDBMS case method must be used.