test

Developing OTDs for Database Adapters

Chapter 1 Developing OTDs for Database Adapters

The following sections provide instructions on how to create 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, Stored Procedures, or Prepared 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. Each of the database adapters below provides additional support, such as the Sybase adapter which supports double-byte character set (DBCS). The DBCS is a set of characters in which each character is represented by 2 bytes. The Korean language requires double-byte character sets.

The following topics are discussed:

Creating a New DB2 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 Java methods, refer to your JDBC developer’s reference guide.

Note –

Database OTDs are not messagable. For more information on messagable OTDs, see the Enterprise Service Bus User’s Guide

The following steps are required to create a new OTD for the DB2 UDB Adapter.

Select Wizard Type

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

ProcedureTo Select a Wizard Type

  1. On the project tree list, right click on the project and select Create an Object Type Definition from the shortcut menu.

  2. From the OTD Wizard Selection window, select the DB2 Database and click Next (see the following figure).

    Figure 1–1 OTD Wizard Selection

    Select Wizard Type

Connecting to a Database

ProcedureTo Connect to a Database

  1. Select the Connection type using the drop-down list (see the following figure). The rest of the Connection Information fields displayed will depend on your selection.

    Connect to Database

  2. Specify the applicable connection information (depending on the Connection type) for your database including:

    • Host Name - The server where DB2 resides.

    • Port - The port number of DB2.

    • Location (AS/400 and z/OS specific) - The name of the DB2 subsystem. To find the location of the DB2 subsystem, use the Database Query Tool to issue the following query: select current server from sysibm.sysdummy1.

    • Database (Windows/Unix specific) - The name of the database instance.

    • Collection (AS/400 and z/OS specific) - The name that identifies a group of packages. For more information on Packages, refer to Packages.

    • User Name - The user name that the adapter uses to connect to the database.

    • Password - The password used to access the database.

    • An Optional Parameters field (only for AS/400 and z/OS) will display in the OTD Wizard if the DB2_ConnectionInfo.txt file is present in the netbeans\usrdir\modules\ext\ db2adapter directory. This field allows additional connection parameters for the DB2 OTD wizard. Please contact Sun Microsystems, Inc. for more information on Optional Parameters.

      Note –

      This parameter is not specific to an OTD. It’s only specific to the session where Java CAPS IDE runs. When you edit the OTD, the current value that shows up is the value that was entered previously in the Wizard. It may or may not be the same value you used for creating the original OTD.

      Parameter examples include:

      • The showSelectableTables=false parameter can be set to false (default is true) to see additional tables listed under a user, such as the behavior in a previous Adapter which uses an older version of the driver.

      • The AlternateID=user1 parameter allows you to set the object owner to be user1 otherwise the user in the User Name field will be used.

      • Multiple parameters can be used and must be separated by a semi-colon (;).

      • Click Next. The Select Database Objects window appears.

Packages

This Adapter uses a DataDirect driver (previously known as Merant) to execute SQL calls in DB2. The DataDirect driver requires packages to be created in the DB2 System. Packages do not contain specific SQL statements like static SQL packages but rather dynamic sections, used like cursors to help facilitate the driver’s executing of dynamic SQL queries and returning results.

Creating packages on the server, also known as binding packages, needs only be done once. The first user of the OTD Wizard must have bind permission to create the packages. Without bind authority the user receives an error message when the driver attempts to bind the packages and they will be unable to issue any SQL call. Packages are created automatically, under the Collection ID, when the user fills in the Wizard entries. If the Collection ID is left as blank, it will generate the packages under NULLID.

The driver creates SQL packages on the database including: DDJC330A, DDJC330B, DDJC330C, etc. When connecting, the driver queries a system table to determine whether the default packages exist on the system. If none exist, the driver creates them.

Note –

SQL applications that execute dynamic SQL against DB2 need to have packages bound on the server. In the case of some IBM native tools this may not be obvious because the packages are already installed on the database by default.

Selecting Database Objects

ProcedureTo Select a Database

  1. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the .otd file. Click Next to continue (see the following figure).

    Select Database Objects

  2. Click Next to continue. The Select Tables/Views window appears.

    Note –

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

Selecting Table/Views/Aliases

ProcedureTo Select Tables/Views/Aliases

  1. In the Select Tables/Views/Aliases window, click Add (see the following figure).

    Tables/Views/Aliases

  2. In the Add Tables window, select the type of criteria to be used for your search, consisting of table data, view only data, or both. You can include system tables in your search by selecting the checkbox.

  3. From the Table/View Name drop down list, select the location of your database table and click Search. You can 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 the table of choice and click OK. The table selected is added to the Selected window (see the following figure).

    Add Tables

  5. On the Selected Tables/Views window, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. If you clicked Change on the Selected Tables/Views window, you can select or deselect your table columns on the Table/View Columns window. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down (see the following figure).

    Table View Columns

  7. Click Advanced to change the data type, precision/length, or scale. In general, do not change the precision/length or the scale. Once you have finished your table choices, click OK (see the following figure).

    Table View Column Advanced

Selecting Procedures

ProcedureTo Select Procedures

  1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

    Select Procedures

  2. On the Select Procedures window, enter the name of a Procedure or select a table from the drop down list. Click Search. Wildcard characters can also be used.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Add Procedures

  4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure (see the following figure).

    Procedure Parameters

  5. To restore the data type, click Restore. When finished, click OK.

  6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.

  7. Click Add to add the type of Resultset node you would like to generate.

    Edit ResultSets

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are the "By Executing", "Manually", and "With Assistance" modes.

    • By Executing Mode — “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes.

    • With Assistance Mode — "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query.

    • Manually Mode — "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query.


      SELECT A, B, 3*C FROM table T

      is generated by the database. In this case, "With Assistance" mode is a better choice.

      If you modify the ResultSet generated with the by the “By Executing Mode” of the Database Wizard, you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.

  8. When using Prepared Statement packages, select Use fully qualified table/view names in the generated Java code.

