Orbeon Search

Comments? Feedback?

This wiki does not yet support public comments (a limitation of Google Sites), so we encourage you to post your comments either:

On Twitter by responding to @orbeon.

On our community mailing list: subscribe sending an email to ops-users-subscribe@ow2.org (content of subject/body doesn't matter), you'll get a response with the email to use to send your message to the community mailing list.

Recent site activity

Advanced: Database Services

Contents

  1. 1 Availability
  2. 2 Configuration

Availability

This is an Orbeon Forms PE feature.

Configuration

The Database Service Editor allows you to create simple SQL services. The idea is that you create a template for a SQL query or update, which runs against a Java datasource.


This is the meaning of the fields:

  • Service Name. This is the name of the service, as seen by Form Builder. Must start with a letter, and may not contain spaces.
  • Datasource. JNDI name of the datasource (without leading jdbc/).
  • SQL Query. Template for the SQL query to run.
You write the SQL query as it will run in your database, with the exception that parameters (if any) must have placeholders in the form of XML elements. Those look like this:

<sql:param type="xs:string" select=""/>

The "type" attribute corresponds to the SQL type to use. Use:
  • xs:string for a string
  • xs:integer for an integer
  • Etc.
The "select' attribute must be left blank. It is filled-out by the action editor when the service is called.

The database service returns data in XML format as follows:
  • A <response> root element
  • For each row returned, a <row> element
  • Within each <row> element, one element per column, named after each column returned by the query
NOTE: All database column names are automatically converted to lowercase first! This means that the resulting response will contain all-lowercase XML element names. When using XPath expressions against the response, make sure to use lowercase as both XML and XPath are cases-sensitive.

For example, if the query returns columns called:
  • ID
  • First_Name
  • last_name
  • phone_number
The response might look like:

<response>
    <row>
        <id>12</id>
        <first_name>John</first_name>
        <last_name>Smith</last_name>
        <phone_number>(555) 123 4567</phone_number>
    </row>
    <row>
        <id>34</id>
        <first_name>Alice</first_name>
        <last_name>Martin</last_name>
        <phone_number>(555) 987 6543</phone_number>
    </row>
</response>

Like for HTTP Services, once your service is defined, the Save buttons saves it to the form. You can come back to it and modify it later by clicking on the Edit icon next to the service name. You can also delete the service using the trashcan icon.

NOTE: It has been reported that using column aliases doesn't work. For example, in a SQL statements like:

SELECT foo AS bar FROM my_table

The response might contain foo instead of the expected bar.

As of 2011-08-16, we have committed a fix for this. Please let us know if aliases still don't work for you.