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 properties
          • 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 dialog
      • 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
      • International Securities Identification Number (ISIN)
      • Legal Entity Identifier (LEI)
      • Number
      • Open selection
      • Repeater
      • Formatted Text / Rich Text Editor
      • Section
      • Single-selection tree
      • Source code editor
      • Time
      • US Employer Identification Number (EIN)
      • US phone
      • US Social Security Number (SSN)
      • 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
  • Database setup
  • Oracle database setup
  • MySQL database setup
  • SQL Server database setup
  • PostgreSQL database setup
  • Db2 database setup
  • Application server setup
  • Tomcat datasource configuration
  • Oracle application server setup
  • MySQL application server setup
  • SQL Server application server setup
  • PostgreSQL application server setup
  • DB2 application server setup
  • Orbeon Forms setup
  • Disabling the embedded SQLite provider
  • With a single schema
  • With multiple schemas
  • Flat view or table
  • Flat view support
  • Manual relational table setup with MySQL
  • Auditing
  • See also
  1. Form Runner
  2. Persistence

Using a relational database

PreviousPersistenceNextRelational database schema

Last updated 1 month ago

Overview

Out-of-the-box, Orbeon Forms includes an SQLite embedded database with multiple demo forms. This setup is designed for a quick start, but for development or production use, you should configure Orbeon Forms to utilize a separate relational database.

The setup for the relational persistence layers is a 3-step process. The first two steps are database specific, so please refer to the relevant subsections below.

  1. Database setup: You set up the database and create a schema with a few tables. This is typically be done by a DBA.

  2. Application server setup: You configure your application server to use the database.

  3. Orbeon Forms setup: You configure Orbeon Forms to use the relevant persistence layer.

You can find more about the relational database schema in the page.

Support for Oracle, SQL Server, and DB2 are features.

See also .

Database setup

Oracle database setup

  1. Make sure that Oracle's Database Character Set is set to AL32UTF8, also as . You can see you database parameters by running the following query: select * from nls_database_parameters, and the Database Character Set is identified by nls_characterset.

  2. Create a user/schema in Oracle, for instance with the commands below. In this example "all privileges" are granted to the newly created user/schema, which is not strictly required. You might want to fine-tune permissions on your system as appropriate. If you had already created this schema and that the definition changed, or that you want to restart from scratch for some other reason, you can first delete the schema with all the data it contains with drop user orbeon cascade.

    > sqlplus sys/password as sysdba
    SQL> CREATE USER ORBEON IDENTIFIED BY password ;
    SQL> GRANT ALL PRIVILEGES TO orbeon ;
    SQL> GRANT UNLIMITED TABLESPACE TO orbeon ;
  3. Run the following DDL to create or update your Orbeon database, and note that if upgrading to 2016.2, you need to .

Orbeon Forms version(s)
DDL to create from scratch
DDL to upgrade from previous format

2024.1 and newer

2022.1 to 2023.1

Oracle binary XML storage

With Oracle 11.2, XMLType values are stored by default using the binary XML storage. The binary XML storage has numerous benefits over the basic file storage. In many respect, it is the "proper" way to store XML. However, we found that Oracle fails to properly save some documents when the binary XML storage is used. In particular, when documents have attributes with long values (several thousands of characters), when retrieving the document, the value of some attributes is missing. For this reason, until this issue is solved by Oracle, we recommend you store XMLType values as "basic file", per the above DDL.

MySQL database setup

Supported MySQL versions

  • [SINCE Orbeon Forms 2018.2]

  • [UP TO Orbeon Forms 2018.1]

Unicode support

--character-set-server=utf8mb4
--collation-server=utf8mb4_0900_ai_ci

Setting up users and schema

  1. Create a new user orbeon. Orbeon Forms will connect to MySQL as that user.

    mysql -u root
    mysql> CREATE USER 'orbeon'@'%' IDENTIFIED BY 'orbeon';
  2. Create a new schema orbeon. This schema will contains the tables used to store your forms definitions and form data.

    mysql> CREATE schema orbeon;
  3. If needed, grant permissions, for example:

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'orbeon'@'%' WITH GRANT OPTION;
Orbeon Forms version(s)
DDL to create from scratch
DDL to upgrade from previous format

