Choose a Design Approach

There are three ways of connecting your form to your database. frevvo recommends that you read and understand each approach carefully before moving forward with form design in order to determine the most efficient approach, and prevent the need to redesign later. This table can help you decide which design approach to use based on your business requirements.

	The Doc URI Approach	Post via Web Hook	The Business Rules Approach	Dynamic Options
Overview	Automatically create the controls in your form based on your database table, and use the Document Action user interface to set up Doc URIs that can perform CRUD Operations.	Automatically post data from your form/workflow to your database mid-workflow using the Web Hook feature.	Write JavaScript business rules to perform CRUD operations.	Dynamically retrieve options at runtime from the Database Connector (or any RESTful web service) using the Options Src control property, no code needed.
Recommended Use Cases	Create or update multiple records at a time Perform multiple CRUD operations in each database call	Create records mid-workflow	Create or update a single record Get/Write only a few values from/to the DB Populate options in a repeating selection control, and/or use a complex option label	Standalone selection control (not repeating) Simple value and label from a DB column
Form Design	from Schema design method Instantly creates controls that match your DB columns' names and types Some control properties available in form designer, others edited by modifying the schema Automatically passes all control values in Doc Action	from Schema design method Instantly creates controls that match your DB columns' names and types Automatically passes all control values in Web Hook	from Palette and/or from Schema design methods Designer creates and configures all controls to match Database column names and types. All control properties available for editing in Form Designer (from Palette) Must pass each individual control value in Business Rule(s)	Use Checkbox, Dropdown, Radio or ComboBox control with the from palette design method.
Performance	Call the DB Connector once for all operations (using the autocreate feature) → fast, reliable performance	Call the DB Connector once after the prior step unloads (Continue is clicked).	Call the DB Connector query for every add/update operation (this can impact performance)	Calls the DB connector for each instance of the selection control. Fast, reliable performance for non-repeating controls. Performance issues may be seen for repeating controls.
Technical Skills	No code needed Create/Modify xsd schema document	No code needed Create/Modify xsd schema document	JavaScript code required	No code needed
Documentation	Generating XML Schema \| Data Sources \| CRUD Operations Example \| 10-Minute Doc URI Video	Post via Web Hook	Forms Designer \| Palette Controls \| Integrations \| Business Rules HTTP Methods \| Rule Examples	Control Property Settings - Dynamic Options

You can also use a combination of these methods when integrating your forms and workflows with your database.

Design Methods

Create a form from an XML schema. In this case, the controls will be automatically created for you based on the XML schema definitions, and you can use the Doc URI approach.
Drag and drop controls from the designer palette. You will have all control properties available in the designer, but you will only be able to use the Business Rule approach for database integration.
You may also use a combination of the two methods and create some controls from schema and others from the palette.

Our Client Services team can help you determine which approach is right your for business requirements. Contact us to get started.

The Doc URI Approach

This is the recommended approach because it involves automatically generating controls in your form based on the database table you will be integrating with, and using the no-code Document Action: Manually Set Doc URIs to perform CRUD Operations on your database. The Doc URI Approach must be used with the "from an XML schema" design method. Follow these steps.

Generate the XML Schema based on your SQL Statements that allows you to add form controls that match your database table columns.
Add Form Controls from Schema in the form/workflow designer.
Set Doc URIs to automatically read from and write to the database.

Check out this 10-minute video showing how you can perform all CRUD Operations on your database using the no-code Doc URI Approach.

Generate the XML Schema

You can use the database connector to generate the schemas you will need to use in your frevvo form. To fetch the schema, type a URI in the browser that looks like the example below. This is almost identical to the query we used to test the connector, but it tells the connector to GET the schema instead.

http://<host>:<port>/database/<queryset name>/<query name>/schema?<template name>=<template value>

If your SQL Retrieve statement includes template variables, you’ll need to add the variable names as shown below. The schema itself does not require variables but the connector fetches the schema the same way it fetches data for the retrieve statement, so the connector expects the template names and values in the URI.

