Database services allow business processes to interact with databases by:
Invoking a SQL query
Executing a stored procedure or function
Configure your database service endpoint before you define the service. See Configuring Endpoint Connections for Database Services.
Use the New Service Wizard to define a new database service. The wizard guides you through the creation of a database service.
In Orchestration Navigator for your project, right-click on the Services directory.
Select New>Service.
The New Service Wizard guides you through the definition of a new service.
When the service is defined, two files are created in the Navigator: one with a .wsdl extension and one with an .iface extension.
Consider the following as you define your database service:
When adding an operation, you can use either a SQL query or a stored procedure.
An operation can be added at the time you define the service or later, at any time before deployment.
The wizards use database metadata to automatically populate parameters, results sets, and return values for procedures from the database. This information may not be complete. For stored procedures, you may need to manually add some of the information for parameters and result sets. For SQL statements, you must manually add all parameters.
You must define the result sets to match results referenced by the stored procedure or SQL query.
The Discover result sets... button on the Result Set page of the Database Service Wizard. Use this to execute your procedure or statement and automatically populate your result sets.
Note: As you define operations in database services, remember that the inputs and outputs you define for the operation must match the underlying stored procedure or SQL query. The wizard provides a summary of the operation inputs and outputs before the operation is created. Review this summary carefully.
For ASE:
The ASE database always returns stored procedures parameter modes as unknown. You must specify the actual parameter modes of in, out, or in/out. Information about ASE stored procedure result sets cannot be determined automatically unless you execute the stored procedure when the operation is created. To create the operation, use the Discover Result Sets button on the Result Sets page of the New Operation Wizard. You can also specify result sets manually, but you must be certain that the results sets you specify match the results returned by the stored procedure.
Be sure you have not used single quotation marks inside double quotation marks in your SQL statements. If you have not followed this convention, you will get a JDBC exception.
For ASE databases, the quotation delimiter used for SQL statement fragments must be a single quote.
For SQL Server 2000:
When using a stored procedure with the data type Uniqueidentifier as an input value, the process fails at runtime. The problem is that the Unique Identifier as an input value is being mapped to char. You must manually change the type to varbinary. As you are defining the database service, review the inputs and outputs. If Unique Identifier is included as a data type of char, change the type to varbinary. If you have not made changes when defining the database service, make the changes as specified in Troubleshooting.