JavaScript is required to for searching.
Skip Navigation Links
Exit Print View
Developing OTDs for Oracle Java CAPS Database Adapters     Java CAPS Documentation
search filter icon
search icon

Document Information

Developing OTDs for Database Adapters

About Database OTDs

Prepared Statements

Generating ResultSet Nodes of a Stored Procedure

DB2 Database Configuration Information

DB2 Database Connectivity Information

DB2 and Binding Packages

DB2 Connect Database Connection Information

JDBC Database Connection Information

Informix Database Connection Information

Oracle Database Connection Information

SQL Server Database Connectivity Information

Sybase Database Connectivity Information

VSAM Database Connectivity Information

Creating a Database OTD

Selecting the Wizard Type

To Select the Database OTD Wizard

Connecting to a Database

To Connect to a Database

Selecting Database Objects

To Select Database Objects

Selecting Table/Views/Aliases

To Select Table/Views/Aliases

Selecting Procedures

To Select Procedures

Adding Prepared Statements

To Add Prepared Statements

Specifying the OTD Name

To Specify the OTD Name

Review Selections

To Review Your OTD Selections

Editing Existing OTDs

To Edit an Existing OTD

Creating a Database OTD

The Database OTD Wizard generates OTDs by connecting to external data sources and creating corresponding Object Type Definitions. The OTD Wizard can create OTDs based on any combination of Tables and Stored Procedures or Prepared SQL Statements. Field nodes are added to the OTD based on the Tables in the external data source. Java method and parameter nodes are added to provide the appropriate JDBC functionality. For more information about the Java methods, refer to your JDBC developer’s reference.


Note - Database OTDs are not messageable. The images in the following procedure reflect an Oracle OTD, and might appear different from the images you see depending on the type of database OTD you are creating.


Perform the following steps to create a new database OTD:

Selecting the Wizard Type

Select the type of wizard required to build an OTD in the New Object Type Definition Wizard.

To Select the Database OTD Wizard

  1. On the Project tree, right-click the Project, point to New, and then select Object Type Definition from the shortcut menu.

    The New Object Type Definition Wizard appears with the Select Wizard Type page displayed.

  2. Select the type of database to use from the list of available types.

    Figure 1 OTD Wizard Selection

    image:OTD Wizard Selection
  3. Click Next.

    The Specify Database Connection Information window appears.

Next Steps

Proceed to the following step, Connecting to a Database.

Connecting to a Database

In order to create a database OTD, you must first connect to the database for which you are creating it by specifying the connection information.

To Connect to a Database

Before You Begin

Complete the previous step, Selecting the Wizard Type.

  1. In the Connection drop-down list, select the type of connection to use.

    The resulting connection information fields that appear depend on your selection.


    image:Specify Database Connection Information Window.
  2. Specify the applicable connection information (depending on the Connection type) for your database.

    The connection information for each database type is different. See the following topics for more information.

  3. Click Next.

    The Select Database Objects window appears.

Next Steps

Proceed to the following step, Selecting Database Objects.

Selecting Database Objects

You can select any combination of Tables, Views, Procedures, or Prepared Statements that you want to include in the Database OTD.

To Select Database Objects

Before You Begin

Complete the previous step, Connecting to a Database.

  1. Select any combination of Tables/Views/Aliases, Procedures, and Prepared Statements.

    Note - Views are read-only and are for informational purposes only.



    image:Select Database Objects
  2. Click Next to continue.

    The next window to appear depends on the combination of databases objects you selected.

Next Steps

Do one of the following:

Selecting Table/Views/Aliases

Select the types of tables or views required in the OTD.


Note - Aliases are not supported in the current release of the Oracle Adapter.


To Select Table/Views/Aliases

Before You Begin