Add 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 execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

ProcedureTo Add Prepared Statements

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 the ”if’ statement. You can process a resultset by looping through the next() method.

  1. On the Add Prepared Statements window, click Add.

    Figure 1–2 Prepared Statement

    Add Prepared Statement

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. When finished creating the statement, click Save As giving the statement a name. This name appears as a node in the OTD. Click OK (see the following figure).

    Figure 1–3 Prepared SQL Statement

    Prepared Statement Screen

  3. On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

    Note –

    When doing 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.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK (see the following figure).

    Figure 1–4 Edit the Prepared Statement Parameters

    Prepared Statement Parameters

  5. 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. Click OK (see the following figure).

    Note –

    The OTD Wizard fails to 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.

    Figure 1–5 ResultSet Columns

    ResultSet Columns

  6. On the Add Prepared Statements window, click OK.

Specifying the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes (see the following figure).

    Specify the OTD Name

  2. Click Next.

Reviewing Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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 (see the following figure).

    Figure 1–6 Database Wizard - Summary

    DB Wizard Summary

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

Editing Existing OTDs

A single OTD can consist of many Database objects. They can be a mixture of Tables, Prepared Statements and Stored Procedures. By using the Database OTD Wizard, the OTD Edit feature allows you to:

ProcedureTo Edit an Existing OTD

When a minor change is needed for an existing OTD, there is no need to rebuild it from scratch; instead, you can edit the OTD. To edit an OTD, complete the following steps:

  1. In the project tree list, right-click on the OTD. From the submenu, click Edit. The Database Connection Information Wizard opens.

  2. Connect to the DB2 database by entering the applicable information in the wizard. Once the connection is established, the Database Wizard opens, allowing you to make modifications to the OTD.

  3. Once you have completed editing the OTD, click the Finish button to save the changes.

    Caution – Caution –

    Once the OTD has been edited, you must verify that the changes are reflected in the Collaboration so that no errors occur at runtime. For example, if during the edit process, you delete a database object that is included in a Collaboration, the Collaboration could fail at build or run-time.

    When editing an OTD, you can connect to another instance of the database under the following conditions:

    • The same type of DB2 database must be used. Because of incompatibility of certain features in the databases, switching between DB2 databases that run on z/OS, AS/400 and Windows/UNIX is not supported.

    • The same version of the database should be used unless the newer version is compatible with the older version.

    • Tables in the database must be defined with the same definition.

    • The stored procedures must be identical.

    • For tables/stored procedures built with 'qualified-name’, the schema name for the tables/stored procedures must be identical in both database instances.

Creating a DB2 Connect 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 messagable. For more information on messagable OTDs, see the Sun Enterprise Service Bus User’s Guide.

The following steps are required to create a new OTD for the DB2 Connect Adapter.

Select Wizard Type

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

ProcedureTo Select the Oracle Database OTD Wizard

  1. On the project tree, right click on the project and select Create an Object Type Definition from the shortcut menu.

  2. From the OTD Wizard Selection window, select the DB2 Connect Database and click Next (see the following figure).

    Figure 1–7 OTD Wizard Selection

    Wizard Selection

Connecting to Database

Enter the DB2 Connect connection information in the Connection Information frame.

ProcedureTo Connect to a Database

  1. Select the Connection type using the drop-down list (see the following figure). The resulting Connection Information fields displayed will depend on your selection (i.e. selecting a Type 2 or Type 4 connector).

  2. Specify the applicable connection information (depending on the Connection type) for your database including:

    Connect to Database

    For the Type 2 Connection (shown above):

    • Database - The name of the database instance.

    • User Name - The user name that the Adapter uses to connect to the database.

    • Password - The password used to access the database.

      DB Connect Type 4

    For the Type 4 Connection(shown above):

    • Host Name - The server where DB2 Connect resides.

    • Port - The port number of DB2 Connect.

    • Database - The name of the database instance that the DB2 is running on Windows/Unix. The name of the locator for the DB2 running in z/OS or AS/400.

    • User Name - The user name that the adapter uses to connect to the database.

    • Password - The password used to access the database.

  3. Click Next. The Select Database Objects window appears.

Selecting Database Objects

Select the type of DB2 Connect objects you want included in the OTD.

ProcedureTo Select Database Objects

  1. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the .otd file. Click Next to continue. (See the following figure).

    Note –

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

    Select Database Objects

  2. Click Next to continue. The Select Tables/Views/Aliases window appears.

Selecting Table/Views/Aliases

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

ProcedureTo Select Table/Views/Aliases

  1. In the Select Tables/Views window, click Add (see the figure below).

    Select Tables/Views

  2. In the Add Tables window, select the type of criteria to be used for your search, consisting of table data, view only data, or both. You can include system tables in your search by selecting the checkbox.

  3. From the Table/View Name drop down list, select the location of your database table and click Search (see the following figure). You can 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 the table of choice and click OK. The table selected is added to the Selected window (see the following figure).

    DB Wizard System

  5. On the Selected Tables/Views window, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. If you clicked Change on the Selected Tables/Views window, you can select or deselect your table columns on the Table/View Columns window. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down (see the following figure).

    Table View Columns

  7. Click Advanced to change the data type, precision/length, or scale. In general, do not change the precision/length or the scale. Once you have finished your table choices, click OK (see the following figure).

    Table View Advanced

  8. When using Prepared Statement packages, select Use fully qualified table/view names in the generated Java code.

Selecting Procedures

Select the procedure required in the OTD.

