Orbeon Forms
  • Getting started
  • Installation
    • Logging
    • Configuration banner
    • Docker
    • Azure
    • Tomcat
    • WildFly
    • WebSphere
    • WebLogic
    • GlassFish
    • Caches
    • Replication
    • Upgrading
  • Configuration
    • Properties
      • General
        • HTTP client
      • Form Runner
        • Detail page
          • Attachments
          • Email
          • PDF
          • Table of contents
        • Persistence
        • Summary page
      • Form Builder
      • XForms
    • Advanced
      • Workflows
      • Session management
      • State handling
      • Client-side error handling
      • Clustering and High Availability
      • Configuring a Form Runner eXist database
      • Creating a production WAR
      • Environments
      • JavaScript and CSS assets
      • Limiter filter
      • Run modes
      • Security
        • Content-Security-Policy header
      • SAP Hybris Module
      • XForms logging
    • Troubleshooting
      • Troubleshooting with the orbeon.log
      • Memory and threads
      • Relational database logging
      • Misc
  • Form Builder
    • Form settings
      • Time window
    • Form editor
      • Form area
      • Toolbox
      • Buttons bar
      • Control settings
      • Dependent fields and sections
      • Validation
      • Choices editor
      • Publishing
      • Cut, copy and paste
      • Section and grid settings
      • Section settings
      • Grid settings
      • Quick control search
      • Repeat settings
      • Repeated grids
      • Undo and redo
      • Keyboard shortcuts
    • Formulas
      • Examples of formulas
      • Formulas inspector
      • Formulas console
    • Summary page
    • Form localization
    • Advanced
      • Edit source
      • Services and actions
        • HTTP services
        • Database services
        • Simple Actions
        • Action Syntax
        • Action Syntax examples
        • Synchronizing repeated content
      • Testing a form in web mode
      • Testing PDF production
      • Testing offline functionality
      • Email settings
      • Field-level encryption
      • Messages
      • Section templates
      • Template syntax
      • XML Schemas support
      • Extensibility
        • Extension API
        • Integration
        • Toolbox component metadata
  • Form Runner
    • Overview
      • Terminology
    • Pages
      • Landing page
      • Published Forms page
      • Forms Admin page
      • Summary page
    • Components
      • Alert dialog
      • Attachment
      • Autocomplete
      • Captcha
      • Character counter
      • Checkbox input
      • Currency
      • Date
      • Dropdown date
      • Static and dynamic dropdown
      • Error summary
      • Grid
      • Handwritten signature
      • Hidden field
      • Image
      • Image annotation
      • Image attachment
      • Number
      • Open selection
      • Repeater
      • Formatted Text / Rich Text Editor
      • Section
      • Single-selection tree
      • Source code editor
      • Time
      • US phone
      • US state
      • Video
      • Video attachment
      • Wizard
      • XForms inspector
      • Yes/No answer
    • Features
      • Automatic calculations dependencies
      • Datasets
      • Excel and XML import
      • Excel and XML export
      • Summary page Excel Export
      • Form definitions and form data Zip Export
      • Purging historical data
      • Lease
      • Localization
      • Supported languages
      • Mobile support
      • Multitenancy
      • Form Runner navigation bar
      • PDF production
        • Automatic PDF
        • Automatic PDF header and footer configuration
        • PDF templates
      • Responsive design
      • Revision history
      • S3 storage
      • Simple data migration
      • TIFF production
      • Versioning
      • Wizard view
      • Workflow stage
    • Persistence
      • Using a relational database
      • Relational database schema
      • Purging old data using SQL
      • Auditing
      • Autosave
      • Database support
      • Flat view
    • Linking and embedding
      • Linking
      • Java Embedding API
      • JavaScript Embedding API
      • Liferay full portlet
      • Liferay proxy portlet
      • Securing Form Runner access
      • Form Runner offline embedding API
      • Angular component
      • React component
    • Access control and permissions
      • Users
      • Login & Logout
      • Deployed forms
      • Form fields
      • Editing forms
      • Owner and group member
      • Organizations
      • Scenarios
      • Token-based permissions
    • Styling
      • CSS
      • Grids CSS
      • Automatic PDF styling and CSS
    • APIs
      • Authentication of server-side service APIs
      • Persistence API
        • CRUD API
        • Search API
        • List form data attachments API
        • Form Metadata API
        • Lease API
        • Reindexing API
        • Caching
        • Versioning
        • Revision History API
        • Zip Export API
        • Custom persistence providers
      • Other APIs
        • Connection context API
        • Duplicate form data API
        • File scan API
        • Form Runner JavaScript API
        • Generate XML Schema API
        • PDF API
        • Publish form definition API
        • Run form in the background API
      • Data formats
        • Form data
        • Date and time
        • Form definition
    • Architecture and integration
      • Architecture
      • Access form data
      • Integration
    • Advanced
      • Buttons and processes
        • Simple process syntax
        • Core actions
        • Form Runner actions
          • Save action
          • Send action
          • Email action
        • XForms actions
        • Predefined buttons, processes and dialogs
        • Summary page buttons and processes
      • Custom dialogs/model logic
      • Services
      • Singleton form
      • Monitoring HTTP requests
  • XForms
    • Core
      • Attribute Value Templates (AVTs)
      • Binds
      • Validation
      • Variables
      • Keyboard focus
      • XForms JavaScript API
      • Error handling
        • Detailed behavior
      • Model-Bind variables
      • XForms 2.0 support
    • Events
      • Standard support
      • UI refresh events
      • Keyboard events
      • Extension events
      • Extension context information
      • Other event extensions
    • Actions
      • Repeat, insert and delete
      • Scripting actions
      • Extensions
    • Controls
      • Label, hint, help
      • Input
      • Output
      • Text area
      • Button
      • Upload
      • Dialog
    • Submission
      • Standard support
      • JSON support
      • Asynchronous submissions
      • Caching extension
      • Other submission extensions
    • XPath
      • Type annotations
      • Expression analysis
      • Tips
      • Compatibility
      • Standard functions
      • Maps and arrays Functions
      • Extension functions
        • Core functions
        • Utility functions
        • Model functions
        • Controls functions
        • XML functions
        • JSON functions
        • HTTP functions
        • Form Runner functions
        • Other functions
        • Deprecated functions
    • XBL components
      • FAQ
      • Guide
        • XBL Tutorial
        • Bindings
        • XForms models
        • Including content
        • Event handling
        • Conventions
        • Map XBL example
        • Learning from existing components
      • Advanced topics
        • XBL Modes
        • JavaScript companion classes
        • XBL library
        • Extensions
        • Attachment controls
    • XForms tutorial
      • Introduction
      • Installation
      • The Hello application
      • The Bookcast application
        • The basic app
        • Database access
        • Polishing the app
        • Adding a feed
    • Using XForms from Java apps
  • XML Platform
    • Page Flow Controller
      • Basics
      • XML submission
      • Navigating between pages
      • Paths and matchers
      • Other configuration elements
      • Typical combinations of page model and page view
      • Examples
      • Authorizing pages and services
    • Processors
      • URL generator
      • Request generator
      • PDF to image converter
    • Resources
      • Resource managers
      • Setting up an external resources directory
    • Other
      • Binary and text documents
  • FAQ
    • Licensing
    • PE and Dev Support
    • Form Builder and Form Runner
    • Resources and support
    • Other technical questions
  • Contributors
    • Automated tests
    • Building Orbeon Forms
    • Localizing Orbeon Forms
    • Validation functions
    • Contributor License Agreement
  • Release notes
    • Orbeon Forms 2022.1.9
    • Orbeon Forms 2024.1.1
    • Orbeon Forms 2023.1.7
    • Orbeon Forms 2024.1
    • Orbeon Forms 2023.1.6
    • Orbeon Forms 2023.1.5
    • Orbeon Forms 2021.1.11
    • Orbeon Forms 2022.1.8
    • Orbeon Forms 2023.1.4
    • Orbeon Forms 2023.1.3
    • Orbeon Forms 2023.1.2
    • Orbeon Forms 2022.1.7
    • Orbeon Forms 2023.1.1
    • Orbeon Forms 2023.1
    • Orbeon Forms 2022.1.6
    • Orbeon Forms 2021.1.10
    • Orbeon Forms 2022.1.5
    • Orbeon Forms 2021.1.9
    • Orbeon Forms 2022.1.4
    • Orbeon Forms 2022.1.3
    • Orbeon Forms 2021.1.8
    • Orbeon Forms 2022.1.2
    • Orbeon Forms 2022.1.1
    • Orbeon Forms 2022.1
    • Orbeon Forms 2021.1.7
    • Orbeon Forms 2021.1.6
    • Orbeon Forms 2021.1.5
    • Orbeon Forms 2021.1.4
    • Orbeon Forms 2021.1.3
    • Orbeon Forms 2021.1.2
    • Orbeon Forms 2021.1.1
    • Orbeon Forms 2021.1
    • Orbeon Forms 2020.1.6
    • Orbeon Forms 2019.2.4
    • Orbeon Forms 2019.1.2
    • Orbeon Forms 2018.2.5
    • Orbeon Forms 2018.1.4
    • Orbeon Forms 2020.1.5
    • Orbeon Forms 2020.1.4
    • Orbeon Forms 2020.1.3
    • Orbeon Forms 2020.1.2
    • Orbeon Forms 2019.2.3
    • Orbeon Forms 2020.1.1
    • Orbeon Forms 2020.1
    • Orbeon Forms 2019.2.2
    • Orbeon Forms 2019.2.1
    • Orbeon Forms 2019.1.1
    • Orbeon Forms 2019.2
    • Orbeon Forms 2019.1
    • Orbeon Forms 2018.2.4
  • Release history
  • Use cases
  • Product roadmap
  • Index of features
