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, version 5.7. 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\.

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, it will use the same connector library file.

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.

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.jdbc.Driver, and the connector using using the account “ExUser” whose password is “ExPswd”.

Each query of a queryset is surounded 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 statments, 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, the 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