ProcedureTo Select Procedures

  1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

    Select Procedures

  2. On the Select Procedures window, enter the name of a Procedure or select a table from the drop down list. Click Search. Wildcard characters can also be used.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Add Procedure

  4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure. (see the following figure).

    Procedure Parameters

  5. To restore the data type, click Restore. When finished, click OK.

  6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.

  7. Click Add to add the type of Resultset node you would like to generate (see the following figure).

    Edit ResultSet

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are the "By Executing", "Manually", and "With Assistance" modes.

    By Executing Mode 

    “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes. 

    With Assistance Mode 

    "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query. 

    Manually Mode 

    "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query. 


    SELECT A, B, 3*C FROM table T

    is generated by the database. In this case, "With Assistance" mode is a better choice. 

    If you modify the ResultSet generated by the “Execute” mode of the Database Wizard you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved. 

  8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

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 (?, ?, ?)

ProcedureTo 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 the ”if’ statement.You can process a resultset by looping through the next() method.

  1. On the Add Prepared Statements window, click Add.

    Prepared Statements

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. When finished creating the statement, click Save As giving the statement a name. This name will appear as a node in the OTD. Click OK (see the following figure).

    Add Prepared Statement

  3. On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the data type by clicking in the Type field and selecting a different type from the list.

    Note –

    When doing 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.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK. (see the following figure).

    Prepared Statement Parameters

  5. 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. Click OK (see the following figure).

    Note –

    The OTD Wizard fails to 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.

    Resultset Columns

  6. On the Add Prepared Statements window, click OK.

Specifying the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD.

    The OTD contains the selected tables and the package name of the generated classes (see the following figure).

    DB OTD Name Screen

  2. Click Next to continue. The Review your Selections window appears.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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 (see the following figure).

    Figure 1–8 Database Wizard - Summary

    Wizard Summary

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

Editing Existing OTDs

A single OTD can consist of many Database objects. They can be a mixture of Tables, Prepared Statements and Stored Procedures. By using the Database OTD Wizard, the OTD Edit feature allows you to:

ProcedureTo Edit an Existing OTD

When a minor change is needed for an existing OTD, there is no need to rebuild it from scratch; instead, you can edit the OTD.

  1. In the project tree, right-click on the OTD. From the submenu, click Edit. The Database Connection Information Wizard opens.

  2. Connect to the database by entering the applicable information in the wizard. Once the connection is established, the Database Wizard opens, allowing you to make modifications to the OTD.

  3. Once you have completed editing the OTD, click the Finish button to save the changes.

    Caution – Caution –

    Once the OTD has been edited, you must verify that the changes are reflected in the Collaboration so that no errors occur at runtime. For example, if during the edit process, you delete a database object that is included in a Collaboration, the Collaboration could fail at activation or run-time.

    When editing an OTD, you can connect to another instance of the database under the following conditions:

    • The same version of the database should be used unless the newer version is compatible with the older version.

    • Tables in the database must be defined with the same definition.

    • The stored procedures must be identical.

    • For tables/stored procedures built with qualified-name, the schema name for the tables/stored procedures must be identical in both database instances.

Creating a New Informix OTD

The following steps are required to create a new OTD for the Informix Adapter.

Select Wizard Type

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

ProcedureTo Select the Informix Database OTD Wizard

  1. On the Project tree, right click the Project and select New > Object Type Definition from the shortcut menu. The Select Wizard Type page appears, displaying the available OTD wizards.

  2. From the New Object Type Definition Wizard window, select the Informix Database and click Next. See the following figure.

    Figure 1–9 OTD Wizard Selection

    OTD Wizard Selection

Connect To Database

ProcedureTo Connect to a Database

  1. From the New Object Type Definition Wizard window, select Informix Database and click the Next button. The New Wizard - Informix Database window appears.

    Database Connection Information

  2. Enter the Informix database connection information in the Connection Information frame.

    Required Database Connection Fields include:

    • Host name– the name of the host to which you are connecting.

    • Port ID– the host port number (1526 is the default).

    • Informix Server– the name of the Informix server.

    • Database name– the name of the database to which you are connecting.

    • User name– a valid Informix database username.

    • Password– a password for the user name noted above.

  3. Click Next. The Select Database Objects window appears.

ProcedureSelect Database Objects

Select the type of Informix database objects you want included in the OTD.

Steps Required to Select Database Objects Include:

  1. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the OTD file. Click Next to continue. See the following figure.

    Note –

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

    Figure 1–10 Select Database Objects

    Select Database Objects

ProcedureSelect Tables/Views/Aliases

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

Steps Required to Select Table/Views/Aliases Include:

  1. In the Select Tables/Views/Aliases window, click Add. See the following figure.

    Figure 1–11 Select Tables/Views

    Select Tables/Views

  2. In the Add Tables window, select if your selection criteria will include table data, view only data, both, and/or system tables.

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

    Figure 1–12 Database Wizard - All Schemes

    Database Wizard - All Schemes

  4. Select the table of choice and click OK.

    The table selected is added to the Selected Tables/Views/Aliases section (see the following figure).

    Figure 1–13 Selected table and column window

    Selected table and column window

  5. In the Selected Tables/Views/Aliases section, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. In the Table/View Columns window, you can select or deselect your table columns. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down list. If you would like to change any of the tables columns, click Change. See the following figure.

    The data type is usually listed as Other when the driver cannot detect the data type. In these situations we recommend changing the data type to one that is more appropriate for the type of column data.

    Figure 1–14 Table/View Columns window

    Table/View Columns window

  7. Click Advanced to change the data type, percision/length, or scale. Once you have finished your table choices, click OK. In general, you will not need to make any changes.

    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.

ProcedureSelect Procedures

Select the type of stored procedures required in your OTD.

