The OpenAccess SDK SQL engine allows the IP to manage the schema by having it handle all requests for information about tables, columns, indexes and foreign keys. This feature is valuable in the following types of situations:
• The data source's schema is dynamic; it can be changed by the end-user. This requires the IP to be able to expose a dynamic schema.
• Flexibility in schema exposed to different users—even in cases where the schema exposed for the data source is fixed, implementing it in the IP allows the IP to be selective in what tables and columns are shown to what users. It can also expose different schema depending on the type of platform or the version of data source it is accessing.
• Ease of distribution—by having the IP handle the schema in its code, there is no need to configure and ship the Schema Database to each of your customers.
This section describes what the IP must do to implement a dynamic schema that can be used by the OpenAccess SDK SQL engine when executing queries and when handling catalog functions that are initiated by ODBC calls such as SQLTables and SQLColumns, and similar calls from JDBC, ADO and .NET.
To implement a dynamic schema:
1. Have the IP return TRUE for GETSUPPORT( IP_SUPPORT_SCHEMA).
2. Implement the SCHEMA function in your IP to handle requests for OA_TABLES, OA_COLUMNS, OA_STATISTICS, OA_FKEYS, OA_PROC and OA_PROCCOLUMNS tables. Here you will access the schema information of your data source using the methods that are available to you and then format it into the schema objects that are required by the OpenAccess SDK SQL engine.
Example3, which is installed with OpenAccess SDK, contains sample dynamic schema code that you can start with. It includes additional code in case you want to implement schema filtering as part of the IP (see Filtering schema objects based on the search object).
The OpenAccess SDK SQL engine calls the SCHEMA function in the IP to ask for different types of schema objects. The object type requested is specified in the following list:
• DAMOBJ_TYPE_TABLE—retrieves a list of tables that isbased on the search condition.
• DAMOBJ_TYPE_COLUMN—retrieves a list of columns that is based on the specified search condition.
• DAMOBJ_TYPE_STAT—retrieves a list of indexes that is based on the specified search condition
• DAMOBJ_TYPE_FKEY—retrieves a list of foreign keys that is based on the specified search condition.
• DAMOBJ_TYPE_PKEY—retrieves a list of primary keys that is based on the specified search condition.
• DAMOBJ_TYPE_PROC—retrieves a list of procedures that is based on the search condition.
• DAMOBJ_TYPE_PROC_COLUMN—retrieves a list of procedure columns that is based on the specified search condition.
• DAMOBJ_TYPE_CATALOG—Returns list of Catalogs (Qualifiers or Databases) by using the damobj_table object and setting just the catalog field.
• DAMOBJ_TYPE_SCHEMA—Returns list of Schemas (Owners) by using the damobj_table object, and by setting the owner field and the qualifier field.
• DAMOBJ_TYPE_TABLETYPE—Returns list of Table Types by using the damobj_table object and setting just the table type field.
The job of IP SCHEMA is to build objects of the type specified in the call and to add them to a list provided by the OpenAccess SDK SQL engine. All objects are built using the OpenAccess SDK SQL engine schema management functions. For details on how to build the schema objects, refer to the specific OpenAccess SDK Programmer’s Reference for your programming language.
In C/C++ functions such as dam_add_damobj_table and dam_add_damobj_column are used to create schema objects for the OpenAccess SDK SQL engine. In Java and .NET, the method to add a schema object to the list of objects returned to the OpenAccess SDK SQL engine is dam_add_schemaobj.
When adding to the list, specify whether the filtering has been taken care of. If you do not filter the requested objects, then the OpenAccess SDK SQL engine handles it. The SCHEMA function is called to retrieve information about one type of object at a time. When filling in data, a null value for a character type is indicated by sending a NULL pointer and for integers by setting the value to DAMOBJ_NOTSET.
