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 Database Server 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. Queries allow forms to execute SQL functions on databases, with the added benefit of using form information when needed. 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 return dynamic results by using variables. The format for a variable is the name surrounded by curly braces: {VariableName}. These variables can be set using url arguments, as will be shown further on. The code block below shows some example queries.

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

--calling a stored procedure from the database using mulitple variables. This procedure may store a new record to the database.
CALL MyCustomProcedure({NewID},{NewData});

Once a query is created, it can be used in form rules. For some rule examples and how rules recieve query results, see here: Using Database Results. The rule would make an HTTP get request to a data source url which takes a format like one seen below. Query variables are set in the URL on the left of the equal sign, and the rule fills in the value on the right. Rules are able to calculate values or may use form fields.

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

// this example query has two arguments: arg1, arg2
// both are being set as "someValue"
// No port is needed if Tomcat is running on the default http port