V4 Database Connector V2.0
From Frevvodocs
Introduction
frevvo’s Professional Edition comes with a free database connector that lets you create forms that read from and write to your database. Users can access any data from your database on your frevvo forms, and if your business rules permit it, use the forms to update or delete the data as well. The connector translates the SQL queries you run against your database into a format your Web browser can understand. Your forms access the connector via a URI that you provide in the Forms Designer.
Please Contact Us if you are using frevvo's Standard Edition and wish to upgrade.
This documentation describes the v2 frevvo database connector. If you are still using the v1.0 database connector you can find the documentation here.
Also refer to the database connector tutorial and additional examples.
What You'll Need
The database connector is a web application and can be deployed under any Servlet container. The connector relies on the JDBC API. You can use any relational database as long as you have a JDBC driver for that database.
- A Servlet Container compatible with the Servlet 2.5 specification. For instance, Tomcat (6 or higher) or Jetty (6 or higher). If you have installed the frevvo-tomcat Live Forms In-house bundle you already have what you need.
- A JDBC driver for your database.
The database connector download comes with several pre-installed drivers. See Install a JDBC driver below.
Installation
Throughout this documentation we are using the frevvo-tomcat bundle as the installation location for the database connector. This is just an example as the connector will work under any compatible servlet container. This documentation assumes that the frevvo-tomcat bundle is installed in c:\frevvo. We'll refer to this directory below as <frevvo-home>.
If you have already downloaded the frevvo-tomcat bundle for Live Forms In-house then you already have the database connector. It is located in the unpacked frevvo-tomcat bundle in <frevvo-home>\frevvo\ext\connectors and is named dbconnector.zip. If not or if you wish to download the latest version then download the database connector from the frevvo web site. You will need to login with your frevvo account
Follow these steps to install the connector:
1) Unpack the database.zip (or dbconnector.zip if from the bundle ext\connectors folder) file to a temporary location of your choice: e.g. c:\tmp. This will create a folder c:\tmp\database. We'll refer to this directory below as <db-tmp>
2) Copy <db-tmp>\database.war to <frevvo-home>\tomcat\webapps
- If frevvo is running this will automatically expand database.war into a <frevvo-home>\webapps\database sub folder
3) If frevvo is not running, start frevvo: Double click <frevvo-home>\start-frevvo
- This will start frevvo and it will automatically expand database.war into a <frevvo-home>\webapps\database sub folder
4) Test the connector installation by copying this Url directly into your browser: http://localhost:8082/database/myStore/allOrders
If the Connector Test failed then:
- Review the previous installation steps
- See starting and testing the connector installation for more detailed instructions and common problems.
- If you cannot get the database connector working please see Getting Help to contact frevvo support.
Follow these steps to configure the connector:
If the connector test above was successful you are now ready to configure the database connector to work with your own database.
1) Copy the JDBC driver appropriate for your database into <frevvo-home>\tomcat\webapps\database\WEB-INF\lib
- The database connector contains pre-installed drivers for the most popular databases. So you may be able to skip this step. See Install a JDBC driver below
2) If you did have to copy a JDBC driver into lib you must then Restart frevvo, Double click <frevvo-home>\stop-frevvo. Wait until frevvo stops. Then Double click <frevvo-home>\start-frevvo
3) Configure your database connector Data Source by editing <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml
4) Define SQL Statements to read/write to your databasse by editing <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml.
We recommend that you do not leave your database configuration.xml file in its default locations. See configuration file location below.
Install a JDBC driver
The connector relies on the JDBC API. You can use any relational database as long as you have a JDBC driver for that database. The database connector download database.zip comes with several pre-installed drivers.
Once you install the database connector, look in <frevvo-home>/tomcat/webapps/database/WEB-INF/lib and you will see (among other jar files) the following pre-installed driver jar files:
- mysql-connector-java-5.1.6.jar - MySql driver
- com.microsoft.sqlserver.jdbc-1.1.3.jar - SQL Server driver
- hsqldb-1.8.0.7.jar - HSQLDB jdbc driver
- org.objectweb.rmijdbc-1.0.0.jar - Access driver (also can be used for SQL Server)
If you don't find what you need pre-installed you will need to locate a driver compatible to your database. Usually you can download your driver from the internet. Try one of these locations
Once you have the driver you need, copy it to <frevvo-home>/tomcat/webapps/database/WEB-INF/lib.
It is also appropriate to copy the driver into any location that is in the CLASSPATH of your servlet container. In a tomcat installation another location would be <CATALINA_HOME>/lib.
Configuration File Location
We recommend not leaving the database configuration file in its default location under the <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml. To move it for example to c:/frevvo/data/database/configuration.xml:
- Copy <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml to c:\frevvo\data\database\configuration.xml
- Edit <frevvo-home>\tomcat\conf\catalina\localhosts\context.xml.default. Add this line to context.xml.default (as a Parameter tag):
<Parameter name="frevvo.connectors.database.configuration" value="c:/frevvo/data/database/configuration.xml" override="false"/>
Note: If frevvo is running, when you edit and save this change to context.xml.default, frevvo should pickup this change within a couple seconds. If it doesn't, then stop and restart frevvo by: Double click <frevvo-home>\stop-frevvo. Wait for it to stop. Then double click <frevvo-home>\start-frevvo
NOTE:'
Currently you MUST restart frevvo after moving the configuration file location. If you don't restart frevvo the connector will return a blank page rather than data and the frevvo-tomcat log will show an NPE error.
Define the Data Source
The frevvo database connector needs to know:
- the path to your database
- a username to login to your database
- a password for this username
- which JDBC driver your database requires.
This information is setup in the file <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml. If you moved this file out of the webapps\database directory to a better configuration file location then edit the file there.
To configure the database connector to interact with your database:
- Edit configuration.xml
- You will see a <queryset> element already in this file (approx 164 lines long). This is the test query you ran when first testing the database connector installation.
- Make a duplicate copy of the entire <queryset> element (all approx 164 lines) so that you have two <queryset> elements in the configuration.xml
- Edit the <resource-def> element in the <queryset> element you just duplicated. The <resource-def> element defines where and how to connect to your own database.
This is the format of the <resource-def> element:
<resource-def>
<url>YOUR database Url HERE</url>
<driver>YOUR driver HERE</driver>
<user>YOUR database user HERE</user>
<password>YOUR password HERE</password>
</resource-def>
For example here is a mySql database named "test" running on localhost on the default port. The login for user "root" requires no password:
<resource-def>
<url>jdbc:mysql://localhost/test</url>
<driver>com.mysql.jdbc.Driver</driver>
<user>root</user>
<password></password>
</resource-def>
A database URL is a Universal Resource Locator (URL) that specifies a particular type of database server (compatible with the JDBC driver you installed or was pre-installed in database\WEB-INF\lib) and a particular host. In addition you can also specify the database name to use for the connection.
The format for the database URL is database driver specific. In the example above for mySql the correct format is:
jdbc:mysql://[host][:port optional]/[database name]
Check with your database administrator or the documentation for your database drive for details on the correct database URL format.
Here is an example that connects to an HSQLDB driver.
<resource-def>
<url>jdbc:hsqldb:file:mystore</url>
<driver>org.hsqldb.jdbcDriver</driver>
<user>sa</user>
<password></password>
</resource-def>
Here is an example that connects to a SQL Server driver.
<resource-def>
<url>jdbc:sqlserver://192.168.1.102;DatabaseName=mystore;</url>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<user>yourUser</user>
<password>yourPassword</password>
</resource-def>
Here is an example that connects to an AS400/DB2 Open Universal driver. Notice that the url also specifics a date separator used in this database.
<resource-def>
<url>jdbc:as400://170.254.98.250:8888;date separator=-</url>
<driver>com.ibm.as400.access.AS400JDBCDriver</driver>
<user>yourUser</user>
<password>yourPassword</password>
</resource-def>
NOTE:
Under most operating systems the database connector will automatically reload the configuration.xml as soon as you edit and save changes, without the need to restart tomcat or the database connector itself.
However under Mac OS auto reload does not work. To ensure that the change is reflected restart tomcat manually via the stop/start frevvo scripts.
Starting and Testing the Connector
Start your servlet container. If you are using the frevvo-tomcat bundle double click <frevvo-home>\start-frevvo. This will start the database connector web application. Access the connector by typing one of the test Urls below directly in your browser.
The database connector comes with a built-in hsqldb database for testing purposes. And the database connector's configuration.xml comes pre-configured with a few sample SQL queries. If you installed database.jar under the frevvo-tomcat bundle, run the following test queries. This will retrieve data from the built-in database:
For the tomcat-bundle running on the default port 8082, this query will return an xml document with two orders.
http://localhost:8082/database/myStore/allOrders
This query will select the order where orderId is equal to 102, and return an xml document with that single order.
http://localhost:8082/database/myStore/orders?orderId=102
Where:
- <host> - the URL for the web server on which you deployed database.war.
- <port> - port where the container is listening for http requests. This defaults to 8082 if the database connector was deployed in the frevvo-tomcat bundle.
- <queryset name> - queryset name defined in the database connector configuration file.
- <query name> – query name defined in the database connector configuration file.
Common Installation Problems
frevvo-tomcat running on non-default port
If you have configured your frevvo-tomcat in <frevvo-home>/tomcat/conf/server.xml to run on a port other than the default 8082, then you must set the port in the Url to the database connector to that different port number.
This will not work:
http://localhost:8082/database/myStore/allOrders
Change 8082 to your tomcat port.
Your browser is not on the same machine as the database connector
If your web browser is not running on the machine where your database connector is installed then you cannot use "localhost" in the Url. You must replace this with the name or ipaddr of the machine running the database connector.
This will not work:
http://localhost:8082/database/myStore/allOrders
Change "localhost" to the actual hostname or ipaddr
The query returns a blank page after you changed the configuration.xml file location
In the v2.1 database connector you MUST restart frevvo after changing the location of configuration.xml. Even though it appears that the changed location is picked up automatically after you save the change in context.default.xml, when you try your query after this change the tomcat logs will have an NPE and none of your queries will work.
The solution is to stop and restart the database connector. If you are using the frevvo-tomcat bundle you can do this by: Double click <frevvo-home>\stop-frevvo. Wait until frevvo stops. Then Double click <frevvo-home>\start-frevvo
Quick Start Sample
The database connector contains a working sample HSQLDB database with some test data. If you are already running the Live Forms In-house frevvo form server you can get started very quickly.
Perform steps 1-4 in the Installation instructions under the title Follow these steps to install the connector. After performing these steps you will have:
- Successfully installed the frevvo database connector and,
- Retrieved data from the built-in test database
If you have any problems with the Quick Start please see Getting Help to contact frevvo support.
Define the SQL Statements
The database connector reads its definitions from a configuration file. By default this file is located in <frevvo-home>/tomcat/webapps/database/WEB-INF/etc/configuration.xml but you can change the location.
This is an example configuration file:
<dbconnector version="2.0">
<queryset name="myStore" timeStampFormat="yyyy-MM-dd HH:mm:ss"
dateFormat="yyyy-MM-dd"
xmlDateFormat="yyyy-MM-dd">
<!-- HSQLDB -->
<resource-def>
<url>jdbc:hsqldb:file:mystore</url>
<driver>org.hsqldb.jdbcDriver</driver>
<user>sa</user>
<password></password>
</resource-def>
<query name="allOrders">
<retrieve>
<statement>
SELECT * FROM "orders"
</statement>
</retrieve>
</query>
<query name="orders" autocreate="true">
<retrieve> <!-- Maps to the HTTP GET method -->
<statement>
SELECT * FROM "orders" WHERE "orderId"='{orderId}'
</statement>
</retrieve>
</query>
<query name="allProducts">
<retrieve>
<statement>
SELECT * FROM "products"
</statement>
</retrieve>
</query>
</queryset>
</dbconnector>
The xml elements in the file are as follows:
- The queryset element groups together a Data Source definition and a list of query elements.
- The SQL queries defined under each query element will be executed against the respective Data Source.
- Each query element defines a SQL statement for each CRUD operation (create, retrieve, update, delete).
- You can define as many queryset elements as required with each pointing to a different Data Source. In essence, you can have one instance of the database connector working with multiple databases.
If you update the configuration file, the database connector will pick up changes automatically.
SQL Statements
You define the scripts that will work with your database in the configuration file. For example, the sample configuration file defines a query called customers. That query assumes that a table such as the one below exists in your Data Source.
CREATE TABLE customers ( customerId INT, firstName VARCHAR(50), lastName VARCHAR(50) )
The SQL statements are nested inside a <query> element and one query can include up to four SQL statements. This is part of the SQL-to-Browser translation—four is the “magical” number because under the covers the connector is translating the four basic SQL functions: create, retrieve, update and delete (CRUD) to the four basic browser functions of POST, GET, PUT and DELETE. That is reflected in the children elements of the <query> element: <create>, <update>, <retrieve> and <delete>.
One query cannot have two SQL statements of the same type. If you need two different <retrieve> statements (for example, Select * from customers and Select * from Product), you’ll need two different <query> elements. A query may have fewer than four SQL statements—if users can’t delete data from your database via your forms, your query does not need a <delete> operation.
Here is the retrieve operation for query customers. The SQL statement returns all records from the customers table that match a given customer id:
<query name="customers">
<retrieve>
<!-- maps to the http GET method -->
<statement>
SELECT * FROM customers
WHERE customerId='{customerId}'
</statement>
</retrieve>
<!-- Omitted other statements -->
</query>
You can use any valid SQL statement in the configuration.
Note the string {customerId}. The database connector SQL statements are actually templates that are resolved at run time using values passed in the http request. In the example above, if there is a parameter in the http GET request that hits the connector with customerId=1234 than the statement would return the record for customer 1234.
Auto Create Rows
You can set the attribute autocreate in a query element.
<query name="customers" autocreate="true">
This property applies only when users submit an HTTP PUT request to the database connector. The property tells the database connector to create a new row in the database if one doesn't exist already meaning that the connector will run the create statement automatically if the update statement fails. In summary:
- If the user is updating an existing record, the Update statement will work as it normally does and the autocreate function won’t kick in.
- If the user is adding a new record, the update statement will fail (by design, because the record cannot exist if the user hasn’t added it yet) and the Connector will then run the create statement.
The autocreate feature is particularly useful when working with frevvo's repeat control. frevvo's repeat control gives you the ability to work with dynamic collections, for instance: customers, cars, addresses, dependents and others. When the user loads the form, the form may be initialized with some items (we will see how to do that with frevvo later). If the user adds new items to the collection and submits the form, those items will be automatically added to the database if autocreate=true
This behavior is actually enabled by default so if you want to turn it off you can set autocreate to false.
Auto Delete Rows
The autodelete feature is useful when working with frevvo repeat controls. Imagine you have a collection of elements in the form that were initialized from a database. If you eliminate an item in the collection and submit the form, the connector will automatically remove the item from the database. For that to happen, set the attribute autodelete to true in the query element.
<query name="customers" autocreate="true" autodelete="true" deleteKey="customerId">
Behind the scenes, the connector actually compares the items in the database with what is submitted in the form. That comparison criteria is based on a key that you define with the attribute deleteKey (required). The deleteKey value is normally the name of the primary key in the table that contains the repeat items.
Dates and Timestamps
If you define a date, time or timestamp column in your database the database connector will need to know the format of those dates in order to properly parse them. Also, when the connector reads the dates from the database, it will transform them to XML dates and those also can have a specific format. You can define both the databse and the xml date formats. Those definitions are done by defining attributes in the queryset element. For instance:
<queryset name="myStore" timeStampFormat="yyyy-MM-dd HH:mm:ss" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd">
<query name="customers">
<retrieve>
<statement>
SELECT * FROM customers
WHERE customerId='{customerId}'
</statement>
</retrieve>
</query>
</queryset>
In this case, the time stamp and date formats in the database are "yyyy-MM-dd HH:mm:ss" and "yyy-MM-dd" respectively. That is the format the database connector will use to parse the date types from the database.
On the other hand, when the XML documents is created, the date format will follow the definition of the attribute xmlDateFormat.
Creating a frevvo form to work with your database
There are two ways of creating frevvo forms:
- dragging and dropping controls from the designer palette
- creating a form from an XML schema. In this case, the controls will be automatically created for you based on the XML schema definitions.
When working with the database connector, you will always used the latter and create the controls from the schema. In the future we intend to relax this requirement.
Generating the XML schema
You can use the database connector to generate the schemas you will need to use in the 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>/dbconnector/<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 customers example we've been using so far, point your browser to the url:
http://localhost:8181/dbconnector/myStore/customers/schema?customerId=1234
You should get an XML schema similar to the following:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns="http://www.frevvo.com/database/customers" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.frevvo.com/database/customers">
<xsd:element name="customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="row">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="customerId">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="11"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element minOccurs="0" name="firstname">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element minOccurs="0" name="lastname">
<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>
Save the schema as an .xsd file in your file system. You can then upload the schema to frevvo and generate controls from the schema elements that map to your database tables. If you have multiple queries in your configuration file, you’ll need to generate a schema for each query.
Creating a frevvo form to work with your database
There are two ways of creating frevvo forms:
- dragging and dropping controls from the designer palette
- creating a form from an XML schema. In this case, the controls will be automatically created for you based on the XML schema definitions.
When working with the database connector, you will always used the latter and create the controls from the schema. In the future we intend to relax this requirement.
Generating the XML schema
You can use the database connector to generate the schemas you will need to use in the 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>/dbconnector/<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 customers example we've been using so far, point your browser to the url:
http://localhost:8181/dbconnector/myStore/customers/schema?customerId=1234
You should get an XML schema similar to the following:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns="http://www.frevvo.com/database/customers" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.frevvo.com/database/customers">
<xsd:element name="customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="row">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="customerId">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="11"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element minOccurs="0" name="firstname">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element minOccurs="0" name="lastname">
<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>
Save the schema as an .xsd file in your file system. You can then upload the schema to frevvo and generate controls from the schema elements that map to your database tables. If you have multiple queries in your configuration file, you’ll need to generate a schema for each query.
Document URI Properties
After you’ve generated controls from the elements in your database schema, set your form’s Document URI. To access the form’s properties click the toolbar at the top of the form, then click the Advanced tab in the left-hand side of the Forms Designer.
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 initialize 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:8181/dbconnector/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 a Document URI 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 than 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 than execute the updade 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’ve retrieved it.
If you have already uploaded the schema to frevvo, you can still make the changes and update the schema. The process of updating the schema is described here;
Below are common customizations:
Adding Dropdown/Radio Options
Suppose you want users to select a specific manager’s name from a dropdown list. Your database has a 50-character limit but naturally does not include specific manager name restrictions, so the schema you retrieve initially might look something like this:
<xsd:element minOccurs="0" name="Manager">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
The control you generate from the initial schema will be formatted as a text control. You can make the control a dropdown by changing the control’s Format As property but you must add the actual restrictions to the schema itself, as shown below.
<xsd:element minOccurs="0" name="Manager" type="ManagerType"/>
<xsd:simpleType name="ManagerType">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="John Doe"/>
<xsd:enumeration value="Pat Johnson"/>
<xsd:enumeration value="Mary Smith"/>
</xsd:restriction>
</xsd:simpleType>
You also can populate dropdown boxes dynamically from your database by writing a rule.
Adding Checkbox Options
You may also need a control to allow multi-select options. Like dropdowns and radios controls described above, the control you generate from the initial schema will be formatted as a text control. You can make the control a checkbox by changing the control’s Format As property to checkbox and changing the actual schema typeto xsd:list as shown below:
<xsd:element minOccurs="0" name="colorChoice">
<xsd:simpleType>
<xsd:list itemType="xsd:string"/>
</xsd:simpleType>
</xsd:element>
Once the Color Choice control is formatted as a checkbox, a labels property will display in the properties settings tab for this control. Enter your checkbox options here.
Limitations
Currently it is not possible to list the options directly in the schema.
Currently it is not possible to have a checkbox option containing a space. So a label of "dark blue" will appear in the database as "dark_blue".
Changing text controls to date Controls
Controls you generate based on SQL Server date columns initially will be text controls, because all SQL Server dates are in datetime format and therefore have a type of xsd:datetime in the schema you retrieve from the database.
In the schema, change the XSD type from xsd:string to xsd:date If in your you want a date control instead of a text control.
Limiting potentially repeating Data
Often the schema from your database will allow any number of elements even though you know your form will never use more than one at a time. Every schema includes the “rows” element as shown in the partial schema below—note that based on the maxOccurs value of “unbounded” in the schema, there can be an unlimited number of customer elements.
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.frevvo.com/database/Customer"
targetNamespace="http://www.frevvo.com/database/Customer">
<xsd:element name="Customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="row">
<xsd:complexType>
<xsd:sequence>
Since the schema says the element is unbounded, the control you generate initially will be a repeat control. You’ll see the Repeat Row heading in the Forms Designer and if you don’t make any changes, users will see the + sign that comes with repeat controls.
In this case, change the schema to indicate you only want to work with one customer at a time, as shown below.
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.frevvo.com/database/Customer"
targetNamespace="http://www.frevvo.com/database/Customer">
<xsd:element name="Customer">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="1" name="row">
<xsd:complexType>
<xsd:sequence>
Note that now maxOccurs="1" and that will force the + sign to disappear.
Type Restrictions
Sometimes the database field will let you enter any string such as a VarChar field. However if you field is a phone number you may which to have the form restrict the input to only valid phone numbers. See the section on patterns for more common type restrictions. In this example you still want the form input to be a text box. So we do not change the Format As.
Edit your xsd:
<xsd:element minOccurs="0" name="Home Phone">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="12"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
Add the following simpleType restriction to your xsd.
<xsd:simpleType name="phoneType">
<xsd:restriction base="xsd:string">
<xsd:pattern value="\d{2}-\d{4}-\d{4}"/>
<xsd:pattern value="\d{4}-\d{3}-\d{3}"/>
</xsd:restriction>
</xsd:simpleType>
Finally change the existing type for the Home Phone element to:
<xsd:element minOccurs="0" name="Home Phone" type="phoneType"/>
Now the frevvo form will only accept valid phone numbers. If you enter an invalid phone the form will flag the field as invalid.
Dynamic Dropdown Options
Individual controls also may trigger SQL statements, if you’ve customized your form with rules. See Database Connector Rest Service for an example of using the ResultSet returned from the Database Connector to dynamically populate the options in a dropdown control.
Show/Hide Required Fields
There are times when you have several fields that you want hidden and then only want to make visible depending on the value entered into another field. Thus only when the fields are visible do you want them to be required. Currently hidden required fields are still required and will causes the submit button to remain disabled. Automatically making hidden fields not-required may be added in a future release of Live Forms.
See the documentation for Data Sources and Schemas a solution for implementing show/hide with required controls.
Security
When accessing your database from an externally hosted SaaS frevvo Form Server, follow these steps to ensure your data is secure. You may also wish to consider one or all of these step even when using the frevvo in-house version if you feel your intranet is not secure.
Using the frevvo Database Connector's security mechanism, combined with only accepting SSL connections to the database connector from the web application container, will prevent unauthorized access to your database queries. The steps below describe how to secure your data.
Configure SSL
The database connector doe not have any specific configuration to handle SSL. Since it runs inside the Servelet container, it is typically the responsibility of the container to handle this layer of security. In any case, here is what we have done for customers that needed SSL and were using Tomcat:
SSL (Secure Socket Layer), is a technology which allows web clients and web servers to communicate over a secured connection. This means the data being sent is encrypted by one side, transmitted, and then decrypted by the other side before processing. This is a two-way process, meaning that both the server AND the client encrypt all traffic before sending out data.”. The basic steps:
- Configure Tomcat to accept only SSL connections to the frevvo Database Connector. This encrypts data sent between the hosted frevvo Form Server and the frevvo Database Connector installed in your local machine, thus protecting the queryKey.
- Create a SSL self-signed certificate and install in Tomcat’s keystore. The self-signed certificate will ensure that the data being transmitted and received by the frevvo Database Connector is private and cannot be snooped by anyone who may be eavesdropping on the connection.
- If you are running the frevvo Database Connector under Tomcat you can find more details here: Tomcat6 SSL How To
The queryKey attribute
The queryKey attribute enables a password authentication mechanism that limits query execution to only those who know the queryKey password string. Every HTTP request that is sent to the database connector to execute that query must contain the key. The connector will deny all requests that do not contain the key.
For example, given the configuration below:
<query name="customers" queryKey="abc123">
<retrieve>
<!-- maps to the http GET method -->
<statement>
SELECT * FROM customers WHERE customerId='{customerId}'
</statement>
</retrieve>
</query>
A valid request would be:
http://localhost:8181/dbconnector/myStore/customers?queryKey=abc123&customerId=23434
SQL Injection Protection
The frevvo Database Connector automatically protects your data from Injection Attacks. No configuration is required for this security measure.
Examples
There are additional resources designed to help you learn how to use the power of frevvo forms with your database.
The database connector tutorial contains step by step instructions that demonstrate how to populate a web form with data from the database using the master/detail pattern. And also walks you thru populating a frevvo dropdown control with values dynamically from a database.
The database connector examples contain commonly used form design patterns using data from your database in simple examples designed to get you going quickly and give you ideas for your own forms.
SQL Query Examples
A solid understand of SQL syntax is helpful when creating forms that interact with your database. Below are common and useful example queries.
Like Query
Sometimes it is useful to match a row where the matching string is not exact. To do this use the SQL Like and % wild card. In this example we want to retrieve all customers that have an email address with a specific email domain 'frevvo.com'. The % wild card must be coded into the configuration.xml query. It cannot be passed down to the query as part of the URI template.
<query name="customers">
<retrieve>
<statement>
SELECT * FROM customers
WHERE emailAddr='%{domain}%'
</statement>
</retrieve>
</query>
