Sun Microsystems eWay JDBC/ODBC Adapter, User Manual

Page 1: ...SUN SEEBEYOND eWAY JDBC ODBC ADAPTER USER S GUIDE Release 5 1 2 ...

Page 2: ...nis Tous droits réservés Sun Microsystems Inc détient les droits de propriété intellectuels relatifs à 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 à l adresse http www sun com patents et un ou les brevets supplémentaires ou les applicatio...

Page 3: ...Way 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 JDBC ODBC eWay 17 Installing the JDBC eWay 17 Installing the JDBC eWay on an eGate supported system 18 Adding the eWay to an Existing Sun Java Composite Application...

Page 4: ...tbound JDBC eWay Properties 32 JDBC Connector Settings 33 Connection Retry Settings 34 Outbound non Transactional JDBC eWay Properties 35 JDBC Connector Settings 35 Connection Retry Settings 37 Outbound XA JDBC eWay Properties 37 JDBC Connector Settings 38 Connection Retry Settings 39 Chapter 4 Using the JDBC ODBC eWay Database Wizard 41 About the Database OTD Wizard 41 Creating a New JDBC OTD 41 ...

Page 5: ...73 Importing a Sample Project 73 Building and Deploying the prjJDBC_JCD Sample Project 74 Creating a Project 75 Creating the OTDs 75 Creating a Connectivity Map 76 Populating the Connectivity Map 77 Creating the Collaboration Definitions Java 78 jcdDelete Collaboration 78 jcdInsert Collaboration 79 jcdPsSelect Collaboration 79 jcdTableSelect Collaboration 80 jcdPsUpdate Collaboration 80 Create the...

Page 6: ...g Elements 100 Configuring the bpelDelete Modeling Elements 102 Configuring the bpelTableSelect Modeling Elements 104 Creating the Connectivity Map 107 Populating the Connectivity Map 107 Binding the eWay Components 108 Creating an Environment 109 Configuring the eWays 110 Configuring the eWay Properties 111 Configuring the Environment Explorer Properties 111 Configuring the Integration Server 113...

Page 7: ...k on page 15 1 1 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 J...

Page 8: ... 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 t...

Page 9: ... 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 server s ope...

Page 10: ...ectivity 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...

Page 11: ...ced 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 ...

Page 12: ...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 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...

Page 13: ... 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 t...

Page 14: ...bject Type Definitions to be used with the JDBC eWay Chapter 5 Using JDBC ODBC Operations Provides instructions on 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 J...

Page 15: ...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 1 6 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...

Page 16: ...C ODBC eWay Documentation Feedback JDBC ODBC eWay Adapter User s Guide 16 Sun Microsystems Inc 1 7 Documentation Feedback We appreciate your feedback Please send any comments or suggestions regarding this document to CAPS_docsfeedback sun com ...

Page 17: ...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...

Page 18: ...te Products to Install box 5 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 6 Once your product s installation is f...

Page 19: ...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 Do...

Page 20: ...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 appl...

Page 21: ... 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 Enterprise 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 Installatio...

Page 22: ...s 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 ...

Page 23: ...al 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 dat...

Page 24: ...ems Inc 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 ...

Page 25: ...ectivity 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 JDBC External System window Collaboration or Business Process JDBC eWay properties may also be ...

Page 26: ...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 Thi...

Page 27: ...on 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 a...

Page 28: ...stem 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 Under the scenario noted above if you want 5 1 2 behavior for a LocalTransaction then set your e...

Page 29: ...onfigure 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 informati...

Page 30: ...hat section 5 Click on any property field to make it editable After modifying the configuration properties click OK to save the changes 3 4 eWay Connectivity Map Properties The eWay Connectivity Map consists of the following properties categories Outbound eWay Configuration Sections Include Connectivity Map Outbound eWay Properties on page 31 Outbound non Transactional eWay Configuration Settings ...

Page 31: ...base 3 5 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 JDBC eWay properties There are four eWay connection types that the JDBC ODBC eWay implements Property Categories Configured in the Lo...

Page 32: ...on The description of the database A valid string ClassName Displays the Java class in the JDBC driver that is used to implement the Driver Manager interface Change this as needed for your driver A valid class name See JDBC ODBC Drivers on page 117 for some of the popular drivers you can use with this eWay URL This is the JDBC URL required to gain access to the database The URL usually starts with...

Page 33: ...e 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 of the external database server Any valid string PortNumber Specifies the I O port number on which the server is listening for connection requests A valid port number DatabaseName Specifies the name of the databas...

