Manuals / Sun Microsystems / Computer Equipment / Network Card

Sun Microsystems JDBC/ODBC Adapter manual Executing Stored Procedures

Models: JDBC/ODBC Adapter

1 133

Download 133 pages 48.36 Kb

Page 64

Chapter 5	Section 5.2
Using JDBC/ODBC Operations	JDBC eWay Database Operations (JCD)

Executing Stored Procedures

The OTD represents the Stored Procedure “LookUpGlobal” with two parameters, an inbound parameter (INLOCALID) and an outbound parameter (OUTGLOBALPRODUCTID). These inbound and outbound parameters are generated by the Database Wizard and are represented in the resulting OTD as nodes. 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:

1Specify the input values.

2Execute the Stored Procedure.

3Retrieve the output parameters if any. For example:

package Storedprocedure;

public class sp_jce

{

public com.stc.codegen.logger.Logger 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().setEmployee_no( java.lang.Integer.parseInt( 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

Sun Microsytems, Inc.

Image 64

Sun Microsystems JDBC/ODBC Adapter manual Executing Stored Procedures, Steps for executing the Stored Procedure include

Contents

EWAY JDBC/ODBC Adapter USER’S Guide Part Number Version Contents Transaction Support Levels Between Different Versions Activity Input and OutputCreating the Collaboration Definitions Java Stored ProcedureCreate the Collaboration Business Rules Building and Deploying the prjJDBCBPEL Sample Project Building and Deploying the Project Running the SampleJdbc Drivers About Java Database Connectivity JdbcWhat’s In This Chapter Type I JDBC-ODBC Bridge Type One DriverType II Partial Java driver Type Two DriverPros ConsPure Java driver for database middleware Type Three DriverTypical Type 3 Middleware Driver Configuration Type Four Driver Direct-to-database pure Java driverTypical Type 4 Driver Configuration What’s New in This Release About the JDBC/ODBC eWayNew for Version Scope About This DocumentJdbc eWay Javadoc Related Documents Sun Microsystems, Inc. Web SiteIntended Audience Text ConventionsDocumentation Feedback Installing the JDBC/ODBC eWay Installing the Jdbc eWayInstalling the Jdbc eWay on an eGate supported system After Installation Ican 5.0 Project Migration ProceduresExtracting the Sample Projects and Javadocs Import the Project Install Java CapsDeploy the Project Installing Enterprise Manager eWay Plug-Ins To add plug-ins from the Enterprise ManagerViewing Alert Codes To View the eWay Alert CodesAlert Codes for the JDBC/ODBC eWay Alert Code\Description Description Details User ActionsDuncommitted Chapter Section Creating and Configuring a Jdbc eWay Configuring the eWay Connectivity Map PropertiesTo configure the eWay properties Connectivity Map with ComponentsTransaction Support Levels Between Different Versions ExampleTransaction Support Levels Configuring the eWay Environment Properties To Configure the Environment PropertiesOutbound eWay Configuration Sections Include EWay Connectivity Map PropertiesOutbound XA eWay Configuration Settings Include EWay Environment PropertiesConnectivity Map Outbound eWay Properties Connectivity Map Outbound non-Transactional eWay PropertiesInbound Jdbc eWay-Parameter Settings Inbound Jdbc eWay PropertiesOutbound Jdbc eWay Properties Name Description Required ValueJdbc Connector Settings Outbound Jdbc eWay-JDBC Connector SettingsIs Jdbc Connection Pool Datasource ConnectionPoolDataSourceOutbound Jdbc eWay-Connection Retry Settings Connection Retry SettingsXADataSource or ConnectionPoolDataSource Outbound non-Transactional eWay -JDBC Connector Settings Outbound non-Transactional Jdbc eWay PropertiesOutbound non-Transactional eWay -JDBC Connector Settings Outbound non-Transactional eWay-Connection Retry Settings Outbound XA Jdbc eWay PropertiesIs Jdbc XA Datasource Outbound XA Jdbc eWay-JDBC Connector SettingsConnectionPoolDataSource or Outbound XA Jdbc eWay-Connection Retry Settings Outbound XA Jdbc eWay-Connection Retry Settings About the Database OTD Wizard Creating a New Jdbc OTDSelect Wizard Type Connect To DatabaseRequired Database Connection Fields include Select Database ObjectsSteps Required to Select Database Objects Include Select Tables/Views/Aliases Steps Required to Select Table/Views/Aliases IncludeSelect Tables/Views/Aliases Database Wizard All Schemes Selected Tables/Views/Aliases window with a table selected Select Procedures Table/View ColumnsSteps Required to Select Stored Procedures Include Add Procedures Edit Resultset Add Prepared Statement Steps Required to Add Prepared Statements IncludePrepared Statement Edit the Prepared Statement Parameters Specify the OTD Name Review SelectionsSteps Required to Specify the OTD Name Steps Required to Review Your OTD SelectionsSteps to Edit an Existing Jdbc OTD Steps to Edit the OTD from the Enterprise Designer IncludeJdbc eWay Database Operations Bpel Activity Input and OutputJDBC.ODBC Operations EInsight Operations Activity Input Activity OutputJdbc eWay Database Operations JCD Query Select Operation To perform a query operation on a tableInsert Operation To perform an insert operation on a tableUpdate Operation To perform an update operation on a tableDelete Operation Stored ProcedureTo perform a delete operation on a table Executing Stored Procedures Steps for executing the Stored Procedure includeFileClient1.write Collaboration usability for a stored procedure ResultSet Prepared Statement Batch OperationsChapter Section Using JDBC/ODBC Operations Implementing the JDBC/ODBC eWay Sample Projects About the Jdbc eWay Sample ProjectsSample output JCD files Sample input filesSample output Bpel files Sample Project Data Operations Used in the Jdbc Sample ProjectsAssigning Operations in JCD Assigning Operations in BpelAbout the eInsight Engine and eGate Components Running the Sample ProjectsSample Projects Drivers Running the SQL Script Importing a Sample ProjectBuilding and Deploying the prjJDBCJCD Sample Project Steps required to create the sample projectCreating the OTDs Creating a ProjectSteps required to create a Jdbc Database OTD Creating a Connectivity Map Click Next the Add Prepared Statements Wizard appearsSteps required to create a new Connectivity Map Populating the Connectivity MapSteps required to select a Jdbc External System JcdDelete Collaboration Creating the Collaboration Definitions JavaSteps required to create the jcdDelete Collaboration JcdInsert Collaboration JcdPsSelect CollaborationSteps required to create the jcdInsert Collaboration Steps required to create the jcdPsSelect CollaborationJcdPsUpdate Collaboration Steps required to create the jcdPsUpdate CollaborationJcdTableSelect Collaboration Steps required to create the jcdTableSelect CollaborationCreating the jcdDelete Business Rules Create the Collaboration Business RulesCreating the jcdInsert Business Rules JcdInsert Business Rules Creating the jcdPsSelect Business Rules JcdPsSelect Business Rules Sample code from the jcdPsSelect IncludesCreating the jcdTableSelect Business Rules JcdTableSelect Business Rules Sample code from the jcdTableSelect IncludesCreating the jcdUpdate Business Rules Binding the eWay ComponentsCreating an Environment Steps required to bind eWay components togetherConfiguring the eWays Steps required to create an EnvironmentConfiguring the eWay Properties Steps required to configure the eWay propertiesFileClientIN eWay Property Settings FileClientOUT eWay Property SettingsJdbc eWay Environment Properties Configuring the Environment Explorer PropertiesSection Property Name Required Value Creating the Deployment Profile Configuring the Integration ServerFile eWay Environment Properties Creating and Starting the Domain Steps required to create the Deployment ProfileBuilding and Deploying the Project Running the SampleSteps required to create and start the domain Build the ProjectBuilding and Deploying the prjJDBCBPEL Sample Project Steps required to create a Jdbc Database OTD include Creating the Business Process Creating the Business Process FlowSteps required to create inbound and outbound DTD OTDs Steps to create a business process flow includeBusiness Process Activities Configuring the bpelPsInsert Modeling ElementsBusiness Process Activity BpelPsInsert Business Process Configuring the bpelUpdate Modeling Elements BpelUpdate Business Process Steps required to configure the bpelUpdate business processConfiguring the bpelDelete Modeling Elements Steps required to configure the bpelDelete business process BpelDelete Business ProcessConfiguring the bpelTableSelect Modeling Elements BpelTableSelect Business ProcessBpelTableSelect Business Rule # BpelSelectTable Business Rule # Steps required to create the Connectivity Map Creating the Connectivity MapTo Select a Jdbc External System Binding the eWay Components To Select a Jdbc Business Process109 FileClientOUT eWay FileClientIN eWay EaJDBCOUT eWay 111 112 113 114 Running the Sample Project 116 JDBC/ODBC Drivers Configuration Properties AS/400 Toolbox DriverOTD Wizard Database Connection Information AS/400 Database Connection InformationEnvironment Properties Attunity DriverAS/400 Database Environment Properties Attunity Driver Database Connection Information Attunity Driver Database Environment PropertiesMySQL Connector/J Driver MySQL Connector/J Driver Database Connection InformationMySQL Connector/J Driver Environment Properties Parameter Value MaxIdleTime Default isPostgreSQL Driver PostgreSQL Driver Connection InformationPostgreSQL Driver Environment Properties Sybase JConnect Driver Database Connection Information SyBase JConnect DriverSybase JConnect Driver Environment Properties Sequelink DataDirect Informix Odbc Driver Sequelink DataDirect MS Access Odbc Driver MS Access Odbc Driver Database Connection Information MS Access Odbc Driver Environment PropertiesTeradata Driver Database Connection Information Teradata DriverTeradata Driver Environment Properties Installing JDBC/ODBC Drivers Troubleshooting Index Jdbc Update count