Most of your company’s important data likely lives in a SQL database.
Practically every organization has at least one SQL database and often many of them.
But, here’s the rub: Most of your company has no idea what SQL even means, let alone how to get the information they need out of the database. So, that means your IT or development team spends an unreasonable amount of time either manually running queries and generating reports or building one-off applications and HTML forms for every department under the sun to get the data they need.
It’s not a good use of time.
What you need is a form builder with configurable SQL database integration that can help you stand up solutions quickly. Rather than rolling your own applications, you can use frevvo to build custom forms, configure advanced workflows, integrate data from a MySQL database, and ship a solution—in about an hour.
Note: This is a fairly technical article that contains SQL queries, JavaScript etc.
Using frevvo’s Database Connector to Connect SQL Databases with HTML Forms
To perform the steps outlined in this article, you’ll need a frevvo account. You can sign up to try frevvo free for 30-days.
Let’s take a look at how you can easily connect your workflows and web forms to a SQL database using frevvo’s Database Connector.
The Database Connector allows you to perform sophisticated integration with your database. We’ll explore several possibilities in detail such as dynamic dependent picklists and master detail views.
These implementations will allow you to create forms that populate form fields based on database entries. For instance, you could pull in customer order history based on the company’s name.
First let’s take a look at how the DBConnector works.
frevvo communicates with the outside world using XML (or JSON) over HTTP(S). I.e., it’s RESTful. Databases want you to talk SQL and return data as ResultSets.
With frevvo, the steps are straightforward:
- Configure the Database Connector using SQL or Stored Procedures.
- Generate form controls from the configured queries. (No programming required.)
- Arrange controls in the drag-and-drop form builder.
- Connect your forms to queries using URLs exported by the Database Connector.
frevvo takes care of all the data transformations in both directions. JSON, XML, SQL – it’s all transparent to the form designer and you don’t have to worry about programming any of this. The result of a SQL query could be a single row or multiple rows each with many columns – the form will instantly update to show you the information relevant to your request.
Try now: Master Detail form gets customer information from SQL.
You’ll need to install and configure the Database Connector one time so it can connect to your SQL database. Then follow the steps below for each section.
Use Case: Dynamic Pick Lists
Let’s take a look at dynamic pick lists (drop downs). It’s a very common scenario and, with frevvo, you can use business rules to dynamically initialize the options (choices) in a pick list from a SQL query. The steps are as follows:
1. Define the SQL Query
<queryset name="BIRT" ...>
<query name="allCustomers">
<retrieve>
<statement> SELECT customerNumber,customerName from
Customers order by customerName </statement>
</retrieve>
</query>
</queryset>
This is a straightforward query that simply retrieves the list of customers from the customers table in the database. Once the query is configured, it can be accessed via a URL that looks something like this: http://<server>/database/BIRT/allCustomers
BIRT is the queryset name above and allCustomers is the query name.
You can name these whatever you want. The Database Connector Tutorial explains how URLs map to queries in more detail. This URL will return the data in the ResultSet above in the appropriate format for frevvo to use.
2. Create the frevvo form
For our example, we create a new form in the Designer. Drag in a Dropdown control. Label it whatever you want. In the Properties panel, change the Name to sc.
3. Write a business rule
To connect the dropdown to the query, we use a business rule.
/*member customerName, customerNumber, resultSet */
var x;
if (form.load)
{
eval ('x=' + http.get('http://<server>/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.
- if (form.load) – this implies that the rule will execute when the form first loads.
- eval (‘x=’ + http.get(‘http://<server>/database/BIRT/allCustomers’)) – this calls the above URL and returns the data. Since it’s a rule, the data will automatically come back as JSON.
- Loop over the JSON and create an array in the format value=label (e.g. 242=Alpha Cognac).
- Finally, set the options of the dropdown to the array of options using the Name we chose earlier (sc).
That’s it. When the form is loaded, the drop down will be populated with the list of customers as shown in the image above.
Try it now: SQL Dynamic Picklists example.
The example initializes the customers pick list as above. When you select a customer, it uses a similar rule to initialize a second (dependent) pick list using the value from the customer pick list. You can read more about this in the Database Connector Tutorial.
Use Case: Complete Form Fields from a SQL Database with a Master-Detail View
Master-detail from SQL
Master-detail views are another very common scenario in real applications, for example, when you want to look up details for a customer or a student or a part number. With frevvo’s Database Connector, complex forms like this can be created without programming.
The steps are as follows:
1. Define the SQL Query
<queryset name=”BIRT” …>
<query name=”customerByNumber”>
<retrieve>
<statement>SELECT * from Customers where customerNumber={cnum}
…This is a straightforward query that simply retrieves customer details. It can be accessed via a URL that looks like this: http://<server>/database/BIRT/customerByNumber?cnum=242.
In this query, notice the {cnum} in curly braces. That’s a variable. When frevvo asks the DB connector to run this query, it will provide a value for cnum in the URL. You’ll get back an XML document with customer details for Alpha Cognac.
Try now: Get Customer Information by Customer Number
Connect this to the form. Since customer details is a complex structure (first name, last name, phone etc.) we will use Live Forms’s built-in XML features to generate controls and populate them from the database.
2. Generate an XML Schema
The DB connector can generate an XML schema from any query’s <retrieve> operation. This schema can then be used to generate a Live Forms form. Try now: Generate an XML Schema. You’ll get back an XML schema. Save the resulting XML schema file to disk and upload it to Live Forms. See the documentation for detailed instructions. Note that we need to pass in a known customer number so that the query can successfully execute.
3. Create the form
We’ll use the form from the Dynamic Pick Lists section discussed above. It already has a dynamic pick list which lists the customers in the format value=label (e.g. 242=Alpha Cognac). Edit the form and:
- Create a new data source from the Schema uploaded above in Step 2.
- Add controls to the form and rearrange them as desired.
4. Link to the database
Connect to the DB query defined above. We use Doc URI wizards to do this. In a nutshell, this means configuring the form so that the data source is bound to the URL for the query that we saw in Step 1. The Database Connector Tutorial explains this in great detail.
Use Case: Pull Multiple Results from SQL Database
Multiple Rows from SQL
Now we’ll take a look at queries that return multiple results. It’s another very common scenario in real applications e.g. look up order line items for a particular order. With frevvo’s Database Connector, complex forms like this can be created without programming.
The steps are as follows:
1. Define the SQL Query
<queryset name=”BIRT” …>
<query name=”orderDetailsByOrder”>
<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
WHEREo.productCode=p.productCode and o.orderNumber=
{onum} ORDER by o.orderLineNumber
…This is a straightforward query that simply retrieves order details for a particular order number. It can be accessed via a URL that looks like this: http://<server>/database/BIRT/orderDetailsByOrder?onum=10100. As before, the {onum} in curly braces is a variable whose value should be passed in the URL. You’ll get back an XML document with order details.
Connect this to the form. We will use Live Forms’s built-in XML features to generate controls and populate them from the database.
2. Generate an XML Schema
The DB connector can generate an XML schema from any query’s <retrieve> operation. This schema can then be used to generate a Live Forms form. Try now: Generate XML schema in your browser, save the resulting XML schema file to disk and upload it to Live Forms. See the documentation for detailed instructions. Note that we need to pass in a known order number so that the query can successfully execute.
3. Create the form
We’ll use a copy of the form we created for the Dynamic Pick List. It already has a dynamic pick list which lists the customers in the format value=label (e.g. 242=Alpha Cognac). Edit the form and:
- Create a new data source from the Schema uploaded above in Step 2.
- Add controls to the form and rearrange them as desired.
4. Link to the database
Connect to the DB query defined above. As before, we use Doc URI wizards to do this. In a nutshell, this means configuring the form so that the data source is bound to the URL for the query that we saw in Step 1. The Database Connector Tutorial explains this in great detail.
Example Business Forms Using frevvo’s Database Connector and a SQL Database
To bring it full circle, let’s look at specific use cases where you may want to use frevvo and the Database Connector to build a business form.
Manage Customer Orders from a Dropdown List
One common use of the frevvo Database Connector is to create a form for members of your team to see and manage customers orders.
From this form, you can select the customer from the drop-down list.
Once you choose a customer, you’ll be able to see a list of orders associated with that customer in the other dropdown and select an individual order to see the details below.
Try now: Dynamic Populate Sales Orders from a Database