Powered by GitBook
On this page
  • Overview
  • Format of database query responses
  • Populating a dropdown
  • 1. Connect with the database
  • 2. Write the SQL query
  • 3. Link the dropdown
  • 4. Select a value
  • Populating fields using another field value
  • 1. Set a service parameter
  • 2. Set control values
  • Example: fields pre-population
  • See also
  1. Form Builder
  2. Advanced
  3. Services and actions

Database services

PreviousHTTP servicesNextSimple Actions

Last updated 7 months ago

Overview

By using database services, a , you can use data stored in any table of a relational database, for instance to dynamically populate a dropdown, or to pre-populate fields based on a value entered by users.

Format of database query responses

A SQL query, when successful, returns an XML document with the following format:

  • A root element which is always <response>.

  • It contains one nested <row> element per row.

  • Each such element in turn contains one element per selected column, derived from the column name:

    • The name is converted to lowercase.

    • Underscores (_) replaced by dashes (-).

For example, values for the dept_no column end up in <dept-no>.

See below for concrete examples.

Populating a dropdown

In what follows, we'll see how you can populate a Department dropdown in your form using values stored in an departments table of your relational database.

1. Connect with the database

You start, in Form Builder, by creating a new database service, clicking Add in the left sidebar under Database Services. This opens the Database Service Editor.