2024.1 and newer

2022.1 to 2023.1

SQL Server database setup

[SINCE Orbeon Forms 4.6]

Orbeon Forms relies on SQL Server's full-text search, which is included out-of-the-box in all SQL Server editions, except the Express and Express with Tools. If you're using one of those two editions of SQL Server, you might want to look into getting Express with Advanced Services.

Orbeon Forms version(s)
DDL to create from scratch
DDL to upgrade from previous format

2024.1 and newer

2023.1

2022.1

Using usql to create an orbeon database

You can create an orbeon database and run the DDL to create the tables and indices used by Orbeon Forms using the command-line tool usql. Follow these steps, making sure to replace PASSWORD with the password for the sa user and VERSION with your desired DDL version.

  • usql mssql://sa:PASSWORD@localhost/

  • CREATE DATABASE orbeon;

  • \q

  • usql mssql://sa:PASSWORD@localhost/orbeon

  • \i sqlserver-VERSION.sql

Verifying indexes

After upgrading your database, we recommend verifying that you have all the latest indexes. Use the following SQL query to get a list of indexes in your database. You can then compare that list to the indexes defined in the latest DDL linked above.

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS IndexColumns
FROM 
    sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
GROUP BY
    t.name, i.name, i.type_desc
ORDER BY 
    t.name, i.name;

PostgreSQL database setup

[SINCE Orbeon Forms 4.8]

Orbeon Forms version(s)
DDL to create from scratch
DDL to upgrade from previous format

2024.1 and newer

2023.1

2022.1

Db2 database setup

[SINCE Orbeon Forms 4.3]

The DDL includes indices since Orbeon Forms 2024.1. Those indices typically cannot be added if the tables are created in the default tablespace, so the 2024.1 DDL includes the declaration for a new tablespace with 32K page size. No upgrade script is provided, as tables need to be moved or recreated in the new tablespace. You may use DB2's SYSPROC.ADMIN_MOVE_TABLE stored procedure to help with this migration.

Orbeon Forms version(s)
DDL to create from scratch
DDL to upgrade from previous format

2024.1 and newer

See above

2022.1 to 2023.1

Application server setup

Tomcat datasource configuration

When using Tomcat, you set up a JDBC data source for your database instance either:

  • in server.xml (not recommended by the Tomcat documentation because it is less flexible)

  • or in a separate context XML file (such as orbeon.xml) for the web app (recommended).

In both cases, you define a <Resource> element containing several configuration attributes. We provide examples below for all the databases covered.

Here is a typical example:

<Resource
    name="jdbc/mysql"
    driverClassName="com.mysql.jdbc.Driver"

    auth="Container"
    type="javax.sql.DataSource"

    initialSize="3"
    maxActive="10"
    maxIdle="10"
    maxWait="30000"

    poolPreparedStatements="true"

    testOnBorrow="true"
    validationQuery="select 1"

    username="orbeon"
    password="orbeon"
    url="jdbc:mysql://localhost:3306/orbeon"/>

If you create a separate context file:

  1. Give it the name of your web app, for example orbeon.xml.

  2. Make sure the context is placed in the appropriate Tomcat folder.

Here is an example of a context file. Note the enclosing <Context> element:

<Context path="/orbeon">
    <Resource
        name="jdbc/mysql"
        driverClassName="com.mysql.jdbc.Driver"
    
        auth="Container"
        type="javax.sql.DataSource"
    
        initialSize="3"
        maxActive="10"
        maxIdle="10"
        maxWait="30000"
    
        poolPreparedStatements="true"
    
        testOnBorrow="true"
        validationQuery="select 1"
    
        username="orbeon"
        password="orbeon"
        url="jdbc:mysql://localhost:3306/orbeon"/>

