External Variable: JDBC Mapping

Overview¶

JDBC External Variables allow access to data stored in external JDBC data sources via the external variable BPEL extension. That is, assigning to a variable has the effect of inserting or updating a row in the database, while reading a variable, has the effect of selecting a row from the database. This extension is capable of handling standard non-LOB SQL data types. It is limited to binding one variable with a single row.

Data Binding¶

The JDBC external variable mechanism is not intended to be a full-featured JDBC-XML mapping engine. Hence, there are some limitations on how variables can be bound to date. The most prominent of these is that a variable may only be bound to a single row. Furthermore, the type of the variable being bound must satisfy the following criteria:

• the variable must be an "element" type variable
• the name of the element can be anything
• the element must contain child elements corresponding to the columns being retrieved, each with a unique local name
• the child elements described above must all be of a simple type
• an xsi:nil attribute is used on the child elements to indicate that the value should be mapped to a database NULL

Deployment Descriptor Format¶

Every external variable referenced in the BPEL process description must be configured in the deployment descriptor for the process. The following is an example deployment descriptor containing a JDBC external variable:

<deploy xmlns="http://www.apache.org/ode/schemas/dd/2007/03"
    xmlns:pns="http://ode/bpel/unit-test"
    xmlns:wns="http://ode/bpel/unit-test.wsdl"
    xmlns:xvar="http://ode.apache.org/externalVariables">

    <process name="pns:HelloWorld2">
        <active>true</active>
        <provide partnerLink="helloPartnerLink">
            <service name="wns:HelloService" port="HelloPort"/>
        </provide>

        <xvar:externalVariable id="evar1" >
            <jdbc:jdbc xmlns:jdbc="http://ode.apache.org/externalVariables/jdbc"
                       xmlns="http://ode.apache.org/externalVariables/jdbc">
                <datasource-ref>testds</datasource-ref>
                <table>extvartable1</table>
                <column name="id1"
                    key="yes"
                    generator="sequence" />
                <column name="id2"
                    column-name="_id2_"
                    key="yes"
                    generator="uuid" />
                <column name="pid"
                    generator="pid" />
                <column name="iid"
                    generator="iid" />
                <column name="cts"
                    generator="ctimestamp" />
                <column name="uts"
                    generator="utimestamp" />
                <column name="foo" />
                <column name="bar" />
            </jdbc:jdbc>
        </xvar:externalVariable>

    </process>
</deploy>

The following sections describe each of the elements.

External Variable Configuration Element¶

<xvar:externalVariable xmlns:xvar="http://ode.apache.org/externalVariables"
                       id="_variable-id_">
  [external-var-config]
</xvar:externalVariable>

This is the deployment-descriptor extension element used to for configure an external variable. The id attribute provides a unique name for each variable. This element must have exactly one child element containing the external variable engine-specific configuration for the variable.

JDBC Engine Configuration Element¶

<jdbc:jdbc xmlns:jdbc="http://ode.apache.org/externalVariables/jdbc">
  [jdbc-specific-external-var-config]
</jdbc:jdbc>

This element contains the JDBC-specific configuration of the external variable. The children of this element are described below. It also is used to identify to the runtime the particular EVE implementation to be used ( i.e. the QNAME of the element is the engine identifier).

JDBC Config: Data Source Reference¶

<datasource-ref> datasource-ref-name </datasource-ref>

This specifies that the name of the data source that should be used to connect to the database. The binding between data source names and actual data sources is configured in the server configuration. Alternatively, JNDI may be used to locate the data source, see below.

JDBC Config: Data Source JNDI Reference¶

<datasource-jndi> datasource-jndi-name </datasource-jndi>

This is used to specify the JNDI name of the data source that should be used to connect to the database.

JDBC Config: Table Name¶

<table> table-name </table>

This element is used to specify the name of the table/view which will be bound to the variable.

JDBC Config: Column Configuration¶

<column name="_name_" column-name="_db-column-name_"? key="yes|no"? 
        generator="sequence|uuid|expression|pid|iid|ctimestamp|utimestamp"? 
        sql="_sql-expression_" />

These elements are used to configure the columns in the table that are bound to the BPEL external variable. A separate element must exist for each column that is to be bound.

The name attribute must be unique for each column. This attribute corresponds to the name of the element to which the column is bound. By default, the name attribute also identifies the name of the database column that is bound. However, this can be overriden by specifying the database column name in the column-name attribute. When set to "yes", the key attribute indicates that the columns is part of the key for the table. Key columns are used in the WHERE clause of SQL statements to select a particular row. More than one column may be specified as a key column permitting compound keys.

The optional generator attribute can be used to specify a value generator for the column. The following generators are supported:

• sequence - column is a sequence column, the value will be generated by the database on an INSERT
• uuid - place a server-generated UUID in the column
• expression - use a custom SQL expression to generate the value (useful for ORACLE SEQ.NEXTVAL)
• pid - place the process id in the column
• iid - place the instance id in the column
• ctimestamp - place a timestamp in this column corresponding to the time of insert
• utimestamp - place a timestamp in this column every time the row is updated

The expression attribute is used in conjunction with the "expression" value generator.

JDBC Config: Initialization Mode¶

<init mode="update|insert|update-insert|delete-insert">

This element specifies how variable initializations should be handled. The following modes are supported:

• update - always use SQL UPDATE. With this mode, it is necessary that the external variable exist in the database beforehand.
• insert - always use SQL INSERT. With this mode, it is necessary that the external variable not exist in the database beforehand.
• update-insert - first attempt an SQL UPDATE, if that fails, do an SQL INSERT. This is the default mode
• delete-insert - first DELETE the row, then INSERT a new one.

Note, that if the key contains a generated by sequence (see previous section), then the insert mode must be used.