SUN SEEBEYOND eWAY™ JDBC/ODBC ADAPTER USER’S GUIDE Release 5.1.
Copyright © 2006 Sun Microsystems, Inc., 4150 Network Circle, Santa Clara, California 95054, U.S.A. All rights reserved. Sun Microsystems, Inc. has intellectual property rights relating to technology embodied in the product that is described in this document. In particular, and without limitation, these intellectual property rights may include one or more of the U.S. patents listed at http://www.sun.com/patents and one or more additional patents or pending patent applications in the U.S.
Contents Contents Chapter 1 Introducing the JDBC/ODBC eWay 7 About Java Database Connectivity (JDBC) 7 JDBC Drivers Type I: JDBC-ODBC Bridge Type One Driver Type II: Partial Java driver Type Two Driver Pure Java driver for database middleware Type Three Driver Type Four Driver: Direct-to-database pure Java driver 7 8 8 9 9 10 10 11 About the JDBC/ODBC eWay 13 What’s New in This Release 13 About This Document 14 JDBC eWay Javadoc Scope Intended Audience Text Conventions 14 14 15 15 Related Do
Contents Chapter 3 Setting Properties of the JDBC/ODBC eWay 25 Creating and Configuring a JDBC eWay 25 Configuring the eWay Connectivity Map Properties 25 Transaction Support Levels Between Different Versions 27 Configuring the eWay Environment Properties 29 eWay Connectivity Map Properties 30 Connectivity Map Outbound eWay Properties Connectivity Map Outbound non-Transactional eWay Properties Connectivity Map Outbound XA eWay Properties eWay Environment Properties 31 31 31 31 Inbound JDBC
Contents The Table The Query (Select) Operation The Insert Operation The Update Operation The Delete Operation The Stored Procedure Executing Stored Procedures Manipulating the ResultSet and Update Count Returned by Stored Procedure Prepared Statement Batch Operations 60 60 61 62 63 63 64 65 67 67 Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects About the JDBC eWay Sample Projects 69 69 Sample Project Data Operations Used in the JDBC Sample Projects Assigning Operations in JCD Assigning Opera
Contents Building and Deploying the Project Running the Sample 94 94 Building and Deploying the prjJDBC_BPEL Sample Project Creating a Project Creating the OTDs Creating the Business Process Creating the Business Process Flow Configuring the bpelPsInsert Modeling Elements Configuring the bpelUpdate Modeling Elements Configuring the bpelDelete Modeling Elements Configuring the bpelTableSelect Modeling Elements Creating the Connectivity Map Populating the Connectivity Map Binding the eWay Components Creati
Chapter 1 Introducing the JDBC/ODBC eWay Welcome to the Sun SeeBeyond eWay™ JDBC/ODBC Adapter User’s Guide. This document includes information about installing, configuring, and using the Sun Java Composite Application Platform Suite JDBC/ODBC eWay™ Adapter, referred to as the JDBC eWay throughout this guide. This chapter provides an overview of database connectivity (JDBC) and open database connectivity (ODBC) APIs. This chapter also introduces the JDBC/ODBC eWay.
Chapter 1 Introducing the JDBC/ODBC eWay Section 1.1 About Java Database Connectivity (JDBC) database and middleware vendors. The various driver types are described in the following sections: “Type I: JDBC-ODBC Bridge” on page 8 “Type II: Partial Java driver” on page 9 “Pure Java driver for database middleware” on page 10 “Type Four Driver: Direct-to-database pure Java driver” on page 11 Type I: JDBC-ODBC Bridge This combination provides JDBC access via ODBC drivers.
Chapter 1 Introducing the JDBC/ODBC eWay Section 1.1 About Java Database Connectivity (JDBC) The pros and cons for using this type of driver are as follows: Pros Allows access to almost any database since the database ODBC drivers are readily available Cons Performance is degraded since the JDBC call goes through the bridge to the ODBC driver then to the native database connectivity interface.
Chapter 1 Introducing the JDBC/ODBC eWay Section 1.
Chapter 1 Introducing the JDBC/ODBC eWay Section 1.1 About Java Database Connectivity (JDBC) different databases. Many mainframe legacy non-relational databases use this kind of driver.
Chapter 1 Introducing the JDBC/ODBC eWay Section 1.1 About Java Database Connectivity (JDBC) Cons: Not optimized for server operating system, so the driver can't take advantage of operating system features. (The driver is optimized for the database and can take advantage of the database vendor's functionality.) User needs a different driver for each different database. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly.
Chapter 1 Introducing the JDBC/ODBC eWay 1.2 Section 1.2 About the JDBC/ODBC eWay About the JDBC/ODBC eWay This document describes how to install and configure the JDBC/ODBC eWay. The JDBC/ODBC eWay enables the eGate system to exchange data with external databases. The Sun Java Composite Application Platform Suite (Java CAPS) contains many database eWays. You should use those eWays to interface with the databases they support.
Chapter 1 Introducing the JDBC/ODBC eWay Section 1.4 About This Document Connectivity Map Generator: Generates and links your Project’s Connectivity Map components using a Collaboration or Business Process. Many of these features are documented further in the Sun SeeBeyond eGate™ Integrator User’s Guide or the Sun SeeBeyond eGate™ Integrator System Administration Guide. 1.
Chapter 1 Introducing the JDBC/ODBC eWay 1.4.2 Section 1.5 Related Documents Intended Audience This guide is intended for experienced computer users who have the responsibility of helping to set up and maintain a fully functioning Java Composite Application Platform Suite system. This person must also understand any operating systems on which the Java Composite Application Platform Suite will be installed (Windows and UNIX), and must be thoroughly familiar with Windows-style GUI operations. 1.4.
Chapter 1 Introducing the JDBC/ODBC eWay 1.7 Section 1.7 Documentation Feedback Documentation Feedback We appreciate your feedback. Please send any comments or suggestions regarding this document to: CAPS_docsfeedback@sun.com JDBC/ODBC eWay Adapter User’s Guide 16 Sun Microsystems, Inc.
Chapter 2 Installing the JDBC/ODBC eWay This chapter describes how to install the JDBC/ODBC eWay. What’s in this Chapter Installing the JDBC eWay on page 17 ICAN 5.0 Project Migration Procedures on page 19 Installing Enterprise Manager eWay Plug-Ins on page 21 2.
Chapter 2 Installing the JDBC/ODBC eWay 2.1.1 Section 2.1 Installing the JDBC eWay Installing the JDBC eWay on an eGate supported system Follow the directions for installing the Sun Java Composite Application Platform Suite in the Sun Java Composite Application Platform Suite Installation Guide.
Chapter 2 Installing the JDBC/ODBC eWay Section 2.2 ICAN 5.0 Project Migration Procedures 7 When prompted, restart the IDE (Integrated Development Environment) to complete the installation. After Installation Once you install the eWay, it must then be incorporated into a Project before it can perform its intended functions. See the eGate Integrator User’s Guide for more information on incorporating the eWay into an eGate Project. 2.1.
Chapter 2 Installing the JDBC/ODBC eWay Section 2.2 ICAN 5.0 Project Migration Procedures 2 From the Project Explorer, right-click your Project and select Export from the shortcut menu. The Export Manager appears. 3 Select the Project that you want to export in the left pane of the Export Manager and move it to the Selected Projects field by clicking the Add to Select Items (arrow) button, or click All to include all of your Projects.
Chapter 2 Installing the JDBC/ODBC eWay Section 2.3 Installing Enterprise Manager eWay Plug-Ins C Select the Environment Explorer tab, and from the Environment Explorer, rightclick the Project’s Environment and select Version Control > Check Out from the shortcut menu. D Select Recurse Environment to specify all components, and click OK. 2 If your imported Project includes File eWays, these must be reconfigured in your Environment prior to deploying the Project.
Chapter 2 Installing the JDBC/ODBC eWay Section 2.3 Installing Enterprise Manager eWay Plug-Ins To add plug-ins from the Sun Java Composite Application Platform Suite Installer 1 From the Sun Java Composite Application Platform Suite Installer’s Downloads tab, select the Plug-Ins you require and save them to a temporary directory. 2 From the Enterprise Manager’s Explorer toolbar, click configuration. 3 Click the Web Applications Manager tab and go to the Manage Applications subtab.
Chapter 2 Installing the JDBC/ODBC eWay Alert Code\Description Section 2.3 Installing Enterprise Manager eWay Plug-Ins Description Details User Actions DBCOMMON-CONNECTFAILED000005=Connection handle not usable. Reason:[{0}] The connection in the pool is stale and is not usable. Probably a database restart occurred causing the connection to be stale, retry the operation after the database is up. DBCOMMON-XARESOURCEFAILED000001=Unable to get XAResource for the database.
Chapter 2 Installing the JDBC/ODBC eWay Section 2.3 Installing Enterprise Manager eWay Plug-Ins Note: An alert code is a warning that an error has occurred. It is not a diagnostic. The user actions noted above are just some possible corrective measures you may take. Refer to the log files for more information. For information on Managing and Monitoring alert codes and logs, see the Sun SeeBeyond eGate Integrator System Administration Guide. JDBC/ODBC eWay Adapter User’s Guide 24 Sun Microsystems, Inc.
Chapter 3 Setting Properties of the JDBC/ODBC eWay This chapter describes how to set the properties of the JDBC/ODBC eWay. What’s In This Chapter Creating and Configuring a JDBC eWay on page 25 Configuring the eWay Connectivity Map Properties on page 25 Configuring the eWay Environment Properties on page 29 eWay Connectivity Map Properties on page 30 eWay Environment Properties on page 31 3.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.2 Configuring the eWay Connectivity Map Properties Outbound JDBC eWay Outbound JDBC XA eWay Outbound JDBC non-Transactional eWay To configure the eWay properties: 1 On the Enterprise Designer’s Connectivity Map, double-click the JDBC eWay icon. The eWay Connections window appears. Figure 5 Connectivity Map with Components JDBC eWay 2 Select a parameter from the list and click OK.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.2 Configuring the eWay Connectivity Map Properties Outbound JDBC eWay: Also referred to as LocalTransaction, this support level is opposite to NoTransaction, and this means that the transaction, when The Properties window opens, displaying the default properties for the eWay. 3 The Properties window opens, displaying the default properties for the eWay. Figure 7 Outbound eWay Properties 3.2.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.2 Configuring the eWay Connectivity Map Properties In 5.1.2, five new records are to be inserted into a table. If one of the records fails to insert (such as when a duplicate key exists), the other four records will not be inserted. This is the behavior of the LocalTransaction. In order to achieve the same result as in 5.1.
Chapter 3 Setting Properties of the JDBC/ODBC eWay 3.3 Section 3.3 Configuring the eWay Environment Properties Configuring the eWay Environment Properties The eWay Environment Configuration properties contain parameters that define how the eWay connects to and interacts with other eGate components within the Environment. When you create a new JDBC External System, you may configure the type of External System required.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.4 eWay Connectivity Map Properties Figure 9 JDBC eWay Environment Configuration 4 Click on any folder to display the default configuration properties for that section. 5 Click on any property field to make it editable. After modifying the configuration properties, click OK to save the changes. 3.4 eWay Connectivity Map Properties The eWay Connectivity Map consists of the following properties categories.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Outbound XA eWay Configuration Settings Include: Connectivity Map Outbound XA eWay Properties on page 31 3.4.1 Connectivity Map Outbound eWay Properties The Outbound eWay Properties include outbound parameters used by the external database. Table 3 Outbound eWay—JDBC Connector Settings Name Description 3.4.2 Description The description of the database. Required Value A valid string.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Outbound JDBC eWay Properties on page 32 Outbound non-Transactional JDBC eWay Properties on page 35 Outbound XA JDBC eWay Properties on page 37 3.5.1 Inbound JDBC eWay Properties Before deploying your eWay, you will need to set the Environment properties.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties JDBC Connector Settings Details for the JDBC Connector Settings used by the external database are detailed in Table 7. Table 7 Outbound JDBC eWay—JDBC Connector Settings Name Description Required Value Description The description of the database. A valid string. The configured default is JDBC Connection Pool Datasource.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Table 7 Outbound JDBC eWay—JDBC Connector Settings (Continued) Name Description Required Value DataSource name Specifies the name of the XADataSource or ConnectionPoolDataSource implementation, to which the DataSource object delegates behind the scenes when there is connection pooling or distributed transaction management being done. The name of the XADataSource or ConnectionPoolDataSource implementation.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Table 8 Outbound JDBC eWay—Connection Retry Settings (Continued) Name Description ConnectionRetryInterval Required Value Specifies the milliseconds of pause before each attempt to reaccess the database. This setting is used in conjunction with the 'Connection Retries' setting. A valid numeric value. The default is 1000.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Table 9 Outbound non-Transactional eWay —JDBC Connector Settings (Continued) Name Description Required Value PortNumber Specifies the I/O port number on which the server is listening for connection requests. A valid port number. DatabaseName Specifies the name of the database instance. Any valid string. User Specifies the user name the eWay uses to connect to the database. Any valid string.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Table 9 Outbound non-Transactional eWay —JDBC Connector Settings (Continued) Name MaxPoolSize Description Required Value Specifies the maximum number of physical connections the pool should keep available at all times. 0 (zero) indicates that there is no maximum. A valid numeric value. The default is 10. If the pool size is too big, you may end up with too many connections with the database.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Connection Retry Settings JDBC Connector Settings Details for the JDBC Connector Settings used by the external database are detailed in Table 11. Table 11 Outbound XA JDBC eWay—JDBC Connector Settings Name Description Required Value Description The description of the database. A valid string. The configured default is JDBC XA Datasource.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Table 11 Outbound XA JDBC eWay—JDBC Connector Settings (Continued) Name Description Required Value DataSource name Specifies the name of the XADataSource implementation, to which the DataSource object delegates behind the scenes when there is connection pooling or distributed transaction management being done. The name of the XADataSource implementation. This property is Optional.
Chapter 3 Setting Properties of the JDBC/ODBC eWay Section 3.5 eWay Environment Properties Table 12 Outbound XA JDBC eWay—Connection Retry Settings (Continued) Name ConnectionRetryInterval Description Specifies the milliseconds of pause before each attempt to reaccess the database. This setting is used in conjunction with the 'Connection Retries' setting. Required Value A valid numeric value. The default is 1000.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard This chapter describes how to use the JDBC eWay Database Wizard to build OTDs. What’s In This Chapter: About the Database OTD Wizard on page 41 Creating a New JDBC OTD on page 41 Steps to Edit an Existing JDBC OTD on page 56 4.1 About the Database OTD Wizard The Database OTD Wizard generates OTDs by connecting to external data sources and creating corresponding Object Type Definitions.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Connect To Database on page 42 Select Database Objects on page 43 Select Tables/Views/Aliases on page 44 Select Procedures on page 48 Add Prepared Statement on page 52 Specify the OTD Name on page 55 Review Selections on page 55 4.2.1 Select Wizard Type Select the type of wizard required to build an OTD in the New Object Type Definition Wizard.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD 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.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 12 Select Database Objects 4.2.4 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 Figure 13. JDBC/ODBC eWay Adapter User’s Guide 44 Sun Microsystems, Inc.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 13 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 Figure 14. JDBC/ODBC eWay Adapter User’s Guide 45 Sun Microsystems, Inc.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 14 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 Figure 15. JDBC/ODBC eWay Adapter User’s Guide 46 Sun Microsystems, Inc.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 15 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.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 16 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 Figure 17. Figure 17 Table/View Columns — Advanced 4.2.5 Select Procedures Select the type of stored procedures required in your OTD. JDBC/ODBC eWay Adapter User’s Guide 48 Sun Microsystems, Inc.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Steps Required to Select Stored Procedures Include: 1 On the Select Procedures and specify Resultset and Parameter Information window, click Add. Figure 18 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.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 19 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 Figure 20. Figure 20 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.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD 7 Click Add to add the type of Resultset node you would like to generate. Figure 21 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).
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Manually Mode Section 4.2 Creating a New JDBC OTD "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.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 22 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 Figure 23. Figure 23 Prepared SQL Statement 3 On the Add Prepared Statement window, the name you assigned to the Prepared Statement appears.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.2 Creating a New JDBC OTD Figure 24 Edit the Prepared Statement Parameters . 5 To edit Resultset Columns, click Edit Resultset Columns. The ResultSet Columns window appears. See Figure 25. Figure 25 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. JDBC/ODBC eWay Adapter User’s Guide 54 Sun Microsystems, Inc.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard 4.2.7 Section 4.2 Creating a New JDBC OTD Specify the OTD Name Specify the name that your OTD will display in the Enterprise Designer Project Explorer. 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 Figure 26. Figure 26 Naming an OTD 2 Click Next. 4.2.8 Review Selections Review the selections made for the new OTD.
Chapter 4 Using the JDBC/ODBC eWay Database Wizard Section 4.3 Steps to Edit an Existing JDBC OTD Figure 27 Database Wizard - Summary 4.3 Steps to Edit an Existing JDBC OTD You can edit any database OTD you create directly from the Enterprise Designer Project Explorer. Steps to Edit the OTD from the Enterprise Designer Include: 1 Unlock the OTD. To do this, right-click the OTD in the Project Explorer and select Version Control > Check Out from the menu. The Version Control - Check Out window appears.
Chapter 5 Using JDBC/ODBC Operations Database operations in the JDBC eWay are used to access the JDBC database. Database operations are either accessed through Activities in BPEL, or through methods called from a JCD Collaboration. What’s in This Chapter JDBC eWay Database Operations (BPEL) on page 57 JDBC eWay Database Operations (JCD) on page 59 5.
Chapter 5 Using JDBC/ODBC Operations Section 5.1 JDBC eWay Database Operations (BPEL) Figure 28 displays the business rules between the FileClient.write and otdJDBC.Db_employeeDelete Activities. In this example, the whereClause appears on the Input side. Figure 28 Input and Output Between Activities The following table lists the expected Input and Output of each database operation Activity. Table 13 JDBC.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) Table 13 JDBC.ODBC Operations (Continued) eInsight Operations SelectMultiple Activity Input number of rows where() clause (optional) Activity Output Returns the number of rows specified that fit the condition of the where() clause, and the number of rows to be returned. For example: If the number of rows that meet the condition are 5 and the number of available rows are 10, then only 5 rows will be returned.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) operation. Check with the respective driver’s vendor for further information. This feature is known as Updatable ResultSet. Note: Refer to the Javadoc for a full description of methods included in the JDBC eWay. 5.2.1 The Table A table OTD represents a database table. It consists of fields and methods. Fields correspond to the columns of a table while methods are the operations that you can apply to the OTD.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) { FileClient_1.setText( "Selecting record(s) from db_employee table via table select .." ); FileClient_1.write(); otdJDBC_1.getDB_EMPLOYEE().select( input.getText() ); while (otdJDBC_1.getDB_EMPLOYEE().next()) { otdOutputDTD_DBemployee_1.setEmpNo( typeConverter.shortToString( otdJDBC_1.getDB_EMPLOYEE().getEMP_NO(), "#", false, "" ) ); otdOutputDTD_DBemployee_1.setLastname( otdJDBC_1.getDB_EMPLOYEE().
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) otdInputDTD_DBemployees_1.unmarshalFromString( input.getText() ); for (int i1 = 0; i1 < otdInputDTD_DBemployees_1.countX_sequence_A(); i1 += 1) { otdJDBC_1.getInsert_Ps().setEmp_no( typeConverter.stringToShort( otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getEmpNo(), "#", false, 0 ) ); otdJDBC_1.getInsert_Ps().setLast_name( otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getLastname() ); otdJDBC_1.getInsert_Ps().
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) otdJDBC_1.getUpdate_Ps().setEmp_no( typeConverter.stringToShort( input.getText(), "#", false, 0 ) ); otdJDBC_1.getUpdate_Ps().executeUpdate(); FileClient_1.setText( "Done Update." ); FileClient_1.write(); } } The Delete Operation To perform a delete operation on a table: 1 Execute the delete() method. In this example DELETE an employee. package prjJDBC_JCDjcdALL; public class jcdDelete { public com.stc.codegen.logger.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) Executing Stored Procedures The OTD represents the Stored Procedure “LookUpGlobal” with two parameters, an inbound parameter (INLOCALID) and an outbound parameter (OUTGLOBALPRODUCTID). These inbound and outbound parameters are generated by the Database Wizard and are represented in the resulting OTD as nodes.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) FileClient_1.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) Collaboration usability for a stored procedure ResultSet The Column data of the ResultSets can be dragged-and-dropped from their nodes to the Business Rules. Below is a code snippet that can be generated by the Collaboration Editor: while (getSPIn().getSpS_multi().resultsAvailable()) { if (getSPIn().getSpS_multi().getUpdateCount() > 0) { System.err.println("Updated "+getSPIn().getSpS_multi().
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) parameters are retrieved. Therefore, you should retrieve all ResultSet(s) and Update Counts first followed by retrieving the OUT type parameters and return values. The following list includes specific ResultSet behavior that you may encounter: The method resultsAvailable() implicitly calls getMoreResults() when it is called more than once. You should not call both methods in your java code.
Chapter 5 Using JDBC/ODBC Operations Section 5.2 JDBC eWay Database Operations (JCD) submit the batch to the database server. Batch operations apply only to Prepared Statements. Not all drivers support batch operations. Check with the respective driver’s vendor for further information. getPrepStatement().getPreparedStatementTest().setAge(23); getPrepStatement().getPreparedStatementTest().setName(‘Peter Pan’); getPrepStatement().getPreparedStatementTest().setDeptNo(6); getPrepStatement().
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects This chapter provides an introduction to the JDBC eWay components, and information on how these components are created and implemented in a Sun Java Composite Application Platform Suite Project. Sample Projects are designed to provide an overview of the basic functionality of the JDBC eWay by identifying how information is passed between eGate and supported external databases.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.1 About the JDBC eWay Sample Projects Figure 29 Database project flow Both the prjJDBC_JCD and prjJDBC_BPEL sample Projects demonstrate how to: Select employee records from a database using a prepared statement. Select employee records from the db_employee table. Insert employee records into the db_employee table. Update an employee record in the db_employee table. Delete an employee record from the db_employee table.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.1 About the JDBC eWay Sample Projects BPEL_TableSelect_output().dat 6.1.1 Sample Project Data Data used for the sample Projects are contained within a table called db_employee. The table has the following columns: Table 14 Sample Project Data - db_employee Table Column Name 6.1.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.2 Running the Sample Projects At run time, the eInsight engine invokes each step in the order that you defined in the Business Process. Using the engine’s Web Services interface, the Activity in turn invokes the eWay. You can open a file specified in the eWay and view its contents before and after the Business Process is executed. Note: Inbound database eWays are only supported within BPEL Collaborations. 6.1.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.3 Running the SQL Script The script creates the tables and records required by the sample Project. 2 Import the sample Projects. 3 Build, deploy, and run the sample Projects. You must do the following before you can run an imported sample Project: Create an Environment Configure the eWays Create a Deployment Profile Create and start a domain Deploy the Project 4 Check the output. 6.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Note: Make sure you save all unsaved work before importing a Project. 2 From the Enterprise Designer’s Project Explorer pane, right-click the Repository and select Import Project from the shortcut menu. The Import Manager appears. Figure 30 Import Manager Dialog Box 3 Browse to the directory that contains the sample Project ZIP file. Select the sample file and click Import.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project 4 Click Next, and select the types of database object you want to include in the sample Project. For our example, select the following: Tables/Views/Aliases Prepared Statements 5 Click Add to select tables from the JDBC database. The Add Tables window appears. 6 Search for or Type in the name of the database. In this example we use the DB_EMPLOYEE table.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Steps required to create a new Connectivity Map: 1 From the Project Explorer tree, right-click the new prjJDBC_JCD Project and select New > Connectivity Map from the shortcut menu. 2 The New Connectivity Map appears and a node for the Connectivity Map is added under the Project, on the Project Explorer tree labeled CMap1. Create four additional Connectivity Maps—CMap2, CMap3.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project jcdInsert jcdPsSelect jcdTableSelect jcdPsUpdate 6.5.4 Creating the Collaboration Definitions (Java) The next step is to create Collaborations using the Collaboration Definition Wizard (Java). Since the sample Project includes five database operations, you must create five separate Collaboration Definitions (Java), or JCDs.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project jcdInsert Collaboration Steps required to create the jcdInsert Collaboration: 1 From the Project Explorer, right-click the sample Project and select New > Collaboration Definition (Java) from the shortcut menu. The Collaboration Definition Wizard (Java) appears. 2 Enter a Collaboration Definition name (for this sample jcdInsert) and click Next.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project 6 Click the Up One Level button twice to return to the Repository. Double-click Sun SeeBeyond > eWays > File > FileClient. The Selected OTDs field now lists the FileClient OTD. 7 Click Finish. The Collaboration Editor with the new jcdPsSelect Collaboration appears in the right pane of the Enterprise Designer.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project 4 For Step 3 of the wizard, from the Select OTDs selection window, double-click prjJDBC_JCD > otdALL > otdJDBC. The otdJDBC OTD is added to the Selected OTDs field. 5 Click the Up One Level button twice to return to the Repository. Double-click Sun SeeBeyond > eWays > File > FileClient. The Selected OTDs field now lists the FileClient OTD. 6 Click Finish.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Figure 32 jcdInsert Business Rules Sample code from the jcdInsert Includes: package prjJDBC_JCDjcdALL; public class jcdInsert { public com.stc.codegen.logger.Logger logger; public com.stc.codegen.alerter.Alerter alerter; public com.stc.codegen.util.CollaborationContext collabContext; public com.stc.codegen.util.TypeConverter typeConverter; public void receive( com.stc.connector.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getEmpNo(), "#", false, 0 ) ); otdJDBC_1.getInsert_Ps().setLast_name( otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getLastname() ); otdJDBC_1.getInsert_Ps().setFirst_name( otdInputDTD_DBemployees_1.getX_sequence_A( i1 ).getFirstname() ); otdJDBC_1.getInsert_Ps().setRate( new java.math.BigDecimal( otdInputDTD_DBemployees_1.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Figure 33 jcdPsSelect Business Rules Sample code from the jcdPsSelect Includes: package prjJDBC_JCDjcdALL; public class jcdPsSelect { public com.stc.codegen.logger.Logger logger; public com.stc.codegen.alerter.Alerter alerter; public com.stc.codegen.util.CollaborationContext collabContext; public com.stc.codegen.util.TypeConverter typeConverter; public void receive( com.stc.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project otdOutputDTD_DBemployee_1.setEmpNo( Integer.toString( otdJDBC_1.getSelect_ps().get$Select_psResults().getEMP_NO() ) ); otdOutputDTD_DBemployee_1.setLastname( otdJDBC_1.getSelect_ps().get$Select_psResults().getLAST_NAME() ); otdOutputDTD_DBemployee_1.setFirstname( otdJDBC_1.getSelect_ps().get$Select_psResults().getFIRST_NAME() ); otdOutputDTD_DBemployee_1.setRate( otdJDBC_1.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Figure 34 jcdTableSelect Business Rules Sample code from the jcdTableSelect Includes: package prjJDBC_JCDjcdALL; public class jcdTableSelect { public com.stc.codegen.logger.Logger logger; public com.stc.codegen.alerter.Alerter alerter; public com.stc.codegen.util.CollaborationContext collabContext; public com.stc.codegen.util.TypeConverter typeConverter; public void receive( com.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project otdOutputDTD_DBemployee_1.setRate( otdJDBC_1.getDB_EMPLOYEE().getRATE().toString() ); otdOutputDTD_DBemployee_1.setLastDate( typeConverter.dateToString( otdJDBC_1.getDB_EMPLOYEE().getLAST_UPDATE(), "yyyy-MM-dd hh:mm:ss", false, "" ) ); \\ marshals XML data from the output data into the otdOutputDTD_DB_Employee_1.marshallToString() method. FileClient_1.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Steps required to bind eWay components together: 1 Double-click a Connectivity Map—in this example cmDelete—in the Project Explorer tree. The cmDelete Connectivity Map appears in the Enterprise Designers canvas. 2 Drag and drop the jcdDelete Collaboration from the Project Explorer to the jcdDelete Service. The Service icon “gears” change from red to green.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Steps required to create an Environment: 1 From the Enterprise Designer’s Enterprise Explorer, click the Environment Explorer tab. 2 Right-click the Repository and select New Environment. A new Environment is added to the Environment Explorer tree. 3 Rename the new Environment to envJDBCProj. 4 Right-click envJDBCProj and select New > JDBC External System.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project You must configure eWay properties in both the Connectivity Map and the Environment Explorer.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Table 16 FileClientOUT eWay Property Settings (Continued) Connectivity Map Property Name cmUpdate Output file name Required Value JCD_Update_output%d.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Table 18 File eWay Environment Properties Section Configuration > Inbound File eWay > Parameter Settings Property Name Directory Required Value Enter the directory that contains the input files (trigger files included in the sample Project). Trigger files include: Configuration > Outbound File eWay > Parameter Settings Directory TriggerDelete.in.~in TriggerInsert.in.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Steps required to create the Deployment Profile: 1 From the Enterprise Explorer’s Project Explorer, right-click the prjJDBC_JCD Project and select New > Deployment Profile. 2 Enter a name for the Deployment Profile (for this sample dpJDBC_JCD). Select envJDBCProj as the Environment and click OK. 3 From the Deployment Editor toolbar, click the Automap icon.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.5 Building and Deploying the prjJDBC_JCD Sample Project Steps required to create and start the domain: 1 Navigate to your \logicalhost directory (where is the location of your Sun Java Composite Application Platform Suite installation). 2 Double-click the domainmgr.bat file. The Domain Manager appears.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project data, and the File eWay sends the output to an Output file name (as defined in the outbound File eWay Properties window). The Where Clause defined in the business rule recognizes the trigger as a placeholder for input, allowing a set condition, such as emp_no = 100, to determine the type of output data. You can modify the following input files to view different output.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project 2 From the Project Explorer tree, right-click the Repository and select New Project. A new Project (Project1) appears on the Project Explorer tree. 3 Right-click Project1 and select Rename from the shortcut menu. Rename the Project (for this sample, prjJDBC_BPEL). 6.6.2 Creating the OTDs The sample Project requires three OTDs to interact with the JDBC eWay.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Note: In our example, the SQL statement includes the ? placeholder for input. This placeholder represents the value for the Where Clause. 9 Click the OK button to close the Prepared Statement window, and then click Next on the Prepared Statements Wizard window. 10 Enter an OTD name. In this example, we use otdJDBC.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Table 19 Business Process Activities Business Process Activity bpelPsInsert FileClient.Receive FileClient.Write otdInputDTD_DBemployees.unmarshal otdJDBC.Insert_PsPSInsert FileClient.Write bpelUpdate FileClient.receive FileClient.write otdJDBC.DB_EMPLOYEEUpdate FileClient.write bpelDelete FileClient.receive FileClient.write otdJDBC.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 40 bpelPsInsert Business Process Steps required to configure the bpelPsInsert business process: 1 Configure the business rule between the FileClient.receive and FileClient.write Activities, as seen in Figure 41. Figure 41 bpelPsInsert Business Rule # 1 2 Configure the business rule between the FileClient.write Activity and otdInputDTD_DBemployees.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 43 bpelPsInsert Business Rule # 3 4 Configure the business rule between the otdJDBC.Insert_psPSInsert Activity and the FileClient.write Activity, as seen in Figure 44. Figure 44 bpelPsInsert Business Rule # 4 Configuring the bpelUpdate Modeling Elements The bpelUpdate business process describes how to update a record in the JDBC database using the Business Process Designer.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Note: Review the eInsight Business Process Manager User’s Guide for a more detailed description of the steps required to connect and add business rules to a modeling elements in a business process. Figure 45 bpelUpdate Business Process Steps required to configure the bpelUpdate business process: 1 Configure the business rule between the FileClient.receive and FileCleint.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 47 bpelUpdate Business Rule # 2 3 Configure the business rule between otdJDBC.DB_EMPLOYEEUpdate and the FileClient.write Activity, as seen in Figure 48. Figure 48 bpelUpdate Business Rule # 3 Configuring the bpelDelete Modeling Elements The bpelDelete business process describes how to delete a record in the JDBC database using the Business Process Designer.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Note: Review the eInsight Business Process Manager User’s Guide for a more detailed description of the steps required to connect and add business rules to a modeling elements in a business process. Figure 49 bpelDelete Business Process Steps required to configure the bpelDelete business process: 1 Configure the business rule between the FileClient.receive and FileClient.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project 3 Configure the business rule between the otdJDBC.DB_EMPLOYEEDelete Activity and the FileClient.write Activity, as seen in Figure 52. Figure 52 bpelDelete Business Rule # 3 Configuring the bpelTableSelect Modeling Elements The bpelTableSelect business process is describes how to select all records the JDBC database using the Business Process Designer.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 54 bpelTableSelect Business Rule # 1 2 Configure the business rule between the FileClient.write Activity and otdJDBC.DB_EMPLOYEESelectAll Activity, as seen in Figure 55. Figure 55 bpelTableSelect Business Rule # 2 3 Configure the business rule between the otdJDBC.DB_EMPLOYEESelectAll Activity and the otdInputDTD_DBemployees.marshal Activity, as seen in Figure 56.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 56 bpelSelectTable Business Rule # 3 4 Configure the business rule between the otdInputDTD_DBemployees.marshal Activity and the FileClient.write Activity, as seen in Figure 57. Figure 57 bpelTableSelect Business Rule # 4 5 Configure the business rule between the FileClient.write Activity and the FileClient.write Activity, as seen in Figure 58.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects 6.6.4 Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Creating the Connectivity Map The Connectivity Map provides a canvas for assembling and configuring a Project’s components. Steps required to create the Connectivity Map: 1 From the Project Explorer tree, right-click the new prjJDBC_BPEL Project and select New > Connectivity Map from the shortcut menu.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project To Select a JDBC Business Process 1 Drag a business process from the Enterprise Explorer Project Explorer onto the corresponding Connectivity Map. For example, drag the dbDelete business process onto the cmDelete Connectivity Map. 2 Save your changes to the Repository Binding the eWay Components The final step in creating a Connectivity Map is binding the eWay components together.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project 5 Minimize the bpelDelete Binding dialog box by clicking the chevrons in the upperright corner. 6 Save your current changes to the Repository, and then repeat this process for each of the other Connectivity Maps. 6.6.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 60 Environment Editor - envJDBCProj 8 Save your current changes to the Repository. 6.6.6 Configuring the eWays eWays facilitate communication and movement of data between the external applications and the eGate system.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Configuring the eWay Properties Steps required to configure the eWay properties: 1 Double-click the FileClientIN eWay on each of the Connectivity Maps and modify the properties for your system, as seen in Table 20. Click OK to close the Properties Editor.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Table 22 JDBC eWay Environment Properties Section Property Name Configuration > Inbound JDBC eWay > JDBC Connector settings Required Value ServerName Enter the host name of the database server being used. DatabaseName Enter the name of the particular database that is being used on the server. User Enter the user account name for the database.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Configuring the Integration Server You must set your SeeBeyond Integration Server Password property before deploying your Project. 1 From the Environment Explorer, right-click IntegrationSvr1 under your Logical Host, and select Properties from the shortcut menu. The Integration Server Properties Editor appears.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project Figure 62 Deployment Profile 6.6.8 Creating and Starting the Domain To build and deploy your Project, you must first create a domain. A domain is an instance of a Logical Host. After the domain is created, the Project is built and then deployed. Note: You are only required to create a domain once when you install the Sun Java Composite Application Platform Suite.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project an Existing Domain button. Once your domain is started, a green check mark indicates that the domain is running. Note: For more information about creating and managing domains see the eGate Integrator System Administration Guide. 6.6.9 Building and Deploying the Project The Build process compiles and validates the Project’s Java files and creates the Project EAR file.
Chapter 6 Implementing the JDBC/ODBC eWay Sample Projects Section 6.6 Building and Deploying the prjJDBC_BPEL Sample Project 2 Verify the output data by viewing the sample output files. See About the JDBC eWay Sample Projects on page 69 for more details on the types of output files used in this sample Project. The output files may change depending on the number of times you execute the sample Project, the input file, and also the content of your database table.
Appendix A JDBC/ODBC Drivers Drivers are uniquely different in what they do and the type of functions they support. The JDBC/ODBC eWay allows you to pick and choose which driver is best suited for your application environment. There can be significant differences and limitations between drivers. The performance and functionality of the JDBC/ODBC eWay depends on the selected driver(s). Certain drivers may not support all JDBC features.
Appendix A JDBC/ODBC Drivers Section A.1 AS/400 Toolbox Driver What’s In This Chapter: AS/400 Toolbox Driver on page 118 Attunity Driver on page 119 MySQL Connector/J Driver on page 121 PostgreSQL Driver on page 122 SyBase JConnect Driver on page 124 Sequelink DataDirect Informix ODBC Driver on page 125 Sequelink DataDirect MS Access ODBC Driver on page 126 Teradata Driver on page 128 Installing JDBC/ODBC Drivers on page 129 Troubleshooting on page 130 Refer to the supplied Readme.
Appendix A JDBC/ODBC Drivers A.1.2 Section A.2 Attunity Driver Environment Properties Use Table 25 to configure the environment properties for the specified JDBC/ODBC driver. Table 25 AS/400 Database Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com.ibm.as400.access.AS400JDBCConnectionPoolDataSource ClassNamefor OtherInterfaces ServerName Server name of the machine hosting the database.
Appendix A JDBC/ODBC Drivers Section A.2 Attunity Driver Table 26 Attunity Driver Database Connection Information Parameter Value Driver Jar Files nvjdbc2.jar Driver Java Class Name com.attunity.jdbc.NvDriver URL Connection String jdbc:attconnect://;DefTdpName=;OneTdpMode=1 Note: The is created in the Attunity server. User Name Leave password field blank. Value configured when the database entry is created in the Attunity Server.
Appendix A JDBC/ODBC Drivers Section A.3 MySQL Connector/J Driver Table 27 Attunity Driver Database Environment Properties (Continued) Parameter MaxIdleTime Value The default is 0. MySQL Connector/J Driver A.3 Configuration Properties: OTD Wizard: Database Connection Information on page 121 Environment Properties on page 121 A.3.
Appendix A JDBC/ODBC Drivers Section A.4 PostgreSQL Driver Table 29 MySQL Connector/J Driver Environment Properties (Continued) Parameter Value ServerName Server name of the machine hosting the database. PortNumber Note: NOTE: Default server port is 3306. DatabaseName User Login name of the account used to access the database. Password Password associated with the login account name used to connect to the database. DriverProperties Delimiter The default is #.
Appendix A JDBC/ODBC Drivers Section A.4 PostgreSQL Driver Table 30 PostgreSQL Driver Connection Information (Continued) Parameter URL Connection String Value jdbc:postgresql://:/ Note: NOTE: Default server port is 5432. User Name Login name of the account used to access the database. Password Password associated with the login account name used to connect to the database. A.4.
Appendix A JDBC/ODBC Drivers Section A.5 SyBase JConnect Driver SyBase JConnect Driver A.5 Configuration Properties: OTD Wizard: Database Connection Information on page 124 Environment Properties on page 124 A.5.1 OTD Wizard: Database Connection Information To connect to Sybase, use the information provided in Table 32 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard. To access Sybase, it is recommended to use the Sybase eWay Adapter.
Appendix A JDBC/ODBC Drivers Section A.6 Sequelink DataDirect Informix ODBC Driver Table 33 Sybase JConnect Driver Environment Properties (Continued) Parameter Password Value Password associated with the login account name used to connect to the database. DriverProperties Delimiter The default is #. DataSourceName MinPoolSize The default is 0. MaxPoolSize The default is 10. MaxIdleTime The default is 0.
Appendix A JDBC/ODBC Drivers A.6.2 Section A.7 Sequelink DataDirect MS Access ODBC Driver Environment Properties Use Table 35 to configure the environment properties for the specified JDBC/ODBC driver. Table 35 Sequelink DataDirect Informix ODBC Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com.ddtek.jdbcx.sequelkink.SequeLinkDataSource ClassNamefor OtherInterfaces ServerName Server name of the machine hosting Sequelink.
Appendix A JDBC/ODBC Drivers A.7.1 Section A.7 Sequelink DataDirect MS Access ODBC Driver OTD Wizard: Database Connection Information To connect to Microsoft Access, via the Microsoft Access ODBC driver, use the information provided in Table 36 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard. Table 36 MS Access ODBC Driver Database Connection Information Parameter Value Driver Jar Files sljc.jar Driver Java Class Name com.ddtek.jdbc.sequelink.
Appendix A JDBC/ODBC Drivers Section A.8 Teradata Driver Table 37 MS Access ODBC Driver Environment Properties (Continued) Parameter Value MaxIdleTime The default is 0. Note: It is not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sequelink DataDirect MS Access ODBC. Teradata Driver A.8 Configuration Properties: OTD Wizard: Database Connection Information on page 128 Environment Properties on page 128 A.8.
Appendix A JDBC/ODBC Drivers Section A.9 Installing JDBC/ODBC Drivers Table 39 Teradata Driver Environment Properties (Continued) Parameter Value ClassNamefor OtherInterfaces ServerName Server name of the machine hosting the database. PortNumber Note: NOTE: Default server port is 6666 for the Type-3 driver Gateway. DatabaseName User Login name of the account used to access the database.
Appendix A JDBC/ODBC Drivers A.10 Section A.10 Troubleshooting Troubleshooting Refer to the following when troubleshooting Driver issues. The ReceiveOne operation in BPEL is not supported when using inbound functions with some drivers. Some drivers do not support Updatable ResultSets. If you find this to be the case, use a Prepared Statement to Update, Insert, and Delete data. Not all drivers provide metadata information such as column names and data types.
Index delete(String sWhere) 59 deleteRow() 59 insert() 59 insertRow() 59 select(String where) 59 update(String sWhere) 59 updateRow() 59 database OTD wizard add prepared statement 52 editing existing OTDs 56 review selections 55 select procedures 48 select tables/views 44 specify the OTD name 55 DataDirect 125, 126 Deployment Profile Automap 93, 113 driver class, JDBC 33, 35, 38 Drivers 117 AS/400 118 Attunity 119 installing 129 MySQL Connector/J 121 PostgreSQL Driver 122 Sequelink Informix ODBC 125 Sequel
Index R JCD operations 59 delete(String sWhere) 59 deleteRow() 59 insert() 59 insertRow() 59 select(String where) 59 update(String sWhere) 59 updateRow() 59 JDBC driver class 33, 35, 38 JDBC eWay Project building and deploying (BPEL) 95 eInsight and eGate components 72 Importing 73 running sample projects 94, 115 running the SQL script 73 JDBC_BPEL_Sample 69 ResultSet collaboration usability for a stored procedure 66 ResultSet methods available 65 enableResultSetandUpdateCounts 65 enableResultSetOnly 65
Index U update count 65 JDBC/ODBC eWay Adapter User’s Guide 133 Sun Microsystems, Inc.
