Creating a New Informix OTD

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

• To Select the Informix Database OTD Wizard

• To Connect to a Database

• Select Database Objects

• Select Tables/Views/Aliases

• Select Procedures

• Add Prepared Statements

• Specify the OTD Name

• Review Selections

Select Wizard Type

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

To 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

   
   

Connect To Database

To 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.

   

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.

Select 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 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

   
   

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

   
   

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

   
   

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

   
   

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.

   ---

Select 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

   
   

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

   
   

   ---

   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

   
   

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

   
   

   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.

Add Prepared Statements

Add a Prepared Statement object to your OTD.

Steps Required to Add Prepared Statements Include:

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

   
   

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

   
   

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

   
   

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

   
   

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

   Figure 1–23 ResultSet Columns

   
   

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.

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

   
   

2. Click Next.

Review 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