Steps Required to Select Stored Procedures Include:

  1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

    Figure 1–15 Select Procedures window

    Select Procedures window

  2. On the Select Procedures window, enter the name of a Procedure or select a table from the drop down list. Click Search. Wildcard characters can also be used.

    Note –

    You must use lower case schema names when calling stored procedures.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Figure 1–16 Add Procedures

    Add Procedures

    Note –

    The Informix database driver currently deployed with the Informix adapter does not return metadata to provide fully the type of stored procedure available when a search is executed. Thus the Type field for all procedures will be populated with “Unknown.”

  4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure.

    Figure 1–17 Procedure Parameters

    Procedure Parameters

  5. To restore the data type, click Restore. When finished, click OK.

  6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.

  7. Click Add to add the type of Resultset node you would like to generate.

    Figure 1–18 Edit Resultset

    Edit Resultset

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are "By Executing", "Manually", and "With Assistance" modes.

    • "By Executing" mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes.

    • "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query.

    • "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query.


      SELECT A, B, 3*C FROM table T

      is generated by the database. In this case, "With Assistance" mode is a better choice.

      If you modify the ResultSet generated by the “Execute” mode of the Database Wizard you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.

  8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

ProcedureAdd Prepared Statements

Add a Prepared Statement object to your OTD.

Steps Required to Add Prepared Statements Include:

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 the ”if’ statement.

  1. On the Add Prepared Statements window, click Add. The Add Prepared Statement window appears.

    Figure 1–19 Prepared Statement

    Prepared Statement

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. When finished creating the statement, click Save As giving the statement a name. This name will appear as a node in the OTD. Click OK. See the following figure.

    Figure 1–20 Prepared SQL Statement

    Prepared SQL Statement

  3. On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK. Figure 1–21.

    Figure 1–21 Add Prepared Statement window

    Add Prepared Statement window

  5. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

    Figure 1–22 Edit the Prepared Statement Parameters

    Edit the Prepared Statement Parameters

  6. Click Add to add a new ResultSet column. Both the Name and Type are editable.

    Figure 1–23 ResultSet Columns

    ResultSet Columns

  7. Click OK to return to the Add Prepared Statements window.

ProcedureSpecify the OTD Name

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

Steps Required to Specify the OTD Name:

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes. See the following figure.

    Figure 1–24 Naming an OTD

    Naming an OTD

  2. Click Next.

ProcedureReview Selections

Review the selections made for the new OTD.

Steps Required to Review Your OTD Selections:

  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. See the following figure.

    The resulting OTD appears on the Java CAPS IDE.

    Figure 1–25 Database Wizard - Summary

    Database Wizard - Summary

Steps to Edit an Existing Informix OTD

You can edit any database OTD you create directly from the Java CAPS IDE.

ProcedureSteps to Edit the OTD from the Java CAPS IDE Include:

  1. Unlock the OTD. To do this, right-click the OTD in the Project tree and select Version Control > Check Out from the menu.

    The Version Control - Check Out window appears.

  2. Select the OTD you want to check out, then click Check Out.

  3. From the Project tree, right-click the OTD again and select Edit from the menu.

    The Informix Database Connection Information wizard appears.

  4. Enter the connection information as described in To Connect to a Database, and click Next.

  5. Step through each of the wizard steps and click Finish to save your changes.

    Note –

    You must verify during project activation or at runtime that no errors are generated after editing an OTD. Errors could occur if you delete a database object that is included in a Collaboration.

Creating a New JDBC OTD

The following steps are required to create a new OTD for the JDBC Adapter.

Select Wizard Type

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

ProcedureTo Select the JDBC Database OTD Wizard

  1. On the Project tree, right click the Project and select New ⇒ Object Type Definition from the shortcut menu. The Select Wizard Type page appears, displaying the available OTD wizards. See the following figure.

    OTD Wizard Selection

ProcedureTo Connect To Database

  1. From the New Object Type Definition Wizard window, select JDBC Database and click the Next button. The New Wizard - JDBC Database window appears.

  2. Enter the JDBC database connection information in the Connection Information frame.

    Required Database Connection Fields include:

    • Driver Jar Files– the location of the driver JAR file.

    • Driver Java Class Name– the name of the Driver Manager Class.

    • URL Connection String– the URL connection string for the driver.

    • User name– a valid JDBC database username.

    • Password– a password for the user name noted above.

      Database Connection Information

Select Database Objects

Select the type of JDBC database objects you want included in the OTD.

ProcedureTo Select Database Objects

  1. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the OTD file. Click Next to continue. See the following figure.

    Note –

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

    Select Database Objects

Select Tables/Views/Aliases

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

ProcedureTo Select Table/Views/Aliases

  1. In the Select Tables/Views/Aliases window, click Add. See the following figure.

    Select Tables/Views/Aliases

  2. In the Add Tables window, select if your selection criteria will include table data, view only data, both, and/or system tables.

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

    Database Wizard - All Schemes

  4. Select the table of choice and click OK.

    The table selected is added to the Selected Tables/Views/Aliases section. See the following figure.

    Selected Tables/Views/Aliases window with a table selected

  5. In the Selected Tables/Views/Aliases section, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. In the Table/View Columns window, you can select or deselect your table columns. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down list. If you would like to change any of the tables columns, click Change. See the following figure.

    The data type is usually listed as Other when the driver cannot detect the data type. In these situations we recommend changing the data type to one that is more appropriate for the type of column data.

    Table/View Columns

  7. Click Advanced to change the data type, precision/length, or scale. Once you have finished your table choices, click OK. In general, you will not need to make any changes. See the following figure.

    Table/View Columns—Advanced

Select Procedures

Select the type of stored procedures required in your OTD.

