Page Comparison

Table of Contents

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

...

The database connector maps between HTTP requests issued from forms frevvo 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 frevvo project zip file that you can upload into your user account. See Project Home Page documentation for more information on uploading this project zip file.

...

The database connector maps between HTTP requests issued from and frevvo 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 frevvo 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 formsfrevvo 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. frevvo forms can use JSON data in business rules for dynamic behavior.

...

if (form.load) - this implies that the rule will execute when the form first loads.
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 Cloudwith frevvo Cloud, replace localhost:8082 with "app.frevvo.com" in the UR
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.
Finally, we set the options of the dropdown to the array of options using the name we chose earlier (sc).

...

Click the Rules mode in the Guided Designer navigation bar.
Click to create a new rule.
Set the Name to 'Show customersGet Account Manager ID'.
Select Rule Code then click Edit Code to open the rule editor.

In the Rule text area: copy and paste the following:

Code Block

/*member accountMgrId, resultSet*/
var x;
eval ('x=' + http.get('http://localhost:8082/database/customers/getAccountMgrId?cnum=' + sc.value));
for (var i=0; i < x.resultSet.length; i++) {
if (x.resultSet) {
AccountManagerID.value =  x.resultSet[i].accountMgrId;
}
}

This rule runs the http.get and sets the query template 'cnum' to the value of our Select Customer ('sc') control from Example 1. This will resolve the customerNumber. The query runs the SELECT statement, which gets that customer's Account Manager ID, and sets that value in the text control 'AccountManagerID'. If you were to use this form in a workflow, you might have an Account Manager Review step assigned to the templatized string {AccountManagerID}, dynamically routing the correct Account Manager for this customer.

Expand

title	Click here to see this used in a Workflow Step Assignment...

...

Since order details is a complex structure (product name, quantity, price etc.) we will use frevvo'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:

...

The database connector can generate an XML schema from any query's <retrieve> operation. This schema can then be used to generate a formfrevvo form.

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.
Upload the schema to frevvo. See Data Sources for instructions.

...

Make a copy of the form from Example 2 above and open it for editing.
In the properties panel on the left, open the Data Sources pane by clicking on it.
Click 'New from XSD'.
You should see your schema in the resulting dialog box. Expand it and add the 'orderDetailsByOrder' element to the form by clicking the + icon next to it.
Close the dialog box by clicking X in the top right corner.
In the Data Sources pane, click the green + icon next to on the right of the orderDetailsByOrder data source. will frevvo will generate controls in the form.
You can drag and drop to re-arrange as you wish.
Change the label of the Section named 'row' to the string '{product}'. We'll explain this in more detail below.
Collapse this Section.

This step generates controls that are bound to the orderDetailsByOrder data source by frevvo. You can render the controls as you wish but will frevvo will always generate an XML document conforming to the XML schema above.

...

Go to the Settings mode in the Guided Designer navigation bar.
Select the Document Actions tab and the Send Data sub-tab.
Select Manually set document URIs.
You should see the Document name change to 'orderDetailsByOrder', which is the data source we are interested in.
Set the URL to: http://localhost:8082/database/BIRT/orderDetailsByOrder?onum={so} (for standalone bundle with Cloudwith frevvo Cloud, replace <server:port> with app.frevvo.com.)
Set the Read method to GET.
Save the form.

This step indicates to that frevvo that the 'orderDetailsByOrder' document is linked to the above URL via the READ method GET. will frevvo will issue an HTTP GET request to the above URL and if an XML document conforming to the schema above is returned, will frevvo 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 frevvo 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.

...

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

...

Code Block
<xsd:element name="row">

Upload the schema to frevvo. See Data Sources for instructions.

...