</Context>

See also the following external links:

Oracle application server setup

General

Assuming:

  • ${HOST}: the host Oracle server is running on, for example oracle.acme.com

  • ${PORT}: the port the Oracle server is running on, for example 1521

  • ${INSTANCE}: the instance name, for example orcl

  • ${USERNAME}: the user/schema, for example orbeon

  • ${PASSWORD}: the password, for example password

Tomcat

Put the Oracle jar file that contains the JDBC driver (e.g. ojdbc6_g.jar, xdb.jar, and xmlparserv2.jar) in the appropriate directory for your application server (on Tomcat: common/lib or simply lib, depending on the version). If you don't already have it, you can download the Oracle JDBC driver from the Oracle site.

<Resource
    name="jdbc/oracle"
    driverClassName="oracle.jdbc.OracleDriver"

    auth="Container"
    type="javax.sql.DataSource"

    initialSize="3"
    maxActive="10"
    maxIdle="10"
    maxWait="30000"

    poolPreparedStatements="true"

    testOnBorrow="true"
    validationQuery="select * from dual"

    username="orbeon"
    password="orbeon"
    url="jdbc:oracle:thin:@//localhost:1521/globaldb"/>

JBoss 5.0.1 / JBoss EAP 5.0.1

  1. Place ojdbc5_g.jar into server/default/lib/.

  2. Create an Oracle datasource as server/default/deploy/oracle-ds.xml, for example:

    <datasources>
        <local-tx-datasource>
            <jndi-name>OracleDS</jndi-name>
            <connection-url>jdbc:oracle:thin:@//${HOST}:${PORT}/${INSTANCE}</connection-url>
            <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
            <user-name>${USERNAME}</user-name>
            <password>${PASSWORD}</password>
            <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
            <metadata>
                <type-mapping>Oracle9i</type-mapping>
            </metadata>
        </local-tx-datasource>
    </datasources>
  3. Update WEB-INF/jboss-web.xml to:

    <jboss-web>
        <resource-ref>
            <res-ref-name>jdbc/oracle</res-ref-name>
            <jndi-name>java:/OracleDS</jndi-name>
        </resource-ref>
    </jboss-web>

MySQL application server setup

  1. Copy it in the appropriate directory for your application server (on Tomcat: common/lib or simply lib, depending on the version).

  2. <Resource
        name="jdbc/mysql"
        driverClassName="com.mysql.jdbc.Driver"
    
        auth="Container"
        type="javax.sql.DataSource"
    
        initialSize="3"
        maxActive="10"
        maxIdle="10"
        maxWait="30000"
    
        poolPreparedStatements="true"
    
        testOnBorrow="true"
        validationQuery="select 1"
    
        username="orbeon"
        password="orbeon"
        url="jdbc:mysql://localhost:3306/orbeon?useUnicode=true&amp;characterEncoding=UTF8"/>

SQL Server application server setup

[SINCE Orbeon Forms 4.6]

  1. Uncompress the zip file, and copy the sqljdbc4.jar it contains to the appropriate directory for your application server (on Tomcat: common/lib or simply lib with newer Tomcat version).

  2. <Resource
        name="jdbc/sqlserver"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    
        auth="Container"
        type="javax.sql.DataSource"
    
        initialSize="3"
        maxActive="10"
        maxIdle="10"
        maxWait="30000"
    
        poolPreparedStatements="true"
    
        validationQuery="select 1"
        testOnBorrow="true"
    
        username="orbeon"
        password="orbeon"
        url="jdbc:sqlserver://server;databaseName=orbeon;trustServerCertificate=true"/>

PostgreSQL application server setup

