On this page:

Looking for a section on this page? Hover your cursor over the Table of Contents icon Image Added to the right to quickly navigate this page.

Image Added

Introduction

The database connector maps between HTTP requests issued from  forms to SQL queries executed against your database. The connector enables you to use the database of your choice to save and load your form data. You can create a new database or you can automatically generate forms that connect to your existing database tables to save and load data. The database connector contains working sample databases with some test data. The BIRT (ClassicModels) and myStore querySets are preloaded with in-memory Derby databases. We will use the in-memory databases to successfully complete this tutorial.

...

Download the Database Connector Tutorial project. This project contains all of the example forms used in this tutorial. This download is a  project zip file that you can uploaded upload into your user account. See Project Home Page documentation for more information on uploading this project zip file.

...

Cloud customers should verify that the Database Connector is accessible from the frevvo servers. Cloud customers should replace <localhost:8082> with <app.frevvo.com> in the Business Rule and Document Action URL examples below.

Define a Queryset

The Typically, the first step in connecting your form/workflow to your database is to define a queryset and give it a name - BIRT in the configuration.xml file. Since we are using the built-in configuration.xml file, you do not have to do this. Here is an example of the BIRT queryset and the allCustomers query that is described below.

...

 The database connector can simultaneously connect to multiple databases; each must be defined in its own queryset. The queryset consists of a resource definition and any number of named queries. The resource definition is configured in the dbconnector.properties file. You will need to modify it as required: change localhost to the name of the machine running MySQL, specify the MySQL driver, change the database user name and password.

Next you would define a simple query named 'allCustomers' in the configuration.xml file. Queries can contain four SQL statements (create, retrieve, update, delete). In this example, our query contains just one SQL statement to get the list of customers. Here is an example of the BIRT queryset and the allCustomers query that is described above. Learn more about this process in the Defining SQL Queries documentation.

 <dbconnector>
    <queryset name="BIRT">
         <query name="allCustomers" autocreate="true">
            <retrieve>
                <statement>
                    SELECT "customerNumber","customerName" from "Customers" order by "customerName"
                </statement>
            </retrieve>
        </query>
    </queryset>
</dbconnector>

Mapping HTTP requests to SQL queries

The database connector can simultaneously connect to multiple databases; each must be defined in its own queryset. The queryset consists of a resource definition and any number of named queries. The resource definition is shown above for MySQL. It is configured in the dbconnector.properties file. You will need to modify it as required: change localhost to the name of the machine running MySQL, specify the MySQL driver, change the database user name and password.

Next we define a simple query named 'allCustomers' in the configuration.xml file. Queries can contain four SQL statements (create, retrieve, update, delete). In this example, our query contains just one SQL statement to get the list of customers.

Mapping HTTP requests to SQL queries

The database connector maps between HTTP requests issued from and SQL queries. It is important to understand how HTTP URLs map to SQL queries in the database connector. The above query returns a resultset that contains data as shown below:

Image Removed

 forms work with XML or JSON data. You can test the above query using the maps between HTTP requests issued from and SQL queries. It is important to understand how HTTP URLs map to SQL queries in the database connector. The above query returns a resultSet that contains data as shown below:

Image Added

 forms work with XML or JSON data. You can test the above query using the URL: http://localhost:8082/database/BIRT/allCustomers. This should return an XML document with customerNumber and customerName for each customer as shown below. The DB connector has converted the SQL data into a format that is usable by   forms.

Image Added

Let's take a closer look at this URL.

Image Added

• The 'localhost:8082' points to the database connector inside the tomcat servlet container on localhost and listening on port 8082.
• 'database' refers to the context path for the database connector web application running inside Tomcat.
• 'BIRT' refers to the name of the queryset defined above.
• 'allCustomers' is the name of the query.
• Since we are issuing an HTTP GET from the browser, this maps to the <retrieve> SQL.
•  _mediaType=xml tells the connector to return an XML document. This is the default; if you leave out a _mediaType, the connector will return XML. When the HTTP GET is issued, the connector executes the <retrieve> SQL for the 'allCustomers' query in the 'BIRT' queryset using the resource definition to connect to the appropriate database. The data in the resultset is converted to XML and returned.

The connector is also capable of returning JSON data. Try the URL:http://localhost:80818082/database/BIRT/allCustomers. This should return an XML document with customerNumber and customerName for each customer as shown below. The DB connector has converted the SQL data into a format that is usable by   forms.