ProcedureTo Select Stored Procedures

  1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

    Select Procedures and specify Resultset and Parameter Information

  2. On the Select Procedures window, enter the name of a Procedure or select a schema from the drop down list. Click Search. Wildcard characters can also be used.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Add Procedures

  4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure. See the following figure.

    Procedure Parameters

  5. To restore the data type, click Restore. When finished, click OK.

  6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.

  7. Click Add to add the type of Resultset node you would like to generate.

    Edit Resultset

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are "By Executing", "Manually", and "With Assistance" modes.

    By Executing Mode 

    “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes. 

    With Assistance Mode 

    "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query. 

    Manually Mode 

    "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query. 


    SELECT A, B, 3*C FROM table T

    is generated by the database. In this case, "With Assistance" mode is a better choice. 

    If you modify the ResultSet generated by the “Execute” mode of the Database Wizard you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved. 

  8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

    Note –

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

Add Prepared Statement

Add a Prepared Statement object to your OTD.

ProcedureTo Add Prepared Statements

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 the if statement.

  1. On the Add Prepared Statements window, click Add.

    Prepared Statement

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. When finished creating the statement, click Save As giving the statement a name. This name will appear as a node in the OTD. Click OK. See the following figure.

    Prepared SQL Statement

  3. On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK. See the following figure.

    Edit the Prepared Statement Parameters

  5. To edit Resultset Columns, click Edit Resultset Columns. The ResultSet Columns window appears. See the following figure.

    ResultSet Columns

  6. Click Add to add a new ResultSet column. Both the Name and Type are editable.

  7. Click OK to return to the Add Prepared Statements window.

Specify the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes. See the following figure.

    Naming an OTD

  2. Click Next.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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. See the following figure.

    The resulting OTD appears on the Java CAPS IDE.

    Database Wizard - Summary

Editing an Existing JDBC OTD

You can edit any database OTD you create directly from theJava CAPS IDE.

ProcedureTo Edit the OTD from the Java CAPS IDE

  1. Unlock the OTD. To do this, right-click the OTD in the Project tree and select Version Control ⇒ Check Out from the menu.

    The Version Control - Check Out window appears.

  2. Select the OTD you want to check out, then click Check Out.

  3. From the Project tree, right-click the OTD again and select Edit from the menu.

    The JDBC Database Connection Information wizard appears.

  4. Enter the connection information as described in To Connect To Database, and click Next.

  5. Step through each of the wizard steps and click Finish to save your changes.

    Note –

    You must verify during project activation or at runtime that no errors are generated after editing an OTD. Errors could occur if you delete a database object that is included in a Collaboration.

Creating an Oracle OTD

The following steps are required to create a new OTD for the Oracle adapter.

Select Wizard Type

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

ProcedureTo Select the Oracle Database OTD Wizard

  1. On the project tree, right click the Project and select New ⇒ Object Type Definition from the shortcut menu. The Select Wizard Type page appears, displaying the available OTD wizards.

  2. From the New Object Type Definition Wizard window, select the Oracle Database and click Next. (see the following figure).

    Figure 1–26 OTD Wizard Selection

    OTD Wizard Selection

Connect To Database

Enter the Oracle database connection information in the Connection Information frame.

Database Connection Information

ProcedureTo Connect to the Database

  1. Specify the applicable connection information for your database including:

    • Host Name - The server where Oracle resides.

    • Port ID- The port number of Oracle.

    • SID - The name of the Oracle instance (equivalent to the database name).

    • User Name - The user name that the Adapter uses to connect to the database.

    • Password - The password used to access the database.

  2. Click Next. The Select Database Objects window appears.

Select Database Objects

Select the type of Oracle database objects you want included in the OTD.

ProcedureTo Select Database Objects

  1. In the Select Database Objects dialog box (shown below), select Tables/Views for this sample. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the .otd file. Click Next to continue.

    Note –

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

    Select Database Objects

  2. Click Next to continue. The Select Tables/Views/Aliases window appears (depending on your selection).

Select Tables/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.

ProcedureTo Select Table/Views/Aliases

  1. In the Select Tables/Views/Aliases window, click Add (see the figure below).

    Select Tables/Views

  2. In the Add Tables window, select the type of criteria to be used for your search, consisting of table data, view only data, or both. You can include system tables in your search by selecting the checkbox.

  3. From the Table/View Name drop down list, select the location of your database table and click Search. You can 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 the table of choice and click OK. The table selected is added to the Selected window (see the figure below).

    Selected Tables/Views window with a table selected

  5. On the Selected Tables/Views window, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. If you clicked Change on the Selected Tables/Views window, you can select or deselect your table columns on the Table/View Columns window. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down (see the figure below).

    Tables/Views Columns

  7. Click Advanced to change the data type, precision/length, or scale. In general, do not change the precision/length or the scale. Once you have finished your table choices, click OK (see the figure below).

    Tables/Views Columns - Advanced

  8. When using Prepared Statement packages, select Use fully qualified table/view names in the generated Java code.

Select Procedures

Select the procedure required in the OTD.

ProcedureTo Select Procedures

  1. In the Select Procedures and specify ResultSet and Parameter Information dialog box, click Add.

    Select Procedures and specify ResultSet and Parameter Information

  2. In the Select Procedures dialog box, enter the name (case-sensitive) of a Procedure or select a table from the drop-down list. Click Search. You can use Wildcard characters.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Add Procedures

  4. In the Select Procedures and specify ResultSet and Parameter Information dialog box, click Edit Parameters to make changes (as needed) to the selected Procedure (see the figure below).

    Procedure Parameters

  5. To restore the data type, click Restore. When you are finished, click OK.

  6. Click Edit ResultSets to have the wizard generate the ResultSet(s) for the Oracle OTD.

  7. Select the type of Resultset you want to generate and click Add.

    Edit ResultSet

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are the "By Executing", "Manually", and "With Assistance" modes.

    • By Executing Mode — “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes.

    • With Assistance Mode — "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query.

    • Manually Mode — "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query.


      SELECT A, B, 3*C FROM table T

      is generated by the database. In this case, "With Assistance" mode is a better choice.

      If you modify the ResultSet generated with the by the “By Executing Mode” of the Database Wizard, you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.

  8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

