Home

Sun Microsystems F815D Personal Computer User Manual

Contents

1. Column Name Data Type Data Length emp_no INTEGER 10 last_name VARCHAR 30 first_name VARCHAR 30 rate FLOAT 15 last_update TIMESTAMP 19 61 2 Operations Used in the JDBC Sample Projects The following database operations are used in both the JCD and BPEL sample projects Insert Update Delete Select Assigning Operations in JCD Database operations are listed as methods in the JCD Perform the following steps to access these methods 1 Create a Collaboration that contains an OTD using JDBC 2 Right click the OTD listed in your Collaboration and then select Select Method to Call from the shortcut menu 3 Browse to and select a method to call Assigning Operations in BPEL You can associate an eInsight Business Process Activity with the eWay both during the system design phase and during runtime To make this association 1 Select the desired receive or write operation under the eWay in the Enterprise Explorer 2 Drag the operation onto the eInsight Business Process canvas The operation automatically changes to an Activity with an icon identifying the component that is the basis for the Activity JDBC ODBC eWay Adapter User s Guide 71 Sun Microsystems Inc Chapter 6 Section 6 2 Implementing the JDBC ODBC eWay Sample Projects 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 Servi
2. Boolean String Nodes Number XSDOperation v Pont Business Process Attributes Business Process Attributes bad FileClientwwrite Output A lita i a BC DB_EMPLOYEEUpdate Input bd bad FileClient receive Output song ga input text g S EMP_NO Lara e byteAray FIRST_NAME encoding LAST_NAME LAST_UPDATE A 7 3 A RATE string literal whereClause Brian e C S 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 Business Rule Designer ot 3 B x Conversion Datetime Operator Boolean String Nodes Number XSDOperation lt _ Business Process Attributes Business Process Attributes a otdJDBC DB_EMPLOYEEUpdate A 5 3 Ta FileClientwrite Input1 bad string literal text Update Done e byteArray encoding TL lt n gt Configuring the bpelDelete Modeling Elements The bpelDelete business process describes how to delete a record in the JDBC database using the Business Process Designer Once you have connected the modeling elements together begin adding the business processes necessary to facilitate the Delete operation See Figure 49 for an illustration of how all the modeling elements appear when connected Note The where clause in the business rule reads the trigger value as a placeholder
3. Parameter Value URL Connection String jdbc postgresql lt server name gt lt server port gt lt database name gt 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 a42 Environment Properties Use Table 31 to configure the environment properties for the specified JDBC ODBC driver Table 31 PostgreSQL Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName org postgresql jdbc3 Jdbc3ConnectionPool ClassNamefor OtherlInterfaces ServerName Server name of the machine hosting the database PortNumber lt server port gt Note NOTE Default server port is 5432 DatabaseName lt database name gt 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 DataSourceName MinPoolSize The default is 0 MaxPoolSize The default is 10 MaxlIdleTime The default is 0 Note Itis not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for PostgreSQL JDBC ODBC eWay Adapter User s Guide 123 Sun Microsystems Inc Appendix A Section A 5 JDBC ODBC Drivers SyBase JConnect Drive
4. 212 Extracting the Sample Projects and Javadocs The JDBC eWay includes sample Projects and Javadocs The sample Projects are designed to provide you with a basic understanding of how certain database operations are performed using the eWay while Javadocs provide a list of classes and methods exposed in the eWay Steps to extract the Javadoc include 1 Click the Documentation tab of the Sun Java Composite Application Platform Suite Installer then click the Add ons tab 2 Click the JDBC eWay Adapter link Documentation for the JDBC eWay appears in the right pane 3 Click the icon next to Javadoc and extract the ZIP file 4 Open the index html file to view the Javadoc Steps to extract the Sample Projects include 1 Click the Documentation tab of the Sun Java Composite Application Platform Suite Installer then click the Add ons tab 2 Click the JDBC eWay Adapter link Documentation for the JDBC eWay appears in the right pane 3 Click the icon next to Sample Projects and extract the ZIP file Note that the JDBC_eWay_Sample zip file contains two additional ZIP files for each sample Project Refer to Importing a Sample Project on page 73 for instructions on importing the sample Project into your repository via the Enterprise Designer 22 ICAN 5 0 Project Migration Procedures This section describes how to transfer your current ICAN 5 0 x Projects to the Sun Java Composite Application Platform Suite 5 1 2 To migrate your IC
5. JDBC ODBC eWay Adapter User s Guide 63 Sun Microsytems Inc Chapter 5 Using JDBC ODBC Operations Executing Stored Procedures JDBC eWay Data Section 5 2 base Operations JCD 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 Within the Transformation Designer you can drag values from the input parameters execute the call collect data and drag the values to the output parameters Steps for executing the Stored Procedure include 1 Specify the input values 2 Execute the Stored Procedure 3 Retrieve the output parameters if any For example package Storedprocedure public class sp_jce public com stc codegen logger Logger log public com stc codegen alerter Alerter a public void receive com stc connector appconn file FileTextMessa ger lerter ge input com stc connector appconn file FileApplication FileClient_1 employeedb Db_employee employeedb_with_top_db_employee_1 insert_DB Insert_DBOTD insert_DB_1 throws Throwable employeedb_with_top_db_employee_1
6. Sun Microsystems Inc Chapter 3 Section 3 5 Setting Properties of the JDBC ODBC eWay eWay Environment Properties Table 8 Outbound JDBC eWay Connection Retry Settings Continued Name Description Required Value ConnectionRetryInterval Specifies the milliseconds of pause A valid numeric value The before each attempt to reaccess the default is 1000 database This setting is used in conjunction with the Connection Retries setting For example In the event that the eWay cannot connect to the Database the eWay will try to reconnect to the database 10 times in 5 seconds apart when the Connection Retries is 10 and the Connection Retry Interval is 5000 35 Outbound non Transactional JDBC eWay Properties The Outbound non Transactional JDBC eWay includes the following configuration sections JDBC Connector Settings Connection Retry Settings JDBC Connector Settings Details for the JDBC Connector Settings used by the external database are detailed in Table 9 Table 9 Outbound non Transactional eWay JDBC Connector Settings Name Description Required Value Description The description of the database A valid string The configured default is JDBC non Transactional Connection Pool Datasource ClassName Specifies the Java class in the JDBC A valid class name driver that is used to implement the For example ConnectionPoolDataSource com ddtek jdbcx sequelink SequeLin XADataSource
7. 30 eWay environment properties 29 eWay plug ins installing 21 eWay Properties Inbound JDBC eWay Properties 32 Outbound JDBC eWay Properties 32 35 37 Importing sample Projects 73 Inbound JDBC eWay Properties 32 installation 17 Installing alert codes 22 eWay plug ins 21 migration procedures 19 Repository on UNIX 17 sample Projects and Javadocs 19 J Javadocs installing 19 Sun Microsystems Inc Index 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 M migration procedures 19 MySQL Connector J Driver 121 O operations 57 OTD editing existing 56 outbound eWay properties 31 Outbound JDBC eWay Properties 32 35 37 outbound XA properties 31 P PostgreSQL Driver 122 prepared statement batch operations 67 executing 67 Project importing 73 properties ConnectionRetries 34 37 39 ConnectionRetryInterval 35 37 40 DatabaseName 33 36 38 Delimiter 33 36 38 Description 31 32 33 35 38 DriverProperties 33 36 38 MaxIdleTime 34 37 39 MaxPoolSize 34 37 39 MinPoolSize 34 36 39 Password 32 33 36 38 PortNumber 33 36 38 ServerName 33 35 38 User 32 33 36 38 JDBC ODBC eWay
8. 819 7387 10 Version 20061006184430 JDBC ODBC eWay Adapter User s Guide 2 Sun Microsystems Inc Contents Contents Chapter 1 Introducing the JOBC ODBC eWay 7 About Java Database Connectivity DBC 7 JDBC Drivers 7 Type l JOBC ODBC Bridge 8 Type One Driver 8 Type II Partial Java driver 9 Type Two Driver 9 Pure Java driver for database middleware 10 Type Three Driver 10 Type Four Driver Direct to database pure Java driver 11 About the JOBC ODBC eWay 13 What s New in This Release 13 About This Document 14 JDBC eWay Javadoc 14 Scope 14 Intended Audience 15 Text Conventions 15 Related Documents 15 Sun Microsystems Inc Web Site 15 Documentation Feedback 16 Chapter 2 Installing the JOBC ODBC eWay 17 Installing the JOBC eWay 17 Installing the JOBC eWay on an eGate supported system 18 Adding the eWay to an Existing Sun Java Composite Application Platform Suite Installation 18 After Installation 19 Extracting the Sample Projects and Javadocs 19 ICAN 5 0 Project Migration Procedures 19 Installing Enterprise Manager eWay Plug Ins 21 Viewing Alert Codes 22 JDBC ODBC eWay Adapter User s Guide 3 Sun Microsystems Inc Contents Chapter 3 Setting Properties of the JOBC ODBC eWay Creating and Configuring a JDBC eWay Configuring the eWay Connectivity Map Properties Transaction Support Levels Between Different Versions Configuring the eWay Environment Properties eWay Connectivity Map Properties Connectivity Map Outbo
9. E FileClient receive Qutput A S A FileClient write Input E string literal text byteArray Selecting record s from db_employee table via encoding A gt 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 Business Rule Designer E 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation a oo Business Process Attributes Business Process Attributes ba FileClient write Output DB_EMPLOYEESelectAll Input a WA FileClient receive Qutput input ER Q text whereClause byteArray encoding a gt 3 Configure the business rule between the otdJDBC DB_EMPLOYEESelectAll Activity and the otdInputDTD_DBemployees marshal Activity as seen in Figure 56 JDBC ODBC eWay Adapter User s Guide 105 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDOBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Figure 56 bpelSelectTable Business Rule 3 Business Rule Designer E 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation v oon i Business Process Attributes Business Process Attributes Des otdJDBC DB_EMPLOYEESelectA str strina TD_DBemployees marshal Input bad gt a output DBemployees m
10. FileClient_1l 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 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 appconn file FileTextMessage input dtd otdInputDTD_1394195520 DBemployees otdInputDTD_DBemployees_1 otdJDBC OtdJDBCOTD otdJDBC_1 dtd otdOutputDTD8 82991309 DBemployee otdOutputDTD_DBemployee_1 com stc connector appconn file FileApplication FileClient_1 throws Throwable FileClient_1 setText Delete record FileClient_1 write otdJDBC_1 getDB_EMPLOYEE delete input getText FileClient_1 setText Done delete FileClient_1l write 5 2 2 The Stored Procedure A Stored Procedure OTD represents a database stored procedure Fields correspond to the arguments of a stored procedure while methods are the operations that you can apply to the OTD It allows you to execute a stored procedure Remember that while in the Collaboration Editor you can drag and drop nodes from the OTD into the Collaboration Editor
11. These OTDs include JDBC Database OTD Inbound DTD OTD Outbound DTD OTD Steps required to create a JDBC Database OTD include 1 Right click your new Project in the Enterprise Designer s Project Explorer and select New gt Object Type Definition The New Object Type Definition Wizard window appears 2 Select the JDBC Database OTD Wizard from the list of OTD Wizards and click Next 3 Enter the connection information for the JDBC database Connection fields include Driver Jar Files Driver Java Class Name URL Connection String User name Password 4 Click Next and select the types of database object you want to include in the sample Project For this 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 Click Select when the database appears in the Results selection frame Click OK to close the Add Tables window 7 Click Next the Add Prepared Statements Wizard appears 8 Click Add the Add Prepared Statement window appears Enter the following Prepared Statement Name Select_ps SOL Statement select from db_employee where emp_no gt order by emp_no JDBC ODBC eWay Adapter User s Guide 96 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects Building
12. 1 pter 2 Installing the JOBC ODBC eWay Installing the JOBC eWay 211 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 After you have installed Core Products do the following 1 From the Sun Java Composite Application Platform Suite Installer s Select Sun Java Composite Application Platform Suite Products Installed table Administration tab click the Click to install additional products link Expand the eWay option Select the products for your Sun Java Composite Application Platform Suite and include the following File eWay the File eWay is used by most sample Projects JDOBCeWay To upload the JDBC eWay User s Guide Help file Javadoc Readme and sample Projects expand the Documentation option and select JDBCeWayDocs Once you have selected all of your products click Next in the top right or bottom right corner of the Select Sun Java Composite Application Platform Suite Products to Install box From the Selecting Files to Install box locate and select your first product s SAR file Once you have selected the SAR file click Next Your next selected product appears Follow this procedure for each of your selected products The Installation Status window appears and installation begins after the last SAR file has been selected Once your product
13. 3 From the Environment Explorer tree right click the File External System esFileClient in this sample and select Properties The Properties Editor opens to the File eWay Environment configuration 4 Modify the File eWay Environment configuration properties for your system as seen in Table 18 and click OK JDBC ODBC eWay Adapter User s Guide 91 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project Table 18 File eWay Environment Properties Section Property Name Required Value Configuration gt Inbound Directory Enter the directory that contains File eWay gt Parameter the input files trigger files Settings included in the sample Project Trigger files include TriggerDelete in in TriggerInsert in in TriggerPsSelect in in TriggerTableSelect in in TriggerPsUpdate in in Configuration gt Outbound Directory Enter the directory where output File eWay gt Parameter files are written In this sample Settings Project the output files include JCD_Delete_output0 dat JCD_Insert_output0 dat JCD_PsSelect_output0 dat JCD_TableSelect_output0 dat JCD_PsUpdate_output0 dat 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 Logic
14. 9 rowCount X_sequence_A objecti Ded DB_EMPLOYEESelectal EmpNo EMP_NO return string Lastname FIRST_NAME Firstname LAST_NAME Rate LastDate LAST_UPDATE RATE StF string resultS etAvailable i objecti return string C ae 2 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 Business Rule Designer ot ga x Conversion Datetime Operator Boolean String Nodes Number XSDOperation x E Business Process Attributes ma otdinputD TD_DBemployees marsh L contents Business Process Attributes FileClientwite Input1 E text byteArray encoding ae a gt 5 Configure the business rule between the FileClient write Activity and the FileClient write Activity as seen in Figure 58 Figure 58 bpelTableSelect Business Rule 5 Business Rule Designer ot 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation 6 Business Process Attributes FileClientwrite Output w m A string literal Done table select Business Process Attributes FileClientuwrite Input2 E text byteArray encoding JDBC ODBC eWay Adapter User s Guide 106 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing t
15. Codes for the JOBC ODBC eWay Alert Code Description Description Details User Actions DBCOMMON CONNECT Occurs during the Database is down start your FAILED000001 Failed to connect initial database database to database 0 on host 1 connection External configuration information Reason The Pooled connection establishment is invalid You may need to verify could not be allocated 2 the following Server name Database name User Password Port DBCOMMON CONNECT Occurs while Verify that the database has not FAILED000002 Operation failed retrieving a terminated with unexpected because of a database connection from the errors connection error Reason 0 database or the connection pool JDBC ODBC eWay Adapter User s Guide 22 Sun Microsystems Inc Chapter 2 Installing the JOBC ODBC eWay Section 2 3 Installing Enterprise Manager eWay Plug Ins Alert Code Description Description Details User Actions DBCOMMON CONNECT FAILED000005 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 XARESOURCE FAILED000001 Unable to get XAResource for the database Reason 0 Could not obtain XAResource for the connection Check if the database supports XA and has been configured
16. Integrator User s Guide What s in This Chapter About the JDBC eWay Sample Projects on page 69 Running the Sample Projects on page 72 Running the SQL Script on page 73 Importing a Sample Project on page 73 Building and Deploying the prjJDBC_JCD Sample Project on page 74 Building and Deploying the prjJDBC_BPEL Sample Project on page 95 61 About the JDBC eWay Sample Projects The JDBC eWay JDBC_eWay_Sample zip file contains two sample Projects that provide basic instruction on using JDBC operations in the Java Collaboration Definition JCD or the Business Process Excecution Language BPEL Projects The prjJDBC_JCD sample Project uses input files to pass data into Collaborations There are four Collaborations that demonstrate the Insert Update and Table Select operations and two Collaboration to demonstrate Prepared Statements Select and Update Results are written out to an output file The prjJDBC_BPEL sample Project uses input files to pass data into business process There are four business processes that demonstrate the Insert Update Delete and Select operations Results are written out to an output file JDBC ODBC eWay Adapter User s Guide 69 Sun Microsystems Inc Chapter 6 Section 6 1 Implementing the JDBC ODBC eWay Sample Projects About the JDBC eWay Sample Projects Figure 29 Database project flow File Received File Outbound Both the prjJDBC_JCD and prjJDBC_BPEL sample Projects demonstrate ho
17. ODBC Operations elnsight Operations Activity Input Activity Output SelectAll where clause optional Returns all rows that fit the condition of the where clause 58 Sun Microsytems Inc JDBC ODBC eWay Adapter User s Guide Chapt er5 Using JDBC ODBC Operations Section 5 2 JDBC eWay Database Operations JCD Table 13 JDBC ODBC Operations Continued elnsight Operations Activity Input Activity Output SelectMultiple number of rows where clause optional 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 Alternately if the number of rows that meet the condition are 20 but if the number of available rows are 10 then only 10 rows are returned SelectOne where clause optional Returns the first row that fits the condition of the where clause Insert definition of new item to be inserted Returns status Update where clause Returns status Delete where clause Returns status 52 JDBC eWay Database Operations JCD The same database operations are also used in the JCD but appear as methods to call from the Collaboration Tables Views and Stored Procedures are manipulated through OTDs Methods to call include insert insertRow update String sWh
18. OUT JDBC ODBC eWay Adapter User s Guide 66 Sun Microsytems Inc Section 5 2 Chapt JDBC eWay Database Operations JCD er5 Using JDBC ODBC Operations 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 Doing so may result in skipped data from one of the ResultSets when more than one ResultSet is present The methods available and getResultSet can not be used in conjunction with multiple ResultSets being open at the same time Attempting to open more the one ResultSet at the same time closes the previous ResultSet The recommended working pattern is Open one ResultSet ResultSet_1 and work with the data until you have completed your modifications and updates Open ResultSet_2 ResultSet_1 is now closed and modify When you have completed your work in ResultSet_2 open any additional ResultSets or close ResultSet_2 If you modify the ResultSet generated by the Execute mode of the Database Wizard you need to assure the indexes match the stored procedure By doing this your ResultSet indexes are preserved Generally getMoreResults does not need to be called It is needed if y
19. 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 JDBC ODBC eWay Adapter User s Guide 51 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD 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 y
20. access the database JDBC ODBC eWay Adapter User s Guide 124 Sun Microsystems Inc Appendix A Section A 6 JDBC ODBC Drivers Sequelink DataDirect Informix ODBC Driver Table 33 Sybase JConnect Driver Environment Properties Continued Parameter Value Password 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 MaxlIdleTime The default is 0 Note Itis not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sybase as Sequelink DataDirect Informix ODBC Driver Configuration Properties OTD Wizard Database Connection Information on page 125 Environment Properties on page 126 A61 OTD Wizard Database Connection Information The settings in Table 34 describe how to use the DataDirect Sequelink JOBC ODBC bridge with the JOBC ODBC eWay This information demonstrates how Sequelink can be used to interface with the ODBC driver To connect to an Informix database it is recommended to use the Informix eWay Adapter Table 34 Sequelink DataDirect Informix ODBC Driver Database Connection Information Parameter Value Driver Jar Files sljc jar Driver Java Class Name com ddtek jdbc sequelkink SequeLinkDriver URL Connection String jdbc sequelink lt server name gt lt server port gt N
21. 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 11 Click Next and review your settings then click Finish to create the OTD Steps required to create inbound and outbound DTD OTDs 1 Right click your new Project in the Enterprise Designer s Project Explorer and select New gt Object Type Definition The New Object Type Definition Wizard window appears 2 Select DTD from the list of OTD Wizards and click Next 3 Browse to and then select a DTD file For our example select one of the following DTD files from the sample Project and then click Next otdInputDTD dtd otdOutputDTD dtd 4 The file you select appears in the Select Document Elements window Click Next 5 Click Finish to complete the DTD based OTD Repeat this process again to create the second DTD file 663 Creating the Business Process Steps required to create the Business Process include Creating the business process flow Configuring the modeling elements Creating the Business Process Flow The business process flow contains all the BPEL elements that make up a business process Steps to create a business process flow include 1 Right click
22. between the external applications and the eGate system Each Connectivity Map in the The prjJDBC_BPEL sample Project use three eWays that are represented as a nodes between the External Applications and the Business Process as seen in Figure 61 You must configure eWay properties in both the Connectivity Map and the Environment Explorer Figure 61 eWays in the cmDelete Connectivity Map FileClientOUT eWay FileClientIN eWay FileClientOUT cmDelete_bpDelete bpel1 eaJDBCOUT eWay FileClientIN eaJDBCOUT JDBC ODBC eWay Adapter User s Guide 110 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 Table 20 FileClientIN eWay Property Settings Connectivity Map Property Name Required Value cmDelete Input file name TriggerDelete in cmInsert Input file name TriggerBpInsert in cmTableSelect Input file name TriggerTableSelect in cmUpdate Input file name TriggerUpdate in 2 Double click the FileClientOUT eWay on each of the Connectivity Maps and modify the properties for your system as seen in Table 21 Click OK to close the Properties Ed
23. bpDelete Business Rule 2 Business Rule Designer ot 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation v Business Process Attributes Business Process Attributes e FileClient write Output DBC DB_EMPLOYEEDelete Input bad bad FileClient receive Output input o text whereClause F byteAray encoding Ly gt lt gt JDBC ODBC eWay Adapter User s Guide 103 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JOBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project 3 Configure the business rule between the otdJ DBC DB_EMPLOYEEDelete Activity and the FileClient write Activity as seen in Figure 52 Figure 52 bpelDelete Business Rule 3 Business Rule Designer E x a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation Pon Business Process Attributes Business Process Attributes ba otdJDBC DB_EMPLOYEEDelete C A 3 n FileClient write Input1 al string literal text byteArray Delete Done encoding a LO Configuring the bpelTableSelect Modeling Elements The bpelTableSelect business process is describes how to select all records the JDBC database using the Business Process Designer Once you have connected the modeling elements together begin adding the business processes necessary to facilita
24. create an Environment 1 From the Enterprise Designer s Enterprise Explorer click the Environment Explorer tab Right click the Repository and select New Environment A new Environment is added to the Environment Explorer tree Rename the new Environment to envJDBCProj Right click envJDBCProj and select New gt JDBC External System Name the External System esJDBC Click OK esJDBC is added to the Environment Editor Right click envJDBCProj and select New gt File External System Name the External System esFileClient Click OK esFileClient is added to the Environment Editor Right click envJDBCProj and select New gt Logical Host The LogicalHost1 box is added to the Environment and LogicalHost1 is added to the Environment Editor tree Right click LogicalHost1 and select New gt Sun SeeBeyond Integration Server A new Integration Server IntegrationSvr1 is added to the Environment Explorer tree under LogicalHost1 See Figure 60 JDBC ODBC eWay Adapter User s Guide 109 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Figure 60 Environment Editor envJDBCProj FB envJIDBCproj LogicalHosti amp IntegrationSvr1 amp e spumsiomgrt EE Ge esJDBCOut E E esFileClient 8 Save your current changes to the Repository 6 6 6 Configuring the eWays eWays facilitate communication and movement of data
25. domainmgr bat file The Domain Manager appears 3 If you have already created a domain select your domain in the Domain Manager and click the Start an Existing Domain button Once your domain is started a green check mark indicates that the domain is running Your domain will continue to run unless you shut it down 4 If there are no existing domains a dialog box indicates that you can create a domain now Click Yes The Create Domain dialog box appears 5 Make any necessary changes to the Create Domain dialog box and click Create The new domain is added to the Domain Manager Select the domain and click the Start an Existing Domain button Once your domain is started a green check mark indicates that the domain is running For more information about creating and managing domains see the eGate Integrator System Administration Guide 65 11 Building and Deploying the Project The Build process compiles and validates the Project s Java files and creates the Project EAR file Build the Project 1 From the Deployment Editor toolbar click the Build icon 2 If there are any validation errors a Validation Errors pane will appear at the bottom of the Deployment Editor and displays information regarding the errors Make any necessary corrections and click Build again 3 After the Build has succeeded you are ready to deploy your Project Deploy the Project 1 From the Deployment Editor toolbar click the Deploy icon Click Yes when the
26. domains domain1 lib where lt JavaCAPS51 gt is the directory where the Sun Java Composite Application Platform Suite is installed When using any driver to build OTDs be sure to include the absolute path of the locally installed JAR file in the Database Connection Information window See Figure 11 on page 43 62 Running the Sample Projects The following steps are required to run the sample Projects that are contained in the JDBCeWayDocs sar file 1 Run the SQL script JDBC ODBC eWay Adapter User s Guide 72 Sun Microsystems Inc Chapter 6 Section 6 3 Implementing the JDBC ODBC eWay Sample Projects 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 63 Running the SQL Script The data used for both the JCD and BPEL sample Projects are contained within a table called db_employee You create this table by using the SQL statement JDBC_sample_script sql that is included in the sample Project Note that you must use a database tool to run the script Following is the SQL statement designed for the sample Projects drop table db_employee go create table db_employee EMP_NO int LAST_
27. for Distributed Transaction Support DBCOMMON XACONNECT FAILED000001 Failed to connect to database 0 on host 1 The XA connection could not be allocated Reason 2 Occurs during the initial database connection establishment Check if the database is configured for XA and if the database is running External configuration information is invalid You may need to verify the following Server name Database name User Password Port DBCOMMON XASTART FAILED000001 Unable to perform XAStart for the connection Reason 0 A connection error has occurred which caused XASTART to fail Check if the database is running and there are no network issues DBCOMMON XAEND FAILED000001 XAEnd failed Reason 0 Error occurred during commit on XA connection Look for the detailed error mentioned in the alert for the appropriate action DBCOMMON CANNOT GET ISOLATION LEVEL Unable to get isolationLevel for the transaction Reason 0 Could not read transaction isolation information of the connection Transaction isolation is one of the following constants Connection TRANSACTION_REA D_UNCOMMITTED Connection TRANSACTION_REA D_COMMITTED Connection TRANSACTION_REPE ATABLE_READ Connection TRANSACTION_SERI ALIZABLE Connection TRANSACTION_NO NE Note Confirm with the vendor that the getlsolation method of the connection i
28. interface Change as kDataSource needed for your driver ClassNamefor For Sun Java Composite Application A valid class name OtherlInterfaces Platform Suite components that do not use ConnectionPoolDataSource use this entry to enter the name of the driver class This class will not be used by the eWay ServerName This setting specifies the host name Any valid string of the external database server JDBC ODBC eWay Adapter User s Guide 35 Sun Microsystems Inc 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 A valid port number which the server is listening for connection requests DatabaseName Specifies the name of the database Any valid string instance User Specifies the user name the eWay Any valid string uses to connect to the database Password Specifies the password used to Any valid string access the database DriverProperties The Connection Pool DataSource implementation may need to execute additional methods to assure a successful run The additional methods will need to be identified in the Driver Properties You must ensure that the driver is installed on both the Logical Host machine and the Enterprise Designer machine Set the driver properties according to driv
29. may need to execute additional methods to assure a successful run The additional methods will need to be identified in the Driver Properties You must ensure that the driver is installed on both the Logical Host machine and the Enterprise Designer machine Set the driver properties according to driver vendor s instruction For example setDeffdpName DBSQL setWorkspa ce Navigator Delimiter This is the delimiter character to be The default is used in the DriverProperties prompt JDBC ODBC eWay Adapter User s Guide 38 Sun Microsystems Inc Chapter 3 Section 3 5 Setting Properties of the JDBC ODBC eWay eWay Environment Properties Table 11 Outbound XA JDBC eWay JDBC Connector Settings Continued Name Description Required Value DataSource Specifies the name of the The name of the XADataSource name XADataSource implementation to implementation This property is which the DataSource object Optional In most cases leave this box delegates behind the scenes when empty there is connection pooling or distributed transaction management being done MinPoolSize Specifies the maximum number of A valid numeric value The default is 0 physical connections the pool should keep available at all times 0 zero indicates that there is no maximum The pool size you set depends on the transaction volume and response time of the application If the pool size is too big you may end up with too many connectio
30. messagable For more information on messagable OTDs see the eGate Integrator User s Guide 42 Creating a New JDBC OTD The following steps are required to create a new OTD for the JDBC Adapter Select Wizard Type on page 42 JDBC ODBC eWay Adapter User s Guide 41 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard 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 421 Select Wizard Type Select the type of wizard required to build an OTD in the New Object Type Definition Wizard Steps Required to Select the JDBC Database OTD Wizard Include On the Project Explorer tree right click the Project and select New gt Object Type Definition from the shortcut menu The Select Wizard Type page appears displaying the available OTD wizards See Figure 10 Figure 10 OTD Wizard Selection Select Wizard Type Select Wizard Type OTD Wizard Description _ __ _ J Cobol Copybo Uses a COBOL CopyBook to create an OTD DTD Uses a DTD to create an OTD JDBC Datahba Uses a JDBC database to create an OTD Siebel EAl Uses Siebel Integration Object or ASI to create Sybase Datab Uses a Sybase database to create an OTD User Defined Allows the user to create a custo
31. non Transactional eWay User LO JDBC Connector settings Password LO Connection Retry Settings Outbound JDBC XA eWay LO JDBC Connector settings Connection Retry Settings Description parameter settings Comments parameter settings Properties Cancel 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 34 eWay Connectivity Map Properties The eWay Connectivity Map consists of the following properties categories Outbound eWay Configuration Sections Include Connectivity Map Outbound eWay Properties on page 31 Outbound non Transactional eWay Configuration Settings Include Connectivity Map Outbound non Transactional eWay Properties on page 31 JDBC ODBC eWay Adapter User s Guide 30 Sun Microsystems Inc Chapter 3 Section 3 5 Setting Properties of the JDBC ODBC eWay eWay Environment Properties Outbound XA eWay Configuration Settings Include Connectivity Map Outbound XA eWay Properties on page 31 34 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 Required Value Description The description of the database A valid string 3 4 2 Connectivity Map Outbound non Transact
32. not supported by those eWays The JOBC ODBC eWay uses Java Collaborations to interact with one or more external databases By using a Java Collaboration Service it is possible for eGate components such as eWay Adapters to connect to external databases and execute business rules 13 What s New in This Release The Sun SeeBeyond eWay JDBC Adapter includes the following changes and new features New for Version 5 1 2 WebLogic Support Supports automatic deployment of EAR files to WebLogic Application Server version 9 1 New for Version 5 1 1 This is a maintenance release No new features New for Version 5 1 0 Version Control An enhanced version control system allows you to effectively manage changes to the eWay components Multiple Drag and Drop Component Mapping from the Deployment Editor The Deployment Editor now allows you to select multiple components from the Editor s component pane and drop them into your Environment component Support to read configuration parameters from LDAP at runtime Connection Retry Support Allows you to specify the number of attempts to reconnect and the interval between retry attempts in the event of a connection failure Relaunchable OTD Support An OTD can be rebuilt and saved under the same name then relaunched back to the same Java Collaboration or BPEL This allows you to change the metadata in an OTD without having to completely recreate the business logic from scratch Edi
33. on page 126 Teradata Driver on page 128 Installing JOBC ODBC Drivers on page 129 Troubleshooting on page 130 Refer to the supplied Readme txt file for information regarding the location of the required driver files necessary to connect using the JOBC ODBC eWay a1 AS 400 Toolbox Driver Configuration Properties OTD Wizard Database Connection Information on page 118 Environment Properties on page 119 A11 OTD Wizard Database Connection Information To connect to AS 400 use the information provided in Table 24 to complete the Connect to Database step of the JOBC ODBC OTD Wizard To access DB2 it is recommended to use the DB2 eWay Adapter or the DB2 Connect eWay Adapter Table 24 AS 400 Database Connection Information Parameter Value Driver Jar Files jt400 jar Driver Java Class Name com ibm as400 access AS400J DBCDriver URL Connection String jdbc as400 lt server name gt lt server port gt Note NOTE Default server port is 446 User Name Login name of the account used to access the AS 400 database Password Password associated with the login account name used to connect to the AS 400 database JDBC ODBC eWay Adapter User s Guide 118 Sun Microsystems Inc Appendix A Section A 2 JDBC ODBC Drivers Attunity Driver a12 Environment Properties Use Table 25 to configure the environment properties for the specified JDBC ODBC driver Table 25 AS 400 Database Environme
34. ptjJDBC_JCD gt otdALL gt otdJDBC The otdJDBC OTD is added to the Selected OTDs field 5 In the same window double click otdOutputDTD_DBemployee The otdOutputDTD_DBemployee OTD is added to the Selected OTDs field Note that the otdOutputDTD_DBemployee OTD is created from the otdOutputDTD dtd that is included in the Sample Project JDBC ODBC eWay Adapter User s Guide 79 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 gt eWays gt File gt 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 jcdTableSelect Collaboration Steps required to create the jcdTableSelect Collaboration 1 From the Project Explorer right click the sample Project and select New gt Collaboration Definition Java from the shortcut menu The Collaboration Definition Wizard Java appears 2 Enter a Collaboration Definition name for this sample jcdTableSelect and click Next 3 For Step 2 or the wizard from the Web Services Interfaces selection window double click Sun SeeBeyond gt eWays gt File gt FileClient gt receive The File Name field now displays receive Click Next 4 For Step 3 of the wizard fr
35. 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 JDBC ODBC eWay Adapter User s Guide 47 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 16 Table View Columns Columns For DB_LEMPLOYEE Column Selection VARCHAR LASTSNAME VARCHAR FIRSTNAMES VARCHAR EMP_NO SMALLINT LAST_NAME VARCHAR FIRST_NAME VARCHAR RATE M M m m k Select All Invert Selection Indicates Primary Key soe noe 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 Additional column properties for DB_EMPLOYEE Column Selection EMPNO VARCHAR LAST NAME VARCHAR FIRSTNAMES VARCHAR EMP_NO SMALLINT LAST_NAME VARCHAR FIRST_NAME VARCHAR RATE DECIMAL Ra Select All Invert Selection Clear All 425 Select Procedures Select the type of stored procedures required in your OTD JDBC ODBC eWay Adapter User s Guide 48 Sun Microsystems Inc Section 4 2 Chapter 4 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Steps Required to Select Stored Procedures Include 1 On the Select Procedures and specify Resultset and Parameter Information windo
36. used for Outbound eWays The Inbound eWay uses native JDBC and must use Driver Manager see Table 6 on page 32 It is recommended that you use the Oracle eWay when using the native Oracle driver The JDBC eWay does not support some of the functions available in the Oracle eWay such as creating an OTD from a Prepared Statement using a Stored Procedure with ResultSets and CLOB support It is also recommended that you use the SQL Server eWay The JDBC driver available for download from the Microsoft web site may not contain the latest version from the vendor Not all drivers support Updatable ResultSets However it does allow standard Insert and Update operations when used with the Prepared Statement feature Insert into employee empno values Remember to ensure that the input parameter data types match the data types specified in the database table targeted by the Prepared Statements as some drivers always return the data type as a string Optionally you may perform the data conversion in the Collaboration JDBC ODBC eWay Adapter User s Guide 17 Sun Microsystems Inc Appendix A Section A 1 JDBC ODBC Drivers 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
37. visible between FileClientIN and jcdDelete 5 From the jcdDelete Binding dialog box map otdJDBC_1 under Invoked Services to the es DBCOUT External Application 6 From the jcdDelete Binding dialog box map FileClient_1 to the FileClientOUT External Application as seen in Figure 36 Figure 36 Connectivity Map Associating Binding the Project s Components o a FileClientOut FileClientin FileClientin cmDelete_jcdDelete1 cmDelete_jcdDelete1 cmDelete_jcdDelete1 _FileCliertOut cmDelete_jcdDelete1 Rule jcdDelete Implemented Services Invoked Services FileClient input otdinput otdlnput otdJDBC otdJDB otdOutp otdOutp ete jcdDeletet cmDelete_jcdDelete1_eaJDBCOut FileClient FileClie eaJDBCOut 7 Minimize the jcdDelete Binding dialog box by clicking the chevrons in the upper right corner 8 Save your current changes to the Repository and then repeat this process for each of the other Connectivity Maps 65 7 Creating an Environment Environments include the external systems Logical Hosts Integration Servers and message servers used by a Project and contain the configuration information for these components Environments are created using the Enterprise Designer s Environment Editor JDBC ODBC eWay Adapter User s Guide 88 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Pro
38. you will need to set the Environment properties The Inbound JDBC eWay includes the following configuration section Parameter Settings Details for the Inbound JDBC eWay Parameter Settings are listed in Table 6 Table 6 Inbound JDBC eWay Parameter Settings Name Description Required Value Description The description of the database A valid string ClassName Displays the Java class in the JDBC driver A valid class name that is used to implement the Driver See JDBC ODBC Drivers Manager interface Change this as needed on page 117 for some of the for your driver popular drivers you can use with this eWay URL This is the JDBC URL required to gain The applicable JDBC URL access to the database The URL usually See JDOBC ODBC Drivers starts with jdbc followed by on page 117 for some of the lt subprotocol gt and ends with information popular drivers you can use that identifies the data source as follows with this eWay jdbc lt driver gt lt data source name gt lt attribute name gt lt attribute value gt If you do not select URL in the connection method this parameter is ignored For more information on the JDBC URL please consult the documentation of your specific driver User Specifies the user name the eWay uses to Any valid string connect to the database Password Specifies the password used to access the Any valid string database 35 2 Outbound JDBC eWay Properti
39. your new Project in the Enterprise Designer s Project Explorer and select New gt Business Process from the shortcut menu The eInsight Business Process Designer appears and BusinessProcess1 is added to the Project Explorer tree Rename BusinessProcess1 to bpelPsInsert 2 Create three additional business processes and rename them as follows bpelUpdate bpelDelete bpTableSelect 3 Add the following Activities to the Business Process Designer canvas JDBC ODBC eWay Adapter User s Guide 97 Sun Microsystems Inc Chapter 6 Implementing the JDBC ODBC eWay Sample Projects Table 19 Business Process Activities Section 6 6 Building and Deploying the prjJDBC_BPEL Sample Project 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 DB_EMPLOYEEDelete FileClient write bpelTableSelect FileClient receive FileClient write otdJDBC DB_EMPLOYEESelectAll otdInputDTD_DBemployees marshal FileClient write FileClient write Configuring the bpelPsInsert Modeling Elements Business Rules created between the Business Process Activities allow you to configure the relationships between the input and output Attributes of the Activities
40. 0c S400L0C S400L0c S400L0C Selected 1 S400LOC Remove 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 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 15 Selected Tables Views Aliases window with a table selected Select Tables Views Aliases and specify Column information Select Wizard Type Selected Tables Views Aliases Connect to Database Select Database Objects Te Schema Tns Select Tables Views Aliases Select Procedures Add Prepared Statements pecify the OTD Name eview Selections Description DB_EMPLOYEE 8 columns selected ganos O Use fully qualified table view names in the generated Java code Back Nete Finish Cancel Help 5 Inthe 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 Inthe 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 Figure 16 The data type is usually listed as Other when
41. 7 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 JOBC ODBC eWay This chapter describes how to install the JOBC 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 21 Installing the JDBC eWay The Java Composite Application Platform Suite Installer referred to throughout this guide as the Suite Installer is a web based application that is used to select and upload core products composite applications and add on files eWays during the installation process The following section describes how to install the components required for this eWay Refer to the readme for the latest information on Supported Operating Systems System Requirements External System Requirements The JDBC eWay Readme is uploaded with the eWay s documentation file JDBCeWayDocs sar and can be accessed from the Documentation tab of the Suite Installer Note When the Repository is running on a UNIX operating system the eWays are loaded from the Suite Installer running on a Windows platform connected to the Repository server using Internet Explorer JDBC ODBC eWay Adapter User s Guide 17 Sun Microsystems Inc Chapter Section 2
42. AN 5 0 x Projects to the Sun Java Composite Application Platform Suite 5 1 2 do the following Export the Project 1 Before you export your Projects save your current ICAN 5 0 x Projects to your Repository JDBC ODBC eWay Adapter User s Guide 19 Sun Microsystems Inc Chapter 2 Section 2 2 Installing the JOBC ODBC eWay 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 4 Inthe same manner select the Environment that you want to export in the left pane of the Export Manager and move it to the Selected Environments field by clicking the Add to Select Items arrow button or click All to include all of your Environments 5 Browse to select a destination for your Project ZIP file and enter a name for your Project in the ZIP file field 6 Click Export to create the Project ZIP file in the selected destination Install Java CAPS 5 1 2 1 Install Java CAPS 5 1 2 including all eWays libraries and other components used by your ICAN 5 0 Projects 2 Start the Java CAPS 5 1 2 Enterprise Designer Import the Project 1 From the Java CAPS 5 1 2 Enterprise Designer s Project Explorer tree right click the Repo
43. Adapter User s Guide 132 R ResultSet collaboration usability for a stored procedure 66 ResultSet methods available 65 enableResultSetandUpdateCounts 65 enableResultSetOnly 65 enableUpdateCountsOnly 65 getUpdateCount 65 next 65 resultsAvailable 65 S sample Projects 73 sample projects installing 19 Select Database Objects 43 Select Procedures 49 Select Table Views 44 Sequelink Informix ODBC Driver 125 Sequelink MS Access ODBC Driver 126 Setting Properties configuring JDBC eWay 25 connection retry settings 34 37 39 eWay Connectivity Map 25 30 eWay environment properties 29 outbound eWay 31 outbound XA properties 31 SQL 60 SQL operations table 60 SQL operations table Delete 63 SQL operations table Insert 61 SQL operations table Select 60 SQL operations table Update 62 Stored Procedure executing 64 manipulating ResultSet and update count 65 supporting documents 15 Sybase JConnect Driver 124 T Table SQL operations 60 SQL operations Delete 63 SQL operations Insert 61 SQL operations Select 60 SQL operations Update 62 Teradata Driver 128 text conventions 15 Transaction Support Levels 27 Troubleshooting 130 Sun Microsystems Inc Index U update count 65 JDBC ODBC eWay Adapter User s Guide 133 Sun Microsystems Inc
44. C ODBC Drivers Drivers are uniquely different in what they do and the type of functions they support The JOBC 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 JOBC ODBC eWay depends on the selected driver s Certain drivers may not support all JDBC features Consult the documentation for your respective drivers for more information This appendix provides database configuration information and environment properties specifications for specific JOBC ODBC drivers You should use the information listed in the included tables to define values for required input parameters While any standards compliant JOBC ODBC database driver may be used the drivers covered in this chapter are used more frequently For runtime only drivers that support Connection Pool Data Source and XA Data Source are supported Connection Pool Data Source takes advantage of the Integration Service s connection pooling in order to improve performance For the OTD Wizard the driver Manager Class will work However not all drivers support all metadata discovery methods some of which are needed to build the OTD Additionally not all drivers support Updatable ResultSets Stored Procedures or Stored Procedures with ResultSets Check with your driver vendor for what is supported The ConnectionPoolDataSource should only be
45. Client gt receive The File Name field now displays receive Click Next 4 For Step 3 of the wizard from the Select OTDs selection window double click ptjJDBC_JCD gt otdALL gt otdJDBC The otdJDBC OTD is added to the Selected OTDs field 5 In the same window double click otdInputDTD_DBemployees The otdInputDTD_DBemployees OTD is added to the Selected OTDs field Note The otdOutputDTD_DBemployees OTD is created from the otdInputDTD dtd that is included in the Sample Project 6 Click the Up One Level button twice to return to the Repository Double click Sun SeeBeyond gt eWays gt File gt FileClient The Selected OTDs field now lists the FileClient OTD 7 Click Finish The Collaboration Editor with the new jcdInsert Collaboration appears in the right pane of the Enterprise Designer jcdPsSelect Collaboration Steps required to create the jcdPsSelect Collaboration 1 From the Project Explorer right click the sample Project and select New gt Collaboration Definition Java from the shortcut menu The Collaboration Definition Wizard Java appears 2 Enter a Collaboration Definition name for this sample jcdPsSelect and click Next 3 For Step 2 of the wizard from the Web Services Interfaces selection window double click Sun SeeBeyond gt eWays gt File gt FileClient gt receive The File Name field now displays receive Click Next 4 For Step 3 of the wizard from the Select OTDs selection window double click
46. Deploy prompt appears 2 A message appears when the project is successfully deployed You can now test your sample 6 5 12 Running the Sample Additional steps are required to run the deployed sample Project Steps required to run the sample Project 1 Rename one of the trigger files included in the sample Project from lt filename gt in in to lt filename gt in to run the corresponding operation The File eWay polls the directory every five seconds for the input file name as defined in the Inbound File eWay Properties window The JCD then transforms the JDBC ODBC eWay Adapter User s Guide 94 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 TriggerTableSelect in TriggerDelete in TriggerPsUpdate in Having no content in these files causes the operation to read all records 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 T
47. FileClient_1l write The Update Operation To perform an update operation on a table 1 Execute the update method 2 Using a while loop together with next move to the row that you want to update 3 Assign updating value s to the fields of the table OTD 4 Update the row by calling updateRow package prjJDBC_JCDjcdALL public class jcdUpdate 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 appconn file FileTextMessage input otdJDBC OtdJDBCOTD otdJDBC_1 dtd otdOutputDTD882991309 DBemployee otdOutputDTD_DBemployee_1 dtd otdInputDTD_1394195520 DBemployees otdInputDTD_DBemployees_1 com stc connector appconn file FileApplication FileClient_1 throws Throwable FileClient_1 setText Update the Rate and Last_update fields using Prepared Statement FileClient_1l write JDBC ODBC eWay Adapter User s Guide 62 Sun Microsytems Inc Chapter 5 Section 5 2 Using JDOBC ODBC Operations 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
48. For example In the event that the eWay cannot connect to the Database the eWay will try to reconnect to the database 10 times in 5 seconds apart when the Connection Retries is 10 and the Connection Retry Interval is 5000 A valid numeric value The default is 1000 JDBC ODBC eWay Adapter User s Guide 40 Sun Microsystems Inc Chapter 4 Using the JOBC ODBC eWay Database Wizard This chapter describes how to use the JOBC 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 41 About the Database OTD Wizard 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 The OTD Wizard allows the addition and removal of columns nodes in an OTD Nodes with the same name and type as existing nodes are allowed by the wizard but should not be created and will result in generic code generation errors upon activation of the OTD Note Database OTDs are not
49. NAME varchar 30 FIRST_NAME varchar 30 RATE float LAST_UPDATE datetime go The sample Projects provided with the JDBC eWay use input files to pass predefined data or conditions into the Collaboration or BPEL business process which then transforms the database contents and delivers the ResultSet 64 Importing a Sample Project Sample eWay Projects are included as part of the installation CD ROM package To import a sample eWay Project to the Enterprise Designer do the following 1 Extract the samples from the Sun Java Composite Application Platform Suite Installer to a local file Sample files are uploaded with the eWay s documentation SAR file and then downloaded from the Installer s Documentation tab The JOBC_eWay_Sample zip file contains the various sample Project ZIP files JDBC ODBC eWay Adapter User s Guide 73 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 Specify the ZIP file and the root to import to From ZIP fie JOBCeWayiNew rojects p_JDBC510_SAMPLE_projectstpriJOBC_JCDtpriJDBC_JCD zip Browse Root project Root enviro
50. Only method added to the PreparedStatement Agent class allows only ResultSets to be returned and thus every resultsAvailable called only returns Boolean true if a ResultSet is available Likewise the enableUpdateCountsOnly causes resultsAvailable to return true only if an Update Count is available The default case of enableResultsetsAndUpdateCount method allows both ResultSets and Update Counts to be returned JDBC ODBC eWay Adapter User s Guide 65 Sun Microsytems Inc Chapter 5 Section 5 2 Using JDBC ODBC Operations 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 gt 0 System err println Updated getSPIn getSpS_multi getUpdateCount rows if getSPIn getSpS_multi getNormRS available while getSPIn getSpS_multi getNormRS next Customer Id getNormRS getCustomerId Customer Name getNormRS getCustomerName System err println getSPIn getSpS_multi System err printin PI getSPIn getSpS_multi System err println System err printlin else if getSPIn getSpS_multi
51. ResultSet Columns Back Next gt Finish fi Cancel Help 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 Prepared Statement Name PreparedStatement1 SAL Statement Select from db_employee where emp_no Open Save As LI Discard statement parameter overrides Cancel 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 24 JDBC ODBC eWay Adapter User s Guide 53 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 24 Edit the Prepared Statement Parameters Add Prepared Statements Select Wizard Type Connect to Database Prepared Statements Select Database Objects Select TablesViews Aliases Select Procedures Description PreparedStatement1 Edit Parameters 1 parameter s 5 resultset column s Edit ResultSet Columns a 5 To edit Resultset Columns c
52. SUN SEEBEYOND eWAY JDBC ODBC ADAPTER USER S GUIDE Release 5 1 2 sS Re SUN microsystems 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 and in other countries U S Government Rights Commercial software Government users are subject to the Sun Microsystems Inc standard license agreement and applicable provisions of the FAR and its supplements Use is subject to license terms This distribution may include materials developed by third parties Sun Sun Microsystems the Sun logo Java Sun Java Composite Application Platform Suite SeeBeyond eGate elnsight eVision eTL eXchange eView elndex eBAM eWay and JMS are trademarks or registered trademarks of Sun Microsystems Inc in the U S and other countries All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International Inc in the U S and other countries Products bearing SPARC trademarks are based upon architecture developed by Sun Microsystems Inc UNIX is a registered trademark in the U S and
53. Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Note Review the elnsight 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 3 FileClient write FE 3 FileCliert write E gt O FileClient receive otdIDBC la DB_EMPLOYEEU pdate Steps required to configure the bpelUpdate business process 1 Configure the business rule between the FileClient receive and FileCleint write Activities as seen in Figure 46 Figure 46 bpelUpdate Business Rule 1 Business Rule Designer cau 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation v Business Process Attributes Business Process Attributes WA FileClient receive Output A nll FileClientwite Input 90 string literal text Updating Record byteArray encoding A gt 2 Configure the business rule between the FileClient write Activity and otdJ DBC DB_EMPLOYEEUpdate Activity as seen in Figure 47 JDBC ODBC eWay Adapter User s Guide 101 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JOBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Figure 47 bpelUpdate Business Rule 2 Business Rule Designer E 3 a x Conversion Datetime Operator
54. Select_psResults getLAST_UPDATE toSt ring FileClient_1 setText otdOutputDTD_DBemployee_1 marshalToString FileClient_1 write else FileClient_1 setText No record found FileClient_1l write Writes a message to JCD PsSelect_output0 dat to confirm when records are selected or when no records are available FileClient_1 setText Done Select FileClient_l write Creating the jcdTableSelect Business Rules The jcdTableSelect Collaboration implements the Input Web Service Operation to read the TriggerTableSelect in file It then copies the database resultset into the otdInputDTD_DBEmployee OTD and selects all available records from the database that meet a certain criteria The Collaboration also writes a message to JCD_TableSelect_output0 dat to confirm when records are selected or when no records are available Note The where clause in the business rule reads the trigger value as a placeholder for input This permits you to modify the query to select a specific record Also note that all records are selected from the database when the TriggerTableSelect in file is empty The jedTableSelect Collaboration contains the Business Rules displayed in Figure 34 JDBC ODBC eWay Adapter User s Guide 85 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Buil
55. Specify the OTD Name eview Selections URL Connection String idbe as400 imorpheus stc com 446 User Name steqa Password nana Back Next gt Finish Cancel Help 423 Select Database Objects Select the type of JDBC 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 Figure 12 Note Views are read only and are for informational purposes only JDBC ODBC eWay Adapter User s Guide 43 Sun Microsystems Inc Chapter 4 Using the JDBC ODBC eWay Database Wizard Steps Select Wizard Type Connect to Database Select Database Objects Select Tables Views Aliases Select Procedures Add Prepared Statements Specify the OTD Name Review Selections Figure 12 Select Database Objects Select Database Objects Section 4 2 Creating a New JDBC OTD Which of the following database objects would you like to include in your OTD Select at least one M TablesiViews Aliases Procedures M Prepared Statements Connection established Driver Class Name com ibm as400 access 4S400JDBCDriver URL Connection String Jdbe as4o lt Back Next gt Finish Cancel Help 424 Select Tables Views Aliases Select the types of tables views or aliases requ
56. You have to use a Prepared Statement to perform such an operation Check with the respective driver s vendor for further information This feature is known as Updatable ResultSet 5 11 Activity Input and Output The Sun SeeBeyond Enterprise Designer Business Rules Designer includes Input and Output columns to map and transform data between Activities displayed on the Business Process Canvas JDBC ODBC eWay Adapter User s Guide 57 Sun Microsytems Inc Section 5 1 Chapter 5 Using JDBC ODBC Operations 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 xo 1 WO BE aasad gt Oo 08 Q fai Tai Start d dia gt 3 FileClient write 3 3 FileCliert write gt O aiaia o f rex gt End FileClient receive otdJDBC DB_EMPLOYEED elete O eens O Business Rule Designer 5 x a x Conversion Datetine Operator Boolean String Nodes Number lt Business Process Attributes otdJDBC DB_EMPLOYEEDelete Input 3 input o whereClause Business Process Attributes WA FileClientwrite Output a FileClient receive Output text byteArray encoding The following table lists the expected Input and Output of each database operation Activity Table 13 JDBC
57. _name lt gt Copy otdinputD TD_DBemployees_1 X_sequence_A i1 Firstname to otdJDBC_1 Insert_Ps First_name lt gt Copy new BigDecimal otdinputD TD_DBemployees_1 X_sequence_A i1 Rate to otdJDBC_1 Insert_Ps Rate lt gt Copy stringToSQLDate otdInputD TD_DBemployees_1 _sequence_A i1 LastDate to otdJDBC_1 Insert_Ps Last_update L lt gt otdJDBC_1 Insert_Ps executeUpdate lt gt Copy Done Insert to FileClient_1 Text L lt gt FileClient_1 write LE logger E alerter E collabContext L E typeConverter 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 appconn file FileTextMessage input com stc connector appconn file FileApplication FileClient_1 dtd otdInputDTD_1394195520 DBemployees otdInputDTD_DBemployees_1 dtd otdOutputDTD882991309 DBemployee otdOutputDTD_DBemployee_1 otdJDBC OtdJDBCOTD otdJDBC_1 throws Throwable Writes out a message stating records are being inserted FileClient_1 setText Inserting records into db_employee table FileClient_1 write Unmarshals data from the input XML d
58. a Last_name Firstname Last_update Rate Rate LastDate number objecti return number E 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 Business Rule Designer oe 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation v Pont Co Business Process Attributes s Business Process Attributes H otdJDBC Insert_PsPSInsert Output A q J A I FileClient write Input1 mu string literal text Insert Done e byteArray J encoding B LJ Configuring the bpelUpdate Modeling Elements The bpelUpdate business process describes how to update a record in the JDBC database using the Business Process Designer Once you have connected the modeling elements together begin adding the business processes necessary to facilitate the Update operation Figure 45 illustrates how all the modeling elements appear when connected Note The where clause in the business rule reads the trigger value as a placeholder for input This permits you to modify the query to select a specific record Also note that all records are selected from the database when the TriggerUpdate in file is empty JDBC ODBC eWay Adapter User s Guide 100 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JOBC ODBC eWay
59. 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 0 versions you can choose the method below A Inthe Connectivity Map delete the link to the database external application then reconnect the link and select NoTransaction B Fill in the NoTransaction property for the database external system under the Environment C Rebuild the Project The following charts identifies what transaction support levels changed between 5 0 5 and 5 1 2 and 5 1 0 and 5 1 2 respectively Note that there are no changes when migrating from ICAN version 5 0 5 and Java CAPS 5 1 2 Figure 8 Transaction Support Levels ICAN Version 5 0 5 Java CAPS 5 1 2 Outbound Informix eWay Outbound Informix eWay LocalTransaction LocalTransaction Outbound Informix XA Outbound Informix XA eWay eWay XATransaction XATransaction Java CAPS 5 1 0 Java CAPS 5 1 2 Outbound Informix non Transaction eWay NoTransaction Outbound Informix eWay LocalTransaction Outbound Informix XA eWay XATransaction Outbound Informix XA eWay XATransaction Under the scenario noted above if you want 5 1 2 behavior for a LocalTransaction then set your eWay connection to be Outbound JDBC ODBC non Transactional eWay NoTransaction JDBC ODBC eWay Adapter User s Guide 28 Sun Microsystems Inc Chapter 3 S
60. al Host and select Properties from the shortcut menu The Integration Server Properties Editor appears 2 Click the Password property field under Sun SeeBeyond Integration Server Configuration An ellipsis appears in the property field 3 Click the ellipsis The Password Settings dialog box appears 4 Enter STC as the Specific Value and as the Confirm Password and click OK 5 Click OK to accept the new property and close the Properties Editor For more information on deploying a Project see the Sun SeeBeyond Java Composite Application Platform Suite Deployment Guide 659 Creating the Deployment Profile A Deployment Profile is used to assign services and message destinations to the Integration Server and message server Deployment profiles are created using the Deployment Editor JDBC ODBC eWay Adapter User s Guide 92 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 gt 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 The Project s components are automatically mapped to their system windows See Figure 39 Figure 39 Deplo
61. ameters are modified for your specific system The JDBC eWay configuration parameters are modified from two locations Connectivity Map These parameters most commonly apply to a specific component eWay and may vary from other eWays of the same type in the Project Environment Explorer These parameters are commonly global applying to all eWays of the same type in the Project The saved properties are shared by all eWays in the JOBC External System window Collaboration or Business Process JDBC eWay properties may also be set from your Collaboration or Business Process in which case the settings will override the corresponding properties in the eWay s Connectivity Map configuration Any properties that are not overridden retain their configured default settings 32 Configuring the eWay Connectivity Map Properties When you connect an External Application to a Collaboration Enterprise Designer automatically assigns the appropriate eWay to the link Each eWay is supplied with a list of eWay connections transaction support levels from which to choose Transaction support levels provided by the JDBC eWay include JDBC ODBC eWay Adapter User s Guide 25 Sun Microsystems Inc Chapter 3 Section 3 2 Setting Properties of the JDBC ODBC eWay 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 Ent
62. ample Projects Building and Deploying the prjJDBC_JCD Sample Project otdOutputDTD_DBemployee_1 setRate otdJDBC_1 getDB_FMPLOYEE getRATE toString otdOutputDTD_DBemployee_1 setLastDate typeConverter dateToString otdJDBC_1 getDB_FMPLOYEE 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 setText otdOutputDTD_DBemployee_1 marshalToString FileClient_1l write Writes a message to confirm when records are selected or when no records are available FileClient_1 setText Done table select FileClient_l write Creating the jcdUpdate Business Rules The jcdUpdate Collaboration implements the Input Web Service Operation to read the TriggerUpdate in file and then update a particular record The Collaboration also writes a message to JCD_Update_output0 dat to confirm an updated record Note The where clause in the business rule reads the trigger value as a placeholder for input This permits you to modify the query to select a specific record Also note that all records are selected from the database when the TriggerUpdate in file is empty The jedUpdate Collaboration contains the Business Rules displayed in Figure 35 Figure 35 jcdTableUpdate O GD jedUpdate O
63. appears 2 Click the Password property field under Sun SeeBeyond Integration Server Configuration An ellipsis appears in the property field Click the ellipsis The Password Settings dialog box appears 4 Enter STC as the Specific Value and as the Confirm Password and click OK 5 Click OK to accept the new property and close the Properties Editor For more information on deploying a Project see the Sun SeeBeyond Java Composite Application Platform Suite Deployment Guide 667 Creating the Deployment Profile A Deployment Profile is used to assign services and message destinations to the Integration Server and message server Deployment profiles are created using the Deployment Editor Steps required to create the Deployment Profile 1 From the Enterprise Explorer s Project Explorer right click the prjJDBC_BPEL Project and select New gt Deployment Profile 2 Enter a name for the Deployment Profile for this sample dpJDBC_BPEL Select envJDBCProj as the Environment and click OK 3 From the Deployment Editor toolbar click the Automap icon The Project s components are automatically mapped to their system windows as seen in Figure 62 JDBC ODBC eWay Adapter User s Guide 113 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Figure 62 Deployment Profile E cmDelete_bpDelete bpel1 gt FileClientOUT FileClientIN gt
64. ars Enter a Collaboration Definition name for this sample jcdDelete and click Next For Step 2 of the wizard from the Web Services Interfaces selection window double click Sun SeeBeyond gt eWays gt File gt FileClient gt receive The File Name field now displays receive Click Next For Step 3 of the wizard from the Select OTDs selection window double click ptjJDBC_JCD gt otdALL gt otdJDBC The otdJDBC OTD is added to the Selected OTDs field Click the Up One Level button twice to return to the Repository Double click Sun SeeBeyond gt eWays gt File gt FileClient The Selected OTDs field now lists the FileClient OTD Click Finish The Collaboration Editor with the new jcdDelete Collaboration appears in the right pane of the Enterprise Designer JDBC ODBC eWay Adapter User s Guide 78 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 gt 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 3 For Step 2 of the wizard from the Web Services Interfaces selection window double click Sun SeeBeyond gt eWays gt File gt File
65. ata into the otdInputDTD_DBEmployees OTD otdInputDTD_DBemployees_1 unmarshalFromString input getText Calls the otdJDBC OTD and inserts multiple records into the database via a For Loop The first insert method opens the table ResultSet for insert operations while the insertRow method inserts records into the table ResultSet for int il 0s 11 lt otdInputDTD_DBemployees_1 countX_sequence_A il 1 otdJDBC_1 getInsert_Ps setEmp_no typeConverter stringToShort JDBC ODBC eWay Adapter User s Guide 82 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project otdInputDTD_DBemployees_1 getX_sequence_A il getEmpNo false 0 otdJDBC_1 getInsert_Ps setLast_name otdInputDTD_DBemployees_1 getX_sequence_A il getLastname otdJDBC_1 getInsert_Ps setFirst_name otdInputDTD_DBemployees_1 getX_sequence_A il getFirstname otdJDBC_1 getInsert_Ps setRate new java math BigDecimal otdInputDTD_DBemployees_1 getX_sequence_A il getRate otdJDBC_1 getInsert_Ps setLast_update typeConverter stringToSQLDate otdInputDTD_DBemployees_1 getX_sequence_A il getLastDate yyyy MM dd hh mm ss false otdJDBC_1 getI
66. ating 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 Creating an Environment Configuring the eWays Configuring the eWay Properties Configuring the Environment Explorer Properties Configuring the Integration Server Creating the Deployment Profile Creating and Starting the Domain Building and Deploying the Project Running the Sample Project Appendix A JDBC ODBC Drivers Index JDBC ODBC eWay Adapter User s Guide 6 94 94 95 95 96 97 97 98 100 102 104 107 107 108 109 110 111 111 113 113 114 115 115 117 131 Sun Microsystems Inc Chapter 1 Introducing the JOBC 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 JOBC ODBC eWay Adapter referred to as the JOBC eWay throughout this guide This chapter provides an overview of database connectivity JDBC and open database connectivity ODBC APIs This chapter also introduces the JOBC ODBC eWay What s In This Chapter About Java Database Connectivity JDBC on page 7 Ab
67. ating the OTD See Figure 27 The resulting OTD appears on the Enterprise Designer s Project Explorer JDBC ODBC eWay Adapter User s Guide 55 Sun Microsystems Inc Chapter 4 Section 4 3 Using the JOBC ODBC eWay Database Wizard Steps to Edit an Existing JOBC OTD Figure 27 Database Wizard Summary Review your Selections Select Wizard Type You have successfully completed the Database Wizard Please review your selections Connect to Database Select Database Objects Select TablesfViews Aliases Select Procedures Connection Information Driver Jar Files CAJDBCDrivers AS400 jtopen_4_7Vib jt400 jar URL Connection String jdbe as400 morpheus stc com 446 Driver Java Class Name com ibm as400 access AS400JDBCDriver User Name steqa Selected Tables Views Aliases DB_EMPLOYEE Selected Procedures IRP_DBEMPLOYEE Selected Prepared Statements PreparedStatementt OTD Information OTD Name otdJDBC To close this wizard click Finish lt Back Next gt Finish cancel Help 43 Steps to Edit an Existing JOBC 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 Note Unlock the OTD To do this right click the OTD in the Project Explorer and select Version Control gt Check Out from the menu The Version Control Check Out window appears Select the OTD you want to check out
68. base logical name gt is created in the Attunity server User Name Leave password field blank Value configured when the database entry is created in the Attunity Server Password Leave password field blank Value configured when the database entry is created in the Attunity Server Use Table 27 to configure the environment properties for the specified JDBC ODBC a22 Environment Properties driver Table 27 Attunity Driver Database Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com attunity jdbc NvXADataSource ClassNamefor OtherInterfaces ServerName Server name of the machine hosting the database PortNumber lt server port gt Note NOTE Default server port is 2551 DatabaseName lt database name gt User Leave user field blank Value configured when the database entry is created in the Attunity Server Password Leave password field blank Value configured when the database entry is created in the Attunity Server DriverProperties setDefdpName lt database logical name gt setWorkspace Navigator Delimiter The default is DataSourceName MinPoolSize The default is 0 MaxPoolSize The default is 10 JDBC ODBC eWay Adapter User s Guide 120 Sun Microsystems Inc Appendix A Section A 3 JDBC ODBC Drivers MySQL Connector J Driver Table 27 Attunity Driver Database Environment Properties C
69. ces 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 613 About the elnsight Engine and eGate Components You can deploy an eGate component as an Activity in an eInsight Business Process Once you have associated the desired component with an Activity the eInsight engine can invoke it using a Web Services interface Examples of eGate components that can interface with elnsight in this way are Object Type Definitions OTDs An eWay Collaborations Using the eGate Enterprise Designer and elnsight you can add an Activity to a Business Process then associate that Activity with an eGate component for example an eWay When elnsight run the Business Process it automatically invokes that component via its Web Services interface 614 Sample Projects Drivers Sample Projects included with this eWay were built using the AS 400 JDBC Toolbox Driver jt400jar You must get this or a different driver from a third party vendor and install and configure the eWay as per the vendors specification using the vendor s specific driver jar file and appropriate driver specific parameters such as driver class name URL connection string and so forth The JAR file for the driver you select must be copied to the following folder lt JavaCAPS51 gt logicalhost is
70. ch is then translated to a DBMS protocol by a middleware server The middleware provides connectivity to many different databases This driver translates JDBC calls into the middleware vendor s protocol which is then converted to a database specific protocol by the middleware server software Pros Better performance than Types 1 and 2 Can be used when a company has multiple databases and wants to use a single JDBC driver to connect to all of them Server based so no need for JDBC driver code on client machine For performance reasons the back end server component is optimized for the operating system on which the database is running Cons Needs some database specific code on the middleware server If the middleware must run on different platforms a Type 4 driver might be more effective Type Three Driver A net protocol fully Java enabled driver translates JDBC API calls into a DBMS independent net protocol which is then translated to a DBMS protocol by a server This net server middleware is able to connect all of its Java technology based clients to many JDBC ODBC eWay Adapter User s Guide 10 Sun Microsystems Inc Chapter 1 Section 1 1 Introducing the JOBC ODBC eWay About Java Database Connectivity JDBC different databases Many mainframe legacy non relational databases use this kind of driver Figure 3 Typical Type 3 Middleware Driver Configuration GUI Host Machine Participating Host Server Net Protocol Pure Java D
71. crnPsinsert_bpPsinsertt E cmPsinsert_bpPsinsertt gt FileClientOUT FileClientIN gt cmTableSelect_bpTableSelect bpel1 cmTableSelect_bpTableSelect bpel gt FileClientOUT fe FileClientIN gt cmUpdate_bpUpdate bpel1 cmUpdate_bpUpdate bpell gt FileClientOUT he Be IntegrationSwr1 fz cmDelete_bpDelete bpelt fe cmPsinsert_bpPsinsertt fe cmTableSelect_bpTableSelect bpel1 fe cmUpdate_bpUpdate bpelt E SBJMSIQMgr1 668 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 Steps required to create and start the domain 1 Navigate to your lt JavaCAPS51 gt logicalhost directory where lt JavaCAPS51 gt is the location of your Sun Java Composite Application Platform Suite installation 2 Double click the domainmgr bat file The Domain Manager appears 3 If you have already created a domain select your domain in the Domain Manager and click the Start an Existing Domain button Once your domain is started a green check mark indicates that the domain is running Your domain will continue to run unless you shut it down 4 If there are no existing domains a dialog box indicates that you can create a domain now Click Yes The Create Domain dialog box a
72. cted Procedure See Figure 20 Figure 20 Procedure Parameters Procedure Parameters For RP_DBEMPLOYEE Parameters CHAR DECIMAL 1G 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 JDBC ODBC eWay Adapter User s Guide 50 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD 7 Click Add to add the type of Resultset node you would like to generate Figure 21 Edit Resultset ResultSets For RP DBEMPLOYEE ResuttSets z Add J Remove By Executing NA PEZEN With Assistance Index Original Name Column Name Type Add Column Remove Column Save Ce 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
73. 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 getPreparedStatementTest addBatch getPrepStatement getPreparedStatementTest setAge 45 getPrepStatement getPreparedStatementTest setName Harrison Ford getPrepStatement getPreparedStatementTest setDeptNo 7 getPrepStatement getPreparedStatementTest addBatch getPrepStatement getPreparedStatementTest executeBatch JDBC ODBC eWay Adapter User s Guide 68 Sun Microsytems Inc Chapter 6 Implementing the JOBC ODBC eWay Sample Projects This chapter provides an introduction to the JOBC 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 It is assumed that you understand the basics of creating a Project using the Enterprise Designer For more information on creating an eGate Project see the eGate Tutorial and the eGate
74. ding and Deploying the prjJDBC_JCD Sample Project Figure 34 jcdTableSelect Business Rules Q a jedTableSelect Q receive lt gt Copy Selecting record s from db_employee table via table select to FileClient_1 Text lt gt FileClient_4 write lt gt otdJDBC_1 DB_EMPLOYEE select input Text KT while condition otdJDBC_1 0B_EMPLOYEE next rules lt gt Copy shortToString otdJDBC_1 DB_EMPLOYEE EMP_NO to otdOutputD TD_DBemployee_1 EmpNo lt gt Copy otdJDBC_1 DB_EMPLOYEE LAST_NAME to otdOutputD TD_DBemployee_1 Lastname lt gt Copy otdJDBC_1 DB_EMPLOYEE FIRST_NAME to otdOutputD TD_DBemployee_1 Firstname lt gt Copy otdJDBC_1 DB_EMPLOYEE RATE toString to otdOutputD TD_DBemployee_1 Rate lt gt Copy date ToString otdJDBC_1 DB_EMPLOYEE LAST_UPDATE to otdOutputD TD_DBemployee_1 LastDate lt gt Copy otdOutputDTD_DBemployee_1 marshalToString to FileClient_1 Text L lt gt FileClient_1 write lt gt Copy Done table select to FileClient_1 Text L lt gt FileClient_1 write E logger E alerter E collabContext E typeConverter 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 publ
75. e 3 From the bpelDelete Binding dialog box map JDBC_otdJDBC under Invoked Services to the eaJDBCOUT External Application 4 From the bpelDelete Binding dialog box map FileReceiver to the FileClientOUT External Application as seen in Figure 59 Figure 59 Connectivity Map Associating Binding the Project s Components 6 gt fo FileClientOut FileCliertin FileClientIn FileClientIn_clnDelete_bpDelete bpel1 cmDelete_bpDelete bpelt cmDelete_bpDelete bpelt _FileClientOut He P cmDelete_bpDelete bpel1 Rule bpelDelete Implemented Services Invoked Services gFileSender FileSender amp FileRe FileRe ges fh BUDBC_ IDBC_ cmDelete_bpDelete bpelt cmDelete_bpDelete bpel1_eaJDBCOut eaJDBCOut JDBC ODBC eWay Adapter User s Guide 108 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project 5 Minimize the bpelDelete Binding dialog box by clicking the chevrons in the upper right corner 6 Save your current changes to the Repository and then repeat this process for each of the other Connectivity Maps 665 Creating an Environment Environments include the external systems Logical Hosts Integration Servers and message servers used by a Project and contain the configuration information for these components Environments are created using the Enterprise Designer s Environment Editor Steps required to
76. e JDBC ODBC eWay Connection Retry Settings JDBC Connector Settings Details for the JDBC Connector Settings used by the external database are detailed in Table 11 Section 3 5 eWay Environment Properties 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 ClassName Specifies the Java class in the JDBC A valid class name driver that is used to implement the ConnectionPoolDataSource or XADataSource interface Change as needed for your driver For example com attunity jdbc NvXADataSource ClassNamefor OtherInterfaces For Sun Java Composite Application Platform Suite components that do not use ConnectionPoolDataSource use this entry to enter the name of the driver class This class will not be used by the eWay A valid class name ServerName This setting specifies the host name Any valid string of the external database server PortNumber Specifies the I O port number on A valid port number which the server is listening for connection requests DatabaseName Specifies the name of the database Any valid string instance User Specifies the user name the eWay Any valid string uses to connect to the database Password Specifies the password used to Any valid string access the database DriverProperties The DataSource implementation
77. e represented as nodes between the External Applications and the Business Process See Figure 38 JDBC ODBC eWay Adapter User s Guide 89 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project You must configure eWay properties in both the Connectivity Map and the Environment Explorer Figure 38 eWays in the cmDelete Connectivity Map eaJDBCOUT eWay FileClientIN eWay D s Eb FileClientIN FileCliertOUT cmDelete_jcdDelete1 FileClientOUT eWay 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 15 Click OK to close the Properties Editor Table 15 FileClientIN eWay Property Settings Connectivity Map Property Name Required Value cmDelete Input file name TriggerDelete in cmInsert Input file name TriggerInsert in cmPsSelect Input file name TriggerPsSelect in cmTableSelect Input file name TriggerTableSelect in cmUpdate Input file name TriggerUpdate in 2 Double click the FileClientOUT eWay on each of the Connectivity Maps and modify the properties for your system as seen in Table 16 Click OK to close the Properties Editor Table 16 FileClientOUT eWay Property Settings Connectivity Map Property Name Required Val
78. e the select method with the where clause specified if necessary 2 Loop through the ResultSet using the next method 3 Process the return record within a while loop For example 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 stc connector appconn file FileTextMessage input dtd otdInputDTD_1394195520 DBemployees otdInputDTD_DBemployees_1 otdJDBC OtdJDBCOTD otdJDBC_1 dtd otdOutputDTD882991309 DBemployee otdOutputDTD_DBemployee_1 com stc connector appconn file FileApplication FileClient_1 throws Throwable JDBC ODBC eWay Adapter User s Guide 60 Sun Microsytems Inc Chapter 5 Section 5 2 Using JDBC ODBC Operations JDBC eWay Database Operations JCD FileClient_1 setText Selecting record s from db_employee table via table select FileClient_1l write otdJDBC_1 getDB_EMPLOYEE select input getText while otdJDBC_1 getDB_EMPLOYEE next otdOutputDTD_DBemployee_1 setEmpNo ty
79. ection 3 3 Setting Properties of the JOBC ODBC eWay Configuring the eWay Environment Properties 33 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 Available External System properties include Inbound JDBC eWay Outbound JDBC eWay Outbound JDBC non Transactional eWay Outbound JDBC XA eWay To Configure the Environment Properties 1 In Enterprise Explorer click the Environment Explorer tab 2 Expand the Environment created for the JDBC Project and locate the JDBC External System Note For more information on creating an Environment see the Sun SeeBeyond eGate Integrator Tutorial 3 Right click the External System created for the JDBC Project and select Properties from the list box The Environment Configuration Properties window appears JDBC ODBC eWay Adapter User s Guide 29 Sun Microsystems Inc Chapter 3 Section 3 4 Setting Properties of the JDBC ODBC eWay eWay Connectivity Map Properties Figure 9 JDBC eWay Environment Configuration Configuration JAg A m O O Inbound JDBC eWay L a Parameter Settings Description JDBC DriverManager Outbound JDBC eWay ClassName O JDBC Connector settings URL LO Connection Retry Settings O Outbound JDBC
80. er vendor s instruction For example setDeffdpName DBSQL setWorkspa ce Navigator Delimiter This is the delimiter character to be The default is used in the DriverProperties prompt DataSource Specifies the name of the The name of the XADataSource or name XADataSource or ConnectionPoolDataSource ConnectionPoolDataSource implementation This property is implementation to which the Optional In most cases leave this box DataSource object delegates behind empty the scenes when there is connection pooling or distributed transaction management being done MinPoolSize Specifies the maximum number of A valid numeric value The default is 0 physical connections the pool should keep available at all times 0 zero indicates that there is no maximum The pool size you set depends on the transaction volume and response time of the application If the pool size is too big you may end up with too many connections with the database JDBC ODBC eWay Adapter User s Guide 36 Sun Microsystems Inc Chapter 3 Section 3 5 Setting Properties of the JDBC ODBC eWay eWay Environment Properties Table 9 Outbound non Transactional eWay JDBC Connector Settings Continued Name Description Required Value MaxPoolSize Specifies the maximum number of A valid numeric value The default is physical connections the pool 10 should keep available at all times 0 zero indicates that there is no maximum If the poo
81. ere updateRow delete String sWhere deleteRow select String where The ability to perform any of the above methods using a table OTD may not be possible with all third party drivers You have to use a Prepared Statement to perform such an JDBC ODBC eWay Adapter User s Guide 59 Sun Microsytems Inc Chapter 5 Section 5 2 Using JDBC ODBC Operations 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 This allows you to perform Query Update Insert and Delete SQL operations in a table The ability to update via a ResultSet is called Updatable ResultSet which is a feature supported by this eWay By default the Table OTD has UpdatableConcurrency and ScrollTypeForwardOnly Normally you do not have to change the default setting The type of result returned by the select method can be specified using SetConcurrencytoUpdatable SetConcurrencytoReadOnly SetScrollTypetoForwardOnly SetScrollTypetoScrollSensitive SetScrollTypetolnsensitive The Query Select Operation To perform a query operation on a table 1 Execut
82. erprise Designer s Connectivity Map double click the JDBC eWay icon The eWay Connections window appears Figure 5 Connectivity Map with Components JDBC eWay Le gt mj JDBC1 fe 2 File1 CMap1_Service 6 p a File2 2 Select a parameter from the list and click OK Figure 6 Template window Choose an eWay connection Outbound JDBC non Transactional eWay a Outbound JDBC XA eWay Outbound JDBC eray The choices to make are as follows Outbound JDBC non Transactional eWay Also referred to as NoTransaction this support level indicates that the Collaboration does not support transactions This means that when a transaction aborts there is no ability to roll back any changes to the previous update Outbound JDBC XA eWay Also referred to as XATransaction this support level allows two phase commit This means that the transaction when aborted will roll back all changes when one of the updates fails The update could occur in the database eWay or other eWays that support XA Additionally the Collaboration can contain only the database eWay or a combination of database eWay and other eWays that support XA JDBC ODBC eWay Adapter User s Guide 26 Sun Microsystems Inc Chapter 3 Section 3 2 Setting Properties of the JOBC ODBC eWay 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 t
83. erprise Manager to target specific alert codes for each eWay type as well as start and stop the inbound eWays The Sun Java Composite Application Platform Suite Installation Guide describes how to install Enterprise Manager The Sun SeeBeyond eGate Integrator System Administration Guide describes how to monitor servers Services logs and alerts using the Enterprise Manager and the command line client The eWay Enterprise Manager Plug ins are available from the List of Components to Download under the Sun Java Composite Application Platform Suite Installer s Downloads tab There are two ways to add eWay Enterprise Manager plug ins From the Sun SeeBeyond Enterprise Manager From the Sun Java Composite Application Platform Suite Installer To add plug ins from the Enterprise Manager 1 From the Enterprise Manager s Explorer toolbar click configuration 2 Click the Web Applications Manager tab go to the Auto Install from Repository sub tab and connect to your Repository 3 Select the application plug ins you require and click Install The application plug ins are installed and deployed JDBC ODBC eWay Adapter User s Guide 21 Sun Microsystems Inc Chapter 2 Section 2 3 Installing the JDBC ODBC eWay 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
84. es The Outbound JDBC eWay includes the following configuration sections JDBC Connector Settings Connection Retry Settings JDBC ODBC eWay Adapter User s Guide 32 Sun Microsystems Inc Chapter 3 Setting Properties of the JDBC ODBC eWay JDBC Connector Settings Details for the JDBC Connector Settings used by the external database are detailed in Section 3 5 eWay Environment Properties 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 ClassName Specifies the Java class in the JDBC A valid class name driver that is used to implement the ConnectionPoolDataSource XADataSource interface Change as needed for your driver For example com ibm as400 access AS400JDBCCon nectionPoolDataSource See JDOBC ODBC Drivers on page 117 for some of the popular drivers you can use with this eWay ClassNamefor OtherInterfaces For Sun Java Composite Application Platform Suite components such as eTL that do not use ConnectionPoolDataSource use this entry to enter the name of the driver class This entry is not used by the JDBC eWay A valid class name ServerName This setting specifies the host name Any valid string of the external database server PortNumber Specifies the I O port number on A valid port number which the server
85. fic eWay Adapters for Oracle SQL Server Sybase DB2 and so forth to peform such operations JDBC stored procedures do not return records as ResultSets Instead the records are returned through output reference cursor parameters Reference Cursor parameters are essentially ResultSets The results Available method added to the PreparedStatementAgent class simplifies the whole process of determining whether any results be it Update Counts or ResultSets are available after a stored procedure has been executed Although JDBC provides three methods getMoreResults getUpdateCount and getResultSet to access the results of a stored procedure call the information returned from these methods can be quite confusing to the inexperienced Java JDBC programmer and they also differ between vendors You can simply call resultsAvailable and if Boolean true is returned you can expect either a valid Update Count when getUpdateCount is called and or the next ResultSet has been retrieved and made available to one of the ResultSet nodes defined for the Stored Procedure OTD when that node s availableQ method returns true Frequently Update Counts information that is returned from a Stored Procedures is insignificant You should process returned ResultSet information and avoid looping through all of the Update Counts The following three methods control exactly what information should be returned from a stored procedure call The enableResultSets
86. for input This permits you to modify the query to select a specific record Also note that all records are selected from the database when the TriggerDelete in file is empty JDBC ODBC eWay Adapter User s Guide 102 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 gt 20 u gt A FileClient write FileClient write otdJDBC End DB_EMPLOYEED FileClient receive elete Steps required to configure the bpelDelete business process 1 Configure the business rule between the FileClient receive and FileClient write Activities as seen in Figure 50 Figure 50 bpelDelete Business Rule 1 Business Rule Designer ob 3 A x Conversion Datetime Operator Boolean String Nodes Number XSDOperation ont E Business Process Attributes Business Process Attributes Ge FileClient receive Qutput Wa FileClientwrite Input E A string literal tet byteArray encoding Delete record e C gt 2 Configure the business rule between the FileClient write Activity and otdJDBC DB_EMPLOYEEDelete Activity as seen in Figure 51 Figure 51
87. g or distributed transaction management being done MinPoolSize Specifies the minimum number of A valid numeric value The default is 0 physical connections the pool should keep available at all times 0 zero indicates that there is no maximum The pool size you set depends on the transaction volume and response time of the application If the pool size is too big you may end up with too many connections with the database MaxPoolSize Specifies the maximum number of physical connections the pool should keep available at all times 0 zero indicates that there is no maximum If the pool size is too big you may end up with too many connections with the database The pool size depends on the transaction volume and response time A valid numeric value The default is 10 MaxldleTime Specifies the maximum number of seconds that a physical connection may remain unused before it is closed 0 zero indicates that there is no limit A valid numeric value The default is 0 Connection Retry Settings Details for the Connection Retry Settings used by the external database are detailed in Table 8 Table 8 Outbound JDBC eWay Connection Retry Settings Name Description Required Value ConnectionRetries to acquire one Specifies the number of retries to establish a connection upon failure A valid numeric value The default is 0 JDBC ODBC eWay Adapter User s Guide 34
88. getDbEmployee available while getSPIn getSpS_multi getDbEmployee next System err printin EMPNO getSPIn getSpS_multi getDbEmployee getEMPNO System err printin ENAME 7 getSPIn getSpS_multi getDbEmployee getENAME System err printin JOB getSPIn getSpS_multi getDbEmployee getJOB System err printin MGR getSPiIn getSpS_multi getDbEmployee getMGR System err printin HIREDATE 4 getSPIn getSpS_multi getDbEmployee getHIREDATE System err printin SAL getSPIn getSpS_multi getDbEmployee getSAL System err println COMM getSPIn getSpS_multi getDbEmployee getCOMM System err println DEPTNO getSPIn getSpS_multi getDbEmployee getDEPTNO System err printlin System err printin Note resultsAvailable and available cannot be indiscriminately called because each time they move ResultSet pointers to the appropriate locations After calling resultsAvailable the next result if available can be either a ResultSet or an UpdateCount if the default enableResultSets AndUpdateCount was used Because of limitations imposed by some DBMSs it is recommended that for maximum portability all of the results in a ResultSet object should be retrieved before
89. gin 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 DataSourceName MinPoolSize The default is 0 MaxPoolSize JDBC ODBC eWay Adapter User s Guide The default is 10 127 Sun Microsystems Inc Appendix A Section A 8 JDBC ODBC Drivers Teradata Driver Table 37 MS Access ODBC Driver Environment Properties Continued Parameter Value MaxldleTime The default is 0 Note Itis not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sequelink DataDirect MS Access ODBC as Teradata Driver Configuration Properties OTD Wizard Database Connection Information on page 128 Environment Properties on page 128 a81 OTD Wizard Database Connection Information To connect to Teradata via the Teradata driver use the information provided in Table 38 to complete the Connect to Database step of the JOBC ODBC OTD Wizard Table 38 Teradata Driver Database Connection Information Parameter Value Driver Jar Files teradata jar Driver Java Class Name com ncr teradata TeraDriver URL Connection String jdbc teradata lt server name gt lt server port gt lt database server name gt Note NOTE Default server port is 6666 for the Type 3 driver Gateway User Name Login name of the account used
90. he JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project 664 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 gt 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 three additional Connectivity Maps CMap2 CMap3 and CMap4 and rename them as follows cmDelete cmPsInsert cmTableSelect cmUpdate The icons in the toolbar represent the available components used to populate the Connectivity Map canvas Populating the Connectivity Map Add the Project components to the Connectivity Map by dragging the icons from the toolbar to the canvas Each Connectivity Map in the prjJDBC_BPEL sample Project requires the following components File External Application 2 JDBC External Application Business Process Any eWay added to the Connectivity Map is associated with an External System To establish a connection to JDBC first select JDBC as an External System to use in your Connectivity Map To Select a JDBC External System 1 Click the External Application icon on the Connectivity Map toolbar 2 Select the external systems necessary
91. he 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 66 Building and Deploying the prjJDBC_BPEL Sample Project The following provides step by step instructions for manually creating the prjJDBC_BPEL sample Project Steps required to create the sample project include Creating a Project on page 95 Creating the OTDs on page 96 Creating the Business Process on page 97 Creating the Connectivity Map on page 107 Creating an Environment on page 109 Configuring the eWays on page 110 Creating the Deployment Profile on page 113 Creating and Starting the Domain on page 114 Building and Deploying the Project on page 115 Running the Sample Project on page 115 66 1 Creating a Project The first step is to create a new Project in the Enterprise Designer 1 Start the Enterprise Designer JDBC ODBC eWay Adapter User s Guide 95 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 662 Creating the OTDs The sample Project requires three OTDs to interact with the JOBC eWay
92. hem before saving the OTD JDBC ODBC eWay Adapter User s Guide 130 Sun Microsystems Inc Index Index A activity input and output 57 Add Prepared Statements 52 alert codes viewing 22 AS 400 Driver 118 Attunity Driver 119 Automap 93 113 B binding dialog box 88 109 BPEL operations 57 Delete 57 Insert 57 ReceiveOne 57 SelectAll 57 SelectMultiple 57 SelectOne 57 Update 57 C Collaboration editor 78 configuring JDBC eWay 25 connection retry settings outbound 34 37 39 Connectivity Map Inbound JDBC eWay Properties 32 Outbound JDBC eWay Properties 32 35 37 conventions text 15 D database operations BPEL 57 activity input and output 57 Delete 57 Insert 57 ReceiveOne 57 SelectAll 57 SelectMultiple 57 SelectOne 57 Update 57 database operations JCD 59 JDBC ODBC eWay Adapter User s Guide 131 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 Sequelink MS Access ODBC 126 Sybase JConnect 124 Teradata 128 E eWay Connectivity Map 25
93. ic void receive com stc connector appconn file FileTextMessag input dtd otdInputDTD_1394195520 DBemployees otdInputDTD_DBemployees_1 otdJDBC OtdJDBCOTD otdJDBC_1 dtd otdOutputDTD882991309 DBemployee otdOutputDTD_DBemployee_1 com stc connector appconn file FileApplication FileClient_1 throws Throwable Writes out a message stating records are being selected FileClient_1 setText Selecting record s from db_employee table via table select FileClient_1 write Copies the database resultset into the otdInputDTD_DBEmployee XML OTD and selects all available records from the database that meet a certain criteria Checking the next method ensures all rows are retrieved in the while loop 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 getLAST_NAME otdOutputDTD_DBemployee_1 setFirstname otdJDBC_1 getDB_EMPLOYEE getFIRST_NAME T JDBC ODBC eWay Adapter User s Guide 86 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay S
94. ing 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 ODBC binary code and in many cases database client code must be loaded on each client machine that uses a JDBC ODBC Bridge A product called SequeLink from Data Direct Technologies provides a driver that supports some ODBC drivers for example Microsoft Access Type one drivers provide JDBC access via one or more Open Database Connectivity ODBC drivers ODBC which predates JDBC is widely used by developers to connect to databases in a non Java environment Pros A good approach for learning JDBC May be useful for companies that already have ODBC drivers installed on each client machine typically the case for Windows based machines running productivity applications May be the only way to gain access to some low end desktop databases Cons Not for large scale applications Performance suffers because there s some overhead associated with the translation work to go from JDBC to ODBC Doesn t support all the features of Java User is limited by the functionality of the underlying ODBC driver Type One Driver A JDBC ODBC bridge provides JDBC API access through one or more ODBC drivers Some ODBC native code and in ma
95. ional eWay Properties The Outbound non Transactional eWay Properties include outbound parameters used by the external database Table 4 Outbound non Transactional eWay JDBC Connector Settings Name Description Required Value Description The description of the database Any valid string 34 3 Connectivity Map Outbound XA eWay Properties The Outbound XA eWay Properties include outbound parameters used by the external database Table 5 Outbound XA eWay JDBC Connector Settings Name Description Required Value Description The description of the database Any valid string 35 eWay Environment Properties eWay External System properties must be configured from within the Environment Until you have successfully configured all eWays for your Java CAPS project your project cannot be properly executed The following list identifies the JOBC eWay properties There are four eWay connection types that the JOBC ODBC eWay implements Property Categories Configured in the Logical Host Environment Inbound JDBC eWay Properties on page 32 JDBC ODBC eWay Adapter User s Guide 31 Sun Microsystems Inc Chapter 3 Section 3 5 Setting Properties of the JDBC ODBC eWay 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 35 1 Inbound JDBC eWay Properties Before deploying your eWay
96. ired 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 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 13 Select Tables Views Aliases Select TablesMiews Aliases and specify Column informat Select Wizard Type Selected Tablesiviews Aliases Connect to Database Select Database Objects Select TablesMiews Aliases Select Procedures Add Prepared Statements Specify the OTD Name eview Selections Add Remove Description 0 columns selected Change O use fully qualified table view names in the generated Java code lt Back Next gt Finish Cancel Help 2 Inthe 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 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 14 Database Wizard All Schemes Tablefview Search Type the exact Table View Name or use Wildcard characters Tables Only Views Only Both J Include system tables Tables Views Aliases Selection Results 57 Records S400L
97. is listening for connection requests DatabaseName Specifies the name of the database Any valid string instance User Specifies the user name the eWay Any valid string uses to connect to the database Password Specifies the password used to Any valid string access the database DriverProperties The Connection Pool DataSource implementation may need to execute additional methods to assure a successful run The additional methods will need to be identified in the Driver Properties You must ensure that the driver is installed on both the Logical Host machine and the Enterprise Designer machine Set the driver properties according to driver vendor s instruction For example setDeffdpName DBSQL setWorkspa ce Navigator Delimiter This is the delimiter character to be The default is used in the DriverProperties prompt JDBC ODBC eWay Adapter User s Guide 33 Sun Microsystems Inc 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 Specifies the name of the The name of the XADataSource or name XADataSource or ConnectionPoolDataSource ConnectionPoolDataSource implementation This property is implementation to which the Optional In most cases leave this box DataSource object delegates behind empty the scenes when there is connection poolin
98. itor Table 21 FileClientOUT eWay Property Settings Connectivity Map Property Name Required Value cmDelete Output file name BPEL_Delete_output d dat cmInsert Output file name BPEL_Insert_output d dat cmTableSelect Output file name BPEL_TableSelect_output d dat cmUpdate Output file name BPEL_Update_output d dat Configuring the Environment Explorer Properties Steps required to configure the Environment Explorer properties 1 From the Environment Explorer tree right click the JDBC External System esJDBC in this sample and select Properties The Properties Editor opens to the JDBC eWay Environment configuration 2 Modify the JDBC eWay Environment configuration properties for your system as seen in Table 22 and click OK JDBC ODBC eWay Adapter User s Guide 111 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Table 22 JDBC eWay Environment Properties Section Property Name Required Value Configuration gt Inbound ServerName Enter the host name of the JDBC eWay gt JDBC database server being used Connector settings DatabaseName Enter the name of the particular database that is being used on the server User Enter the user account name for the database Password Enter the user account password for the database 3 From the Environment Explorer tree right click
99. ject 1 Rename one of the trigger files included in the sample Project from lt filename gt in in to lt filename gt in to run the corresponding operation The File eWay polls the directory every five seconds for the input file name as defined in the Inbound File eWay Properties window The Business Process then transforms the 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 TriggerTableSelect in TriggerDelete in TriggerBpUpdate in Having no content in these files causes the operation to read all records JDBC ODBC eWay Adapter User s Guide 115 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 JDBC ODBC eWay Adapter User s Guide 116 Sun Microsystems Inc Appendix A JDB
100. ject 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 Rename the new Environment to envJDBCProj 4 Right click envJDBCProj and select New gt JDBC External System Name the External System esJDBC Click OK esJDBC is added to the Environment Editor 5 Right click envJDBCProj and select New gt File External System Name the External System esFileClient Click OK esFileClient is added to the Environment Editor 6 Right click envJDBCProj and select New gt Logical Host The LogicalHost1 box is added to the Environment and LogicalHost1 is added to the Environment Editor tree 7 Right click LogicalHost1 and select New gt Sun SeeBeyond Integration Server A new Integration Server IntegrationSvr1 is added to the Environment Explorer tree under LogicalHost1 See Figure 37 Figure 37 Environment Editor envJDBCProj BB enwDBCproj LogicalHost1 SN IntegrationSvr1 ET e sBsmsiomgrt esJDBCOut E e esFileClient E IntegrationSyr1 L E SBJMSIQMgr1 8 Save your current changes to the Repository 658 Configuring the eWays eWays facilitate communication and movement of data between the external applications and the eGate system Each Connectivity Map in the prjJDBC_JCD sample Project uses three eWays that ar
101. l size is too big you may end up with too many connections with the database The pool size depends on the transaction volume and response time MaxldleTime Specifies the maximum number of A valid numeric value The default is 0 seconds that a physical connection may remain unused before it is closed 0 zero indicates that there is no limit Connection Retry Settings Details for the Connection Retry Settings used by the external database are detailed in Table 10 Table 10 Outbound non Transactional eWay Connection Retry Settings Name Description Required Value ConnectionRetries Specifies the number of retries to A valid numeric value The establish a connection upon failure default is 0 to acquire one ConnectionRetryInterval Specifies the milliseconds of pause A valid numeric value The before each attempt to reaccess the default is 1000 database This setting is used in conjunction with the Connection Retries setting For example In the event that the eWay cannot connect to the Database the eWay will try to reconnect to the database 10 times in 5 seconds apart when the Connection Retries is 10 and the Connection Retry Interval is 5000 35 4 Outbound XA JDBC eWay Properties The Outbound XA JDBC eWay includes the following configuration sections JDBC Connector Settings JDBC ODBC eWay Adapter User s Guide 37 Sun Microsystems Inc Chapter 3 Setting Properties of th
102. lick Edit Resultset Columns The ResultSet Columns window appears See Figure 25 Figure 25 ResultSet Columns ResultSet Columns For PreparedStatementt ResultSet Columns emp_no INTEGER last_name VARCHAR first_name VARCHAR last_update 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 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD 42 7 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 Specify the OTD Name Select Wizard Type Connect to Database OTD Name Select Database Objects Select Tables Views Aliases Select Procedures Add Prepared Statements Specify the OTD Name eview Selections Back Next gt Finish Cancel Help 2 Click Next 428 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 gener
103. m OTD UD OTD from Uses a text file to create a custom OTD VSAM Databa Uses a VSAM database to create an OTD xSD Uses an XSD to create an OTD WebLogic Ap Uses a WebLogic AppServer EJB to create an Finish f Cancel Help 422 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 JDBC ODBC eWay Adapter User s Guide 42 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JOBC ODBC eWay Database Wizard 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 Figure 11 Database Connection Information Specify Database Connection Information Select Wizard Type Connect to Database Please enter the JDBC database connection information below Select Database Objects Select Connection Information TablesViews Aliases Select Procedures Driver Jar Files JDBCDrivers AS400 topen_4_ 7 Vib yt400 jar Add Prepared Statements Driver Java Class Name com ibm as400 access AS400JDBCDriver
104. nment repository td Importing selected projects Importing 0 environments Project Exclude J priJDBC_JCD NIA j l Import C cise _ 3 Browse to the directory that contains the sample Project ZIP file Select the sample file and click Import Click Close after successfully importing the sample Project 65 Building and Deploying the prjJDBC_JCD Sample Project This section provides step by step instructions for manually creating the prjJDBC_JCD sample Project Steps required to create the sample project Creating a Project on page 75 Creating the OTDs on page 75 Creating a Connectivity Map on page 76 JDBC ODBC eWay Adapter User s Guide 74 Sun Microsystems Inc ip eii the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Tna Creating the Collaboration Definitions Java on page 78 Create the Collaboration Business Rules on page 81 Binding the eWay Components on page 87 Creating an Environment on page 88 Configuring the eWays on page 89 Creating the Deployment Profile on page 92 Creating and Starting the Domain on page 93 Building and Deploying the Project on page 94 Running the Sample on page 94 65 1 Creating a Project The first step is to create a new Project in the Enterprise Designer 1 Start the Enterprise Designer 2 From the Project Explorer tree right click the Repository and select New Project A new Project Project1 appears on
105. ns with the database MaxPoolSize Specifies the maximum number of A valid numeric value The default is physical connections the pool 10 should keep available at all times 0 zero indicates that there is no maximum If the pool size is too big you may end up with too many connections with the database The pool size depends on the transaction volume and response time MaxldleTime Specifies the maximum number of A valid numeric value The default is 0 seconds that a physical connection may remain unused before it is closed 0 zero indicates that there is no limit Connection Retry Settings Details for the Connection Retry Settings used by the external database are detailed in Table 12 Table 12 Outbound XA JDBC eWay Connection Retry Settings Name Description Required Value ConnectionRetries Specifies the number of retries to A valid numeric value The establish a connection upon failure default is 0 to acquire one JDBC ODBC eWay Adapter User s Guide 39 Sun Microsystems Inc 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 Description Required Value ConnectionRetryInterval Specifies the milliseconds of pause before each attempt to reaccess the database This setting is used in conjunction with the Connection Retries setting
106. nsert_Ps executeUpdate Writes a message to confirm the inserted records FileClient_1 setText Done Insert FileClient_1l write Creating the jcdPsSelect Business Rules The jcdPsSelect Collaboration implements the Input Web Service Operation to read the TriggerPsSelect in file It then copies the database resultset as noted in the prepared statement query into the otdInputDTD_DBEmployee OTD and selects all available records from the database The Collaboration also writes a message to JCD_PsSelect_output0 dat to confirm when records are selected or when no records are available The jedPsSelect Collaboration contains the Business Rules displayed in Figure 33 JDBC ODBC eWay Adapter User s Guide 83 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project Figure 33 jcdPsSelect Business Rules G a jedPsSelect P receive lt gt Copy Selecting record s from db_employee table via Prepared Statement select to FileClient_1 Text lt gt FileClient_1 write lt gt Copy Short parseShont 0 to otdJDBC_1 Select_ps Emp_no lt gt otdJDBC_1 Select_ps executeQuery o amp It condition otdJDBC_1 Select_ps resultsAvailable O then wT while condition otdJDBC_1 Select_ps Select_psResults next tules lt gt Copy Integer toString otdJDBC_1 Select_ps Select_psResults EMP_NO t
107. nt Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com ibm as400 access AS400JDBCConnectionPoolDataSource ClassNamefor OtherlInterfaces ServerName Server name of the machine hosting the database PortNumber lt server port gt Note NOTE Default server port is 446 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 DataSourceName MinPoolSize The default is 0 MaxPoolSize The default is 10 MaxlIdleTime The default is 0 a2 Attunity Driver Configuration Properties OTD Wizard Database Connection Information on page 119 Environment Properties on page 120 a21 OTD Wizard Database Connection Information To connect to Attunity use the information provided in Table 26 to complete the Connect to Database step of the JOBC ODBC OTD Wizard JDBC ODBC eWay Adapter User s Guide 119 Sun Microsystems Inc Appendix A JDBC ODBC Drivers Table 26 Attunity Driver Database Connection Information Section A 2 Attunity Driver Parameter Value Driver Jar Files nvjdbc2 jar Driver Java Class Name com attunity jdbc NvDriver URL Connection String jdbc attconnect lt server name gt DefTdpName lt database logical name gt OneTdpMode 1 Note The lt data
108. ny cases native database client code must be loaded on each client machine that uses this type of driver Figure 1 Typical Type 1 Driver Configuration Connection JDBC ODBC eWay Adapter User s Guide 8 Sun Microsystems Inc Chapter 1 Section 1 1 Introducing the JDBC ODBC eWay 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 The results are then sent back through the reverse process Limited Java feature set May not be suitable for a large scale application Type II Partial Java driver This type of driver converts JDBC calls into calls on the client API for Oracle Sybase Informix DB2 or other DBMS Note that like the bridge driver this style of driver requires that some binary code be loaded on each client machine This type of driver converts the calls that a developer writes to the JDBC application programming interface into calls that connect to the client machine s application programming interface for a specific database such as IBM Informix Oracle or Sybase Pros Performance is better than that of Type 1 in part because the Type 2 driver contains compiled code that s optimized for the back end database se
109. o create your Project for this sample JDBC and File Icons representing the selected external systems are added to the Connectivity Map toolbar 3 Rename the following components and then save changes to the Repository File1 to FileClientIN File2 to FileClientOUT JDBC1 to esJDBCOUT 4 Rename each Connectivity Map Service to match the intended operation as for example jcdDelete JDBC ODBC eWay Adapter User s Guide 77 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project jcdInsert jcdPsSelect jcdTableSelect jcdPsUpdate 654 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 Once you create the Collaboration Definitions you can write the Business Rules of the Collaborations using the Collaboration Editor JCDs required for the prjJDBC_JCD sample include jcdDelete jcdInsert jcdPsSelect jcdTableSelect jcdPsUpdate jcdDelete Collaboration Steps required to create the jcdDelete Collaboration 1 From the Project Explorer right click the sample Project and select New gt Collaboration Definition Java from the shortcut menu The Collaboration Definition Wizard Java appe
110. o otdOutputD TD_DBemployee_1 EmpNo lt gt Copy otdJDBC_1 Select_ps Select_psResults LAST_NAME to otdOutputD TD_DBemployee_1 Lastname lt gt Copy otdJDBC_1 Select_ps Select_psResults FIRST_NAME to otdOutputDTD_DBemployee_1 Firstname lt gt Copy otdJDBC_1 Select_ps Select_psResults RATE toString to otdOutputD TD_DBemployee_1 Rate lt gt Copy otdJDBC_1 Select_ps Select_psResults LAST_UPDATE toString to otdOutputD TD_DBemployee_1 LastDate lt gt Copy otdOutputD TD_DBemployee_1 marshalToString to FileClient_1 T ext lt gt FileClient_1 write else lt gt Copy No record found to FileClient_1 T ext lt gt FileClient_1 write lt gt Copy Done Select to FileClient_1 Text L lt gt FileClient_4 write E logger alerter E collabContext E typeConverter 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 connector appconn file FileTextMessage input otdJDBC OtdJDBCOTD otdJDBC_1 dtd otdOutputDTD882991309 DBemployee otdOutputDTD_DBemployee_1 com stc connector appconn file FileApplication FileClient_1 throws Throwable Writes out a message stating record
111. oject 4 For Step 3 of the wizard from the Select OTDs selection window double click ptjJDBC_JCD gt otdALL gt 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 gt eWays gt File gt FileClient The Selected OTDs field now lists the FileClient OTD 6 Click Finish The Collaboration Editor with the new jcdPsUpdate Collaboration appears in the right pane of the Enterprise Designer 655 Create the Collaboration Business Rules The next step in the sample is to create the Business Rules of the Collaboration using the Collaboration Editor Creating the jcdDelete Business Rules The jcdDelete Collaboration implements the Input Web Service Operation to read the TriggerDelete in file and then delete a record The Collaboration also writes a message to JCD_Delete_output0 dat to confirm a deleted record Note The where clause in the business rule reads the trigger value as a placeholder for input This permits you to modify the query to select a specific record Also note that all records are deleted from the database when the TriggerDelete in file is empty The jedDelete Collaboration contains the Business Rules displayed in Figure 31 Figure 31 jcdDelete Business Rules O ae jedDelete P receive i Copy Delete record to FileClient_1 Text lt gt FileClient_1 write L lt gt otdJDBC_1 DB_EMPLOYEE deletefinput Te
112. om the Select OTDs selection window double click ptjJDBC_JCD gt otdALL gt otdJDBC The otdJDBC OTD is added to the Selected OTDs field 5 In the same window double click otdOutputDTD_DBemployee The otdOutputDTD_DBemployee OTD is added to the Selected OTDs field Note The otdOutputDTD_DBemployee OTD is created from the otdOutputDTD dtd that is included in the Sample Project 6 Click the Up One Level button twice to return to the Repository Double click Sun SeeBeyond gt eWays gt File gt FileClient The Selected OTDs field now lists the FileClient OTD 7 Click Finish The Collaboration Editor with the new jcdTableSelect Collaboration appears in the right pane of the Enterprise Designer jcdPsUpdate Collaboration Steps required to create the jcdPsUpdate Collaboration 1 From the Project Explorer right click the sample Project and select New gt Collaboration Definition Java from the shortcut menu The Collaboration Definition Wizard Java appears 2 Enter a Collaboration Definition name for this sample jcdPsUpdate and click Next 3 For Step 2 of the wizard from the Web Services Interfaces selection window double click Sun SeeBeyond gt eWays gt File gt FileClient gt receive The File Name field now displays receive Click Next JDBC ODBC eWay Adapter User s Guide 80 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Pr
113. onnection Information on page 127 Environment Properties on page 127 JDBC ODBC eWay Adapter User s Guide 126 Sun Microsystems Inc Section A 7 Appendix A Sequelink DataDirect MS Access ODBC Driver JDBC ODBC Drivers A71 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 SequeLinkDriver URL Connection String jdbc sequelink lt server name gt lt server port gt Note NOTE Default server port is 19996 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 a72 Environment Properties Use Table 37 to configure the environment properties for the specified JDBC ODBC driver Table 37 MS Access ODBC Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com ddtek jdbcx sequelkink SequeLinkDataSource ClassNamefor OtherlInterfaces ServerName Server name of the machine hosting Sequelink PortNumber lt server port gt Note NOTE Default server port is 19996 DatabaseName User Lo
114. ontinued Parameter Value MaxldleTime The default is 0 a3 MySQL Connector J Driver Configuration Properties OTD Wizard Database Connection Information on page 121 Environment Properties on page 121 a31 OTD Wizard Database Connection Information To connect to MYSQL use the information provided in Table 28 to complete the Connect to Database step of the JOBC ODBC OTD Wizard Table 28 MySQL Connector J Driver Database Connection Information Parameter Value Driver Jar Files mysql connector java 3 0 11 stable bin jar Driver Java Class Name com mysql jdbc Driver URL Connection String jdbc mysql lt server name gt lt server port gt lt database name gt Note NOTE Default server port is 3306 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 A3 2 Environment Properties Use Table 29 to configure the environment properties for the specified JDBC ODBC driver Table 29 MySQL Connector Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName ccom mysql jdbc jdbc2 optional MysqilConnectionPoolDataSou rce ClassNamefor OtherlInterfaces JDBC ODBC eWay Adapter User s Guide 121 Sun Microsystems Inc Appendix A JDBC ODBC Drivers Section A 4 PostgreSQL Driver Table 29 MySQL Connect
115. or J Driver Environment Properties Continued Parameter Value ServerName Server name of the machine hosting the database PortNumber lt server port gt Note NOTE Default server port is 3306 DatabaseName lt database name gt 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 DataSourceName MinPoolSize The default is 0 MaxPoolSize The default is 10 MaxlIdleTime The default is 0 Note Itis not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for MySQL a4 PostgreSQL Driver Configuration Properties OTD Wizard Database Connection Information on page 122 Environment Properties on page 123 A441 OTD Wizard Database Connection Information To connect to SQL use the information provided in Table 30 to complete the Connect to Database step of the JOBC ODBC OTD Wizard To access SQL it is recommended to use the SQL Server eWay Adapter Table 30 PostgreSQL Driver Connection Information Parameter Value Driver Jar Files postgresql 8 0 310 jdbc3 jar Driver Java Class Name org postgresql Driver JDBC ODBC eWay Adapter User s Guide 122 Sun Microsystems Inc Appendix A JDBC ODBC Drivers PostgreSQL Driver Table 30 PostgreSQL Driver Connection Information Continued
116. ords into db_employee table byteArray encoding encoding Tse sss 2 Configure the business rule between the FileClient write Activity and otdInputDTD_DBemployees unmarshal Activity as seen in Figure 42 Figure 42 bpelPsInsert Business Rule 2 Business Rule Designer cau 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation v Output All mt All Business Process Attributes Business Process Attributes bad FileClient write Output D_DBemployees unmarshal Input bad bad FileClient receive Output contents m text byteAray encoding Dla m 3 Configure the business rule between otdInputDTD_DBemployees unmarshal and the otdJDBC Insert_psPSInsert Activity as seen in Figure 43 JDBC ODBC eWay Adapter User s Guide 99 Sun Microsystems Inc Chapter 6 Implementing the JOBC ODBC eWay Sample Projects Business Rule Designer ob 3 B x Conversion Datetime Operator Boolean String Nodes oot Figure 43 bpelPsInsert Business Rule 3 Business Process Attributes bal otdinputD TD_DBemployees unma number Section 6 6 Building and Deploying the prjJDBC_BPEL Sample Project Number XSDOperation Business Process Attributes otdJDBC Insert_PsPSInsert Input 18 q DBemployees input ER Q X_sequence_A objecti Emp_no EmpNo First_name P Lastname Patueniniin bar
117. ote NOTE Default server port is 19996 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 JDBC ODBC eWay Adapter User s Guide 125 Sun Microsystems Inc Appendix A JDBC ODBC Drivers Sequelink DataDirect MS Access ODBC Driver a6 2 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 OtherlInterfaces ServerName Server name of the machine hosting Sequelink PortNumber lt server port gt Note NOTE Default server port is 19996 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 DataSourceName MinPoolSize The default is 0 MaxPoolSize The default is 10 MaxlIdleTime The default is 0 Note Itis not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sequelink DataDirect Informix ODBC a7 Sequelink DataDirect MS Access ODBC Driver Configuration Properties OTD Wizard Database C
118. other countries exclusively licensed through X Open Company Ltd This product is covered and controlled by U S Export Control laws and may be subject to the export or import laws in other countries Nuclear missile chemical biological weapons or nuclear maritime end uses or end users whether direct or indirect are strictly prohibited Export or reexport to countries subject to U S embargo or to entities identified on U S export exclusion lists including but not limited to the denied persons and specially designated nationals lists is strictly prohibited Copyright 2006 Sun Microsystems Inc 4150 Network Circle Santa Clara California 95054 Etats Unis Tous droits r serv s Sun Microsystems Inc d tient les droits de propri t intellectuels relatifs a la technologie incorpor e dans le produit qui est d crit dans ce document En particulier et ce sans limitation ces droits de propri t intellectuels peuvent inclure un ou plus des brevets am ricains list s a l adresse http www sun com patents et un ou les brevets suppl mentaires ou les applications de brevet en attente aux Etats Unis et dans les autres pays L utilisation est soumise aux termes de la Licence Cette distribution peut comprendre des composants d velopp s par des tierces parties Sun Sun Microsystems le logo Sun Java Sun Java Composite Application Platform Suite Sun SeeBeyond eGate elnsight eVision eTL eXchange eView elndex eBAM et eWay sont des ma
119. ou do not want to use our enhanced methods and you want to follow the traditional JDBC calls on your own The DBWizard Assistant expects the column names to be in English when creating a ResultSet 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 Dept No value insert into EMP_TAB Age Name To execute a prepared statement set the input parameters and call executeUpdate and specify the input values if any getPrepStatement getPreparedStatementTest setAge 23 getPrepStatement getPreparedStatementTest setName Peter Pan getPrepStatement getPreparedStatementTest setDeptNo 6 getPrepStatement getPreparedStatementTest executeUpdate Batch Operations To achieve better performance consider using a bulk insert if you have to insert many records This is the Add Batch capability The only modification required is to include the addBatch method for each SQL operation and then the executeBatch call to JDBC ODBC eWay Adapter User s Guide 67 Sun Microsytems Inc Chapter 5 Section 5 2 Using JDBC ODBC Operations JDBC eWay Database Operations JCD submit the batch to the
120. ou need to make sure the indexes match the Stored Procedure This assures your ResultSet indexes are preserved u 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 4 2 6 Add Prepared Statement 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 JDBC ODBC eWay Adapter User s Guide 52 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 22 Prepared Statement Add Prepared Statements Select Wizard Type Connect to Database Prepared Statements Select Database Objects Name SQL Statement Select Tahbles Views Aliases Select Procedures Add Prepared Statements Specify the OTD Name eview Selections Remove Description Hame Edit Parameters 0 parameter s 0 resultset column s Edit
121. out the JOBC ODBC eWay on page 13 What s New in This Release on page 13 About This Document on page 14 Related Documents on page 15 Sun Microsystems Inc Web Site on page 15 Documentation Feedback on page 15 11 About Java Database Connectivity JDBC Java Database Connectivity JDBC is an implementation of the Java programming language that dictates how databases communicate with each other Through a standardized application programming interface API connectivity from database management systems DBMS to a wide range of SQL databases is accomplished By deploying database drivers laced with JDBC technology it is possible to connect to any database even in a heterogeneous environment and access tables tabular data flat files and more When using JDBC Java programmers have the ability to request connections to a database send queries to the database using SQL statements and receive results for advanced processing 111 JDBC Drivers To connect with individual databases JDBC requires drivers for each database Those drivers come in four varieties Driver types 1 and 2 are typically intended for programmers that write applications Driver types 3 and 4 are typically used by JDBC ODBC eWay Adapter User s Guide 7 Sun Microsystems Inc Chapter 1 Section 1 1 Introducing the JOBC ODBC eWay About Java Database Connectivity JDBC database and middleware vendors The various driver types are described in the follow
122. oyee otdOutputDTD_DBemployee_1 otdJDBC OtdJDBCOTD otdJDBC_1 throws Throwable FileClient_1 setText Inserting records into db_employee table FileClient_1l write JDBC ODBC eWay Adapter User s Guide 61 Sun Microsytems Inc Chapter 5 Section 5 2 Using JDOBC ODBC Operations JDBC eWay Database Operations JCD otdInputDTD_DBemployees_1 unmarshalFromString input getText for int il 0 il lt otdInputDTD_DBemployees_1 countX_sequence_A il 1 otdJDBC_1 getInsert_Ps setEmp_no typeConverter stringToShort otdInputDTD_DBemployees_1 getX_sequence_A il getEmpNo false 0 otdJDBC_1 getInsert_Ps setLast_name otdInputDTD_DBemployees_1 getX_sequence_A il getLastname otdJDBC_1 getInsert_Ps setFirst_name otdInputDTD_DBemployees_1 getX_sequence_A il getFirstname otdJDBC_1 getInsert_Ps setRate new java math BigDecimal otdInputDTD_DBemployees_1 getX_sequence_A il getRate otdJDBC_1 getInsert_Ps setLast_update typeConverter stringToSQLDate otdInputDTD_DBemployees_1 getX_sequence_A il getLastDate yyyy MM dd hh mm ss false otdJDBC_1 getInsert_Ps executeUpdate FileClient_1 setText Done Insert
123. peConverter shortToString otdJDBC_1 getDB_EMPLOYEE getEMP_NO false otdOutputDTD_DBemployee_1 setLastname otdJDBC_1 getDB_EMPLOYEE getLAST_NAME otdOutputDTD_DBemployee_1 setFirstname otdJDBC_1 getDB_EMPLOYEE getFIRST_NAME otdOutputDTD_DBemployee_1 setRate otdJDBC_1 getDB_EMPLOYEE getRATE toString otdOutputDTD_DBemployee_1 setLastDate typeConverter dateToString otdJDBC_1 getDB_FMPLOYEE getLAST_UPDATE yyyy MM dd hh mm ss false FileClient_1 setText otdOutputDTD_DBemployee_1 marshalToString FileClient_1l write FileClient_1 setText Done table select FileClient_l write The Insert Operation To perform an insert operation on a table 1 Execute the insert method Assign a value to a field 2 Insert the row by calling insertRow This example inserts an employee record 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 appconn file FileTextMessage input com stc connector appconn file FileApplication FileClient_1 dtd otdInputDTD_1394195520 DBemployees otdInputDTD_DBemployees_1 dtd otdOutputDTD8 82991309 DBempl
124. ppears 5 Make any necessary changes to the Create Domain dialog box and click Create The new domain is added to the Domain Manager Select the domain and click the Start JDBC ODBC eWay Adapter User s Guide 114 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 669 Building and Deploying the Project The Build process compiles and validates the Project s Java files and creates the Project EAR file Build the Project 1 From the Deployment Editor toolbar click the Build icon 2 If there are any validation errors a Validation Errors pane will appear at the bottom of the Deployment Editor and displays information regarding the errors Make any necessary corrections and click Build again 3 After the Build has succeeded you are ready to deploy your Project Deploy the Project 1 From the Deployment Editor toolbar click the Deploy icon Click Yes when the Deploy prompt appears 2 A message appears when the project is successfully deployed You can now test your sample 66 10 Running the Sample Project Additional steps are required to run the deployed sample Project Steps required to run the sample Pro
125. r as SyBase JConnect Driver Configuration Properties OTD Wizard Database Connection Information on page 124 Environment Properties on page 124 a51 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 JOBC ODBC OTD Wizard To access Sybase it is recommended to use the Sybase eWay Adapter Table 32 Sybase JConnect Driver Database Connection Information Parameter Value Driver Jar Files jconn2 jar Driver Java Class Name com sybase jdbc2 jdbc SybDriver URL Connection String jdbc sybase Tds lt server name gt lt server port gt Note NOTE Default server port is 4100 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 a5 2 Environment Properties Use Table 33 to configure the environment properties for the specified JDBC ODBC driver Table 33 Sybase JConnect Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com sybase jdbc2 jdbc SybConnectionPoolDataSource ClassNamefor OtherlInterfaces ServerName Server name of the machine hosting the database PortNumber lt server port gt Note NOTE Default server port is 4100 DatabaseName lt database name gt User Login name of the account used to
126. ransaction 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 onfiguration JAg l m JDBC Connector settings Description JDBC Connection Pool Datasource Description JDBCConnectorSetti JDBC Connector settings Comments JDBCConnectorSett Properties 321 Transaction Support Levels Between Different Versions The types of transaction support levels used in Java CAPS 5 1 0 may be different from the support levels used in Java CAPS 5 1 2 Projects that are imported from a Java CAPS 5 1 0 version can potentially display different results depending on whether the 5 1 0 Java Collaboration Definition JCD included multiple insert update delete operations This only affects non XA transactions If you are using an XA transaction then you can skip this section Example In 5 1 0 five new records are to be inserted into a table If the last record fails to insert such as when a duplicate key exists all previous records will have been inserted This is the behavior of NoTransaction support JDBC ODBC eWay Adapter User s Guide 27 Sun Microsystems Inc Chapter 3 Section 3 2 Setting Properties of the JOBC ODBC eWay 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
127. receive lt gt Copy Update the Rate and Last_update fields using Prepared Statement to FileClient_4 Text lt gt FileClient_1 write lt gt Copy stringToShortinput Text to otdJDBC_1 Update_Ps Emp_no lt gt otdJDBC_1 Update_Ps executeUpdate k lt gt Copy Done Update to FileClient_1 Text L lt gt FileClient_1 write E logger HE alerter E collabContext E typeConverter e 65 6 Binding the eWay Components The final step in creating a Connectivity Map is binding the eWay components together JDBC ODBC eWay Adapter User s Guide 87 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 3 Double click the jcdDelete Service The jcdDelete Binding dialog box appears 4 Map the input FileClient under Implemented Services to the FileClientIN File External Application To do this click on FileSender in the jcdDelete Binding dialog box and drag the cursor to the FileClientIN External Application in the Connectivity Map A link is now
128. river The pros and cons for using this type of driver are as follows Pros Allows access to almost any database since the databases ODBC drivers are readily available Offers significantly better performance than the JOBC ODBC Bridge and Type 2 Drivers Advanced Java feature set Scalable Caching Advanced system administration Does not require applicable database client libraries Cons Requires a separate JDBC middleware server to translate specific native connectivity interface Type Four Driver Direct to database pure Java driver This style of driver converts JDBC calls into a network protocol that sends the converted packets in a proprietary format to be used directly by DBMSs thus allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access This type of driver has become very popular recently and is supported by most database software vendors All JDBC drivers from Data Direct Technologies driver vendor are Type 4 drivers Pros Better performance than Types 1 and 2 No need to install special software on client or server JDBC ODBC eWay Adapter User s Guide 1 Sun Microsystems Inc Chapter 1 Section 1 1 Introducing the JOBC ODBC eWay 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
129. rques de fabrique ou des marques d pos es de Sun Microsystems Inc aux Etats Unis et dans d autres pays Toutes les marques SPARC sont utilis es sous licence et sont des marques de fabrique ou des marques d pos es de SPARC International Inc aux Etats Unis et dans d autres pays Les produits portant les marques SPARC sont bas s sur une architecture d velopp e par Sun Microsystems Inc UNIX est une marque d pos e aux Etats Unis et dans d autres pays et licenci e exclusivement par X Open Company Ltd Ce produit est couvert a la l gislation am ricaine en mati re de contr le des exportations et peut tre soumis a la r glementation en vigueur dans d autres pays dans le domaine des exportations et importations Les utilisations ou utilisateurs finaux pour des armes nucl aires des missiles des armes biologiques et chimiques ou du nucl aire maritime directement ou indirectement sont strictement interdites Les exportations ou r exportations vers les pays sous embargo am ricain ou vers des entit s figurant sur les listes d exclusion d exportation am ricaines y compris mais de mani re non exhaustive la liste de personnes qui font objet d un ordre de ne pas participer d une facon directe ou indirecte aux exportations des produits ou des services qui sont r gis par la l gislation am ricaine en mati re de contr le des exportations et la liste de ressortissants sp cifiquement d sign s sont rigoureusement interdites Part Number
130. rs B Select Recurse Project to specify all components and click OK JDBC ODBC eWay Adapter User s Guide 20 Sun Microsystems Inc Chapter 2 Section 2 3 Installing the JDBC ODBC eWay Installing Enterprise Manager eWay Plug Ins C Select the Environment Explorer tab and from the Environment Explorer right click the Project s Environment and select Version Control gt 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 To reconfigure your File eWays do the following A From the Environment Explorer tree right click the File External System and select Properties from the shortcut menu The Properties Editor appears B Set the inbound and outbound directory values and click OK The File External System can now accommodate both inbound and outbound eWays 3 Deploy your Projects Note Only projects developed on ICAN 5 0 2 and later can be imported and migrated successfully into the Sun Java Composite Application Platform Suite 23 Installing Enterprise Manager eWay Plug Ins The Sun SeeBeyond Enterprise Manager is a Web based interface you use to monitor and manage your Sun Java Composite Application Platform Suite applications The Enterprise Manager requires an eWay specific plug in for each eWay you install These plug ins enable the Ent
131. rver s operating system Cons User needs to make sure the JDBC driver of the database vendor is loaded onto each client machine Must have compiled code for every operating system that the application will run on Best use is for controlled environments such as an intranet Type Two Driver A native API partly Java technology enabled driver converts JDBC calls into calls on the client API for DBMSs Like the bridge driver this style of driver requires that some binary code be loaded on each client machine An example of this type of driver is the Oracle Thick Driver which is also called OCI see JOBC ODBC Drivers on page 117 regarding JDBC eWay support of Oracle drivers JDBC ODBC eWay Adapter User s Guide 9 Sun Microsystems Inc Chapter 1 Section 1 1 Introducing the JDBC ODBC eWay About Java Database Connectivity JDBC Figure 2 Typical Type 2 Driver Configuration Connection Native API partly Java Driver F JT SS The pros and cons for using this type of driver are as follows Pros Allows access to almost any database since the databases ODBC drivers are readily available Offers significantly better performance than the JOBC ODBC Bridge Limited Java feature set Cons Applicable Client library must be installed Type 2 driver shows lower performance than type 3 or 4 Pure Java driver for database middleware This style of driver translates JDBC calls into the middleware vendor s protocol whi
132. s JDBC ODBC eWay Adapter User s Guide 76 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 gt 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 CMap4 and CMap5 and rename them as follows e cmDelete cmInsert cmPsSelect cmTableSelect cmPsUpdate The icons in the toolbar represent the available components used to populate the Connectivity Map canvas Populating the Connectivity Map Add the Project components to the Connectivity Map by dragging the icons from the toolbar to the canvas Each Connectivity Map in the prjJDBC_JCD sample Project requires the following components File External Application 2 JDBC External Application Service Any eWay added to the Connectivity Map is associated with an External System To establish a connection to JDBC first select JDBC as an External System to use in your Connectivity Map Steps required to select a JDBC External System 1 Click the External Application icon on the Connectivity Map toolbar 2 Select the external systems necessary t
133. s implemented correctly For information on Managing and Monitoring alert codes and logs as well as how to view the alert generated by the project component during runtime see the Sun SeeBeyond eGate Integrator System Administration Guide JDBC ODBC eWay Adapter User s Guide 23 Sun Microsystems Inc Chapter 2 Section 2 3 Installing the JDBC ODBC eWay 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 JOBC ODBC eWay This chapter describes how to set the properties of the JOBC 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 31 Creating and Configuring a JOBC eWay All eWays contain a unique set of default configuration parameters After the eWays are established and a JDBC External System is created in the Project s Environment the eWay par
134. s are being selected FileClient_1 setText Selecting record s from db_employee table via Prepared Statement select Copies the database resultset into the otdInputDTD_DBEmployee OTD and selects all available records from the database The executeQuery method executes the prepared statement query while the resultsAvailable method ensures all rows are retrieved in the while loop FileClient_1l write otdJDBC_1 getSelect_ps setEmp_no Short parseShort 0 otdJDBC_1 getSelect_ps executeQuery if otdJDBC_1 getSelect_ps resultsAvailable while otdJDBC_1 getSelect_ps getS Select_psResults next JDBC ODBC eWay Adapter User s Guide 84 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 getSelect_ps get Select_psResults getRATE toString ee otdOutputDTD_DBemployee_1 setLastDate otdJDBC_1 getSelect_ps get
135. s installation is finished continue installing the Sun Java Composite Application Platform Suite as instructed in the Sun Java Composite Application Platform Suite Installation Guide Adding the eWay to an Existing Sun Java Composite Application Platform Suite Installation If you are adding the eWay to an existing Sun Java Composite Application Platform Suite installation do the following 1 2 Complete steps 1 through 4 above Once your products installation is complete open the Enterprise Designer and select Update Center from the Tools menu The Update Center Wizard appears For Step 1 of the wizard simply click Next For Step 2 of the wizard click the Add All button to move all installable files to the Include in Install field then click Next For Step 3 of the wizard wait for the modules to download then click Next The wizard s Step 4 window displays the installed modules Review the installed modules and click Finish JDBC ODBC eWay Adapter User s Guide 18 Sun Microsystems Inc Chapter 2 Section 2 2 Installing the JOBC ODBC eWay 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
136. ser s Guide 129 The database drivers specified in your projects need to be installed on both the Enterprise Designer machine and the Logical host machine When installing the drivers on the Enterprise Designer machine you must specify the absolute path to the driver When installing the drivers on the Logical Host place the driver into the Logical Host stcis directory lt JavaCAPS51 gt logicalhost is lib or lt JavaCAPS51 gt logicalhost is domains domain1l lib where lt JavaCAPS51 gt is the location of your Sun Java Composite Application Platform Suite installation The driver file must be copied to the latter folder if you are running multiple domains and wish to specify a driver for each domain Otherwise you only need to copy the driver file to the former folder address For procedures on how to install database drivers see Sample Projects Drivers on page 72 Sun Microsystems Inc Appendix A Section A 10 JDBC ODBC Drivers Troubleshootin 8 ato 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 If your table does not have column names and data types add t
137. sitory and select Import Project from the shortcut menu The Import Manager appears 2 Browse to and select your exported Project file 3 Click Import A warning message Missing APIs from Target Repository may appear at this time This occurs because various product APIs were installed on the ICAN 5 0 Repository when the Project was created that are not installed on the Java CAPS 5 1 2 Repository These APIs may or may not apply to your Projects You can ignore this message if you have already installed all of the components that correspond to your Projects Click Continue to resume the Project import 4 Close the Import Manager after the Project is successfully imported Deploy the Project 1 A new Deployment Profile must be created for each of your imported Projects When a Project is exported the Project s components are automatically checked in to Version Control to write protected each component These protected components appear in the Explorer tree with a red padlock in the bottom left corner of each icon Before you can deploy the imported Project the Project s components must first be checked out of Version Control from both the Project Explorer and the Environment Explorer To check out all of the Project s components do the following A From the Project Explorer right click the Project and select Version Control gt Check Out from the shortcut menu The Version Control Check Out dialog box appea
138. stem 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 143 Text Conventions The following conventions are observed throughout this document Table 1 Text Conventions Text Convention Used For Examples Bold Names of buttons files icons Click OK parameters variables methods On the File menu click Exit menus and objects Select the eGate sar file Monospaced Command line arguments code java jar filename jar samples variables are shown in bold italic Blue bold Hypertext links within See Text Conventions on page 15 document Blue underlined Hypertext links for Web http www sun com addresses URLs or email addresses 15 Related Documents The following Sun documents provide additional information about the Sun Java Composite Application Platform Suite product Sun SeeBeyond eGate Integrator User s Guide Sun Java Composite Application Platform Suite Installation Guide 16 Sun Microsystems Inc Web Site The Sun Microsystems web site is your best source for up to the minute product news and technical support information The site s URL is http www sun com JDBC ODBC eWay Adapter User s Guide 15 Sun Microsystems Inc Chapter 1 Section 1 7 Introducing the JDBC ODBC eWay Documentation Feedback 1
139. table OTD Support An existing OTD can be edited and saved using the OTD Wizard This allows you to make minor changes to an OTD without having to completely recreate the OTD from scratch The OTD is then rebuilt saved and then relaunched back to the same Java Collaboration or BPEL JDBC ODBC eWay Adapter User s Guide 13 Sun Microsystems Inc Chapter 1 Section 1 4 Introducing the JDBC ODBC eWay 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 14 About This Document This document includes the following chapters Chapter 1 Introducing the JOBC ODBC eWay Provides an overview description of the product as well as high level information about this document Chapter 2 Installing the JOBC ODBC eWay Describes the system requirements and provides instructions for installing the JOBC eWay Chapter 3 Setting Properties of the JOBC ODBC eWay Provides instructions for configuring the eWay to communicate with JDBC drivers Chapter 4 Using the JDBC ODBC eWay Database Wizard Provides instructions for creating Object Type Definitions to be used with the JDBC eWay Chapter 5 Using JOBC ODBC Operations Provides instructions on
140. 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 This allows a direct call from the client machine to the DBMS server Figure 4 Typical Type 4 Driver Configuration Native Protocol Pure Java Driver The pros and cons for using this type of driver are as follows Pros Allows access to almost any database since the databases ODBC drivers are readily available Offers significantly better performance than the JOBC ODBC Bridge and Type 2 Drivers Scalable Caching Advanced system administration Superior performance Advance Java feature set Does not require applicable database client libraries Cons Each database will require a driver JDBC ODBC eWay Adapter User s Guide 12 Sun Microsystems Inc Chapter 1 Section 1 2 Introducing the JDBC ODBC eWay About the JDBC ODBC eWay 12 About the JOBC ODBC eWay This document describes how to install and configure the JOBC 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 The JOBC ODBC eWay should only be used when you have a driver or a database that is
141. te the SelectAll operation See Figure 53 for an illustration of how all the modeling elements appear when connected Note The where clause in the business rule reads the trigger value as a placeholder for input This permits you to modify the query to select a specific record Also note that all records are selected from the database when the TriggerTableSelect in file is empty Note Review the elnsight 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 53 bpelTableSelect Business Process 7 m A otd JDBC FileClient write FileClient receive PE 3E pE AN ae O z5 DB_EMPLOYEES O p ai Ai r electAll Start FileCliert write otdinputDTD_DBe FileClient write mployees marshal Steps required to configure the bpelTableSelect business process 1 Configure the business rule between the FileClient receive and FileCleint write Activities as seen in Figure 54 JDBC ODBC eWay Adapter User s Guide 104 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDOBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Figure 54 bpelTableSelect Business Rule 1 Business Rule Designer ot 3 a x Conversion Datetime Operator Boolean String Nodes Number XSDOperation x E Business Process Attributes Business Process Attributes
142. the Add Tables window 7 Click Next the Add Prepared Statements Wizard appears 8 Click Add the Add Prepared Statement window appears Enter the following Prepared Statement Name Select_ps SOL Statement select from db_employee where emp_no gt order by emp_no Note In this 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 use otdJDBC 11 Click Next and review your settings then click Finish to create the OTD Steps required to create inbound and outbound DTD OTDs include 1 Right click your new Project in the Enterprise Designer s Project Explorer and select New gt Object Type Definition The New Object Type Definition Wizard window appears 2 Select DTD from the list of OTD Wizards and click Next 3 Browse to and then select a DTD file For this example select one of the following DTD files from the sample Project and then click Next otdInputDTD dtd otdOutputDTD dtd 4 The file you select appears in the Select Document Elements window Click Next Click Finish to complete the DTD based OTD Repeat this process again to create the second DTD file 65 3 Creating a Connectivity Map The Connectivity Map provides a canvas for assembling and configuring a Project s component
143. the File External System esFileClient in this sample and select Properties The Properties Editor opens to the JDBC eWay Environment configuration 4 Modify the File eWay Environment configuration properties for your system as seen in Table 23 and click OK Table 23 File eWay Environment Properties Section Property Name Required Value Configuration gt Inbound Directory Enter the directory that contains File eWay gt Parameter the input files trigger files Settings included in the sample Project Trigger files include TriggerBpPsInsert in in TriggerDelete in in TriggerTableSelect in in TriggerBpUpdate in in Configuration gt Outbound Directory Enter the directory where output File eWay gt Parameter files are written In this sample Settings Project the output files include BPEL_Delete_output0 dat BPEL_PsInsert_output0 dat BPEL_TableSelect_output0 dat BPEL_Update_output0 dat JDBC ODBC eWay Adapter User s Guide 112 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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
144. the Project Explorer tree 3 Right click Project1 and select Rename from the shortcut menu Rename the Project for this sample prjJDBC_JCD 652 Creating the OTDs The sample Project requires three OTDs to interact with the JOBC eWay These OTDs include JDBC Database OTD Inbound DTD OTD Outbound DTD OTD Steps required to create a JDBC Database OTD 1 Right click your new Project in the Enterprise Designer s Project Explorer and select New gt Object Type Definition The New Object Type Definition Wizard window appears 2 Select the JDBC Database OTD Wizard from the list of OTD Wizards and click Next 3 Enter the connection information for the JDBC database Connection fields include Driver Jar Files Driver Java Class Name URL Connection String User name Password JDBC ODBC eWay Adapter User s Guide 75 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects 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 Click Select when the database appears in the Results selection frame Click OK to close
145. then click Check Out From the Project Explorer right click the OTD again and select Edit from the menu The JDBC Database Connection Information wizard appears Enter the connection information as described in Connect To Database on page 42 and click Next Step through each of the wizard steps and click Finish to save your changes 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 JDBC ODBC eWay Adapter User s Guide 56 Sun Microsystems Inc Chapter 5 Using JOBC 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 s1 JDBC eWay Database Operations BPEL Within a BPEL business process the JOBC eWay uses BPEL Activities to perform basic outbound database operations including Insert Update Delete SelectOne SelectMultiple SelectAll In addition to these outbound operations the JDBC eWay also employs the inbound Activity ReceiveOne within a Prepared Statement OTD The ability to perform any of the above methods using a table OTD may not be possible with all third party drivers
146. tions Used in the JDBC Sample Projects Assigning Operations in JCD Assigning Operations in BPEL About the elnsight Engine and eGate Components Sample Projects Drivers Running the Sample Projects Running the SQL Script Importing a Sample Project Building and Deploying the prjJDBC_JCD Sample Project Creating a Project Creating the OTDs Creating a Connectivity Map Populating the Connectivity Map Creating the Collaboration Definitions Java jcdDelete Collaboration jcdInsert Collaboration jcdPsSelect Collaboration jcdTableSelect Collaboration jcdPsUpdate Collaboration Create the Collaboration Business Rules Creating the jcdDelete Business Rules Creating the jcdInsert Business Rules Creating the jcdPsSelect Business Rules Creating the jcdTableSelect Business Rules Creating the jcdUpdate Business Rules Binding the eWay Components Creating an Environment Configuring the eWays Configuring the eWay Properties Configuring the Environment Explorer Properties Configuring the Integration Server Creating the Deployment Profile Creating and Starting the Domain 60 60 61 62 63 63 64 65 67 67 69 69 71 71 71 71 72 72 72 73 73 74 75 75 76 77 78 78 79 79 80 80 81 81 81 83 85 87 87 88 89 90 91 92 92 93 JDBC ODBC eWay Adapter User s Guide 5 Sun Microsystems Inc Contents Building and Deploying the Project Running the Sample Building and Deploying the prjJDBC_BPEL Sample Project Cre
147. to access the database Password Password associated with the login account name used to connect to the database a8 2 Environment Properties Use Table 39 to configure the environment properties for the specified JDBC ODBC driver Table 39 Teradata Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com ncr teradata TeraConnectionPoolDataSource JDBC ODBC eWay Adapter User s Guide 128 Sun Microsystems Inc Appendix A JDBC ODBC Drivers Section A 9 Installing JDBC ODBC Drivers Table 39 Teradata Driver Environment Properties Continued Parameter Value ClassNamefor OtherlInterfaces ServerName Server name of the machine hosting the database PortNumber lt server port gt Note NOTE Default server port is 6666 for the Type 3 driver Gateway DatabaseName lt database name gt 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 setURL jdbc teradata lt server name gt lt server port gt lt database server name gt setDSName lt database server_name gt Delimiter The default is DataSourceName MinPoolSize The default is 2 MaxPoolSize The default is 10 MaxldleTime The default is 0 as Installing JOBC ODBC Drivers JDBC ODBC eWay Adapter U
148. to create your Project for this sample JDBC and File Icons representing the selected external systems are added to the Connectivity Map toolbar 3 Rename the following components and then save changes to the Repository File1 to FileClientIN File2 to FileClientOUT JDBC1 to eaJ DBCOUT JDBC ODBC eWay Adapter User s Guide 107 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JDBC ODBC eWay Sample Projects 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 Steps required to bind eWay components together 1 Open one of the Connectivity Maps and double click a Business Process for example the bpelDelete Business Process in the cmDelete Connectivity Map The bpelDelete Binding dialog box appears 2 From the bpelDelete Binding dialog box map FileSender under Implemented Services to the FileClientIN File External Application To do this click on FileSender in the bpDelete Binding dialog box and drag the cursor to the FileClientIN External Application in the Connectivity Map A link is now visible between FileClientIN and bpelDelet
149. ue cmDelete Output file name JCD_Delete_output d dat cmInsert Output file name JCD_Insert_output d dat cmPsSelect Output file name JCD_PsSelect_output d dat cmTableSelect Output file name JCD_TableSelect_output d dat JDBC ODBC eWay Adapter User s Guide 90 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project Table 16 FileClientOUT eWay Property Settings Continued Connectivity Map Property Name Required Value cmUpdate Output file name JCD_Update_output d dat Configuring the Environment Explorer Properties Steps required to configure the Environment Explorer properties 1 From the Environment Explorer tree right click the JDBC External System esJDBC in this sample and select Properties The Properties Editor opens to the JDBC eWay Environment configuration 2 Modify the JDBC eWay Environment configuration properties for your system as seen in Table 17 and click OK Table 17 JDBC eWay Environment Properties Section Property Name Required Value Configuration gt Inbound ServerName Enter the host name of the JDBC eWay gt JDBC database server being used Connector settings DatabaseName Enter the name of the particular database that is being used on the server User Enter the user account name for the database Password Enter the user account password for the database
150. und eWay Properties Connectivity Map Outbound non Transactional eWay Properties Connectivity Map Outbound XA eWay Properties eWay Environment Properties Inbound JDBC eWay Properties Outbound JDBC eWay Properties JDBC Connector Settings Connection Retry Settings Outbound non Transactional JDBC eWay Properties JDBC Connector Settings Connection Retry Settings Outbound XA JDBC eWay Properties JDBC Connector Settings Connection Retry Settings Chapter 4 Using the JOBC ODBC eWay Database Wizard About the Database OTD Wizard Creating a New JDBC OTD Select Wizard Type Connect To Database Select Database Objects Select Tables Views Aliases Select Procedures Add Prepared Statement Specify the OTD Name Review Selections Steps to Edit an Existing JDBC OTD Chapter 5 Using JDBC ODBC Operations JDBC eWay Database Operations BPEL Activity Input and Output JDBC eWay Database Operations JCD JDBC ODBC eWay Adapter User s Guide 4 41 41 41 42 42 43 44 48 52 55 55 56 57 57 57 59 Sun Microsystems Inc 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 Chapter 6 Implementing the JOBC ODBC eWay Sample Projects About the JDBC eWay Sample Projects Sample Project Data Opera
151. unmarshalFromString input getText insert_DB_1 getInsert_new_employee setEmployee_no java lang Integer parselInt employeedb_with_top_db_employee_1 getEmployee_no insert_DB_1 getInsert_new_employee setEmployee_Lname employeedb_with_top_db_employee_1 getEmployee_lname insert_DB_1 getInsert_new_employee setEmployee_Fname employeedb_with_top_db_employee_1 getEmployee_fname insert_DB_1 getInsert_new_employee setRate java lang Float parseFloat employeedb_with_top_db_employee_1 getRate insert_DB_1 getInsert_new_employee setUpdate_date java sql Timestamp valueOf employeedb_with_top_db_employee_1 getUpdate_date insert_DB_1 getInsert_new_employee execute insert_DB_1 commit FileClient_1 setText procedure executed JDBC ODBC eWay Adapter User s Guide 64 f Sun Microsytems Inc Chapter 5 Section 5 2 Using JDOBC ODBC Operations JDBC eWay Database Operations JCD FileClient_l write Manipulating the ResultSet and Update Count Returned by Stored Procedure For Stored Procedures that return ResultSets and Update Count the following methods are provided to manipulate the ResultSet enableResultSetOnly enableUpdateCountsOnly enableResultSetandUpdateCounts resultsAvailable next getUpdateCount available Many drivers do not support manipulating ResultSets in a Stored Procedure It is recommended that you use speci
152. using JDBC database eWay operations in BPEL and JCD Chapter 6 Implementing the JDBC ODBC eWay Sample Projects Provides instructions for installing and running the sample Projects Appendix A JDBC ODBC Drivers Provides instructions for installing and running database drivers JDBC eWay Javadoc A JDBC eWay Javadoc is also provided that documents the Java methods available with the JDBC eWay The Javadoc is uploaded with the eWay s documentation file JDBCeWayDocs sar and downloaded from the Documentation tab of the Sun Java Composite Application Platform Suite Installer To access the full Javadoc extract the Javadoc to an easily accessible folder and double click the index html file 14 1 Scope This user s guide provides a description of the JOBC eWay Adapter It includes directions for installing the eWay configuring the eWay properties and implementing the eWay s sample Projects This document is also intended as a reference guide listing available properties functions and considerations For a reference of available JDBC eWay Java methods see the associated Javadoc JDBC ODBC eWay Adapter User s Guide 14 Sun Microsystems Inc Chapter 1 Section 1 5 Introducing the JDBC ODBC eWay Related Documents 142 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 sy
153. using the Business Process Designer s Business Rule Designer Once you have connected the modeling elements together begin adding the business processes necessary to facilitate the Insert operation See Figure 40 for an illustration of how all the modeling elements appear when connected Note Review the elnsight 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 JDBC ODBC eWay Adapter User s Guide 98 Sun Microsystems Inc Chapter 6 Section 6 6 Implementing the JOBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_BPEL Sample Project Figure 40 bpelPsInsert Business Process FileClient receive se otdinputDTD_DBe FileClient write 3E 3E 3E Sd mployees O unmarshal gt ese Start FileClient write otdJDBC Insert_PsPSlInsert End 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 3usiness Rule Designer amp 3 2 x Conversion Datetime Operator Boolean String Nodes Number XSDOperation v m Business Process Attributes Business Process Attributes 8 bad FileClient receive Qutput A yw FileClient write Input bad string literal text text byteAnay Inserting rec
154. w click Add Figure 18 Select Procedures and specify Resultset and Parameter Information Select Procedures and specify ResultSet and Parameter infor Select Wizard Type Selected Procedures Connect to Database Select Database Objects Catalog Schema Type Select Tablesiiews Aliases Select Procedures Add Prepared Statements Specify the OTD Name eview Selections Description 0 parameter s Edit Parameters Edit ResultSets 0 ResultSet s O Use fully qualified procedure names in the generated Java code Back Next gt Finish Cancel Help 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 JDBC ODBC eWay Adapter User s Guide 49 Sun Microsystems Inc Chapter 4 Section 4 2 Using the JDBC ODBC eWay Database Wizard Creating a New JDBC OTD Figure 19 Add Procedures Procedure Search Type the exact Procedure Name or use Wildcard characters ne a SCR Procedure Selection Results 29 Records S400L0C PROCEDURE S400L0C PROCEDURE I S400L0C PROCEDURE RP_DBEMPLOYEE_ S400LOC PROCEDURE RP_DBEMPLOYEE S400L0C PROCEDURE Add RP_DBEMPLOYEE S400L0C PROCEDURE Remove 4 On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the sele
155. w 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 In addition to the sample Projects the JDBC510_SAMPLE_projects zip file also includes seven sample input trigger files and nine sample output files as follows Sample input files TriggerInsert in in for JCE projects only TriggerBpPsInsert in in for BPEL projects only TriggerDelete in in TriggerPsUpdate in in for JCE projects only TriggerBpUpdate in in for BPEL projects only TriggerPsSelect in in for JCE projects only TriggerTableSelect in in Sample output JCD files JCE_Insert_output dat JCE_Delete_output dat JCE_PsUpdate_output dat JCE_PsSelect_output dat JCE_TableSelect_output dat Sample output BPEL files BPEL_PsInsert_output dat BPEL_Delete_output dat BPEL_Update_output dat JDBC ODBC eWay Adapter User s Guide 70 Sun Microsystems Inc Chapter 6 Section 6 1 Implementing the JDBC ODBC eWay Sample Projects About the JDBC eWay Sample Projects BPEL_TableSelect_output dat 61 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
156. xt lt gt Copy Done delete to FileClient_1 Text L lt gt FileClient_1 write E logger HE alerter E collabContext L E typeConverter Creating the jcdInsert Business Rules The jcdInsert Collaboration implements the Input Web Service Operation to read the TriggerInsert in file It then unmarshals data from the input data into the otdInputDTD_DBEmployees OTD calls the otdJDBC OTD and inserts records into the database via a For Loop The Collaboration also writes a message to JCD_Insert_output0 dat to confirm an inserted record The jedInsert Collaboration contains the Business Rules displayed in Figure 32 JDBC ODBC eWay Adapter User s Guide 81 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project Figure 32 jcdInsert Business Rules Q Gb jedinsert P receive lt gt Copy Inserting records into db_employee table to FileClient_1 Text lt gt FileClient_4 write lt gt otdInputD TD_DBemployees_1 unmarshalFromString input T ext Ff For Loop counter initialization L VAR CopyO to i1 condition i1 is less than count of otdInputDTD_DBemployees_1 X_sequence_A steps L lt gt Copy i1 1 to it tules lt gt Copy stringToShortfotdinputD TD_DBemployees_1 X_sequence_A i1 EmpNo to otdJDBC_1 Insert_Ps Emp_no lt gt Copy otdinputDTD_DBemployees_1 X_sequence_A i1 Lastname to otdJDBC_1 Insert_Ps Last
157. yment Profile FileClientiIN gt cmDelete_jcdDelete1 cmDelete_jcdDelete1 gt FileClientOUT FileClientiN gt cminsert_jedinsertt cminsert_jedinsertt gt FileClientOUT FileClientiIN gt cmPsSelect_jcdPsSelectt emPsSelect_jedPsSelectt gt FileClientOUT FileClientIN gt CMap1_jcdUpdate1 CMap1_icdUpdate1 gt FileClientOUT FileClientIN gt cmTableSelect_jcdTableSelectt cminsert_jedinsert gt eaJDBCOUT emPsSelect_jedPsSelecti gt eaJDBCOUT vex CMap1_jedUpdate1 gt eaIDBCOUT emTableSelect_jcdTableSelect1 gt eaJDBCOUT he e Integrationsvr1 fe cmDelete_jcdDeletet fe cminsert_jedinsertt fe cmPsSelect_jcdPsSelectt fe CMap1_jcdUpdatet E E sBsmsiamgrt 6 5 10 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 JDBC ODBC eWay Adapter User s Guide 93 Sun Microsystems Inc Chapter 6 Section 6 5 Implementing the JDBC ODBC eWay Sample Projects Building and Deploying the prjJDBC_JCD Sample Project Steps required to create and start the domain 1 Navigate to your lt JavaCAPS51 gt logicalhost directory where lt JavaCAPS51 gt is the location of your Sun Java Composite Application Platform Suite installation 2 Double click the
158. 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 sub tab 4 Browse for and select the WAR file for the application plug in that you downloaded and click Deploy The plug ins is installed and deployed 231 Viewing Alert Codes You can view and delete alerts using the Enterprise Manager An alert is triggered when a specified condition occurs in a Project component The purpose of the alert is to warn the administrator or user that a condition has occurred To View the eWay Alert Codes 1 Add the eWay Enterprise Manager plug in for this eWay 2 From the Enterprise Manager s Explorer toolbar click configuration 3 Click the Web Applications Manager tab and go to the Manage Alert Codes sub tab Your installed eWay alert codes display under the Results section If your eWay alert codes are not displayed under Results do the following A From the Install New Alert Codes section browse to and select the eWay alert properties file for the application plug in that you added The alert properties files are located in the alertcodes folder of your Sun Java Composite Application Platform Suite installation directory B Click Deploy The available alert codes for your application are displayed under Results A listing of the eWay s available alert codes is displayed in Table 2 Table 2 Alert

Download Pdf Manuals

Related Search

Related Contents

pH 2,7 - PurOdor  災害対策用機械の操作訓練を実施します    Fiches thématiques - Semaine de la mobilité et de la sécurité routière  PORTA MASTER PM2000i/T/TS/TSL Betriebsanleitung  FW-375LX  SYBR® Premix Reagent Trial Pack  TRAF-OIL 商品説明書  NL Electronic Health Record Pharmacy Network User Guide  安心サポート規約 - 光ファイバー、インターネット接続サービスのメガ  

Copyright © All rights reserved. Failed to retrieve file