Under Datasource you type the name of a datasource you setup in your application server. This is the the JNDI name of the datasource, without the jdbc/ part. If you type employees, Orbeon Forms will look for java:comp/env/jdbc/employees.

If you're using Tomcat, the simplest way of setting up a datasource is to edit Tomcat's server.xml, there add a <Context> for Orbeon Forms if you don't have one already, and inside it add a <Resource> pointing to your database. On Tomcat, you also need to put the database JDBC driver in Tomcat's lib directory.

2. Write the SQL query

Still in the Database Service Editor, you write the SQL query to run in the database. When that query runs, Orbeon Forms creates an XML document with the returned data, and you'll be referring to parts of that document when linking the database service to a specific dropdown.

The user interface in Form Builder looks like this:

Example response:

<response>
    <row>
        <dept-no>d009</dept-no>
        <dept-name>Customer Service</dept-name>
    </row>
    <row>
        <dept-no>d005</dept-no>
        <dept-name>Development</dept-name>
    </row>
</response>

This diagram summarizes the mapping:

3. Link the dropdown

To "link" the service to the dropdown, you create a new action, set it to run on Form Load, call the service you earlier named list-departments. Here is how the user interface looks like:

With the result of the service you want to set the list of possible values of an a Department dropdown you have in the form. This is where you extract data from the XML document seen earlier, and you do this with 3 XPath expressions. The first points to the "rows", and will almost always be /response/row. Next, you need to tell Orbeon Forms where it can find, inside the <row>, the label (the text shown to users) and the value (what is stored in data when users make a selection).

