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.

...

Example Forms

You can download the DBConnectorTutorial_app.zip containing all of the example forms for the tutorial from the following link: http://www.frevvo.com/bucket/tutorial/dbconnector/DBConnectorTutorial2_app.zip. This zip file can be uploaded to your local  server. See used in this tutorial. This download is a  application zip file that you can uploaded into your user account. See Application Home Page documentation for more information on uploading this application zip file.

Database Connector Configuration

...

 <queryset name="BIRT" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd" xmlTimeStampFormat="MM/dd/yyyy">
        <resource-def>
            <url>jdbc:mysql://localhost/ClassicModels</url>
            <driver>com.mysql.jdbc.Driver</driver>
            <user>root</user>
            <password/>
        </resource-def>
        <query name="allCustomers">
            <retrieve>
                <!-- maps to HTTP GET -->
                <statement> SELECT customerNumber,customerName from Customers order by customerName </statement>
            </retrieve>
        </query>
    </queryset>

    <queryset<query name="BIRTcustomerByNumber" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd" xmlTimeStampFormat="MM/dd/yyyy">>
            <resource-def><retrieve>
            <url>jdbc:odbc:ClassicModels</url>    <!-- maps to HTTP GET -->
   <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>             <user/><statement> SELECT * from Customers where customerNumber={cnum} </statement>
     <password/>
        </resource-def>retrieve>
        ...</query>   
 < </queryset>

Change the port number to the port where your database connector is running. For example if you installed database.war into the  server's tomcat/webapps directory and  is running port 8082, then you must change the <url> from localhost to localhost:8082.

First we define a queryset and give it a name - BIRT in this case. 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. 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.

...

    <queryset name="BIRT" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd" xmlTimeStampFormat="MM/dd/yyyy">

        <resource-def>
            <url>jdbc:odbc:ClassicModels</url>
            <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>
            <user/>
            <password/>
        </resource-def>
        ...
    </queryset>

Change the port number to the port where your database connector is running. For example if you installed database.war into the  server's tomcat/webapps directory and  is running port 8082, then you must change the <url> from localhost to localhost:8082.

First we define a queryset and give it a name - BIRT in this case. 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. 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'. 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.

...

 forms work with XML or JSON data. You can test the above query using the URL: http://localhost:8082/database/BIRT/allCustomers?_mediaType=xml. 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.

...

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 z forms can use JSON data in business rules for dynamic behaviour.

...

We will use these features in the examples below.

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

We will use the query 'allCustomers' that we defined above.

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.

Create a business rule

...

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 Added

The steps are as follows:

Define a query

We will use the query 'allCustomers' that we defined above.

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.

Create a business rule

We will use a business rule to retrieve the list of customers from the database as JSON and dynamically populate this drop down control.

1. Click the Rules button in the toolbar at the top of the Form Designer.
2. Click + to create a new rule and the Edit button to open the rule.
3. Set the Name to 'Show customers'.
4. In the Rule text area: copy and paste the following:

/*member customerName customerNumber resultSet */ 
var x; 
 
if (form.load)  
{   
    eval ('x=' + http.get('http://localhost:8082/database/BIRT/allCustomers'));  
    var opts= [];   
    for (var i=0; i < x.resultSet.length; i++) {     
        if (x.resultSet[i]) {        
            opts[i] = x.resultSet[i].customerNumber + '=' + x.resultSet[i].customerName;     
        }   
    }   
    sc.options = opts; 
}

...

1. Click the Rules button in the toolbar at the top of the Form Designer.
2. Click + to create a new rule and the Edit button to open the rule.
3. Set the Name to 'Show orders'.
4. In the Rule text area: copy and paste the following:
   
   
   

/*member orderDate orderNumber resultSet */ 
var x; 
 
if (sc.value.length > 0) {
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/ordersByCustomer?cnum=' + sc.value));
  var opts= [];
  for (var i=0; i < x.resultSet.length; i++) {
    if (x.resultSet[i]) {
      opts[i] = x.resultSet[i].orderNumber + '=' + x.resultSet[i].orderNumber + ': Date ' + x.resultSet[i].orderDate;
    }
  }
  so.options = opts;
}

...

1. In a browser go to the URL: http://localhost:8082/database/BIRT/orderDetailsByOrder/schema?onum=10100. Save the resulting XML schema file to disk. Note that we need to pass in a known order number so that the query can successfully execute.
2. Upload the schema to . See Data Sources for instructions.

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. 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}.
6. Set the Read method to GET.
7. Click the Finish button.

