Accessing Databases with Services

Overview

All types of services can access databases, including flow services, Java service, and C/C++ services. You can create:

Flow services using the Integration Server Administrator. When you create a flow service that accesses a database, you make selections from Integration Server Administrator screens to describe the function that you want the service to perform. The webMethods Integration Server generates the flow service for you based on your selections.
Java and C/C++ services using webMethods Integration Server or your own development environment. Integration Server provides built-in services that perform basic database operations, such as connecting to a database, selecting rows, inserting rows, and deleting rows. Your services can access these built-in services to perform database operations. Your services can also use other database APIs (for example, JDBC) to access databases.

This chapter describes how to decide which type of service to create, how to create database flow services, how to create database services with Java or C/C++, and how to create clients that access databases.

Deciding Which Type of Service to Create

Use the information in the following table to decide which type of service you should create.

Type of Service	Advantages	Disadvantages
Flow services created with the Integration Server Administrator	You can create the flow service using the Integration Server Administrator user interface. You do not need to recompile code when you make changes to the flow service.	The flow service might not be able to handle nonstandard database features, such as Windows SQL types. You can only make JDBC calls in a flow service. Use a Java or C/C++ service to connect to a database using other libraries.
Java or C/C++ Services	The service can be coded more flexibly in terms of processing data. You can manipulate data directly, rather than invoking Integration Server service libraries. For example, you can use configuration files to guide processing settings. The service can include more robust error handling. For example, you can log errors to your own files or send e-mail notes to several people. The service can make direct calls to database APIs (for example, JDBC, ADO, and ODBC) to handle nonstandard database features.	You must code and compile the service.

Creating Database Flow Services

About this task

When you use the Integration Server Administrator to create flow services that access databases, you progress through a series of screens, selecting options and specifying information that indicates the function you want the service to perform. The server builds a flow service from the selections you specify.

The resulting flow service invokes the same built-in services that you can invoke from Java or C/C++. For more information about these services, see Built-in Database Services.

Using the Integration Server Administrator, you can create a flow service in one of the following ways:

Generate from tables. You select the table that you want the service to access. Then, you select the database operation you want your service to perform (Select, Insert, Delete, or Update). The server displays the columns in the table that you can use to indicate the input that the service expects. When you use this option, the server automatically generates the required SQL statements. For more information, see Generating a Database Flow Service from a Table.
Generate from an SQL statement. You specify the SQL statement that you want the service to perform. The SQL statement you specify can be more complex than the SQL statements the server generates when it generates a service from tables. In addition, you can specify database-specific SQL. Use this option if you want to use database-specific SQL that requires the fully qualified table names. For more information, see Generating a Database Flow Service from an SQL Statement.

Regardless of how you generate them, database flow services are all standard flow services. After a service is created, you can edit it using Designer in the same way you edit any other flow service. You can invoke the resulting database flow service from other services.

Note: All generated database flow services call the pub.db:execSQL service. This service executes an SQL statement. If you want to change the SQL statement the flow service executes, open that service in Designer and change the $dbsql parameter in the INVOKE execSQL step in the flow.

Important: Before you can use the Integration Server Administrator to build flow services, the server must have a configured database alias for the database that you want to access. The database alias must be configured when you create a service and when the server executes the service. For more information about how to configure a database alias, see Identifying Database Aliases.

Generating a Database Flow Service from an SQL Statement

About this task

To generate a service from an SQL statement, you must select the SQL statement that you want the service to perform. You can specify a static SQL statement or a dynamic SQL statement.

Important: The generated flow service is unable to properly call a stored procedure or return the result set or output parameters. If you want to call a stored procedure, create the service in Java or C/C++ and call the stored procedure using the pub.db:call service.

Specifying a Dynamic SQL Statement

About this task

You can create a dynamic SQL statement by including question marks or template tags in the SQL statement. When you specify a dynamic SQL statement, the service expects input values to replace the question marks or template tags that you specify.

Using Question Marks (?) in SQL Statements

About this task

Use a question mark in place of a single parameter that the service expects as input. When you test the service in Integration Server Administrator, it recognizes the question mark and prompts you for the required input value.

Example 1

To select all rows from the Names table that match the values specified for the Last_Name column, specify the following SQL statement:

select * from Names where Last_Name=?

The service expects an input value to use to match rows in the Last_Name column.

Example 2

To add a new row to the Addresses table and populate it with specified values, specify the following SQL statement:

insert into Addresses (name,street,city,state,zip) 
values (?,?,?,?,?)

The service expects input values to use to populate the new row.

Using Template Tags in SQL Statements

About this task

