...

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">
         <statement><query name="allCustomers" autocreate="true">
             <retrieve>
    SELECT "customerNumber","customerName" from "Customers" order by "customerName"      <statement>
          </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 URL: http://localhost:8081/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

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: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 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.

...

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.

...

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.

...