Page 34: ...saction 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 A valid numeric value The default is 0 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 wi...

Page 35: ...etries is 10 and the Connection Retry Interval is 5000 A valid numeric value The default is 1000 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 driver that is used to im...

Page 36: ...ties according to driver vendor s instruction For example setDefTdpName DBSQL setWorkspa ce Navigator Delimiter This is the delimiter character to be used in the DriverProperties prompt The default is DataSource name Specifies the name of the XADataSource or ConnectionPoolDataSource implementation to which the DataSource object delegates behind the scenes when there is connection pooling or distri...

Page 37: ...ecifies 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 Table 10 Outbound non Transactional eWay Connection Retry Settings Name Description Required Value ConnectionRetries Specifies the number of retries to establish a connection upon failure to acquire one A valid numeric...

Page 38: ...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 of the external database server Any valid string PortNumber Specifies the I O port number on which the server is listening for connection requests A valid port number DatabaseName Specifies the name of the database instance Any valid string User Specifies th...

Page 39: ...e of the application If the pool size is too big you may end up with too many connections with the database A valid numeric value The default is 0 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 d...

Page 40: ...he 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 A valid numeric value The default is 1000 Table 12 Outbound XA JDBC eWay Connection Retry Setti...

Page 41: ...s 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 existi...

Page 42: ... 2 1 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 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 ...

Page 43: ...nection 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 4 2 3 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 combinat...

Page 44: ...ay Adapter User s Guide 44 Sun Microsystems Inc Figure 12 Select Database Objects 4 2 4 Select Tables Views Aliases Select the types of tables views or aliases required in the OTD Steps Required to Select Table Views Aliases Include 1 In the Select Tables Views Aliases window click Add See Figure 13 ...

Page 45: ...er s Guide 45 Sun Microsystems Inc Figure 13 Select Tables Views Aliases 2 In the Add Tables window select if your selection criteria will include table data view only data both and or system tables 3 From the Table View Name drop down list select the location of your database table and click Search See Figure 14 ...

Page 46: ...Wizard Creating a New JDBC OTD JDBC ODBC eWay Adapter User s Guide 46 Sun Microsystems Inc Figure 14 Database Wizard All Schemes 4 Select the table of choice and click OK The table selected is added to the Selected Tables Views Aliases section See Figure 15 ...

Page 47: ...ou do not wish to make any additional changes click Next to continue 6 In the Table View Columns window you can select or deselect your table columns You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down list If you would like to change any of the tables columns click Change See Figure 16 The data type is usually listed as O...

Page 48: ...stems Inc Figure 16 Table View Columns 7 Click Advanced to change the data type percision length or scale Once you have finished your table choices click OK In general you will not need to make any changes See Figure 17 Figure 17 Table View Columns Advanced 4 2 5 Select Procedures Select the type of stored procedures required in your OTD ...

Page 49: ...1 On the Select Procedures and specify Resultset and Parameter Information window click Add Figure 18 Select Procedures and specify Resultset and Parameter Information 2 On the Select Procedures window enter the name of a Procedure or select a schema from the drop down list Click Search Wildcard characters can also be used 3 In the resulting Procedure Selection list box select a Procedure Click OK...

Page 50: ...edures 4 On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure See Figure 20 Figure 20 Procedure Parameters 5 To restore the data type click Restore When finished click OK 6 To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets ...

Page 51: ...nd 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 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 b...

Page 52: ...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 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 ...

Page 53: ...click Save As giving the statement a name This name will appear as a node in the OTD Click OK See Figure 23 Figure 23 Prepared SQL Statement 3 On the Add Prepared Statement window the name you assigned to the Prepared Statement appears 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 y...

Page 54: ...rosystems Inc Figure 24 Edit the Prepared Statement Parameters 5 To edit Resultset Columns click Edit Resultset Columns The ResultSet Columns window appears See Figure 25 Figure 25 ResultSet Columns 6 Click Add to add a new ResultSet column Both the Name and Type are editable 7 Click OK to return to the Add Prepared Statements window ...

Page 55: ...D The OTD contains the selected tables and the package name of the generated classes See Figure 26 Figure 26 Naming an OTD 2 Click Next 4 2 8 Review Selections Review the selections made for the new OTD 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...

Page 56: ...select Version Control Check Out from the menu The Version Control Check Out window appears 2 Select the OTD you want to check out then click Check Out 3 From the Project Explorer right click the OTD again and select Edit from the menu The JDBC Database Connection Information wizard appears 4 Enter the connection information as described in Connect To Database on page 42 and click Next 5 Step thro...