[SINCE Orbeon Forms 4.8]

  1. Copy the driver jar to the appropriate directory for your application server (on Tomcat: common/lib or simply lib, depending on the version).

  2. <Resource
        name="jdbc/postgresql"
        driverClassName="org.postgresql.Driver"
    
        auth="Container"
        type="javax.sql.DataSource"
    
        initialSize="3"
        maxActive="10"
        maxIdle="10"
        maxWait="30000"
    
        poolPreparedStatements="true"
    
        validationQuery="select 1"
        testOnBorrow="true"
    
        username="orbeon"
        password="orbeon"
        url="jdbc:postgresql://server:5432/database?useUnicode=true&amp;characterEncoding=UTF8&amp;socketTimeout=30&amp;tcpKeepAlive=true"/>

    The following attributes of the datasource need to be configured as needed:

    • username

    • password

    • url: including the server and database parts of the path

DB2 application server setup

[SINCE Orbeon Forms 4.3]

  1. Uncompress the zip file, and copy the db2jcc4.jar it contains to the appropriate directory for your application server (on Tomcat: common/lib or simply lib, depending on the version).

  2. <Resource
        name="jdbc/db2"
        driverClassName="com.ibm.db2.jcc.DB2Driver"
    
        auth="Container"
        type="javax.sql.DataSource"
    
        initialSize="3"
        maxActive="10"
        maxIdle="10"
        maxWait="30000"
    
        poolPreparedStatements="true"
    
        validationQuery="select 1 from sysibm.sysdummy1"
        testOnBorrow="true"
    
        username="db2inst1"
        password="password"
        url="jdbc:db2://localhost:50000/sample"/>

Orbeon Forms setup

Disabling the embedded SQLite provider

Orbeon Forms comes with an embedded SQLite database in order to show demo forms, and also to allow to quickly get started with Orbeon Forms. When configuring your own provider or providers, you should disable the embedded SQLite database. To do so, add the following properties to your properties-local.xml, assuming here that your new persistence provider is called postgresql:

<property
    as="xs:boolean"
    name="oxf.fr.persistence.sqlite.active"
    value="false"/>
<property 
    as="xs:string"  
    name="oxf.fr.persistence.provider.orbeon.*.form"                     
    value="postgresql"/>
<property 
    as="xs:string"  
    name="oxf.fr.persistence.provider.orbeon-features.*.form"                     
    value="postgresql"/>
<property
    as="xs:string"
    name="oxf.fr.landing.cards"
    value="quick-links published-forms form-builder-forms"/>

With a single schema

<property
    as="xs:string"
    name="oxf.fr.persistence.provider.*.*.*"
    value="postgresql"/>

With multiple schemas

The single schema configuration described in the previous section uses the predefined oracle and mysql providers. To use multiple schemas you need to define you own provider names. For instance, assume that you have two apps, hr and finance, and would like both the form definition and data for those apps to be stored in two separate schemas:

  1. In your application server configuration, you setup two data sources ; let's call them hr-datasource and finance-datasource.

  2. In properties-local.xml, you use the following properties to define two providers hr and finance that you configure to use the desired persistence layer implementation (Oracle in this example) and data source:

    <!-- HR provider -->
    <property
        as="xs:anyURI"
        name="oxf.fr.persistence.hr.uri"
        value="/fr/service/oracle"/>
    <property
        as="xs:string"
        name="oxf.fr.persistence.hr.datasource"
        value="hr-datasource"/>
    
    <!-- Finance provider -->
    <property
        as="xs:anyURI"
        name="oxf.fr.persistence.finance.uri"
        value="/fr/service/oracle"/>
    <property
        as="xs:string"
        name="oxf.fr.persistence.finance.datasource"
        value="finance-datasource"/>
  3. Still in properties-local.xml, you map the hr and finance app to the respective provider:

    <property
        as="xs:string"
        name="oxf.fr.persistence.provider.hr.*.*"
        value="hr"/>
    <property
        as="xs:string"
        name="oxf.fr.persistence.provider.finance.*.*"
        value="finance"/>

Flat view or table

Orbeon Forms stores form data as XML in relational databases, which gives it a lot of flexibility. However, it might be harder for other tools to access this XML data. For this reason, you might want to provide other tools a way to access the XML data through another "flat" table or view that has one column per form field.