The template tags you can use in an SQL statement are the same tags you use in an output template. Besides allowing you to specify the values of individual input parameters, template tags also allow you to dynamically construct entire portions of the SQL statement at run time. (For a complete list of template tags, see the Dynamic Server Pages and Output Templates Developer’s Guide.)

When you use template tags in an SQL statement, you cannot test the statement with the Integration Server Administrator; (the Integration Server Administrator does not recognize template tags and will not prompt you for input when you execute the service). To test a service that uses template tags, you must open that service in Designer and add to its input parameters any variables that are referenced in a tag. You can then test the service with Designer and it will prompt you for each input variable you defined. For information about declaring input parameters for a service, see the webMethods Service Development Help.

Example 1

The following shows an SQL statement that selects all rows satisfying the criteria that will be specified in a variable named condition at run time:

select * from Names where %value condition%

At run time, the server will substitute the value of condition for the %value% tag. The following shows examples of what you might use as the value of condition:

name = 'Steve'

name like 'Jim%'

Example 2

To delete all rows from the Music table that meet a specified condition, specify the following SQL statement:

delete from Music where %value outdated%

The service expects input for an input variable named outdated that it uses to replace the entire token. Following is an example of what a user can specify for %value outdated%:

ReleaseDate < '1950'

Format = '8-track'

Steps for Generating a Flow Service from an SQL Statement

About this task

Use the following procedure to generate a flow service from an SQL statement.

To generate a flow service from an SQL statement

Procedure

Open the Integration Server Administrator if it is not already open.
On the Database menu, select Service Generation.
In the Source alias list, select the database you want the service to access.
In the Package list, select the package in which you want the service to reside.
In the Folder field, type the name of the folder in which you want to save the service.
In the Service field, type the name you want to assign to the service.
In the Execute ACL list, select the ACL group to which you want to limit access to the service. Only the users who are members of the groups in the selected ACL will be able to invoke the service.
If you want to overwrite any existing services with the same name as the new service, next to Overwrite Existing, select the Generated objects overwrite existing objects check box.
Click Generate from SQL. The server displays the Edit SQL Statement screen.
In the Enter SQL statement section of the screen, enter the SQL statement you want the service to execute. You can specify a statement that is dynamic (that is, contains question marks or template tags) or static.
If you included template tags in the SQL statement, select the Process webMethods template tags in this SQL statement check box.
Click Evaluate. The server displays the Input Binding Generation screen.

If you specified question marks (?) in the SQL statement for input values, the server displays a Bind parameters section of the screen. Under Bind parameters, one Parameter # field exists for each question mark you specified in the SQL statement. The first question mark corresponds to Parameter 0, the second to Parameter 1, and so forth. Use this section of the screen to indicate additional information about the input parameters that a user will supply in place of the question marks.

For each Parameter # field under Bind parameters, specify the following information:

For this field...	Specify or select...
name	The name that you want the service to use for the input parameter.
type	The data type of the input parameter.

Click Generate.

Generating a Database Flow Service from a Table

About this task

When you specify that you want to generate the service from database tables, the server displays information about the tables in the selected database. If your database contains many tables, you can restrict the list of tables that the server displays.

Restricting the List of Database Tables

About this task

You can restrict the list of database tables that the server displays by specifying one or more of the following:

Catalog. You specify the name of the catalog whose tables you want to use.
- If you are not working with a distributed database, do not restrict by catalog.
- If you are working with a distributed database, you can specify the name of the database with which you want to work. If you are using DB2, use this field to specify the name of a DB2 location.
Schema pattern. You specify the schemas whose tables you want to work with (if you are using DB2, use this field to specify an AuthID).
- If you want tables for all schemas in the selected database, do not restrict by schema pattern.
- If you want to restrict your search to tables by selected schemas, specify the schema name. You can specify a pattern-matching string if your JDBC supports it. Most drivers support the pattern-matching characters described for Table name pattern below; however, check your driver’s documentation for information about its pattern-matching capabilities.

Table name pattern. You specify the names of the tables with which you want to work. Specify a table name or a pattern-matching string that specifies the names of the tables. Most drivers support the following pattern-matching characters; however, check your driver’s documentation for information about its pattern-matching capabilities.

Use	To match	For example
%	Any string of characters	HR% matches: HR30all, HR15mgmt, HR01payroll, and so forth.
_	A single character	HR3_mgmt matches: HR30mgmt, HR31mgmt, HR3Amgmt, and so forth.

Table type. The type of the tables for which you want information. You can choose from the following common JDBC table types: Table, View, System Table, Global Temporary, Local Temporary, Alias, and Synonym.

Steps for Generating the Flow Service from a Table

About this task

Use the following procedure to generate a flow service from database tables.

To generate a flow service from a table

Procedure