Image Removed

Let's take a closer look at this URL.

Image Removed

• The 'localhost:8082' points to the database connector inside the tomcat servlet container on localhost and listening on port 8082.
• 'database' refers to the context path for the database connector web application running inside Tomcat.
• 'BIRT' refers to the name of the queryset defined above.
• 'allCustomers' is the name of the query.
• Since we are issuing an HTTP GET from the browser, this maps to the <retrieve> SQL.
•  _mediaType=xml tells the connector to return an XML document. This is the default; if you leave out a _mediaType, the connector will return XML. When the HTTP GET is issued, the connector executes the <retrieve> SQL for the 'allCustomers' query in the 'BIRT' queryset using the resource definition to connect to the appropriate database. The data in the resultset is converted to XML and returned.

The connector is also capable of returning JSON data. Try the URL: http://localhost:8082/database/BIRT/allCustomers?_mediaType=json the connector will return the same data as JSON. forms can use JSON data in business rules for dynamic behavior.

Image Removed

We will use these features in the examples below.

Generating xml schema

If you prefer, you can generate the schema for the entire BIRT queryset before you begin. This eliminates the need to create a schema one by one for each query as specified in the instructions. The queryset schema can be uploaded to your project one time and then you can add the relevant schema elements to your forms/workflows as you need them. Browse this URL to get the schema, save the schema as an .xsd file in your file system then upload the schema to Live Forms.

http://<server>:<port>/database/BIRT/schema

Example 1: View customer list

Our first example is to create a form with a dropdown control that is dynamically populated with the above list of customers from the database. When the user selects a customer by name, the value of the dropdown control should be set to the customer number.

Image Removed

The steps are as follows:

Define a query

...

?_mediaType=json the connector will return the same data as JSON. forms can use JSON data in business rules for dynamic behavior.

Image Added

We will use these features in the examples below.

Generating xml schema

If you prefer, you can generate the schema for the entire BIRT queryset before you begin. This eliminates the need to create a schema one by one for each query as specified in the instructions. The queryset schema can be uploaded to your project one time and then you can add the relevant schema elements to your forms/workflows as you need them. Browse this URL to get the schema, save the schema as an .xsd file in your file system then upload the schema to Live Forms.

http://<server>:<port>/database/BIRT/schema

Our first example is to create a form with a dropdown control that is dynamically populated with the above list of customers from the database. When the user selects a customer by name, the value of the dropdown control should be set to the customer number.

Image Added

The steps are as follows:

Define a query

We will use the query 'allCustomers' in the configuration.xml file.

 <query name="allCustomers" autocreate="true">
            <retrieve>
       <retrieve>         <statement>
       <statement>
                    SELECT "customerNumber","customerName" from "Customers" order by "customerName"
                </statement>
            </retrieve>
        </query>

Create a form

1. Create a new form. Accept the defaults by clicking the Finish button. The Form Designer will appear.
2. Drag a Dropdown control from the Palette and drop it into the form.
3. In the properties pane on the left, change the Label as desired.
4. In the properties pane, change the Name to sc.

...

1. if (form.load) - this implies that the rule will execute when the form first loads.

2. eval ('x=' + http.get('http://localhost:8082/database/BIRT/allCustomers')) - this causes an HTTP GET to be issued to the indicated URL. When invoked in the context of a rule, the media type is automatically set to JSON. As we saw above, the database connector will return the list of customers as a JSON object.

   Info
   For the Standalone bundle with  Cloud, replace localhost:8082 with "app.frevvo.com" in the URL.UR

3. The rest of the rule is just JavaScript. We create an array of options in the format value=label (e.g. 242=Alpha Cognac) from the data returned in the JSON.
4. Finally, we set the options of the dropdown to the array of options using the name we chose earlier (sc).

...

Next, we will add a drop down that displays the list of orders for a particular customer. When the user selects a customer name from the first drop down, we will display the list of orders for that customer from the database. This is essentially repeating the steps above except that the database query is triggered by a user action (selecting a customer from the drop down) and depends on the selected customer.

Image RemovedImage Added

Define a query

...

Create a business rule

