Data Sources

This interface allows you to easily connect LiveForms to a database for either retrieving or storing data form data. This menu is available from the side bar on the left. To begin, click the green create button at the top right of the Data Sources page. This brings up an interface for creating a data source.

Create Data SOurce Interface

The Name and Description are for the users to identify what the data source is and may be anything desired. The Description drop down allows you to choose from one of the supported databases. Default supported databases include:

  • MySQL

  • Microsoft SQL

  • Oracle

  • Postgres

You may also select other which allows for a custom database type.

Note

The system must be configured with the drivers before a database conenction can be made. See Database Connector for configuring a database connector.

The Connection URL field is where the database schema is targeted. The schema contains all the tables your queries will be selecting from. In the image above, we are looking in the schema database.

In the Databse Username and Database Password fields, you will need to provide the log in credentials for a user in with access to that database schema.

Setting up Queries

Once a data source is created, you can begin to create queries. When a query is executed, it is done in the form of an HTTP GET. When a query is created, a success message should appear at the top.

Query List

To begin creating a query, click the create button at the top right. This brings up the interface below:

../_images/CreateQuery.png

The Name identifies the query in the set. The Description is for giving a summary for users. The Query field is where the actual SQL for the query is entered. the Pass Phrase will be a unique security feature which depends on the database.

Note

Query names must be unique within a data source.

Queries can also be made to take variables. To do so, include a place holder with the variable name inside of curly braces, like so: {VariableName}.

--selecting a row from a table where a a field is equal to VariableName
SELECT * FROM table WHERE columnName = {VariableName};

--calling a stored procedure using mulitple variables
CALL MyCustomProcedure({NewID},{NewData});

Once a query is created, it can be used in form rules. For some rule examples, see here: Using Database Results. Queries are accessed via HTTP GET, where the URL is in the format of:

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

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