Getting Started with the JDBC Component Editor

Creating a JDBC Connection Resource

Before you create a JDBC Component, you will find it necessary to create a Connection Resource to access the SQL database. Each Connect, including the JDBC connector, uses its own Connection type. Each Connection type is differentiated by the number and types of parameters used to connect to the specific external data source.

About Constant and Expression Driven Connection Parameters

You can specify Connection parameter values in one of two ways: as Constants or as Expressions. A constant based parameter uses the value you type in the Connection dialog every time the Connection is used. An expression based parameter allows you to set the value using a programmatic expression, which can result in a different value each time the connection is used at runtime. This allows the Connection's behavior to be flexible and vary based on runtime conditions each time it is used.

For instance, one very simple use of an expression driven parameter in a JDBC Connection would be to define the User ID and Password as PROJECT Variables (e.g. PROJECT.XPATH("USERCONFIG/MyDeployUser"). This way when you deploy the project, you can update the PROJECT Variables in the Deployment Wizard to values appropriate for the final deployment environment. At the other extreme, you could have a custom script that queries a Java business object in the Application Server to determine what User ID and Password to use.

Procedure To switch a parameter from Constant driven to Expression driven:

Click the right mouse button in the parameter field you are interested in changing.
Select Expression from the context menu and the editor button will appear or become enabled.
Click on the button and then create an expression that evaluates to a valid parameter value at runtime. (Strings should be wrapped in double-quotes.)

About JDBC Drivers and Connection Pools

When you create a Connection Resource, you are asked to provide a Driver Name and Connection Pool.

The JDBC Driver sun.jdbc.odbc.JdbcOdbcDriver is part of the JRE (Java Runtime Environment, which you can find under the exteNdComposer directory), and you can use this driver to establish your connection. But you can also obtain other JDBC drivers. For instance, the Novell exteNd Application Server has its own JDBC drivers. Also, you can visit the Web site of the vendor for the SQL database you're using and download their driver(s).

A connection pool is a set of database connections managed by the application server for the various applications it manages. It provides more efficient use of database and connection resources for multiple applications running in the same application server. This, in turn, can improve overall system performance. You can obtain the Pool Name for your application server from your Server Administrator. For deployments within the Novell exteNd Application Server the pool name will be Databases/DBName/DataSource where DBName is the name that was used when the database was added to the server. For example, if you were connecting to the TutorialBegin3 database provided with the application server, the pool name would be Databases/TutorialBegin3/DataSource.

Procedure To create a JDBC connection resource:

Select File>New> xObject and select the Resource tab. Click on Connection. The "Create a New Connection Resource" Wizard appears.
Type a Name for the connection object.
Optionally, type Description text.
Click Next.
Select JDBC Connection from the Connection Type pull down menu.
In the JDBC Driver field, enter the name of the JDBC driver you want to use. For example, com.sssw.jdbc.mss.odbc.AgOdbcDriver for the Novell exteNd driver. (For more information see About JDBC Drivers and Connection Pools.)

NOTE: This parameter, and all subsequent parameters in this dialog, can be dynamically set using Expressions. See "About Constant and Expression Driven Connection Parameters" earlier in this chapter.
In the JDBC URI field, enter the location of the database you want to reach. For example, jdbc:sssw:odbc:XCTutorial where jdbc:sssw:odbc: is required syntax by the driver and XCTutorial is an ODBC Data Source Name (DSN) defined on the specific computer where the component will run. (The DSN is defined externally from Composer by accessing the ODBC Administrator in the Windows Control Panel.) For deployment, you can maintain the connection described above, provided that the server allows for ODBC connectivity. The more likely scenario is that you will want to take advantage of the power of the application server in managing database access. In that case, you need to provide the connection pool name as described below.

NOTE: The JDBC Driver and JDBC URI fields are both case sensitive.
Enter a valid User ID to sign on to the selected database.
Enter a valid Password for the selected database.
In the DB Params field, enter any database-specific parameters that might apply to your connection. For example, to allow updates to a Novell exteNd SQL Anywhere database, enter S3SqlAnywhereAuth=true as a parameter. Note that parameters should be entered as name=value pairs. If more than one name=value param is specified, separate the pairs using semicolons, e.g., param1=true;param2=true;param3=false.

NOTE: If no database-specific parameters will be used, enter false in this field.
Enter a Pool Name if required. For more information, see About JDBC Drivers and Connection Pools.

NOTE: Connection pooling is only operational in the deployment environment. Setting the name here will not affect Composer connections. Only the deployed project will be affected.
Check the Allow SQL Transactions checkbox if you intend to exercise direct control over transactions (using SQL Begin, Commit, and Rollback verbs) in your component's Action Model.

Checking the Allow SQL Transactions box has a number of effects:

—It turns auto-commit off for the JDBC driver. (The state of the auto-commit flag is restored, however, at the end of the transaction, before returning the connection back to the pool.)

—It causes all SQL commit and rollback commands to be translated to the corresponding JDBC connection calls.

—It causes Composer Enterprise Server to check the final Execute SQL Action in the component to see that the final action is a commit or a rollback. If the final action is not a commit or rollback, Composer Enterprise Server performs a rollback by default, so that a dirty connection (that is, a connection with uncommitted changes) is not inadvertently returned to the pool.

NOTE: For a further discussion of the Allow SQL Transactions checkbox, see the Transactions chapter of the exteNd Composer Application Server Guide for your application server.
Check the Default checkbox if you would like to use the current connection as the default connection for any new JDBC Components you create in your project.
Click Test to see if your connection is successful. A "success" or "failure" message appears for your connection. You can continue creating the resource, even if your connection fails.

NOTE: This does not test the connection pool (if defined).
Click Finish. The newly-created resource connection object appears in the Composer Connection Resource detail pane.

Creating XML Templates for Your Component

In addition to a connection resource, a JDBC component also requires that you have already created XML templates so that you have sample documents for designing your component. (See Chapter 5, Creating XML Templates, in the Composer User's Guide for more information.)

Also, if your component design calls for any other xObject resources such as custom scripts or code table maps, it is best to create these before creating the JDBC Component. For more information, see Creating Custom Scripts in the Composer User's Guide.