1. Click the Image Modified icon in the toolbar at the top of the Form Designer.
2. Click Image Modified to create a new rule, then click the Image Modified Edit icon, Rule Code then click Edit Code to open the rule editor.
3. Set the Name to 'Show orders'.
4. In the Rule text area: copy and paste the following:
   
   

...

Now, we will display order details for any given order. For example, for order number 10397 above, the order details resultset is shown below.

Image RemovedImage Added

Since order details is a complex structure (product name, quantity, price etc.) we will use 's built-in XML features to generate controls and populate them from the database. When an order is selected, the order line items will be displayed as shown below:

Image RemovedImage Added

Define a query

...

Now, we connect the generated controls to the database query defined above. See Doc URl Wizards for help on the steps below.

1. Click on the Doc Action button Form Properties Image Addedicon in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Select the Document Actions tab and the Send Data sub-tab.
   Image Added
3. Select Manually set document URIs'.A new dialog box will appear. Click the Next button.
4. You should see the Document name change to 'orderDetailsByOrder', which is the data source we are interested in.
5. Set the URL to: http://localhost:8082/database/BIRT/orderDetailsByOrder?onum={so} (for standalone bundle with  Cloud, replace <server:port> with app.frevvo.com.)
6. Set the Read method to GET.
7. Click the Finish buttonSubmit.

Image RemovedImage Added

This step indicates to  that the 'orderDetailsByOrder' document is linked to the above URL via the READ method GET.  will issue an HTTP GET request to the above URL and if an XML document conforming to the schema above is returned,  will automatically initialize controls in the form using that document.