Complete the previous step, Selecting Database Objects.

  1. In the Select Tables/Views/Aliases window, click Add.
    image:Select Tables/Views

    The Add Tables window appears.


    image:Selected Tables/Views window with a table selected
  2. In the Add Tables window, select the type of criteria to use for your search.

    Search criteria can consist of table data, view only data, or both. You can include system tables in your search by selecting the check box.

  3. From the Table/View Name drop down list, select the location of your database table and click Search.

    Search for Table/View Names by entering a table name. The use of wildcard characters of ”?’, and ”*’ as part of your Table/View name search allow for greater search capabilities. For example, “AB?CD” or “AB*CD”.

  4. Select a table and then click OK.

    The table selected is added to the Selected window.

  5. Repeat the previous two steps until you have selected all needed tables.
  6. Review the tables you have selected. To make changes to the selected Table or View, click Change and do any of the following:
    1. Select or deselect the table columns on the Table/View Columns window. Change the data type for each table by highlighting the data type and selecting a different one from the drop down.

      Note - The data type is usually listed as Other when the driver cannot detect the data type. In these situations, you should change the data type to one that is more appropriate for the type of column data.



      image:Tables/Views Columns
    2. To change the data type, precision/length, or scale, click Advanced. In general, do not change the precision/length or the scale.

      Note - The Informix database driver currently deployed with the Informix adapter displays non-nullable columns in the OTD wizard dialogue box regardless of whether the columns in the database accept null values or not. When using Prepared Statement packages, select Use Fully Qualified Table/View Names in the Generated Java Code.



      image:Tables/Views Columns - Advanced
  7. When you are done making changes, click OK.
  8. On the Select Tables/Views/Aliases window, select or deselect Use Fully-Qualified Table/View Names in the Generated Java Code.

    Note - When you use fully-qualified names, the schema name or database name is preprended to the table name in the Java Code. For example, a table named employee in a schema named APM would appear as APM.employee in the generated Java code.

    Whether to use fully-qualified names depends largely on your internal business practices. Using fully-qualified named may result in improved performance, and it allows users to access tables outside of their default schemas. However, if the database schema changes in the future, the Java code could be broken. There are many more considerations to take into account. See your database documentation for more information.


  9. Click Next.

    The next window to appear depends on the database objects you chose.

Next Steps

Do one of the following:

Selecting Procedures

Select the procedure required in the OTD.


Note - A fully qualified Procedure name consists of two parts: Schema Name, and the Stored Procedure Table Name. Code that is generated in a Java Collaboration Definition appears fully qualified when the Use Fully-Qualified Procedure Names in the Generated Java Code check box is selected. When this checkbox is not selected, then only the Stored Procedure Table Name appears.


To Select Procedures

Before You Begin

Complete the previous step Selecting Database Objects, and optionally, Selecting Table/Views/Aliases.

  1. On the Select Procedures window, click Add.

    The Add Procedures window appears.


    image:Add Procedures Window
  2. On the Add Procedures window, enter the name of a Procedure or select a table from the drop down list, and then click Search.

    You can use wildcard characters in this search.


    Note - For Informix, you must use lower case schema names when calling stored procedures.


  3. In the resulting Procedure Selection list box, select a Procedure and then click OK.
    image:Add Procedures
  4. To make any changes to the selected Procedure, click Edit Parameters on the Select Procedures window.

    The Procedure Parameters window appears.


    image:Procedure Parameters
  5. To restore the data type, click Restore. When you are done, click OK.
  6. To specify how the OTD should generate the nodes for the ResultSet, click Edit ResultSets.

    The Edit ResultSets window appears.

  7. Click Add to add the type of ResultSet node you would like to generate. Click OK when you are done.
    image:Edit ResultSet

    For more information about Add types, see Generating ResultSet Nodes of a Stored Procedure.

  8. On the Select Procedures, click Next to continue.

    The Add Prepared Statements window appears.


    Note - Keep the following in mind when working with ResultSets:

    • Not all drivers and databases support stored procedures or stored procedures with ResultSets. Contact the driver’s vendor for more information.

    • In some situations, stored procedures that uses a Select statement may not return a ResultSet when a conditional statement is used. In this case, no data is returned when the next() method is called, even if the ResultSet available() method returns “true”. This result is consistent with the type of driver we use.


    • Note - When you use Insert, Update, and Delete operations, in addition to using Select, the stored procedure will return results for each operation used. We recommend invoking the enableResultSetsOnly() method if you only want to return a ResultSet for the Select statement.