Flat view support

Manual relational table setup with MySQL

  • title corresponds to the title form field;

  • author corresponds to the author form field;

  • document_id corresponds to the column with the same name in orbeon_form_data.

Start by creating the bookshelf table:

create table bookshelf (
    document_id varchar(255),
    title  text,
    author text
);

Choose an appropriate type for your columns, depending on the maximum length for the fields. Then create a trigger, which will update your bookshelf table when form data is saved in orbeon_form_data:

delimiter |
create trigger bookshelf_trigger before insert on orbeon_form_data for each row begin
    if new.app = 'orbeon' and new.form = 'bookshelf' then
        delete from bookshelf where document_id = new.document_id;
        if new.deleted = 'N' and new.draft = 'N' then
            insert into bookshelf set document_id = new.document_id,
                title = extractValue(new.xml, '/form/details/title'),
                author = extractValue(new.xml, '/form/details/author');
        end if;
    end if;
end;
|

Since you are interested in data for Bookshelf form, which is in the app orbeon form bookshelf, the trigger only does something if new.app = 'orbeon' and new.form = 'bookshelf'. To enable auditing, the MySQL persistence layer never deletes or updates data; it only inserts new row. So your trigger only needs to be concerned about updates. On insert, you want to make sure you are not creating duplicates in your bookshelf table, hence the delete statement. When a newly inserted row has delete = 'N', this indicates that a user deleted that document, in which case you don't want to insert a row in your bookshelf table, hence the if test.

Auditing

See also

For older DDL see .

MySQL 5.7 and MySQL 8: Since version 2018.2, Orbeon Forms uses the utf8mb4 character set instead of the utf8 character set. The reason being that MySQL's utf8 character set can only store UTF-8-encoded symbols that consist of 1 to 3 bytes, that is characters in the Unicode , which means that none of the characters in the , which include Emojis, could stored. However, the switch to the utf8mb4 character set will prevent you from creating some indexes on MySQL 5.6 where default key prefix limit is 767 bytes. Hence we recommend you use MySQL 5.7, which raised the index key prefix length limit to 3072 bytes for InnoDB tables. If in your situation upgrading to MySQL 5.7 isn't an option, you can explore , or changing the DDL that ships with Orbeon Forms to use utf8 instead of utf8mb4.

Minimum version: The MySQL persistence layer relies on that have been introduced in MySQL 5.1, so you need to be using the MySQL 5.1 (which was released in November 2008) or newer.

Recommended versions: However, we recommend you use MySQL 5.6.4 or newer, as it supports .

MySQL 5.7: With MySQL 5.7, since Orbeon Forms 2016.2, you must set the sql_mode to , or you might get errors while creating the database schema.

By default, the MySQL JDBC driver the character encoding and character collation set on the server. (included), the default character encoding is latin1 and the default collation latin1_swedish_ci. , the default character encoding is utf8mb4 and the default collation utf8mb4_0900_ai_ci. So, if you're using MySQL 5.7 or earlier, you must specify the following 2 parameters when starting MySQL:

Run the following DDL to create or update your Orbeon database, and note that if upgrading to 2016.2, you need to .

For older DDL see .

Run the following DDL to create or update your Orbeon database, and note that if upgrading to 2016.2, you need to .

For older DDL see .

Run the following DDL to create or update your Orbeon database, and note that if upgrading to 2016.2, you need to .

For older DDL see .

Run the following DDL to create or update your Orbeon database, and note that if upgrading to 2016.2, you need to .

For older DDL see .

Tomcat documentation:

Apache Commons documentation:

Blog post:

Your Resource element pointing to the your Oracle instance (see also above). In the example below, the Oracle server is running on localhost, the instance name is globaldb, and the user/schema is orbeon with password orbeon. Those values are highlighted in the configuration below, and you'll most likely want to change them to fit your setup.

Please follow the , but here are some steps that work for us in our test environment.

, called Connector/J, e.g. mysql-connector-java-5.1.39-bin.jar (latest version as of 2016-06-20)