Add Prepared Statement

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 execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

ProcedureTo Add Prepared Statements

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 the if statement.You can process a resultset by looping through the next() method.

  1. In the Add Prepared Statements dialog box, click Add.

    Figure 1–27 Prepared Statement

    Prepared Statement

  2. Enter the name of a Prepared Statement and create a SQL statement by clicking in the SQL Statement dialog box or by clicking the Statement Builder button. When you are finished creating the statement, click Save As, which gives the statement the name you just entered. This name appears as a node in the OTD (see the figure below). Click OK.

    Figure 1–28 Prepared SQL Statement

    Prepared SQL Statement

  3. In the Add Prepared Statement dialog box, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

  4. Click Add if you want to add additional parameters to the Statement, or highlight a row and click Remove to remove the parameter (see the following figure). Click OK.

    Note –

    Once you save the Prepared Statement, make sure that the ResultSet Column Name, in the Prepared Statement parameters, is a valid alpha-numeric string with no special characters (e.g. no brackets).

    Figure 1–29 Edit the Prepared Statement Parameters

    Edit the Prepared Statement Parameters

  5. To edit the ResultSet Columns, click Edit ResultSet Columns (see the figure below). Although both the Name and Type are editable, Sun recommends that you do not change the Name because it can cause a loss of integrity between the ResultSet and the Database. Click OK.

    Note –

    The OTD Wizard fails to 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.

    Figure 1–30 ResultSet Columns

    ResultSet Columns

  6. In the Add Prepared Statements dialog box, click OK.

Specify the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes (see the following figure).

    Figure 1–31 Naming an OTD

    Naming an OTD

  2. Click Next.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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 (see the following figure).

    The resulting OTD appears in the Java CAPS IDE.

    Figure 1–32 Database Wizard - Summary

    Database Wizard - Summary

Creating a New SQL Server 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 messagable. For more information on messagable OTDs, see the Enterprise Service Bus User’s Guide.

The following steps are required to create a new OTD for the SQL Server Adapter.

Select Wizard Type

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

ProcedureTo Select Wizard Type

  1. On the project tree, right click on the project and select Create an Object Type Definition from the shortcut menu.

  2. From the OTD Wizard Selection window, select the SQL Server Database and click Next (see the following figure).

    OTD Wizard Selection

Connect to Database

ProcedureTo Connect to Database

  1. Enter the SQL Server database connection information in the Connection Information frame.

    Required Database Connection Fields include:

    • Host name– the database service host name.

    • Port ID– the database service connection port ID/number.

    • Database name– the name of the SQL Server database.

    • User name– a valid SQL Server database username.

    • Password– a password for the user name noted above.

      Database Connection Information

  2. Click Next. The Select Database Objects dialog box appears

Select Database Objects

ProcedureTo Select Database Objects

  1. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the .otd file.

    Note –

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

    Figure 1–33 Select Database Objects

    Select Database Objects

  2. Click Next to continue.

Select Table/Views/Aliases

ProcedureTo Select Table/Views/Aliases

  1. In the Select Tables/Views/Aliases window, click Add (see the following figure).

    Select Tables/Views/Aliases

  2. In the Add Tables window, select if your selection criteria will include table data, view only data, both, and/or system tables.

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

    Database Wizard - All Schemes

  4. Select the table of choice and click OK. The table selected is added to the Selected Tables/Views window (see the following figure).

    Selected Tables/Views window with a table selected

  5. In the Selected Tables/Views window, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. In the Table/View Columns window, you can select or deselect your table columns. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down list. If you would like to change any of the tables columns, click Change.

    The data type is usually listed as Other when the driver cannot detect the data type. In these situations we recommend changing the data type to one that is more appropriate for the type of column data.

    Table/View Columns

  7. Click Advanced to change the data type, precision/length, or scale. Once you have finished your table choices, click OK. In general, you will not need to change these settings (see the figure below).

    Table/View Columns – Advanced

  8. When using Prepared Statement packages, select Use fully qualified table/view names in the generated Java code.

Select Procedures

ProcedureTo Select Procedures

  1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

    Select Procedures and specify Resultset and Parameter Information

  2. On the Select Procedures window, enter the name of a Procedure or select a table from the drop down list. Click Search. Wildcard characters can also be used.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Add Procedures

  4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure (see the following figure).

    Procedure Parameters

  5. To restore the data type, click Restore. When finished, click OK.

  6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.

  7. Click Add to add the type of Resultset node you would like to generate.

    Edit Resultset

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are "By Executing", "Manually", and "With Assistance" modes.

    • By Executing Mode — “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes.

    • With Assistance Mode — "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query.

    • Manually Mode — "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query.


      SELECT A, B, 3*C FROM table T

      is generated by the database. In this case, "With Assistance" mode is a better choice.

      If you modify the ResultSet generated with the by the “By Executing Mode” of the Database Wizard, you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.

  8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

Add Prepared Statements

Add a Prepared Statement object to your OTD.

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 the if statement.You can process a resultset by looping through the next() method.

ProcedureTo Add Prepared Statements

  1. On the Add Prepared Statements window, click Add.

    Figure 1–34 Prepared Statement

    Prepared Statement

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. When finished creating the statement, click Save As giving the statement a name. This name will appear as a node in the OTD. Click OK (see the following figure).

    Figure 1–35 Prepared SQL Statement

    Prepared SQL Statement

  3. The Add Prepared Statement window displays the name you assigned to the Prepared Statement. To edit the parameters, click Edit Parameters. To change the data type, click in the Type field and select a different type from the list.

    Note –

    When doing 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.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK (see the following figure).

    Figure 1–36 Edit the Prepared Statement Parameters

    Edit the Prepared Statement Parameters

  5. 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. Click OK (see the following figure).

    Note –

    The OTD Wizard fails to 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.

    Figure 1–37 ResultSet Columns

    ResultSet Columns

  6. On the Add Prepared Statements window, click OK.

