Database Connector

LiveForms includes a database connector in order to get data from any JDBC database. Some uses for this could be to store data that can be accessed even after submitting a form, getting a large amount of data needed in multiple forms, or for getting data from another application’s database.

This section will cover the set-up of the database connector and some rule examples showing how to use it.

Getting the Connector Library

In the following example configuration, we will be connecting to a MySQL database. In order to connect to a database, the Tomcat server will need the connector library for that database. The connector .jar will need to be placed in the directory {Tomcat Home}\lib\.

Note

PostgreSQL’s driver, and the older jTDS driver for MS SQL Server, are already bundled inside liveforms.war (this is what the LiveForms application’s own database setup relies on), so no extra jar is needed in {Tomcat Home}\lib\ if you are connecting to one of those. Any other database - including MySQL/MariaDB, or SQL Server via Microsoft’s own current driver - needs its connector jar placed in {Tomcat Home}\lib\ as described here.

Below is the connector file for our database in the lib directory.

Location of the connector jar

Note

If you are connecting to the same database used by LiveForms, and that database’s driver is already bundled (see above), there is no separate library file to place - the database connector will use the same bundled driver already available to the application.

The next step is to create a configuration XML file which will hold the connection information and queries forms can use with the database. In our example installation, we will create the file in {Tomcat Home}\dbconfig\configuration.xml

In order for LiveForms to see the file, we will need to update the liveforms.xml file located in {Tomcat Home}\conf\. In the liveforms.xml file, there will be a parameter tag with the name net.dbsgroup.database.connector.config. In our example, we need to update the associated value to be $CATALINA_HOME/dbconfig/configuration.xml. $CATALINA_HOME points to the Tomcat home directory. In the screenshot above, it points to the Tomcat9 folder.

Setting up Queries

In order to specify the queries the connector will run, we need to set up the configuration.xml file. Below is an example XML file.

<dbconnector>
    <queryset name="Repeat1">
        <resource-def>
            <url>jdbc:mysql://localhost:3306/liveforms_data</url>
            <driver>com.mysql.cj.jdbc.Driver</driver>
            <user>ExUser</user>
            <password>ExPswd</password>
        </resource-def>

        <query name="PostValues">
            <retrieve>
                <statement> CALL PostRepeat1Values({value},{ID});</statement>
            </retrieve>
        </query>

        <query name="GetValues">
            <retrieve>
                <statement> SELECT * FROM repeat1_temp;</statement>
            </retrieve>
        </query>

        <query name="CleanTable">
            <retrieve>
                <statement> CALL CleanRepeat1({index});</statement>
            </retrieve>
        </query>
    </queryset>
</dbconnector>
XML file of the database connector

The “queryset” in the XML is a grouping of queries that all execute on the same database and schema. Since there can be multiple querysets, the name property is the unique identifier for these sets.

Each queryset also needs the database connection info. This is configured using the data inside the resource-def section. There are four defining tags here:

  • url: The connection URL to a given database.

  • driver: This is the class name for the given database driver.

  • user: The name of a database user with rights to that database.

  • password: The password of the above user.

Note

The syntax of the URL and the name of the driver depend on the database you are using. This site gives some class names and URL formats for different databases.

In the example, there is a MySQL server located on the local machine which is accessible on port 3306, and the schema is liveforms_data, the driver class name is com.mysql.cj.jdbc.Driver (the current MySQL Connector/J driver class; older Connector/J releases used com.mysql.jdbc.Driver, which is deprecated), and the connector using the account “ExUser” whose password is “ExPswd”.

Warning

configuration.xml stores database passwords in plain text. Restrict its file permissions to the Tomcat service account, and do not check it into version control or share it outside your ops team.

Each query of a queryset is surrounded by the “query” tag and each has a unique name in that set. The “retrieve” tag also lets the database connector know that the requests will be in the form of an HTTP GET. This is currently the only supported HTTP request.

The innermost set of tags called statement enclose the actual SQL that will be executed on the database. This can either run simple one-line statements, or could make calls to stored procedures in the database which do more complex tasks. Additionally, statements can use the arguments passed from an HTTP request as variables in the query.

In the example above, there are sections in curly brackets. These are arguments passed in the HTTP GET URL. The argument name must have the same name as the variable in the query.

Once the configuration xml is set, the database connector is ready to be used with Rules. To use a query, use a rule that runs an HTTP GET where the URL is in the format of:

"http://localhost:{TomcatPort}/liveforms/database/query/<queryset>/<queryname>?arg1=someValue&arg2=someValue"

//query has two arguments: arg1, arg2
//No port is needed if Tomcat is running on the default http port