Creating tables and data sources automatically to test CMP beans for WebSphere Application Server v6.0

The table and data source creator provides an automated way to test CMP enterprise beans without needing to worry about the persistence mechanism or how to configure a data source. You can test CMP beans as easily as session beans without having to create a data source or database tables. The table and data source creator automatically captures the following: userid, password, alias, host name, port, path to the JDBC drivers, whether to drop tables first or not, and whether to create data sources only and not tables.

The following database systems are supported, with restrictions as stated:

• Cloudscape v5.1 which is included as part of WebSphere Application Server v5.0.2 and above.

• Cloudscape v5.0 which is included as part of WebSphere Application Server v5.0.2.

• DB2 v8.2 installed either locally or on a remote machine. You must use the Type 4 JDBC drivers in db2jcc.jar, and a database must exist. You must have a user ID and password to access the database.

• DB2 v8.1 installed either locally or on a remote machine. You must use the Type 4 JDBC drivers in db2jcc.jar, and a database must exist. You must have a user ID and password to access the database.

• Oracle v10g installed either locally or on a remote server. You must use the JDBC drivers in classes12.jar, and a database must exist. You must have a user ID and password to access the database.

• Oracle v9i installed either locally or on a remote machine. You must use the JDBC drivers in classes12.jar , and a database must exist. You must have a user ID and password to access the database.

• Microsoft SQL Server 2000 installed either locally or on a remote machine. You must use the WebSphere Connect JDBC Driver is the WebSphere branded DataDirect Connect JDBC driver that is shipped with this product. This driver consists of four JAR files: base.jar, sqlserver.jar, util.jar, and (or names similar to these). You must have a user ID and password to access the database.

Prerequisite tasks:

1. Create a Version 1.4 enterprise application project.

2. Create a version 2.1 EJB project in the enterprise application project you just created.

3. Create 2.x CMP beans in the EJB projects that correspond to the enterprise application project you just created.

4. Generate top-down mappings, selecting one of the following as the target database:
   • Cloudscape v5.1

   • Cloudscape v5.0

   • DB2 v8.2

   • DB2 v8.1

   • Oracle v10g

   • Oracle V9i

   • Microsoft SQL Server 2000

5. Generate deployment code for the EJB projects.

6. In the Project Explorer view, right-click the EJB project and select Open with > Deployment Descriptor Editor.

7. Scroll down to the WebSphere Bindings section. Ensure a backend ID is specified in the Current field. For example, DB2UDBNT_V8_1. Save your changes and close the deployment descriptor editor.

8. Create a WebSphere server

9. Add the EAR project to the server.

Note: Close the server editor before you create the tables and data sources, or the data sources will not automatically be created.

To test your CMP beans with the table and data source creator:

1. In the Servers view, right-click the server you created and select Create tables and data sources.

2. For DB2, Oracle and Microsoft SQL Server 2000: A Connection Settings dialog box appears. A database connection is required for creating data source and table creation. Specify the following database connection parameters:
   1. In the User field, type your userid to access the database.

   2. In the Password field, type your password to access the database.

   3. In the Confirm password field, type your password again.

   4. In the Alias field, type your database alias.

   5. In the Host name field, type the host name of the database server.

   6. In the Port field, type the port number that will be used to connect to the database.

   7. In the Jar Files Path field, specify the path to the database JDBC driver.

   Note: If your project has DB2, Oracle, and Microsoft SQL Server 2000 as current backends, the connection settings dialog box will have fields for all of these backends.

3. For DB2, Oracle and Microsoft SQL Server 2000: If you have done a top-down mapping when creating your EJBs, the Create databaseName tables and Drop databaseName tables first check boxes are pre-selected. If the tables already exist or if you do not want to create tables, clear the Create databaseName tables check box.

   If you have done a bottom-up mapping, the Create databaseName tables check box is by default cleared, so you can automatically create a data source if you do not want to create tables.

4. For each backend defined in the EJB project, the Table and Data Source Creator dialog box reports the following details:
   • The project name of the EJB project

   • The database vendor

   • The backend ID used

   • The data source creation status: Indicates whether a data source is added to the EJB project.

   • The table creation status: If the status of the table creation is not sucessful, refer to the list of operations performed for table creation to identify the problem.

   • The list of operations performed for table creation: Contains a list of the SQL operations and any exceptions thrown when performing the table creation.

5. If you want to verify the creation of the table and data source, complete the following:
   • For WebSphere Application server v5.x, you can open the server editor and see that the data source and resource properties were created.

   • For WebSphere Application Server v6.x, you can open the application deployment descriptor to see that the data source and resource properties were created.

6. If you perform this task again, the tables are recreated but the data source remains the same.

7. Now you can test the CMP beans on the server. In the Project Explorer view, right-click the bean and select Run on Server. The Universal test client opens.

8. Test the CMP bean.