The URL itself is a dynamic URL and has a parameter indicated by {so}. Remember that we have a control in the form named 'so' (defined in this step. When the value of the control named 'so' changes (which will happen when the user selects a particular order),   will automatically issue an HTTP GET to the above URL using the new order number and will dynamically update the order details including creating new line items, removing old ones etc. without refreshing the form as shown in the picture below.

Image RemovedImage Added

Example 4: Create a new order

...

Create a business rule

1. Click the Image Modified icon in the toolbar at the top of the Form Designer.
2. Click Image Modified to create a new rule, then click the Image Modified Edit icon, Rule Code then click Edit Code to open the rule editor.
3. Set the Name to 'Copy customer number and generate an order number'.
4. In the Rule text area: copy and paste the following:
   
   

...

Now, we connect the generated controls to the database query defined above. See Doc URI Wizards for help on the steps below.

1. Click on the Doc Action button Form Properties Image Addedicon in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Select the Document Actions tab and the Send Data sub-tab.
   Image Added
3. Select Manually set document URIs'.A new dialog box will appear. Click the Next button.
4. You should see the Document name change to 'createOrder', which is the data source we are interested in.
5. Set the URL to: http://localhost:8082/database/BIRT/createOrder (for standalone bundle with  Cloud, replace <server:port> with app.frevvo.com.)
6. Leave the Read method empty.
7. Set the Write method to POST.
8. Click the Finish buttonSubmit.

Image RemovedImage Added

This step indicates to  that the 'createOrder' document is linked to the above URL via the WRITE method POST. When the form is submitted,  will issue an HTTP POST request to the above URL sending the createOrder XML document in the payload. The database connector will map this to the <create> operation of the createOrder query that we defined above, will use the data in the XML document to resolve the query (replace {cnum} and {onum} with values from the XML) and perform the INSERT.

...

1. Make a copy of the form from Example 4 above and open it for editing.
2. We can hide the Order Info section since it does not contain information entered by the user. Select the section by clicking on its header and uncheck the Visible checkbox in the properties pane. The entire section should gray out.
3. In the properties panel on the left, open the Data Sources pane by clicking on it.
4. Click 'New from XSD'.
5. You should see your schema in the resulting dialog box. Expand it and add the 'createOrderDetail' element to the form by clicking the + icon next to it.
6. Close the dialog box by clicking X in the top right corner.
7. In the Data Sources pane, expand the createOrderDetail Create Order Detail data source by clicking on the + icon to the left.
8. Click the + icon next to the row element.  will generate controls in the form. Note that  automatically generates a repeating element since a single Order can contain any number of line items.
9. You can drag and drop to re-arrange as you wish.
10. Rename the Section named Row to Order Line.
11. Name the repeat control OrderLines. Take note of the "s" in the name.
12. Select the Product Code input control. In the properties panel:
    1. Set the name to pc. 
    2. Select Dropdown from the Display As property list.
13. Select the Onum control inside the Order Line section and in the properties pane set the name to olonum. Uncheck the Visible property.
14. Select the Order Line Number control inside the Order Line section and in the properties pane set the name to oln. Uncheck the Visible property.
15. Set default values in the Onum and Cnum controls inside the Order Info section and in the Onum and Order Line Number controls inside the Order Line section. You can set any values since we will be overriding them in a rule. We need to set a default value otherwise  will gray out the Submit button and the form cannot be submitted.

This step generates controls that are bound to the createOrderDetail data source by . You can render the controls as you wish but  will always generate an XML document conforming to the XML schema above. Here's what our form looks like:

Image RemovedImage Added

Create/Update business rules

• Click the Image Modified icon in the toolbar at the top of the Form Designer.
• Click the Image Modified Edit icon for the Show Customers rule we created in Example 4. Change the name to Show Customers/Products.
• In the Rule text area: copy and paste the following:
  
  

...

• Click + to create a new rule and the Edit button to open the rule.
• Set the Name to 'Order Line Item Added'.
• In the Rule text area: copy and paste the following:
  
  

if (OrderLines.itemAdded) {
  var ix = OrderLines.itemIndex;
  pc[ix].options = pc[0].options;
  oln[ix].value = 0;
  olonum[ix].value = 0;
}

...

Finally, we connect the createOrderDetails document to the database query defined above. See Doc URl Wizards for help on the steps below.

1. Click on the Doc Action button Form Properties Image Addedicon in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Select the Document Actions tab and the Send Data sub-tab.
3. Select Manually set document URIs'.A new dialog box will appear. Navigate to the desired document (createOrderDetail) by clicking the Next button twice
4. You should see the Document name 'createOrder', which is the data source we are interested in.
5. Set the Write URL to: http://localhost:8082/database/BIRT/createOrderDetail
6. Leave the Read method empty.
7. Set the Write method to POST.
8. Click the Finish buttonSubmit.

Image RemovedImage Added

When the form is submitted,  will issue an HTTP POST request to the above URL sending the createOrderDetail XML document in the payload. The database connector will map this to the <create> operation of the createOrderDetail query that we defined above, will use the data in the XML document to resolve the query. Since there are multiple line items, the query will be executed once for each Order line item thereby inserting the multiple line items for an Order.

...

• Click the Test button.
• Select a customer from the drop down.
• Enter at least one line item.
• Submit the form.

Image RemovedImage Added

• Verify that the order was successfully inserted.
• Use the form from Example 3 by clicking the Test button.
• Select the customer.
• You should see your order. Select the order.
• You should see Line Item details appear below.

Image RemovedImage Added

Example 6: Update existing records

...

Connect the customerCreditLimit document to the database query defined above. See Doc URl Wizards for help on the steps below.

1. Click on the Doc Action button Form Properties Image Addedicon in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Select the Document Actions tab and the Send Data sub-tab.
3. Select Manually set document URIs'.A new dialog
4. box will appear. Navigate to the desired document (customerCreditLimit) by clicking the Next buttonYou should see the Document name 'customerCreditLimit', which is the data source we are interested in.
5. Set the Read and Write URLs to: http://localhost:8082/database/BIRT/customerCreditLimit?cnum={sc}
6. Set the Read method to GET.
7. Set the Write method to PUT.
8. Click the Finish buttonSubmit.

Image RemovedImage Added

This step indicates to  that the 'customerCreditLimit' document is linked to the above URL via the READ method GET and the WRITE METHOD PUT.  will issue an HTTP GET request to the above URL and if an XML document conforming to the schema above is returned,  will automatically initialize controls in the form using that document. The URL itself is a dynamic URL and has a parameter indicated by {sc}. When the value of the control named 'sc' changes (which will happen when the user selects a customer),  will automatically issue an HTTP GET to the above URL using the new customer number and will dynamically update the values of the credit Limit and cnum controls without refreshing the form.

When the form is submitted,  will issue an HTTP PUT request to the above URL sending the customerCreditLimit XML document in the payload. The database connector will map this to the <update> operation of the customerCreditLimit query that we defined above, will use the data in the XML document to resolve the query (replace {creditLimit} and {cnum} in the query with the actual values from the XML document) and perform the UPDATE.

...