Oracle® C++ Call Interface Programmer's Guide 10g Release 2 (10.2) Part Number B14294-02 |
|
|
PDF · Mobi · ePub |
This chapter describes how to retrieve metadata about result sets or the database as a whole.
This chapter contains these topics:
Database objects have various attributes that describe them; you can obtain information about a particular schema object by performing a DESCRIBE
operation. The result can be accessed as an object of the Metadata
class by passing object attributes as arguments to the various methods of the Metadata
class.
You can perform an explicit DESCRIBE
operation on the database as a whole, on the types and properties of the columns contained in a ResultSet
class, or on any of the following schema and subschema objects, such as tables, types, sequences, views, type attributes, columns, procedures, type methods, arguments, functions, collections, results, packages, synonyms, and lists
You must specify the type of the attribute you are looking for. By using the getAttributeCount()
, getAttributeId()
, and getAttributeType()
methods of the MetaData
class, you can scan through each available attribute.
All DESCRIBE
information is cached until the last reference to it is deleted. Users are in this way prevented from accidentally trying to access DESCRIBE
information that is already freed.
You obtain metadata by calling the getMetaData()
method on the Connection
class in case of an explicit describe, or by calling the getColumnListMetaData()
method on the ResultSet
class to get the metadata of the result set columns. Both methods return a MetaData
object with the describing information. The MetaData
class provides the get
xxx
()
methods to access this information.
When performing DESCRIBE
operations, be aware of the following issues:
The ATTR_TYPECODE
returns typecodes that represent the type supplied when you created a new type by using the CREATE
TYPE
statement. These typecodes are of the enumerated type TypeCode
, which are represented by OCCI_TYPECODE
constants.
Note:
Internal PL/SQL types (boolean, indexed table) are not supported.The ATTR_DATA_TYPE
returns types that represent the datatypes of the database columns. These values are of enumerated type Type
. For example, LONG
types return OCCI_SQLT_LNG
types.
Describing database metadata is equivalent to an explicit DESCRIBE
operation. The object to describe must be an object in the schema. In describing a type, you call the getMetaData()
method from the connection, passing the name of the object or a RefAny
object. To do this, you must initialize the environment in the OBJECT
mode. The getMetaData()
method returns an object of type MetaData
. Each type of MetaData
object has a list of attributes that are part of the describe tree. The describe tree can then be traversed recursively to point to subtrees containing more information. More information about an object can be obtained by calling the get
xxx
()
methods.
If you need to construct a browser that describes the database and its objects recursively, then you can access information regarding the number of attributes for each object in the database (including the database), the attribute ID listing, and the attribute types listing. By using this information, you can recursively traverse the describe tree from the top node (the database) to the columns in the tables, the attributes of a type, the parameters of a procedure or function, and so on.
For example, consider the typical case of describing a table and its contents. You call the getMetaData()
method from the connection, passing the name of the table to be described. The MetaData
object returned contains the table information. Since you are aware of the type of the object that you want to describe (table, column, type, collection, function, procedure, and so on), you can obtain the attribute list. You can retrieve the value into a variable of the type specified in the table by calling the corresponding get
xxx
()
method.
This section provides code examples for using metadata:
Example 6-1, "How to Obtain Metadata About Attributes of a Simple Database Table"
Example 6-2, "How to Obtain Metadata from a Column Containing User-Defined Types"
Example 6-3, "How to Obtain Object Oetadata from a Reference"
Example 6-4, "How to Obtain Metadata About a Select List from a ResultSet Object"
Example 6-1 How to Obtain Metadata About Attributes of a Simple Database Table
This example demonstrates how to obtain metadata about attributes of a simple database table:
/* Create an environment and a connection to the HR database */ . . /* Call the getMetaData method on the Connection object obtainedv*/ MetaData emptab_metaData = connection->getMetaData( "EMPLOYEES", MetaData::PTYPE_TABLE); /* Now that you have the metadata information on the EMPLOYEES table, call the getxxx methods using the appropriate attributes */ /* Call getString */ cout<<"Schema:"<< (emptab_metaData.getString(MetaData::ATTR_OBJ_SCHEMA))<<endl; if(emptab_metaData.getInt( emptab_metaData::ATTR_PTYPE)==MetaData::PTYPE_TABLE) cout<<"EMPLOYEES is a table"<<endl; else cout<<"EMPLOYEES is not a table"<<endl; /* Call getInt to get the number of columns in the table */ int columnCount=emptab_metaData.getInt(MetaData::ATTR_NUM_COLS); cout<<"Number of Columns:"<<columnCount<<endl; /* Call getTimestamp to get the timestamp of the table object */ Timestamp tstamp = emptab_metaData.getTimestamp(MetaData::ATTR_TIMESTAMP); /* Now that you have the value of the attribute as a Timestamp object, you can call methods to obtain the components of the timestamp */ int year; unsigned int month, day; tstamp.getData(year, month, day); /* Call getVector for attributes of list type, e.g. ATTR_LIST_COLUMNS */ vector<MetaData>listOfColumns; listOfColumns=emptab_metaData.getVector(MetaData::ATTR_LIST_COLUMNS); /* Each of the list elements represents a column metadata, so now you can access the column attributes*/ for (int i=0;i<listOfColumns.size();i++ { MetaData columnObj=listOfColumns[i]; cout<<"Column Name:"<<(columnObj.getString(MetaData::ATTR_NAME))<<endl; cout<<"Data Type:"<<(columnObj.getInt(MetaData::ATTR_DATA_TYPE))<<endl; . . /* and so on to obtain metadata on other column specific attributes */ }
Example 6-2 How to Obtain Metadata from a Column Containing User-Defined Types
This example demonstrates how to obtain metadata from a column that contains user-defined types database table.
/* Create an environment and a connection to the HR database */ ... /* Call the getMetaData method on the Connection object obtained */ MetaData custtab_metaData = connection->getMetaData( "CUSTOMERS", MetaData::PTYPE_TABLE); /* Have metadata information on CUSTOMERS table; call the getxxx methods */ /* Call getString */ cout<<"Schema:"<<(custtab_metaData.getString(MetaData::ATTR_OBJ_SCHEMA)) <<endl; if(custtab_metaData.getInt(custtab_metaData::ATTR_PTYPE)==MetaData::PTYPE_TABLE) cout<<"CUSTOMERS is a table"<<endl; else cout<<"CUSTOMERS is not a table"<<endl; /* Call getVector to obtain list of columns in the CUSTOMERS table */ vector<MetaData>listOfColumns; listOfColumns=custtab_metaData.getVector(MetaData::ATTR_LIST_COLUMNS); /* Assuming metadata for column cust_address_typ is fourth element in list*/ MetaData customer_address=listOfColumns[3]; /* Obtain the metadata for the customer_address attribute */ int typcode = customer_address.getInt(MetaData::ATTR_TYPECODE); if(typcode==OCCI_TYPECODE_OBJECT) cout<<"customer_address is an object type"<<endl; else cout<<"customer_address is not an object type"<<endl; string objectName=customer_address.getString(MetaData::ATTR_OBJ_NAME); /* Now that you have the name of the address object, the metadata of the attributes of the type can be obtained by using getMetaData on the connection by passing the object name */ MetaData address = connection->getMetaData(objectName); /* Call getVector to obtain the list of the address object attributes */ vector<MetaData> attributeList = address.getVector(MetaData::ATT_LIST_TYPE_ATTRS); /* and so on to obtain metadata on other address object specific attributes */
Example 6-3 How to Obtain Object Oetadata from a Reference
This example demonstrates how to obtain metadata about an object when using a reference to it:
Type ADDRESS(street VARCHAR2(50), city VARCHAR2(20)); Table Person(id NUMBER, addr REF ADDRESS); /* Create an environment and a connection to the HR database */ . . /* Call the getMetaData method on the Connection object obtained */ MetaData perstab_metaData = connection->getMetaData( "Person", MetaData::PTYPE_TABLE); /* Now that you have the metadata information on the Person table, call the getxxx methods using the appropriate attributes */ /* Call getString */ cout<<"Schema:"<<(perstab_metaData.getString(MetaData::ATTR_OBJ_SCHEMA))<<endl; if(perstab_metaData.getInt(perstab_metaData::ATTR_PTYPE)==MetaData::PTYPE_TABLE) cout<<"Person is a table"<<endl; else cout<<"Person is not a table"<<endl; /* Call getVector to obtain the list of columns in the Person table*/ vector<MetaData>listOfColumns; listOfColumns=perstab_metaData.getVector(MetaData::ATTR_LIST_COLUMNS); /* Each of the list elements represents a column metadata, so now get the datatype of the column by passing ATTR_DATA_TYPE to getInt */ for(int i=0;i<numCols;i++) { int dataType=colList[i].getInt(MetaData::ATTR_DATA_TYPE); /* If the datatype is a reference, get the Ref and obtain the metadata about the object by passing the Ref to getMetaData */ if(dataType==SQLT_REF) RefAny refTdo=colList[i].getRef(MetaData::ATTR_REF_TDO); /* Now you can obtain the metadata about the object as shown MetaData tdo_metaData=connection->getMetaData(refTdo); /* Now that you have the metadata about the TDO, you can obtain the metadata about the object */ }
Example 6-4 How to Obtain Metadata About a Select List from a ResultSet Object
This example demonstrates how to obtain metadata about a select list from a ResultSet
.
/* Create an environment and a connection to the database */ ... /* Create a statement and associate it with a select clause */ string sqlStmt="SELECT * FROM EMPLOYEES"; Statement *stmt=conn->createStatement(sqlStmt); /* Execute the statement to obtain a ResultSet */ ResultSet *rset=stmt->executeQuery(); /* Obtain the metadata about the select list */ vector<MetaData>cmd=rset->getColumnListMetaData(); /* The metadata is a column list and each element is a column metaData */ int dataType=cmd[i].getInt(MetaData::ATTR_DATA_TYPE); ...
The getMetaData
method is called for the ATTR_COLLECTION_ELEMENT
attribute only.