Make a copy of the form from Example 1 and open it for editing.
In the properties panel on the left, open the Data Sources pane by clicking on it.
Click 'New from XSD'.
You should see your schema in the resulting dialog box. Expand it and add the 'createOrder' element to the form by clicking the + icon next to it.
Close the dialog box by clicking X in the top right corner.
In the Data Sources pane, expand the createOrder data source by clicking on the + icon to the left.
Click the + icon next to the row element. will frevvo will generate controls in the form.
You can drag and drop to re-arrange as you wish.
Rename the Section named Row to Order Info.

This step generates controls that are bound to the createOrder data source by frevvo. You can render the controls as you wish but will frevvo will always generate an XML document conforming to the XML schema above.

...

Go to the Settings mode in the Guided Designer navigation bar.
Select the Document Actions tab and the Send Data sub-tab.
Select Manually set document URIs.
You should see the Document name 'createOrder', which is the data source we are interested in.
Set the URL to: http://localhost:8082/database/BIRT/createOrder (for standalone bundle with with frevvo Cloud, replace <server:port> with app.frevvo.com.)
Leave the Read method empty.
Set the Write method to POST.
Save the form.

This step indicates to that frevvo that the 'createOrder' document is linked to the above URL via the WRITE method POST. When the form is submitted, will frevvo 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.

...

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 frevvo. See Data Sources for instructions.

...

Make a copy of the form from Example 4 above and open it for editing.
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.
In the properties panel on the left, open the Data Sources pane by clicking on it.
Click 'New from XSD'.
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.
Close the dialog box by clicking X in the top right corner.
In the Data Sources pane, expand the Create Order Detail data source by clicking on the + icon to the left.
Click the + icon next to the row element. will frevvo will generate controls in the form. Note that automatically frevvo automatically generates a repeating element since a single Order can contain any number of line items.
You can drag and drop to re-arrange as you wish.
Rename the Section named Row to Order Line.
Name the repeat control OrderLines. Take note of the "s" in the name.
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.
Select the Onum control inside the Order Line section and in the properties pane set the name to olonum. Uncheck the Visible property.
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.
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 frevvo 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 frevvo. You can render the controls as you wish but will frevvo will always generate an XML document conforming to the XML schema above. Here's what our form looks like:

...

Go to the Settings mode in the Guided Designer navigation bar.
Select the Document Actions tab and the Send Data sub-tab.
Select Manually set document URIs.
You should see the Document name 'createOrdercreateOrderDetail', which is the data source we are interested in.
Set the Write URL to: http://localhost:8082/database/BIRT/createOrderDetail
Leave the Read method empty.
Set the Write method to POST.
Click Submit.

...

When the form is submitted, will frevvo 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.

...

Code Block
<xsd:element name="row">

Upload the schema to frevvo. See Data Sources for instructions.

...

Make a copy of the form from Example 1 and open it for editing.
In the properties panel on the left, open the Data Sources pane by clicking on it.
Click 'New from XSD'.
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.
Close the dialog box by clicking X in the top right corner.
In the Data Sources pane, expand the customerCreditLimit data source by clicking on the + icon to the left and expand the row element.
Click the + icon next to the cnum and creditLimit elements. will frevvo will generate controls in the form.
You can drag and drop to re-arrange as you wish.
Hide the cnum element by unchecking the Visible checkbox in the properties pane.

...

Go to the Settings mode in the Guided Designer navigation bar.
Select the Document Actions tab and the Send Data sub-tab.
Select Manually set document URIs.
You should see the Document name 'customerCreditLimit', which is the data source we are interested in.
Set the Read and Write URLs to: http://localhost:8082/database/BIRT/customerCreditLimit?cnum={sc}
Set the Read method to GET.
Set the Write method to PUT.
Click Submit.

This step indicates to that frevvo that the 'customerCreditLimit' document is linked to the above URL via the READ method GET and the WRITE METHOD PUT. will frevvo will issue an HTTP GET request to the above URL and if an XML document conforming to the schema above is returned, will frevvo 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 frevvo 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 frevvo 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.

...

Versions Compared

Old Version 8

New Version Current

Key