Page 57: ...bound 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 You have to use a Prepared Statement to perform such an opera...

Page 58: ... and otdJDBC Db_employeeDelete Activities In this example the whereClause appears on the Input side Figure 28 Input and Output Between Activities The following table lists the expected Input and Output of each database operation Activity Table 13 JDBC ODBC Operations eInsight Operations Activity Input Activity Output SelectAll where clause optional Returns all rows that fit the condition of the wh...

Page 59: ... a Prepared Statement to perform such an 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 con...

Page 60: ...e the default setting The type of result returned by the select method can be specified using SetConcurrencytoUpdatable SetConcurrencytoReadOnly SetScrollTypetoForwardOnly SetScrollTypetoScrollSensitive SetScrollTypetoInsensitive The Query Select Operation To perform a query operation on a table 1 Execute the select method with the where clause specified if necessary 2 Loop through the ResultSet u...

Page 61: ...lse FileClient_1 setText otdOutputDTD_DBemployee_1 marshalToString FileClient_1 write FileClient_1 setText Done table select FileClient_1 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 s...

Page 62: ...s false otdJDBC_1 getInsert_Ps executeUpdate FileClient_1 setText Done Insert FileClient_1 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 clas...

Page 63: ...n 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 FileClient_1 setText Delete record FileClient_1 write otdJ...

Page 64: ... logger public com stc codegen alerter Alerter alerter public void receive com stc connector appconn file FileTextMessage 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 unmarshalFromString input getText insert_DB_1 getInsert_new_employee ...

Page 65: ...es 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...

Page 66: ...S_multi getDbEmployee next System err println EMPNO getSPIn getSpS_multi getDbEmployee getEMPNO System err println ENAME getSPIn getSpS_multi getDbEmployee getENAME System err println JOB getSPIn getSpS_multi getDbEmployee getJOB System err println MGR getSPIn getSpS_multi getDbEmployee getMGR System err println HIREDATE getSPIn getSpS_multi getDbEmployee getHIREDATE System err println SAL getSPIn...

Page 67: ...cute 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 you 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 Res...

Page 68: ... 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 ...

Page 69: ...ects 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 6 1 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 Coll...

Page 70: ...ord 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 project...

Page 71: ...te 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 desir...

Page 72: ...Activity to a Business Process then associate that Activity with an eGate component for example an eWay When eInsight run the Business Process it automatically invokes that component via its Web Services interface 6 1 4 Sample Projects Drivers Sample Projects included with this eWay were built using the AS 400 JDBC Toolbox Driver jt400 jar You must get this or a different driver from a third party...

Page 73: ...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_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...

Page 74: ...ect from the shortcut menu The Import Manager appears Figure 30 Import Manager Dialog Box 3 Browse to the directory that contains the sample Project ZIP file Select the sample file and click Import Click Close after successfully importing the sample Project 6 5 Building and Deploying the prjJDBC_JCD Sample Project This section provides step by step instructions for manually creating the prjJDBC_JC...

Page 75: ...igner 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_JCD 6 5 2 Creating the OTDs The sample Project requires three OTDs to interact with the JDBC eWay These OTDs include JDBC Database OTD Inbound D...

Page 76: ...ment 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 ...

Page 77: ...ity 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 E...

Page 78: ...oration Steps required to create the jcdDelete Collaboration 1 From the Project Explorer right click the sample Project and select New Collaboration Definition Java from the shortcut menu The Collaboration Definition Wizard Java appears 2 Enter a Collaboration Definition name for this sample jcdDelete and click Next 3 For Step 2 of the wizard from the Web Services Interfaces selection window doubl...

Page 79: ...that is included in the Sample Project 6 Click the Up One Level button twice to return to the Repository Double click Sun SeeBeyond eWays File FileClient The Selected OTDs field now lists the FileClient OTD 7 Click Finish The Collaboration Editor with the new jcdInsert Collaboration appears in the right pane of the Enterprise Designer jcdPsSelect Collaboration Steps required to create the jcdPsSel...

Page 80: ...For Step 3 of the wizard from the Select OTDs selection window double click prjJDBC_JCD otdALL otdJDBC The otdJDBC OTD is added to the Selected OTDs field 5 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 Clic...

Page 81: ...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 ...

Page 82: ...ileApplication 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 data into the otdInputDTD_...