Specify the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes (see the following figure).

    Figure 1–38 Naming an OTD

    Naming an OTD

  2. Click Next.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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. (see the following figure).

    The resulting OTD appears on the Java CAPS IDE.

    Figure 1–39 Database Wizard - Summary

    Database Wizard - Summary

Editing Existing OTDs

A single OTD can consist of many Database objects. They can be a mixture of Tables, Prepared Statements and Stored Procedures. By using the Database OTD Wizard, the OTD Edit feature allows you to:

ProcedureTo Edit an Existing OTD

When a minor change is needed for an existing OTD, there is no need to rebuild it from scratch; instead, you can edit the OTD. To edit an OTD, complete the following steps:

  1. In the project tree, right-click on the OTD. From the submenu, click Edit (see the figure below). The Database Connection Information Wizard opens.

    OTD Edit Menu Item

  2. Connect to the database by entering the applicable information in the wizard. Once the connection is established, the Database Wizard opens, allowing you to make modifications to the OTD.

  3. Once you have completed editing the OTD, click the Finish button to save the changes.

    Caution – Caution –

    Once the OTD has been edited, you must verify that the changes are reflected in the Collaboration so that no errors occur at runtime. For example, if during the edit process, you delete a database object that is included in a Collaboration, the Collaboration could fail at activation or run-time.

    When editing an OTD, you can connect to another instance of the database under the following conditions:

    • The same version of the database should be used unless the newer version is compatible with the older version.

    • Tables in the database must be defined with the same definition.

    • The stored procedures must be identical.

    • For tables/stored procedures built with ”qualified-name’, the schema name for the tables/stored procedures must be identical in both database instances.

Creating a New Sybase OTD

The following steps are required to create a new OTD for the Sybase Adapter.

Select Wizard Type

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

ProcedureTo Select the Sybase Database OTD Wizard

  1. On the Project tree, right click on the project and select Create an Object Type Definition from the shortcut menu.

  2. From the OTD Wizard Selection window, select the Sybase Database and click Next. See the following figure.

    Figure 1–40 OTD Wizard Selection

    OTD Wizard Selection

To Connect to Database

Enter the Sybase database connection information in the Connection Information frame.

Required Database Connection Fields

Select Database Objects

Select the type of Sybase database objects you want included in the OTD.

ProcedureTo Select Database Objects

  1. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the .otd file. Click Next to continue. See the following figure.

    Note –

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

    Figure 1–41 Select Database Objects

    Select Database Objects

Select Tables/Views/Aliases

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

ProcedureTo Select Table/Views/Aliases

  1. In the Select Tables/Views/Aliases window, click Add. See the following figure.

    Figure 1–42 Select Tables/Views/Aliases

    Select Tables/Views/Aliases

  2. In the Add Tables window, select if your selection criteria will include table data, view only data, both, and/or system tables.

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

    Figure 1–43 Database Wizard - All Schemes

    Database Wizard - All Schemes

  4. Select the table of choice and click OK.

    The table selected is added to the Selected Tables/Views window. See the following figure.

    Figure 1–44 Selected Tables/Views/Aliases window with a table selected

    Selected Tables/Views/Aliases window with a table selected

  5. In the Selected Tables/Views/Aliases window, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. In the Table/View Columns window, you can select or deselect your table columns. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down list. If you would like to change any of the tables columns, click Change. See the following figure.

    The data type is usually listed as Other when the driver cannot detect the data type. In these situations we recommend changing the data type to one that is more appropriate for the type of column data.

    Figure 1–45 Table/View Columns

    Table/View Columns

  7. Click Advanced to change the data type, percision/length, or scale. Once you have finished your table choices, click OK. In general, you will not need to make any changes. See the following figure.

    Figure 1–46 Table/View Columns— Advanced

    Table/View Columns— Advanced

Select Procedures

Select the type of stored procedures required in your OTD.

ProcedureTo Select Stored Procedures

  1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

    Figure 1–47 Select Procedures and specify Resultset and Parameter Information

    Select Procedures and specify Resultset and Parameter Information

    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 checkbox is selected. When this checkbox is not selected, then only the Stored Procedure Table Name appears.

  2. On the Select Procedures window, enter the name of a Procedure or select a table from the drop down list. Click Search. Wildcard characters can also be used.

  3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

    Figure 1–48 Add Procedures

    Add Procedures

  4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure. See the following figure.

    Figure 1–49 Procedure Parameters

    Procedure Parameters

  5. To restore the data type, click Restore. When finished, click OK.

  6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.

  7. Click Add to add the type of Resultset node you would like to generate.

    Figure 1–50 Edit Resultset

    Edit Resultset

    The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are "By Executing", "Manually", and "With Assistance" modes.

    By Executing Mode 

    “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes. 

    With Assistance Mode 

    "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query. 

    Manually Mode 

    "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query. 


    SELECT A, B, 3*C FROM table T

    is generated by the database. In this case, "With Assistance" mode is a better choice. 

    If you modify the ResultSet generated by the “Execute” mode of the Database Wizard you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved. 

  8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

    Note –

    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.

Add Prepared Statement

Add a Prepared Statement object to your OTD.