Open the Integration Server Administrator if it is not already open.
On the Database menu, select Service Generation.
In the Source alias list, select the database you want the service to access.
In the Package list, select the package in which you want the service to reside.
In the Folder field, type the name of the folder in which you want to save the service.
In the Service field, type the name you want to assign to the service.
In the Execute ACL list, select the ACL group to which you want to limit access to the service. Only the users who are members of the groups in the selected ACL will be able to invoke the service.
If you want to overwrite any existing services with the same name as the new service, next to Overwrite Existing, select the Generated objects overwrite existing objects check box.
Click Generate from table. The server displays the Specify Connection Parameters screen.

Determine which tables you want to search by performing one of the following:

To search all database tables, click Connect.

To restrict your search, fill in one or more fields in the Restrictions parameters as follows, and then click Connect. For more information about how to restrict the search, see Restricting the List of Database Tables.

For this parameter...	Specify
Catalog	Catalog names whose tables you want to use. If you are not working with a distributed database, do not restrict by catalog.
Schema Pattern	Schemas whose tables you want to work with. If you want tables for all schemas in the selected database, do not restrict by schema pattern.
Table Name Pattern	Table name or a pattern-matching string that identifies the names of the table with which you want to work. If the default wildcard character “%” is specified, then every table name will match.
Table Types	Types of tables for which you want information.

After you click Connect, the server displays the Tables in DataSourceName screen.

In the Select a table section of the screen, click the table name that you want to use to generate the service.
In the Service type section of the screen, select the database operation that you want the service to perform (Select, Insert, Delete, or Update). The server displays the Columns in TableName screen.

In the Columns section of the screen, select the columns that you want to use as input parameters. When the service is executed, it expects input values for each of the columns you select. The service uses the input values as follows:

For this operation...	The service...
Select	Selects rows that have columns that match the input values.
Insert	Populates the columns of the inserted rows using the input values. If you want to populate all columns in the row, select all column names.
Delete	Selects rows that have the columns that match the input values and deletes the selected rows.
Update	Selects rows and updates them. To identify the criteria to use to select rows, you check column names in the Criteria column. The service expects input values for each column name you check. The service selects rows that have columns that match the input values. If you want to select all rows, do not check any Criteria columns. To identify the columns to update in the selected rows, use the Set column. The service expects input values for each column you check. The service sets the value of the checked columns (for all selected rows) to the specified input values.

Click Generate SQL. The server displays the Input Binding Generation screen.

If you selected any columns for input parameters, the server displays a Bind parameters section of the screen. There is one Parameter # field for each column you selected.

Use the Bind parameters section of the screen to identify the name and data type for each input parameter. Specify the following information:

For this parameter...	Specify...
name	The name that you want the service to use for the input parameter. By default, the server uses the database column name. If you want to use a different name, type the new name in this field.
type	The data type of the input value. By default, the server uses the data type that is associated with the database column. If you want to use a different data type, select it from the list.

Click Generate.

Output from the Flow Service

When you generate a flow service from a database table, the database operation determines the service output. The following table describes the service output produced by each database operation:

Database Operation	Output
Select	Number of rows that the service retrieved and the columns from those rows that the service requested
Insert	Update count that the database returned in response to the SQL command
Delete	Update count that the database returned in response to the SQL command
Update	Update count that the database returned in response to the SQL command

Creating Database Services with Java or C/C++

About this task

You can code services that access databases in Java or C/C++. Code your own services if you need a service that performs more complex database operations than a flow service can provide.

To assist you in coding database services, you can use the built-in database services to perform basic database operations and test SQL statements before you add them to your services.

If you want your service to access a database that has nonstandard features (for example, data types that are not supported by SQL), use other database connection APIs. For example, you can make direct calls to JDBC or use other connection libraries, such as ADO.

Built-in Database Services

webMethods Integration Server provides several built-in database services that perform basic database operations. These services use JDBC to connect to the database to perform the specific database operation. You can invoke the built-in database services from:

Java and C/C++ services
Any flow service that you create in Designer by using the INVOKE flow step
Clients

The webMethods Integration Server Built-In Services Reference contains a list of built-in database services and shows input and output information for each. Your service or client must invoke the built-in database service that opens a connection to a database before it can invoke any of the other built-in database services.

Testing SQL Statements

About this task

You can use the Integration Server Administrator to test an SQL statement that you want to execute using the built-in database service pub.db:execSQL or that you want to invoke directly using a JDBC call.

Before you can use the Integration Server Administrator to test an SQL statement, the server must have a configured database alias for the database that you want to access. For more information about how to configure a database alias, see Identifying Database Aliases.

To test an SQL statement

Procedure