For the example we've been using so far, point your browser to the url: http://localhost:8082/database/BIRT/allCustomers/schema?customerNumber=237

You should get an XML schema similar to the following:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema xmlns="http://www.frevvo.com/database/allCustomers" xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.frevvo.com/database/allCustomers">
  <xsd:annotation>
    <xsd:appinfo xmlns:f="http://www.frevvo.com/database/" f:connectorVersion="2.5.3-r30969" f:digest="4f2c46033e8ff5a221ab7106c4003833157e7f6a" f:last-modified="Sat Aug 19 19:06:45 EDT 2017" source="http://www.frevvo.com/database/"/>
  </xsd:annotation>
  <xsd:element name="allCustomers">
    <xsd:annotation>
      <xsd:appinfo xmlns:f="http://www.frevvo.com/database/" f:autoCreate="true" f:autoDelete="false" f:enabled="true" source="http://www.frevvo.com/database/"/>
    </xsd:annotation>
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element maxOccurs="unbounded" minOccurs="0" name="row">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="customerNumber" type="xsd:int"/>
              <xsd:element name="customerName">
                <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                    <xsd:maxLength value="50"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The f:connectorVersion="2.5.3-r30969 attribute in the above example reports the version of the Database Connector that was used to generate the schema. The f:last-modified="Sat Aug 19 19:06:45 EDT 2017 attribute reports the date/time of the last modification to your configuration.xml file. These attributes will help you manage xsd changes.

Save the schema as an .xsd file in your file system.

Your schema may not work correctly if you generated the schema in the Chrome browser and then used the Select All, copy/paste function to create the xsd file. Use the Save Page As function instead.

If you have multiple queries in your configuration file, you have two options:

Generate the schemas for an entire queryset. When there are a large number of queries in the configuration.xml, getting the schemas for all of them eliminates having to create a schema one by one for each query. 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.
http://<server>:<port>/database/BIRT/schema

Generate a schema for each query.

http://<server>:<port>/database/BIRT/allCustomers/schema

The word "schema" is a reserved word for query names. If you have a query in your configuration.xml with a name that includes the word "schema" frevvo will show an error.

Add Form Controls from Schema

To use your schema, first, upload the schema in the Schemas tab of your frevvo Project. Visit the Using Your Schema documentation for detailed steps.

frevvo v11.0 Documentation > Creating a Form Connected to your Database > v10.1 Upload Schema.PNG

Next, open your form or workflow and add one or more elements from schema in the Data Sources panel. This allows you to generate controls from the schema elements that map to your database tables. See the Data Sources chapter for detailed steps.

frevvo v11.0 Documentation > Creating a Form Connected to your Database > v10.1 Data Sources.PNG

Set Document URI Properties

After you have generated controls from the elements in your database schema, set your form’s Document URI to the database connector query URL. Follow these steps:

Click the Settings mode.
Click the Document Actions tab. Click Send Data then select Manually set document URIs.

The first section in the wizard is for the form's default document. This is indicated by the Document Name of "default" displayed in the wizard. The default document maps to all of the controls you added to this form by dragging and dropping from the palette. This is typically not the document you want to configure.

The second section is for the next schema document. You should see the Document name change to your schema's document name.

The next screen has two sections: Read URI and Write URI. Typically, when the form loads, frevvo customers read form data from their database to initialize the form and send the updated XML document to the same database upon form submission. Enter the same URI in the Read URI and the Write URI fields, as shown in the image, with the appropriate SQL statement to run if you are reading from and writing to the same database using the database connector. It is possible to read the form initialization data from one database and then update a different database. Enter different URIs in the Read URI and the Write URI fields if this is your situation.

Enter the database connector Url to your query in the wizard's URL input. If the database connector is running in the same host and port as the frevvo form server, then you can omit http://<host>:<port>/ from the URL. Use templates to create dynamic URLs. See this example below.