Page 83: ...ypeConverter stringToSQLDate otdInputDTD_DBemployees_1 getX_sequence_A i1 getLastDate yyyy MM dd hh mm ss false otdJDBC_1 getInsert_Ps executeUpdate Writes a message to confirm the inserted records FileClient_1 setText Done Insert FileClient_1 write Creating the jcdPsSelect Business Rules The jcdPsSelect Collaboration implements the Input Web Service Operation to read the TriggerPsSelect in file I...

Page 84: ... 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 records 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 selec...

Page 85: ...ecord found FileClient_1 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_1 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 t...

Page 86: ...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 al...

Page 87: ...o records are available FileClient_1 setText Done table select FileClient_1 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 r...

Page 88: ...e Binding dialog box and drag the cursor to the FileClientIN External Application in the Connectivity Map A link is now visible between FileClientIN and jcdDelete 5 From the jcdDelete Binding dialog box map otdJDBC_1 under Invoked Services to the esJDBCOUT External Application 6 From the jcdDelete Binding dialog box map FileClient_1 to the FileClientOUT External Application as seen in Figure 36 Fi...

Page 89: ...le External System Name the External System esFileClient Click OK esFileClient is added to the Environment Editor 6 Right click envJDBCProj and select New 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 Sun SeeBeyond Integration Server A new Integration Server IntegrationSvr1 is added t...

Page 90: ...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 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 TriggerTa...

Page 91: ...rer 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 cmUpdate Output file name JCD_Update_output d dat Table 17 JDBC eWay Environment Properties Section Property Name Required V...

Page 92: ...e Properties Editor For more information on deploying a Project see the Sun SeeBeyond Java Composite Application Platform Suite Deployment Guide 6 5 9 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 Table 18 File eWay Environment Properti...

Page 93: ...file 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 Deployment Profile 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...

Page 94: ...ark indicates that the domain is running For more information about creating and managing domains see the eGate Integrator System Administration Guide 6 5 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 er...

Page 95: ...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 6 6 Building and Deploying the prjJDBC_BPEL Sample Project The following provides step by step instructions for manually creating the prjJDBC_BPEL sample P...

Page 96: ...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 examp...

Page 97: ...r 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 6 6 3 Creating the Business Process Steps required to create the Business Process include Crea...

Page 98: ...Figure 40 for an illustration of how all the modeling elements appear when connected 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 Table 19 Business Process Activities Business Process Activity bpelPsInsert FileClient Receive FileClient Write otdInpu...

Page 99: ...ness process 1 Configure the business rule between the FileClient receive and FileClient write Activities as seen in Figure 41 Figure 41 bpelPsInsert Business Rule 1 2 Configure the business rule between the FileClient write Activity and otdInputDTD_DBemployees unmarshal Activity as seen in Figure 42 Figure 42 bpelPsInsert Business Rule 2 3 Configure the business rule between otdInputDTD_DBemploye...

Page 100: ...elUpdate 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 rul...

Page 101: ...on of the steps required to connect and add business rules to a modeling elements in a business process Figure 45 bpelUpdate Business Process Steps required to configure the bpelUpdate business process 1 Configure the business rule between the FileClient receive and FileCleint write Activities as seen in Figure 46 Figure 46 bpelUpdate Business Rule 1 2 Configure the business rule between the FileC...

Page 102: ...g 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...

Page 103: ...quired to connect and add business rules to a modeling elements in a business process Figure 49 bpelDelete Business Process Steps required to configure the bpelDelete business process 1 Configure the business rule between the FileClient receive and FileClient write Activities as seen in Figure 50 Figure 50 bpelDelete Business Rule 1 2 Configure the business rule between the FileClient write Activi...

Page 104: ...esses necessary to facilitate 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 e...

Page 105: ...systems Inc Figure 54 bpelTableSelect Business Rule 1 2 Configure the business rule between the FileClient write Activity and otdJDBC DB_EMPLOYEESelectAll Activity as seen in Figure 55 Figure 55 bpelTableSelect Business Rule 2 3 Configure the business rule between the otdJDBC DB_EMPLOYEESelectAll Activity and the otdInputDTD_DBemployees marshal Activity as seen in Figure 56 ...

Page 106: ... Figure 56 bpelSelectTable Business Rule 3 4 Configure the business rule between the otdInputDTD_DBemployees marshal Activity and the FileClient write Activity as seen in Figure 57 Figure 57 bpelTableSelect Business Rule 4 5 Configure the business rule between the FileClient write Activity and the FileClient write Activity as seen in Figure 58 Figure 58 bpelTableSelect Business Rule 5 ...

