Powered by Zoomin Software. For more details please contactZoomin

DataDirect OpenAccess SDK Help

Table of Contents

Setting up to configure the Schema Database

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
  • Last Updated: May 12, 2026
  • 2 minute read
    • OpenAccess SDK
    • Version 8.1
    • Documentation

The Schema Database configuration is best handled through SQL queries that are placed in a file and then executed using the cmdfile command from one of the Interactive SQL client tools (odbcisql, jdbcisql, oledbisql). Enter data in each query as you would type interactively, ending it with a semicolon and a return.

An example SQL command file, /ip/schema/template/schema.sql, is installed with OpenAccess SDK.

The following table has sample queries for populating data in the various schema tables. First set up a data source for your IP in the OpenAccess SDK Service. For more information, refer to the specific OpenAccess SDK Programmer’s Reference for your programming language.

Prepare your schema.sql file with the schema information for your data source, using the queries shown in the following table.

Queries to configure the Schema Database

Table Query
OA_TABLES INSERT INTO OA_TABLES (
TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, TABLE_STRUCT, TABLE_PATH, OA_USERDATA, OA_SUPPORT, REMARKS
)
VALUES ( 'SCHEMA', 'OAUSER', 'CURVALUE', 'TABLE', 'CVA', null, null, null, 'Current value table');
OA_COLUMNS INSERT INTO OA_COLUMNS (
TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME,COLUMN_NAME, DATA_TYPE, TYPE_NAME, OA_LENGTH, OA_PRECISION, OA_RADIX, OA_SCALE, OA_NULLABLE, OA_SCOPE, OA_USERDATA, OA_SUPPORT, PSEUDO_COLUMN, OA_COLUMNTYPE, REMARKS
)
VALUES('SCHEMA', 'OAUSER', 'CURVALUE', 'TAG', 1, 'CHAR', 12, null, null, null, 0, null, null, null, 1, 0, 'Tag name'
);
OA_STATISTICS INSERT INTO OA_STATISTICS(
TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, NON_UNIQUE, INDEX_QUALIFIER, INDEX_NAME, OA_TYPE, SEQ_IN_INDEX, COLUMN_NAME, OA_COLLATION, OA_CARDINALITY, OA_PAGES, FILTER_CONDITIONS
)
VALUES('SCHEMA', 'OAUSER', 'CURVALUE', 0, null, 'TAGINDX', 3, 1, 'TAG', 'A', null, null, null
);
OA_FKEYS INSERT INTO OA_FKEYS(
PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE, FK_NAME, PK_NAME
)
VALUES('SCHEMA', 'OAUSER', 'CURVALUE', 'TAG','SCHEMA', 'OAUSER', 'TAGDIR', 'TAG',1, null, null, null
);
OA_PROC INSERT INTO OA_PROC(
OA_QUALIFIER, OA_OWNER, OA_NAME, NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS, NUM_RESULT_SETS, REMARKS, PROCEDURE_TYPE, OA_USERDATA
)
values ( 'SCHEMA', 'OAUSER', 'SALARY', NULL, NULL, NULL, 'Salary compute', 1, 's=a+b-c'
);
OA_PROCCOLUMNS INSERT INTO OA_PROCCOLUMNS (
OA_QUALIFIER, OA_OWNER, OA_NAME, COLUMN_NAME, OA_COLUMNTYPE, DATA_TYPE, TYPE_NAME, OA_LENGTH, OA_PRECISION, OA_RADIX, OA_SCALE, OA_NULLABLE, OA_USERDATA, REMARKS
)
VALUES('SCHEMA', 'OAUSER', 'SALARY', 'TOTAL', 0,1, 'CHAR', 12, null, null, null, 0, null, 'Current salary'
);

In the following procedure, substitute the data source name that has been set up for your IP in the OpenAccess SDK Service for *yourip*. The examples use *myip* as the data source name.

  1. Execute Interactive SQL (ODBCISQL, JDBCISQL, or OLEDBISQL).

  2. Connect to the yourip data source by typing:

    ISQL> connect yourip

  3. Execute the schema configuration queries that you set up in the schema.sql file by using the cmdfile command:

    ISQL> cmdfile path_of_the_schema.sql_file

  4. Test the schema configuration by querying for the list of tables:

    ISQL> SELECT * FROM OA_TABLES;

Refer to the OpenAccess SDK Administrator’s Guide for details on using Interactive SQL for ODBC, JDBC, and OLE DB.

TitleResults for “How to create a CRG?”Also Available inAlert