...

• In your browser go to the URL: http://localhost:8082/database/BIRT/createOrder/schema. Save the resulting XML schema file to disk.
• Edit the file. Due to a current limitation in the database connector, the generated schema needs to be edited. Remove the maxOccurs="unbounded" attribute from the row element declaration. Replace:

...

1. Click the Rules button in the toolbar at the top of the Form Designer.
2. Click + to create a new rule and the Edit button to open the rule.
3. Set the Name to 'Copy customer number and generate an order number'.
4. In the Rule text area: copy and paste the following:
   
   

/*member onum resultSet */
var x;

if (sc.value.length > 0) {
  cnum.value = sc.value;
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber'));
  onum.value = x.resultSet[0].onum;
}

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. 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.
6. Leave the Read method empty.
7. Set the Write method to POST.
8. Click the Finish button.

...

• In your browser go to the URL: http://localhost:8082/database/BIRT/createOrderDetail/schema. Save the resulting XML schema file to disk.
• Upload the schema to . See Data Sources for instructions.

...

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 grey 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 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 and in . In the properties pane set 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 grey  will gray out the Submit button and the form cannot be submitted.

...

• Click the Rules button in the toolbar at the top of the Form Designer.
• Edit 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:
  
  

/*member customerName customerNumber productCode productName resultSet */
var x;

if (form.load) 
{
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/allCustomers'));
  var opts= [];
  for (var i=0; i < x.resultSet.length; i++) {
    if (x.resultSet[i]) {
      opts[i] = x.resultSet[i].customerNumber + '=' + x.resultSet[i].customerName;
    }
  }
  sc.options = opts;
  // Products.
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/allProducts'));
  opts= [];
  for (var i=0; i < x.resultSet.length; i++) {
    if (x.resultSet[i]) {
      opts[i] = x.resultSet[i].productCode + '=' + x.resultSet[i].productName;
    }
  }
  pc[0].options = opts;
}

...

• Edit the 'Copy customer number and generate an order number' rule we created in Example 4.
• In the Rule text area: copy and paste the following:
  
  

/*member onum resultSet */
var x;

if (form.unload) {
  cnum.value = sc.value;
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber'));
  onum.value = x.resultSet[0].onum;
  for (var i = 0; i < olonum.value.length; i++) {
    olonum[i].value = x.resultSet[0].onum
    oln[i].value = i+1;
  }
}

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. A new dialog box will appear. Navigate to the desired document (createOrderDetail) by clicking the Next button twice.
4. Set the URL to: http://localhost:8082/database/BIRT/createOrderDetail.
5. Leave the Read method empty.
6. Set the Write method to POST.
7. Click the Finish button.

Image Modified

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.

...

• In your browser go to the URL: http://localhost:8082/database/BIRT/customerCreditLimit/schema?cnum=242. Save the resulting XML schema file to disk.
• Edit the file. Due to a current limitation in the database connector, the generated schema needs to be edited. Remove the maxOccurs="unbounded" attribute from the row element declaration. Replace:

...

1. Make a copy of the form from Example 1 and open it for editing.
2. In the properties panel on the left, open the Data Sources pane by clicking on it.
3. Click 'New from XSD'.
4. You should see your schema in the resulting dialog box. Expand it and add the 'customerCreditLimit' element to the form by clicking the + icon next to it.
5. Close the dialog box by clicking X in the top right corner.
6. In the Data Sources pane, expand the createOrder data customerCreditLimit data source by clicking on the + icon to the left and expand the row element.
7. Click the + icon next to the cnum and creditLimit elements.  will generate controls in the form.
8. You can drag and drop to re-arrange as you wish.
9. Hide the cnum element by unchecking the Visible checkbox in the properties pane.

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. A new dialog box will appear. Navigate to the desired document (customerCreditLimit) by clicking the Next button.
4. Set the URL to: http://localhost:8082/database/BIRT/customerCreditLimit?cnum={sc}.
5. Set the Read method to GET.
6. Set the Write method to PUT.
7. Click the Finish button.

Image Removed]]Image 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.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.

<query name="customerByNumber“> 
            <retrieve>
                  <!-- maps to HTTP GET -->
                  <statement>  
    SELECT * from Customers where customerNumber={cnum}  
                  </statement>
            </retrieve>
</query>