Page 107: ...ar 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 t...

Page 108: ...f 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...

Page 109: ...nvironment 1 From the Enterprise Designer s Enterprise Explorer click the Environment Explorer tab 2 Right click the Repository and select New Environment A new Environment is added to the Environment Explorer tree 3 Rename the new Environment to envJDBCProj 4 Right click envJDBCProj and select New JDBC External System Name the External System esJDBC Click OK esJDBC is added to the Environment Edi...

Page 110: ... eWays eWays facilitate communication and movement of data 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 Figu...

Page 111: ...ment 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 Table 20 FileClientIN eWay Property Settings Connectivity Map Property Name R...

Page 112: ...st name of the database server being used DatabaseName Enter the name of the particular database that is being used on the server User Enter the user account name for the database Password Enter the user account password for the database Table 23 File eWay Environment Properties Section Property Name Required Value Configuration Inbound File eWay Parameter Settings Directory Enter the directory th...

Page 113: ...ecific 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 6 6 7 Creating the Deployment Profile A Deployment Profile is used to assign services and message destinations to the Integration Server and message se...

Page 114: ...main 1 Navigate to your JavaCAPS51 logicalhost directory where JavaCAPS51 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 indic...

Page 115: ... 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 6 6 10 Running the Sample Project 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 ...

Page 116: ... 116 Sun Microsystems Inc 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 ...

Page 117: ... 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 used for Outbound eWays The Inbound eWa...

Page 118: ...ing the JDBC ODBC eWay A 1 AS 400 Toolbox Driver Configuration Properties OTD Wizard Database Connection Information on page 118 Environment Properties on page 119 A 1 1 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 JDBC ODBC OTD Wizard To access DB2 it is recommended to use the DB2 eWay Adap...

Page 119: ...mplete the Connect to Database step of the JDBC ODBC OTD Wizard Table 25 AS 400 Database Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com ibm as400 access AS400JDBCConnectionPoolDataSource ClassNamefor OtherInterfaces ServerName Server name of the machine hosting the database PortNumber server port Note NOTE Default server port is 446 DatabaseName Us...

Page 120: ...unity Server Password Leave password field blank Value configured when the database entry is created in the Attunity Server 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 server port Note ...

Page 121: ...leTime The default is 0 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 server name server port database name Note NOTE Default server port is 3306 User Name Login name of the account used to access the database Password Passwor...

Page 122: ...To access SQL it is recommended to use the SQL Server eWay Adapter ServerName Server name of the machine hosting the database PortNumber server port Note NOTE Default server port is 3306 DatabaseName database name 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...

Page 123: ...th the login account name used to connect to the database Table 31 PostgreSQL Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName org postgresql jdbc3 Jdbc3ConnectionPool ClassNamefor OtherInterfaces ServerName Server name of the machine hosting the database PortNumber server port Note NOTE Default server port is 5432 DatabaseName database name User ...

Page 124: ...er 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 server name server port 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 conne...

Page 125: ... ODBC driver To connect to an Informix database it is recommended to use the Informix eWay Adapter 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 MaxIdleTime The default is 0 Table 34 Sequelink DataDirect Informix ODBC Driver Database Connect...

Page 126: ...n Information on page 127 Environment Properties on page 127 Table 35 Sequelink DataDirect Informix ODBC Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com ddtek jdbcx sequelkink SequeLinkDataSource ClassNamefor OtherInterfaces ServerName Server name of the machine hosting Sequelink PortNumber server port Note NOTE Default server port is 19996 D...

Page 127: ...river URL Connection String jdbc sequelink server name server port 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 Table 37 MS Access ODBC Driver Environment Properties Parameter Value Description JDBC Connection Pool Datasource ClassName com ddtek jdbcx s...

Page 128: ...erties Use Table 39 to configure the environment properties for the specified JDBC ODBC driver MaxIdleTime The default is 0 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 server name server port database server name Note NOTE Default server port is 6666 for...

Page 129: ...he 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 ClassNamefor OtherInterfaces ServerName Server name of the machine hosting the database PortNumber server port Note NOTE Default server ...

Page 130: ...e 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 them before saving the OT...

Page 131: ...g 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 M...

Page 132: ...tNumber 33 36 38 ServerName 33 35 38 User 32 33 36 38 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 Seq...

Page 133: ...Index JDBC ODBC eWay Adapter User s Guide 133 Sun Microsystems Inc U update count 65 ...