|
The
JDBC service enables processes to interact with databases to accomplish
the following tasks:
For information about using the JDBC service, see
LiveCycle ES2.5 Services
.
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
.
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
.
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
.
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 ES2 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.
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
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
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.
|
|
|