Setup a JDBC data source for your MySQL schema (see also above). In the example below, the MySQL server is running on localhost port 3306, the schema is orbeon, the username/password is orbeon/orbeon. Those values are highlighted in the configuration below, and you'll most likely want to change them to fit your setup. Also, on the JDBC URL you're telling the MySQL driver to use Unicode and the UTF-8 encoding when talking to the database, which we highly recommend you to do in order to avoid encoding issues with non-ASCII characters.

(as of 2020-05-04, this is version 8.2 of the driver)

When using Java 11 or newer, you might need to add the JAXB API, which was present in earlier versions of Java. Download the JAR file from and place it in the same directory you placed the JDBC driver.

Set up the JDBC data source for your SQL Server instance (see also above). Example:

.

Setup the JDBC data source for your PostgreSQL instance (see also above). Example:

for the version of DB2 you're using.

Setup the JDBC data source for your DB2 instance (see also above). Example:

What follows applies to Orbeon Forms 4.0 and newer. For Orbeon Forms 3.9, see this .

In addition, since by removing the SQLite demo database you don't have the Orbeon Forms demo forms anymore, you should remove the demo forms tiles from the . You can do this by setting the following property:

In your properties-local.xml, you map an app / form / form type to the implementation of the persistence API you're using with the oxf.fr.persistence.provider.*.*.* . For instance, if using PostgreSQL, set the property to:

See .

Orbeon Forms doesn't provide a way to have a table or view automatically created for a form upon publishing in MySQL. However, you can do this manually. For instance, assume you want to create a "flat" bookshelf table for the sample . You want that table to have 3 columns:

See .

Relational database schema
Orbeon Forms PE
Removing the built-in SQLite database
recommended by Oracle
reindex your Orbeon database
Relational database schema
Basic Multilingual Plane
Supplementary Multilingual Plane
enabling the innodb_large_prefix configuration option on your MySQL 5.6
XML functions
storing fractional seconds
ALLOW_INVALID_DATES
uses
Up to MySQL 5.7
Since MySQL 8
reindex your Orbeon database
Relational database schema
reindex your Orbeon database
Relational database schema
reindex your Orbeon database
Relational database schema
reindex your Orbeon database
Relational database schema
The Tomcat JDBC Connection Pool
BasicDataSource Configuration Parameters
Configuring jdbc-pool for high-concurrency
Tomcat datasource configuration
JBoss documentation first
Download the MySQL JDBC driver
Tomcat datasource configuration
Download the Microsoft JDBC driver for SQL Server
Maven
Tomcat datasource configuration
Download the PostgreSQL JDBC driver
Tomcat datasource configuration
Download the DB2 JDBC driver
Tomcat datasource configuration
legacy documentation
[SINCE Orbeon Forms 2023.1]
Landing page
wildcard property
Flat View
bookshelf form
Auditing
Database support
Relational database schema
Auditing
Revision history
Purging historical data
Purging old data using SQL
Relational Database Logging
Removing the built-in SQLite database
oracle-2024_1.sql
oracle-2019_1-to-2024_1.sql
oracle-2019_1.sql
oracle-2018_2-to-2019_1.sql
mysql-2024_1.sql
mysql-2019_1-to-2024_1.sql
mysql-2019_1.sql
mysql-2018_2-to-2019_1.sql
sqlserver-2024_1.sql
sqlserver-2023_1-to-2024_1.sql
sqlserver-2023_1.sql
sqlserver-2019_1-to-2023_1.sql
sqlserver-2019_1.sql
sqlserver-2017_2-to-2019_1.sql
postgresql-2024_1.sql
postgresql-2023_1-to-2024_1.sql
postgresql-2023_1.sql
postgresql-2019_1-to-2023_1.sql
postgresql-2019_1.sql
postgresql-2018_2-to-2019_1.sql
db2-2024_1.sql
db2-2019_1.sql
db2-2017_2-to-2019_1.sql