Here is how the user interface looks like:

This diagram summarizes the mapping:

4. Select a value

Finally, when your form runs and users make a selection in the dropdown, the value, here the content of the <dept-no> element, is used to populate the element corresponding to the field in the form data.

Populating fields using another field value

1. Set a service parameter

Your SQL query can contain parameters. Those look like: <sql:param type="xs:string" select=""/>. The type attribute corresponds to the SQL type to use (e.g. xs:string, xs:decimal, …). The select attribute must be left blank; it is filled-out by the Actions Editor when the service is called.

When the query runs, the value of each parameter is set to the current value of a form field, and you define the mapping between parameter in the SQL query and form field when you create an action. That mapping is done by position; e.g. in the above query, you'll want to set parameter 1 to the control containing an employee id.

2. Set control values

Finally, you use the Set Response Control Values section of the Actions Editor dialog to extract the information you're interested in from the XML produced based on the result from the query, and populate fields in the form.

Example: fields pre-population

Say you will be passing a request parameter employee-id=100 to your form, and want to load data from a relational database about the employee with specified id, this to pre-populate some fields, such their first name, last name, hire data, and department, as shown in the screenshot below.

You can do this as follows, in Form Builder:

  1. Create a field to hold the employee id passed through the request parameter. You might want to put this field in another section, for instance named "(Internal)", that you hide from end users by setting in its Section Settings dialog, the Visibility to No. For the field, you can use a Hidden Field, or a Calculated Value if you'd like to see its value at runtime when debugging the form.

  2. In the Control Settings for that field, name it employee-id, and in the Formulas tab, set its Initial Value to xxf:get-request-parameter('employee-id').

  3. Create a database service that retrieves the information about the employee, with a statement like the one below.

  4. Create an action that, on form load, runs the database service, passing the value of the employee-id field, and setting the value of the first name, last name, hire data, and department fields to values returned by the SQL query.

SELECT *
  FROM employees
 WHERE employee_id = <sql:param type="xs:string" select=""/>

See also

  • Simple Actions

Say that when users enter a value in employee number, you want to lookup the corresponding employee in your database and populate other fields, First name and Last name, based on the information you find about that employee. We've already know how to establish a , so let's start by seeing how we can use the value of a field in a SQL query.

You can get the , and run it for yourself on the instance of Orbeon Forms deployed on demo.orbeon.orbeon, which has the employees table used by the above query. After you publish the form, try opening its /new page, passing the request parameter ?employee-id=100.

Blog post:

source of the form described above
Making sense of Form Runner Actions
HTTP services
Synchronizing repeated content
connection with the database
PE feature
Dropdown
Connect to the database
Database Service Editor
Mapping of columns to XML elements
Action general settings
Action response
Link to the dropdown
Populate fields
Set service parameter
Set control values
Pre-populated fields