# Database services ## Overview By using *database services*, a [PE feature](https://www.orbeon.com/pricing), 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 ``. * It contains one nested `` 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 ``. 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. ![Dropdown](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzNqdB3FwrZMusPE%2Fdatabase-services-dropdown.png?generation=1528743351489883\&alt=media) ### 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 `` for Orbeon Forms if you don't have one already, and inside it add a `` pointing to your database. On Tomcat, you also need to put the database JDBC driver in Tomcat's `lib` directory. ![Connect to the database](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzNtDt_aoOt2cFza%2Fdatabase-services-connect-db.png?generation=1528743349725789\&alt=media) ### 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: ![Database Service Editor](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LEkBiSDvlXs3VWiO0Zu%2Fuploads%2Fgit-blob-0942d6608f9ee3f2aa38801dec271f24594f0ecd%2Fdatabase-services-example.png?alt=media) Example response: ```xml d009 Customer Service d005 Development ``` This diagram summarizes the mapping: ![Mapping of columns to XML elements](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzNvIKleyzuJTLEg%2Fdatabase-services-run-query.png?generation=1528743337270607\&alt=media) ### 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: ![Action general settings](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LEkBiSDvlXs3VWiO0Zu%2Fuploads%2Fgit-blob-c37798c604d0d52115b4692f8564e46a0fb83f0f%2Faction-itemset-example-general.png?alt=media) 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 ``, 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: ![Action response](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LEkBiSDvlXs3VWiO0Zu%2Fuploads%2Fgit-blob-26010143703fc9008759d4afeca2e39b3557871b%2Faction-itemset-example-response.png?alt=media) This diagram summarizes the mapping: ![Link to the dropdown](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzNx3LSR0Xowy_9-%2Fdatabase-services-link-to-dropdown.png?generation=1528743350132293\&alt=media) ### 4. Select a value Finally, when your form runs and users make a selection in the dropdown, the *value*, here the content of the `` element, is used to populate the element corresponding to the field in the form data. ## Populating fields using another field value 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 [connection with the database](#1-connect-with-the-database), so let's start by seeing how we can use the value of a field in a SQL query. ![Populate fields](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzO08Bq2LaVhV81h%2Fdatabase-services-poulate-fields.png?generation=1528743349698306\&alt=media) ### 1. Set a service parameter Your SQL query can contain *parameters*. Those look like: ``. 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. ![Set service parameter](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzO2tbjM9xocY4s1%2Fdatabase-services-set-service-parameter.png?generation=1528743361474226\&alt=media) ### 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. ![Set control values](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2F-LEkBmMpnaxnftLGDglW%2F-LEkBzO4zmrXNw_WeAfU%2Fdatabase-services-set-control-values.png?generation=1528743356234389\&alt=media) ## 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. ![Pre-populated fields](https://4129616727-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEkBiSDvlXs3VWiO0Zu%2Fsync%2F33554898276b75da962ca58c7e637581a9c2f188.png?generation=1616107270054605\&alt=media) 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. ```sql SELECT * FROM employees WHERE employee_id = ``` You can get the [source of the form described above](https://gist.github.com/orbeon/1e25851b7219f765a2cde373a72e9dea), 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`. ## See also * Blog post: [Making sense of Form Runner Actions](https://www.orbeon.com/2024/09/making-sense-form-runner-actions) * [HTTP services](https://doc.orbeon.com/form-builder/advanced/services-and-actions/http-services) * [Simple Actions](https://doc.orbeon.com/form-builder/advanced/services-and-actions/broken-reference) * [Synchronizing repeated content](https://doc.orbeon.com/form-builder/advanced/services-and-actions/synchronize-repeated-content) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://doc.orbeon.com/form-builder/advanced/services-and-actions/database-services.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.