You also must specify which of the four SQL statements to run—you do this by choosing the appropriate values in the Document URI Read Method and Document URI Write Method dropdowns according to the table below. (You may notice in the Forms Designer that the Document URI Read Method drop down box includes a POST option, but it is not shown below because it is not used by the database connector.)

SQL Statement Type	Document URI Read Method	Document URI Write Method
Create	Leave blank	POST
Retrieve	GET (or leave blank if you are not retrieving data)	Leave blank if your form is read-only
Update	Leave Blank	PUT
Delete	Leave Blank	DELETE

You’ll see one set of Document URI properties for each schema in your form. Type the Document URI next to the schema name—make sure not to type it next to the Default schema. To specify which of the four SQL statements to run, choose the appropriate values in the Read Method and Write Method dropdowns, based on the information below.

Read Method - Choose GET if your form will be initialized with data from your database; leave the dropdown blank if it won’t. (Don’t set the Read Method to POST; this is used for integrating forms with different back ends.)
Write Method - If your form is read-only, leave this blank. Otherwise, use the chart above chose either PUT or POST. PUT maps to the update method while POST maps to create.

Note that you may choose only one Write Method in the Forms Designer.

Still using the customers example, assume the Document URI for the schema customers is:

http://localhost:8082/database/myStore/customers?customerId={customerId}

If you choose the Write method to be POST, the SQL statement that will be executed is:

<create>               
 <statement>
 INSERT into customers (customerId,firstname,lastname)      
 VALUES ({customerId},'{firstname}','{lastname}')
 </statement>          
</create>

At run time, the values of {customerId}, {firstname} and {lastname} will be replaced by the values of the respective form controls.

What happens at run Time?

Let's assume that you defined the same Document URI in the Read and Write URI fields in the wizard for a schema in the form, a GET Read Method and a PUT Write Method.

When a user loads your form, a GET request for that URI will be sent to the database connector. That will cause the database connector to execute the retrieve operation for the query specified in the URI, take the result of the SQL execution and transform it to XML. The database connector will then return the resulting XML back to the form which will be initialized with the contents of the XML file.

When the user submits the form, the same URI will be executed but now with the PUT (or POST) method. The database connector will then execute the update operation identified by that URI.

Customizing the XML Schema

The schema you retrieve from your database will be relatively generic. As a result, the controls you generate from this database schema might need some tweaking to suit the specific needs of your form. You can modify a control’s behavior by making changes to the schema after you have retrieved it. Please visit the Data Sources chapter for more details on modifying the controls generated from schema.

If you have already uploaded the schema to frevvo, you can still make the changes and update the schema.

Post via Web Hook

In a workflow, the Doc URI writes to the database at the end of the workflow, after the final step is submitted. Perhaps you want to post to the database mid-workflow. You could use the Business Rule Approach, or for a no-code approach, you can simply use the same URL you would use for the Doc URI POST in a Web Hook.

In Workflow Editing Mode, click the + icon after the step that should post data to your database.
Select Add web-hook (HTTP POST)
Enter the Database URL for the query that contains your create statement, e.g. http://localhost:8082/database/products/allProducts. Be sure to check the Data checkbox. Click Submit.

You will see the Web Hook displayed in your workflow diagram, and you can edit it at any time. The Database Connector will run the create statement to post data after the user clicks Continue on Step 1.
frevvo v11.0 Documentation > Creating a Form Connected to your Database > dbc webhook.PNG

View the Web Hook Documentation chapter for more information.

The Business Rule Approach

It is also possible to write business rules to perform CRUD operations. Business rules tend to be more complex and time-intensive to implement. The Business Rule Approach can be used with either the "from palette" or the "from an XML schema" design method. This approach is discussed in further detail with several examples in the following documentation:

The Database Connector Tutorial (recommended)
Business Rules HTTP Methods
Rule Examples for the Database Connector