ProcedureTo Add Prepared Statements

  1. On the Add Prepared Statements window, click Add.

    Figure 1–51 Prepared Statement

    Prepared Statement

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. When finished creating the statement, click Save As giving the statement a name. This name will appear as a node in the OTD. Click OK. See the following figure.

    Figure 1–52 Prepared SQL Statement

    Prepared SQL Statement

  3. On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK. See the following figure.

    Figure 1–53 Edit the Prepared Statement Parameters

    Edit the Prepared Statement Parameters.

  5. To edit Resultset Columns, click Edit Resultset Columns. The ResultSet Columns window appears. See the following figure.

    Figure 1–54 ResultSet Columns

    ResultSet Columns

  6. Click Add to add a new ResultSet column. Both the Name and Type are editable.

  7. Click OK to return to the Add Prepared Statements window.

Troubleshooting

When using a Prepared Statement, the ResultsAvailable() method always returns true. Although this method is available, you should not use it with a while loop since it creates an infinate loop at runtime, exhausting all CPU activity.You can process a resultset by looping through the next() method.

If the Prepared Statement is wrong, then the ResultSet Columns count is zero.

Specify the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes. See the following figure.

    Figure 1–55 Naming an OTD

    Naming an OTD

  2. Click Next.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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. See the following figure.

    The resulting OTD appears on the Java CAPS IDE.

    Figure 1–56 Database Wizard - Summary

    Database Wizard - Summary

Editing an Existing Sybase OTD

You can edit any database OTD you create directly from the Java CAPS IDE.

ProcedureTo Edit the OTD from the Java CAPS IDE

  1. Unlock the OTD. To do this, right-click the OTD in the Project tree and select Version Control ⇒ Check Out from the menu.

    The Version Control - Check Out window appears.

  2. Select the OTD you want to check out, then click Check Out.

  3. From the Project tree, right-click the OTD again and select Edit from the menu.

    The Sybase Database Connection Information wizard appears.

  4. Enter the connection information as described in To Connect to Database, and click Next.

  5. Step through each of the wizard steps and click Finish to save your changes.

    Note –

    You must verify during project activation or at runtime that no errors are generated after editing an OTD. Errors could occur if you delete a database object that is included in a Collaboration.

Creating a VSAM OTD

The following steps are required to create a new OTD for the VSAM Adapter.

Select Wizard Type

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

ProcedureTo Select the VSAM Database OTD Wizard

  1. On the Project tree, right click the Project and select New ⇒ Object Type Definition from the shortcut menu. The Select Wizard Type page appears, displaying the available OTD wizards.

  2. From the New Object Type Definition Wizard window, select the VSAM Database and click Next. See Figure 1–26.

    Figure 1–57 OTD Wizard Selection

    OTD Wizard Selection

Connect To Database

Enter the VSAM database connection information in the Connection Information frame.

Required Database Connection Fields include:

Select Database Objects

Select the type of VSAM database objects you want included in the OTD.

ProcedureTo Select Database Objects

  1. When selecting Database Objects, you can select any combination of Tables, Views, or Prepared Statements you would like to include in the OTD file. Click Next to continue (see the following figure).

    Note –

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

    Figure 1–58 Select Database Objects

    Select Database Objects

Select Tables/Views/Aliases

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

Note –

Aliases are not supported in the current release of the VSAM adapter.

ProcedureTo Select Table and Views

  1. In the Select Tables/Views/Aliases window, click Add (see the following figure).

    Figure 1–59 Select Tables/Views/Aliases

    Select Tables/Views/Aliases

  2. In the Add Tables window, select if your selection criteria will include table data, view only data, both, and/or system tables.

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

    Figure 1–60 Database Wizard - All Schemes

    Database Wizard - All Schemes

  4. Select the table of choice and click OK.

    The table selected is added to the Selected Tables/Views/Aliases section (see the following figure).

    Figure 1–61 Selected Tables/Views/Aliases window with a table selected

    Selected Tables/Views/Aliases window with a table selected

  5. In the Selected Tables/Views/Aliases section, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

  6. In the Table/View Columns window, you can select or deselect your table columns. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down list. If you would like to change any of the tables columns, click Change (see the following figure).

    The data type is usually listed as Other when the driver cannot detect the data type. In these situations we recommend changing the data type to one that is more appropriate for the type of column data.

    Figure 1–62 Table/View Columns

    Table/View Columns

  7. Click Advanced to change the data type, percision/length, or scale. Once you have finished your table choices, click OK. In general, you will not need to make any changes (see the following figure).

    Figure 1–63 Table/View Columns— Advanced

    Table/View Columns— Advanced

Add Prepared Statement

Add a Prepared Statement object to your OTD.

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 the if statement.

ProcedureTo Add Prepared Statements

  1. On the Add Prepared Statements window, click Add.

    Figure 1–64 Prepared Statement

    Prepared Statement

  2. Enter the name of a Prepared Statement or create a SQL statement by clicking in the SQL Statement window. If you are not logging into the VSAM database with the default user name, you must enter the Table schema name in the SQL Prepared Statement. When finished creating the statement, click Save As giving the statement a name.

    This name will appear as a node in the OTD. Click OK (see the following figure).

    Figure 1–65 Prepared SQL Statement

    Prepared SQL Statement

  3. On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

  4. Click Add if you want to add additional parameters to the Statement or highlight a row and click Remove to remove it. Click OK.

    Figure 1–66 Edit the Prepared Statement Parameters

    Edit the Prepared Statement Parameters

  5. To edit Resultset Columns, click Edit Resultset Columns. The ResultSet Columns window appears (see the following figure).

    Figure 1–67 ResultSet Columns

    ResultSet Columns

  6. Click Add to add a new ResultSet column. Both the Name and Type are editable.

  7. Click OK to return to the Add Prepared Statements window.

Specify the OTD Name

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

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes (see the following figure).

    Figure 1–68 Naming an OTD

    Naming an OTD

  2. Click Next.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  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 (see the following figure).

    The resulting OTD appears on the Java CAPS IDE.

    Figure 1–69 Database Wizard - Summary

    Database Wizard - Summary

Using the OTD Importer in Netbeans

Placeholder