JDBC

The JDBC service enables processes to interact with databases to accomplish the following tasks:

Execute stored procedures
Execute SQL statements
Query the database

For information about using the JDBC service, see Service reference.

JDBC service configuration

The JDBC service can be configured with default properties for connecting to a database server. (See Editing service configurations.)

When you configure the connection property, the JDBC service operations inherit the property values. However, the default connection property can be overridden using the properties of each operation.

DatasourceName

A string value that represents the JNDI name of the data source to use to connect to the database server. The data source must be defined on the application server that hosts the LiveCycle Server. The default is the JNDI name of the data source for the LiveCycle database.

Call Stored Procedure operation

Executes a stored procedure on the database.

To call stored procedures, the database user account that is used to access the database needs to have the required database permissions.

Note: Not all databases support stored procedures. See the documentation for the database that you are using for information about whether it supports stored procedures.

For information about the General and Route Evaluation property groups, see Common operation properties.

Input properties

Properties for specifying the stored procedure to call.

Datasource Name

A string value that represents the JNDI name of the data source to use to connect to the database server. The data source must be defined on the application server that hosts the LiveCycle Server.

Stored Procedure

The command to call the stored procedure on the database server. You must use the Callable Statement Info Editor dialog box to create the call command. (See About Callable Statement Info Editor.)

Output properties

Properties for specifying where to store the operation results.

Number of Rows Affected

The location to store the number of table rows that the stored procedure affected. The data type is int.

Exceptions

This operation can throw JDBCConnectionException, JDBCIllegalParameterException, SQLException, and JNDIContextUnavailableException exceptions.

Execute SQL Statement operation

Executes a SQL statement on a database server and returns the number of rows that were affected. This operation is typically used for SQL statements that do not return a result set, such as INSERT, UPDATE, and DELETE statements.

The database user account that is used to connect to the database server and perform the SQL statement must have the required database permissions.

For information about the General and Route Evaluation property groups, see Common operation properties.

Input properties

Properties for specifying the stored SQL statement to execute.

Datasource Name

A string value that represents the JNDI name of the data source to use to connect to the database server. The data source must be defined on the application server that hosts the LiveCycle Server.

SQL Statement

A value that represents the SQL statement to execute on the database server. You use the SQL Statement Info Editor to specify the value. (See About SQL Statement Info Editor.)

Output properties

Properties for saving operation results.

Number Of Rows Affected

A location that stores the number of rows that the SQL statement affected. The data type is int.

Exceptions

This operation can throw JDBCConnectionException, JDBCIllegalParameterException, SQLException, and JNDIContextUnavailableException exceptions.

Query for Multiple Rows as XML operation

Queries a database using a SQL statement and returns the result set as XML data.

For information about the General and Route Evaluation property groups, see Common operation properties.

Input properties

Properties for defining the query.

Datasource Name

A string value that represents the JNDI name of the data source to use to connect to the database server. The data source must be defined on the application server that hosts the LiveCycle Server.

SQL Statement

The SQL statement to execute on the database server. You must use the SQL Statement Info Editor dialog box to create the SQL statement. (See About SQL Statement Info Editor.)

XML Information

The definition for the structure of the XML to use for returning the results set. You need to use the XML Document Info Editor dialog box to create the definition. (See About XML Document Info Editor.)

Output properties

Properties for saving operation results.

XML Document

The location to use for storing the results set that is returned. The data type is xml.

Exceptions

This operation can throw JDBCConnectionException, JDBCIllegalParameterException, SQLException, and JNDIContextUnavailableException exceptions.

Query Single Row operation

Queries the database using a SQL statement and saves the first row of the result set.

For information about the General and Route Evaluation property groups, see Common operation properties.

Input properties

Properties for defining the query.

Datasource Name

A string value that represents the JNDI name of the data source to use to connect to the database server. The data source must be defined on the application server that hosts the LiveCycle Server.

SQL Statement

The SQL statement to execute on the database server. You need to use the SQL Statement Info Editor dialog box to create the SQL statement. (See About SQL Statement Info Editor.)

Output properties

Properties for saving operation results.

Data Mapping

The locations in the process data model to use for saving the data in the first row of the query result set. Click the ellipsis button to display the SQL Results Mapping Editor dialog box, which you use to specify the data locations.

The dialog box includes a list that you use to associate database columns with process data locations.

Index:

An integer value that identifies the column/location pair. The index is one-based, so that the first item in the list has an index value of 1.

Column Name:

The name of the database table column from which data is retrieved using the SQL query. Click the cell and type the column name.

Process Variable:

An XPath expression that resolves to the process data location to use to store the data from the corresponding column. Click the cell and then click the ellipsis button to open XPath Builder, which you can use to create the XPath expression.

You can use the Parse Query and Process Metadata buttons to automatically fill the Index and Column Name columns with values:

Click Parse Query to determine column names from the SQL statement that is specified in the SQL Statement property. You can use this button if the SQL statement explicitly names the column, for example SELECT column1, column2 FROM table1. This button does not work for statements such as SELECT * FROM table1, or SELECT concat(a,b) AS FULLNAME FROM table1.
Click Process Metadata to retrieve column names from the database server. A request is sent to the database server to process the query and return metadata about the query. The list of columns is determined from the returned metadata.

After you automatically fill the Index and Column Name columns, you can specify the associated process data locations in the Process Variable column.

If no value is set for the SQL Statement property, or if neither the Parse Query or the Process Metadata buttons successfully populate the Index and Column Name columns, you need to populate these columns manually. For each column in the result set, click the + button to add a row to the table. After the row is added, you can populate the row with values.

Number Of Rows Retrieved

The location in the process data model to use for saving the number of rows in the result set that the query returned. This value is 0 if no rows are in result set and 1 if there is a row in the result set.

Exceptions

This operation can throw JDBCConnectionException, JDBCIllegalParameterException, SQLException, and JNDIContextUnavailableException exceptions.

About Callable Statement Info Editor

Use the Callable Statement Info Editor dialog box to create and test the call to the database that executes a stored procedure. The call to the stored procedure can include XPath expressions as well as placeholders for parameter values (for stored procedures that take parameter values).

View full size graphic

A. Editing area B. Opens XPath Builder C. Enables the use of parameters D. Parameter definitions E. Testing area

You type the procedure call in the Callable Statement box. Procedure calls are in the following format:

CALL procedure_name(parameter_list);

procedure_name is the name of the stored procedure.

(parameter_list) is a series of question marks (?) separated by commas. Each question mark represents a parameter.

The number of parameters depends on the procedure that you are calling. If the procedure requires no parameters, this part of the procedure call is omitted.

For example, to call the procedure named proc1, which takes two input parameters, the call is CALL proc1 (?,?);.

If your procedure call includes placeholders, select Use Parameterized Statement. You must also define the parameter for each placeholder that you include in the statement. (See Define parameters.) You can also perform the following tasks when creating the statement:

Include XPath expressions
Define parametners
Test

Include XPath expressions

You can include references to process data in the procedure call using XPath expressions. In the procedure call, XPath expressions must appear inside braces and between dollar signs, as in {$XPath$}.

For example, the following procedure call is used for a procedure name that is stored in a process variable named strvar:

    CALL {$/process_data/@strvar$};

To create an XPath expression using XPath Builder, click the ellipsis button below the Callable Statement box. The expression is inserted in the statement at the location of the cursor.

Define parameters

You must define a parameter for each parameter placeholder that the procedure call statement includes. For each parameter placeholder, add a row to the table. The order in which the parameters are defined in the table is the order in which they appear in the procedure call.

For each parameter in the procedure call, click the + button to add a corresponding row to the table, and then specify values in each column.

Index:: An index that identifies the parameter. This value is generated automatically when the row is added to the table.
Type:: The data type that the parameter holds. Click the cell in the Type column and then select the data type from the list.
Value:: The value to use for the parameter. Click the cell in the Value column and either type the value or click the ellipsis button to open XPath Builder to create an XPath expression that resolves to a process data location that holds the value you want to use.
Test Value:: A literal value for the parameter to use when testing the procedure call.

Test

Click the Test button to test the call to the stored procedure and to see the results.

Note: Testing executes the stored procedure on the database. Test only in a development environment.

About SQL Statement Info Editor

Use the SQL Statement Info Editor dialog box to create and test SQL statements that you want to execute on the database server. The SQL statements can include XPath expressions as well as parameter values.

SQL Statement: Type the SQL statement in the SQL Statement box. If you want to use a parameter in a SQL statement, use a question mark (?) as a placeholder for the parameter. (See Include parameters.)
Insert a dynamic process data: Opens XPath Builder.
Use Parameterized Query: Enables the use of parameters.
Index, Type, Value, Test Value: Enter parameter definitions.
Test Results: Testing area for the statement.

You can also perform the following tasks when creating SQL statements:

Include XPath expressions
Define parameters
Test

Include XPath expressions

You can include references to process data in SQL statements using XPath expressions. In SQL statements, XPath expressions must appear inside braces and between dollar signs, as in {$XPath$}.

For example, the following SQL statement uses a value stored in the process variable named first_name as the condition for retrieving data from columna of table1 in the database:

SELECT columna FROM table1 WHERE columna LIKE  
'{$ /process_data/@first_name$}%'

To create an XPath expression using XPath Builder, click the ellipsis button below the SQL Statement box. The expression is inserted in the statement at the location of the cursor.

Include parameters

To use parameters in SQL statements, place a question mark (?) in the SQL statement to represent the parameters.

For example, the following SQL statement uses a parameter value as the condition for retrieving data from columna of table1 in the database:

SELECT columna FROM table1 WHERE columna LIKE (?)

For each question mark that you place in the statement, define the parameter in the parameter table. You must also select Use Parameterized Statement.

To define a parameter, add a row to the parameter table. The order in which the parameters are defined in the table is the order in which they appear in the statement. To add a row to the table, click the + button and then specify values in each column.

Index:: An index that identifies the parameter. This value is generated automatically when the row is added to the table.
Type:: The type of data that the parameter holds. Click the cell in the Type column and then select the data type from the list.
Value:: The value to use for the parameter. Click the cell in the Value column and either type the value or click the ellipsis button to open XPath Builder to create an XPath expression that resolves to a process data location that holds the value you want to use.
Test Value:: A literal value to use for the parameter when testing the statement. For example, for the statement SELECT columna FROM table1 WHERE columna LIKE (?), the test value could be A%.

Test

Click the Test button to test the SQL statement and to see the results.

Note: Testing executes the SQL statement on the database. Test only in a development environment.

About XML Document Info Editor

Use XML Document Info Editor dialog box to define the elements of the XML document that is used to return the results set of a SQL query.

Root Element Name

A string value that represents the name of the root element of the XML document. If no value is specified, root is used as the element name.

Repeating Element Name

A string value that represents the name of the XML element to use to contain the information from a row in the result set. The XML document includes one of these elements for each row in the result set. If not value is specified, element is used as the element name.

Column Name Mappings

Use this list to provide the names of the XML elements that store data from database table columns. Each row in the table defines the name of an XML element and the database column that it is associated with.

You use the list to specify values for the following properties.

Index:

An integer value that identifies the column/element pair.

Column Name:

The name of the database table column that the SQL query retrieves data from. Click the cell and type the column name.

Element Name:

(Optional) The name of the XML element to use to contain the data from the table column. Click the cell and type the element name. If you do not specify element names, the column names from the database tables are used as the XML element names.

You can use the Parse Query and Process Metadata buttons to automatically fill the Index and Column Name columns with values:

Click Parse Query to determine column names from the SQL statement that is specified in the SQL Statement property of the Query For Multiple Rows As XML operation. (See Query for Multiple Rows as XML operation.)

You can use this button if the SQL statement explicitly names the column, for example SELECT column1, column2 FROM table1. This button does not work for statements such as SELECT * FROM table1, or SELECT concat(a,b) AS FULLNAME FROM table1.
Click Process Metadata to retrieve column names from the database server. A request is sent to the database server to process the query and return metadata about the query. The column names are determined from the returned metadata.

After you automatically fill the Index and Column Name columns, you can specify the associated XML element names in the Element Name column.

If no value is set for the SQL Statement property of the Query For Multiple Rows As XML operation, or if neither the Parse Query or the Process Metadata buttons successfully populate the Index and Column Name columns, you need to populate these columns manually. For each column in the result set, click the + button to add a row to the list. After the row is added, you can populate the row with values.

Example

The following SQL statement is used for the database query:

SELECT column1, column2 FROM table1.

No value for Root element Name is specified, so that the name of the root element of the XML is root. No value for Repeating Element Name is specified, so that the name of the element that contains a row of data from the result set is element.

The column names are used as the XML elements that contain the column data. If the query returns a result set with three rows, the XML document has the following structure.

<root> 
    <element> 
        <column1>row 1 column1 value</column1> 
        <column2>row 1 column2 value</column2> 
    </element> 
    <element> 
        <column1>row2 column1 value</column1> 
        <column2>row2 column2 value</column2> 
    </element> 
    <element> 
        <column1>row3 column1 value</column1> 
        <column2>row3 column2 value</column2> 
    </element> 
</root>

JDBC exceptions

The JDBC service provides the following exceptions for throwing exception events.

JDBCConnectionException

Thrown when a connection to the database server cannot be established or is lost. The JDBC service configuration may be incorrect, or the database server may be offline.

JDBCIllegalParameterException

Thrown when the SQL statement contains a parameter that is not supported.

SQLException

Thrown when an error occurs during execution of the SQL statement.

JNDIContextUnavailableException

Thrown when the JNDI name of the data source to use to connect to the database server is invalid.