|
On this page: |
|
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.
We will first implement a simple master-detail application to view orders for a customer and line item details for any order in a single form. This demonstrates how to populate a web form with data from the database.
Later, we will also write data back to the database creating new records and updating existing ones. This tutorial does not cover advanced areas such as autocreate, autodelete, date/timestamp considerations. See the documentation for more detailed coverage of these topics
Download the Database Connector Tutorial application. This application contains all of the example forms 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.
Since we are using the in-memory databases, no further configuration is required. This tutorial assumes that frevvo-tomcat bundle or the Database Connector Standalone bundle is installed and that the Database Connector is up and running.
Cloud customers should verify that the Database Connector is accessible from the frevvo servers.
The first step 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.
<dbconnector>
<queryset name="BIRT">
<query name="allCustomers" autocreate="true">
<retrieve>
<statement>
SELECT "customerNumber","customerName" from "Customers" order by "customerName"
</statement>
</retrieve>
</query>
</queryset>
</dbconnector> |
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-custom.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.
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:
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.
Let's take a closer look at this URL.
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 behaviour.
We will use these features in the examples below.
It is very helpful to test your queries as shown above by entering the query URL directly into your web browser and verify that the data returned to your browser as a web page is as you expect. Note however that browsers often cache web pages. If you edit your configuration.xml SQL query and reload/refresh the URL in your browser you may NOT get the updated results due to your browser's caching. Avoid this caching issue by always opening a new browser tab to retest an updated query. |
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 application one time and then you can add the relevant schema elements to your forms/flows 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.
The steps are as follows:
We will use the query 'allCustomers' in the configuration.xml file.
<query name="allCustomers" autocreate="true">
<retrieve>
<statement>
SELECT "customerNumber","customerName" from "Customers" order by "customerName"
</statement>
</retrieve>
</query> |
We will use a business rule to retrieve the list of customers from the database as JSON and dynamically populate this drop down control.
icon in the toolbar at the top of the Form Designer.
to create a new rule, then click the 
Edit icon, Rule Code then click Edit Code to open the rule editor./*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;
} |
Let's analyze this rule.
When the form is loaded, the drop down will be populated with the list of customers as shown in the image above.
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.
Here is the query used in the built-in configuration file. Note that, this query requires a parameter indicated by the {cnum}.
<query name="ordersByCustomer" autocreate="true">
<retrieve>
<statement>
SELECT "orderNumber", "orderDate", "status", "customerNumber" FROM "Orders" WHERE "customerNumber"={cnum} ORDER BY "orderDate"
</statement>
</retrieve>
</query> |
icon in the toolbar at the top of the Form Designer. to create a new rule, then click the Edit icon, Rule Code then click Edit Code to open the rule editor./*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;
}
|
Let's analyze this rule.
When a customer is selected from the first drop down, the orders drop down will populate with orders for that customer. Select a different customer and the orders will update dynamically.
Now, we will display order details for any given order. For example, for order number 10397 above, the order details resultset is shown below.
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:
Here is the query defined in the built-in configuration file. The query requires a parameter indicated by {onum}.
<query name="orderDetailsByOrder" autocreate="true">
<retrieve>
<statement>
SELECT p."productName" as "product", o."quantityOrdered" as "quantity", o."priceEach" as "price", p."productDescription" as "description", p."MSRP"
FROM "OrderDetails" o, "Products" p
WHERE o."productCode"=p."productCode" and o."orderNumber"={onum}
ORDER by o."orderLineNumber"
</statement>
</retrieve>
</query> |
The database connector can generate an XML schema from any query's <retrieve> operation. This schema can then be used to generate a form.
See Data Sources for help on the steps below.
This step generates controls that are bound to the orderDetailsByOrder data source by . You can render the controls as you wish but
will always generate an XML document conforming to the XML schema above.
Now, we connect the generated controls to the database query defined above. See Doc URl Wizards for help on the steps below.
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.
Now, let's look at submitting form data to the database. We will create a form using which a customer can place a new order. The order is then saved to the database. Note that we have simplified the form for demonstration purposes.
There are two queries defined in the built-in configuration file. The first one creates an order. In addition to the usual <retrieve> operation, this query also has a <create> operation.
<query name="createOrder">
<retrieve>
<statement>
SELECT "orderNumber" as "onum", "customerNumber" as "cnum" from "Orders" WHERE "orderNumber"=10100
</statement>
</retrieve>
<create>
<statement>
INSERT INTO "Orders"
("orderNumber","orderDate","requiredDate","status","customerNumber")
VALUES
({onum}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'In Process',{cnum})
</statement>
</create>
</query> |
Let's understand this query. We specify a <retrieve> operation (SELECT statement) in order to generate an XML schema/form controls. When the form is submitted, generates an XML document for those controls. The XML document is used to execute the <create> operation, which INSERTS the data. We have simply hard-coded a known order number for the example but you can use any query that generates a result set with the desired columns.
Note that we'll just insert the current date as the order date and required date for this example. You can easily customize the query to add a date of your choice. If you want the date to come from the form, you must add it to the SELECT statement so that a control is generated in the form. Dates/Times tend to be database-specific and we'll look at an example later.
The second query is simply used to generate an order number.
<query name="getOrderNumber">
<retrieve>
<statement>
SELECT max("orderNumber") + 1 as "onum" FROM "Orders"
</statement>
</retrieve>
</query> |
Once again, this is an oversimplification for tutorial purposes. In practice, order numbers might be generated by the outer application rather than the database.
<xsd:element maxOccurs="unbounded" name="row"> |
with
<xsd:element name="row"> |
See Data Sources for help on the steps below.
This step generates controls that are bound to the createOrder data source by . You can render the controls as you wish but
will always generate an XML document conforming to the XML schema above.
icon in the toolbar at the top of the Form Designer. to create a new rule, then click the Edit icon, Rule Code then click Edit Code to open the rule editor./*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;
} |
Let's analyze this rule.
Now, we connect the generated controls to the database query defined above. See Doc URI Wizards for help on the steps below.
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.
Now, we'll add line items to the order.
There are two queries defined in the built-in configuration.xml file. The first one fetches product codes and descriptions from the database so that the user can select a product for purchase.
<query name="allProducts" autocreate="true">
<retrieve>
<statement>
SELECT "productCode", "productName" from "Products" order by "productName"
</statement>
</retrieve>
</query> |
The second query creates the order detail line item records in the database.
<query name="createOrderDetail">
<retrieve>
<statement>
SELECT
"orderNumber" as "onum","productCode","quantityOrdered","priceEach","orderLineNumber"
FROM
"OrderDetails"
WHERE
"orderNumber"=10100
</statement>
</retrieve>
<create>
<statement>
INSERT INTO "OrderDetails"
("orderNumber","productCode","quantityOrdered","priceEach","orderLineNumber")
VALUES
({onum},'{productCode}',{quantityOrdered},{priceEach},{orderLineNumber})
</statement>
</create>
</query> |
The steps are very similar to the earlier examples.
See Data Sources for help on the steps below.
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:
icon in the toolbar at the top of the Form Designer. Edit icon for the Show Customers rule we created in Example 4. Change the name to Show Customers/Products./*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;
} |
This rule will fire on form load and initialize the customer drop down as before. In addition, we initialize the products drop down in each order line (there could be multiple) with the list of products from the database.
/*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;
}
} |
This rule will fire when the form unloads (just after the user clicks Submit) and set the values of the hidden fields. We make sure that the Order has a valid customer number and order number, the order number is copied into each order detail element and the order line number is correctly set.
if (OrderLines.itemAdded) {
var ix = OrderLines.itemIndex;
pc[ix].options = pc[0].options;
oln[ix].value = 0;
olonum[ix].value = 0;
} |
This rule will fire when the user adds a new Order Line repeat item. We copy the product list into the newly added Order Line and set default values in the hidden fields.
Finally, we connect the createOrderDetails document to the database query defined above. See Doc URl Wizards for help on the steps below.
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 this example, we will update the credit limit for an existing customer.
The query defined in the built-in configuration file is shown below. This query has an <update> operation in addition to the usual <retrieve> operation. We retrieve the customerName in the retrieve operation purely for display purposes.
<query name="customerCreditLimit">
<retrieve>
<statement>
SELECT
"customerNumber" as "cnum","customerName","creditLimit"
FROM
"Customers"
WHERE
"customerNumber"={cnum}
</statement>
</retrieve>
<update>
<statement>
UPDATE "Customers" SET
"creditLimit" = {creditLimit}
WHERE
"customerNumber"={cnum}
</statement>
</update>
</query> |
<xsd:element maxOccurs="unbounded" name="row"> |
with
<xsd:element name="row"> |
See Data Sources for help on the steps below.
Connect the customerCreditLimit document to the database query defined above. See Doc URl Wizards for help on the steps below.
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.
</query>
<query name="customerByNumber" autocreate="true">
<retrieve>
<statement>
SELECT * from "Customers" where "customerNumber"={cnum}
</statement>
</retrieve>
</query> |