Open the Integration Server Administrator if it is not already open.
On the Database menu, select Alias Management.
Under Current Data Sources, select the database alias for the database you want the service to access.
Click Connect. The server displays the Connection Parameters screen.
Indicate which tables you want to search by performing one of the following:
- To search all database tables, click Connect. The server displays the Tables screen.
- To restrict your search, fill in one or more fields in the Restrictions parameters as follows, and then click Connect. The server displays the Tables screen. For more information about how to restrict the search, see Restricting the List of Database Tables.
In the Test SQL Queries section of the Tables screen, type the SQL statement you want to test.
Click Execute query.

The server sends the SQL statement you specified to the database for execution and displays a screen that lists the results from your SQL query.

Error Handling

Some database services return a $dbMessage output value which contains a text message that describes the results of the service. If the service results in an error, the service also returns standard error output values.

Creating Clients that Access Databases

About this task

You can access the databases from browser-based clients and clients coded in Java or C/C++.

Invoking a Database Service from a Browser-based Client

About this task

Several of the database services can accept input from a browser-based client. (The descriptions of the database services in the webMethods Integration Server Built-In Services Reference indicate whether you can invoke a built-in database service from a browser-based client.) When a browser-based client submits input variables containing row information, these services automatically convert the row information into a nested IData object. The service determines which variables to convert based on the names of the variables. All variables except those whose names begin with _ or $db are converted into nested IData objects.

For example, suppose a browser user submits the following name-value pairs:

Variable Name	Contents
`$dbTable`	`Table Name`
`Name`	`Joe B`
`Company`	`Widgets, Inc.`

The pub.db:insert service converts the inputs as follows:

Variable Name	Contents
`$dbTable`	Table Name
`$data`	Variable Name	Contents
	`Name`	`Joe B`
	`Company`	`Widgets, Inc.`

Invoking a Built-in Service from a Java, C/C++, or VB Client

About this task

You can create client applications in Java or C/C++. The client applications can use the built-in database services to perform database operations. For information about these built-in services, see the webMethods Integration Server Built-In Services Reference.

If you want your client to access a database that has nonstandard features (for example, data types that are not supported by SQL), you use other database APIs. For example, you can make direct calls to JDBC or use other connection libraries, such as ADO.

Sample Code - IData

The following shows a sample Java client that accesses a database using an IData object to pass and receive data from the database service.

import com.wm.data.*;  
import com.wm.app.b2b.client.Context;  
  
public class DBClient  
{  
public static void main (String [] args) throws Exception  
{  
IData in = null;  
IData out = null;  
IData criteria = null;  
IData set = null;  
  
//  
// connect to your integration server using an appropriate user  
// name and password (doesn't have to be Administrator)  
//  
Context ctx = new Context();  
ctx.connect ("localhost:5555", "Administrator", "manage");  
//  
// (1) request a DB connection by DB alias (if the DB  
// changes location or something, we won't have to change  
// this client code)  
//  
in = IDataFactory.create();  
IDataCursor inCursor = in.getCursor();  
inCursor.insertAfter ("$dbAlias", "Employees");  
inCursor.destroy();  
out = ctx.invoke ("wm.util.db", "connect", in);  
//  
// (2) update the Identification table to set Fonz's ID  
// to 6500.  note that we couldn't do this from a Web  
// browser because we couldn't build up the complex  
// nested data structures  
in = IDataFactory.create();  
inCursor = in.getCursor();  
inCursor.insertAfter ("$dbAlias", "Employees");  
inCursor.insertAfter ("$dbTable", "Identification");  
criteria = IDataFactory.create();  
IDataCursor criteriaCursor = criteria.getCursor();  
criteriaCursor.insertAfter ("name", "fonzie");  
criteriaCursor.destroy();  
inCursor.insertAfter ("$criteria", criteria);  
set = IDataFactory.create();  
IDataCursor setCursor = set.getCursor();  
setCursor.insertAfter ("ID", "6500");  
setCursor.destroy();  
inCursor.insertAfter ("$set", set);  
inCursor.destroy();  
  
try {  
out = ctx.invoke ("wm.util.db", "update", in);  
  
//  
// (3) look at the return values (updateCount is the  
// most important in this case)  
//  
IDataCursor outCursor = out.getCursor();  
  
if (outCursor.first("updateCount"))  
{  
int uc = Integer.parseInt ((String)outCursor.getValue());  
System.err.println ("Update count: "+uc);  
}  
else  
System.err.println ("Error: no update count returned");  
outCursor.destroy();  
} catch (Exception e) {  
// maybe something went wrong with the DB access; we  
// can get more information here  
if (outCursor.first("$error")) {  
   System.err.println ("Error: "+outCursor.getValue());  
}  
if (outCursor.first("$errorType")) {  
   System.err.println ("Error type: "+outCursor.getValue());  
}  
outCursor.destroy();  
}  
}  
  
}