Next Steps

Do one of the following:

Adding Prepared Statements

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide. Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example: insert into EMP_TAB (Age, Name, Dept No) values (?, ?, ?).

To Add Prepared Statements

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.


Note - When using a Prepared Statement, the ResultsAvailable() method will always return true. Although this method is available, you should not use it with a while loop. Doing so would result in an infinite loop at runtime and will stop all of the system’s CPU. If it is used, it should only be used with an if statement. You can process a ResultSet by looping through the next() method.


Before You Begin

Complete the steps under Selecting Database Objects, and optionally under Selecting Table/Views/Aliases and Selecting Procedures.

  1. On the Add Prepared Statements window, click Add.
    image:Prepared Statement

    The Add Prepared Statement dialog appears.

  2. Enter the name of a Prepared Statement or create a SQL statement by typing directly into the SQL Statement window.

    Note - If you are creating a VSAM OTD and are not logging into the VSAM database with the default user name, you must enter the table schema name in the SQL Prepared Statement.



    image:Prepared SQL Statement
  3. Click Save As, and provide a name for the statement.

    This name will appear as a node in the OTD.

  4. Click OK.

    The name you assigned to the Prepared Statement appears on the Add Prepared Statements window of the wizard.

  5. Repeat the previous steps as needed to add all Prepared Statements.
  6. To edit statement parameters, select the statement and then click Edit Parameters.

    The Prepared Statement Parameters window appears.


    Note - When defining a Prepared Statement with two or more tables, where multiple tables have the same column name, you must put the table name qualifier in the Prepared Statement to build the OTD.



    image:Prepared Statement Parameters
  7. To change the data type, click in the Type field and select a different type from the list.
  8. To add more parameters, click Add and then enter the required information. To remove a parameter, highlight a row and click Remove.
    image:Edit the Prepared Statement Parameters

    Note - Once you save the Prepared Statement, make sure that the ResultSet Column Name in the Prepared Statement parameters is a valid alphanumeric string with no special characters (for example, no brackets).


  9. Click OK.
  10. To edit the ResultSet Columns, click Edit ResultSet Columns.

    Both the Name and Type are editable but it is recommend you do not change the Name. Doing so will cause a loss of integrity between the ResultSet and the Database.


    Note - The OTD Wizard cannot create OTDs with complex prepared statements that use the same column name in different tables. This problem is resolved by modifying the SQL statement to use column name aliases.



    image:ResultSet Columns
  11. To add a ResultSet column, click Add and enter the required information. To remove a ResultSet column, highlight the row and then click Remove.
  12. Click OK.
  13. On the Add Prepared Statements window, click Next.

    The Specify the OTD Name window appears.

Next Steps

Proceed to the following step, Specifying the OTD Name.

Specifying the OTD Name

Specify the name that your OTD will display in the Java CAPS IDE.

To Specify the OTD Name

Before You Begin

Complete the steps one or more of these previous steps (depending on the database objects you created): Selecting Table/Views/Aliases,Selecting Procedures, and Adding Prepared Statements.

  1. Enter a name for the OTD.

    The OTD contains the selected tables and the package name of the generated classes.


    image:Naming an OTD
  2. To create an XSD file for the OTD, do the following:
    1. Select Create XSD File.
    2. Click Save As.
    3. In the Save As dialog, navigate to the directory where you want to store the file, enter a file name, and then click Save.
  3. Click Next to continue. The Review your Selections window appears.

Next Steps

Proceed to the following step, Review Selections.

Review Selections

Review the selections made for the new OTD.

To Review Your OTD Selections

Before You Begin

Complete the previous step, Specifying the OTD Name.

  1. View the summary of the OTD. If you find you have made a mistake, click Back and correct the information.
  2. If you are satisfied with the OTD information, click Finish to begin generating the OTD.

    Figure 2 Database Wizard - Summary

    image:Database Wizard - Summary

    The resulting OTD appears on the Java CAPS IDE’s canvas.