V2.0 Database Connector
From Frevvodocs
Database Connector
Introduction
frevvo’s XML 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.
To download the connector, follow the simple instructions on our Download page. Install the connector on Tomcat if you use our in-house plan; hosted plans should follow the standalone installation instructions.
What You'll Need
- Java 5 or 6
- Apache Ant (for standalone installations)
- A JDBC driver for your database. We provide the freely distributable JDBC driver that comes with Microsoft SQL Server 2000 and 2005, but if you prefer you can use the Oracle, IBM DB2or MySQL JDBC driver.
Basic Steps
Below are the basic steps you’ll follow to install and use the connector. Several steps require elaboration; each is explained in more detail below.
1. Install the connector.
2. Edit the configuration file that comes with the installation so it includes your SQL statements and JDBC driver settings.
3. Start the connector and run a simple test in your browser to ensure that it is configured properly.
4. Download an XML schema from your database. You will do this via a URI.
5. Create a form in frevvo that includes controls you generate from the database schema. You will include in the form properties a URI, which tells the form how to access the connector and which SQL statement or statements to execute.
6. Fine tune your form as necessary to meet your business needs. This typically involves editing the downloaded XML schema.
Standalone Installation (Hosted Plans)
1. Download the database connector, save the zip file to your local disk and unzip it.
2. Unpack database.war into a new directory (<install-dir>): jar xf database.war
3. Copy the JDBC driver into <install-dir>/WEB-INF/database/lib. Note that the Microsoft SQL server driver, freely redistributable for sql 2000 & 2005, is included in database.war.
4. Edit the configuration file (<install-dir>/WEB-INF/etc/configuration.xml) to include your SQL statements and JDBC driver information. This is explained below.
If you are using frevvo's hosted site and running the database connector locally, the frevvo site must be able to access your local server via HTTP. For example, it cannot be blocked behind a firewall.
Tomcat Installation (In-House Plans)
1. Drop database.war into Tomcat's webapps directory. (You also can use the Tomcat Manager to upload it.)
2. Edit the configuration file (<tomcat-webapps-dir>/webapps/database/WEB-INF/etc/configuration.xml) to include your SQL statements and information about your JDBC driver. This is explained below.
3. Edit <tomcat-webapps-dir>/webapps/database/WEB-INF/web.xml to reflect the location of the configuration.xml file (WEB-INF/etc/configuration.xml)
4. Copy the JDBC driver for your database into <tomcat-webapps-dir>/webapps/WEB-INF/database/lib.
Configuration File
Overview
The database connector reads from the configuration file that was added as part of the installation process. The file is a sample file—you must edit it by adding your business logic in the form of specific SQL statements that will retrieve information from your database when users view the form, update your database when users submit the form, and so on. You’ll need to a few other pieces of information too, as described below.
JDBC Driver Settings
The top portion of the configuration file asks for your JDBC settings. The example below is from the sample file you’ll be editing and shows the settings for the mySQL driver. If you are using a different driver, you’ll need to change the settings.
<?xml version="1.0" encoding="UTF-8"?>
<database url="jdbc:mysql://localhost/test"
driver="com.mysql.jdbc.Driver" user="root" password="" dateformat="yyyy-MM-dd" port="8181">
The JDBC settings below are for the SQL Server driver. You’ll need to supply a user, password and the date format required by your database regardless of which driver you’re using. The date format tells the connector what format your database uses for dates, so the connector knows how to parse the dates correctly.
<?xml version="1.0" encoding="UTF-8"?>
<database url="jdbc:sqlserver://192.168.1.102;DatabaseName=frevvoTest;"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="yourUser" password="yourPasswd"
dateformat="yyyy-MM-dd" port="8181">
For standalone installations you must also specify on which port the connector will listen. It runs on the http port 8181 by default. You can change this port setting if necessary to avoid conflict with another running web server.
Your SQL Statements
The remaining portion of the file is for your SQL statements. You can use your existing statements or include new ones that you write specifically for your forms. You may have any number of SQL statements, but you must put the statements into groups that the configuration file refers to as queries.
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.
One configuration file query cannot have two SQL statements of the same type—if you have two different Retrieve statements (for example, Select * from Customer and Select * from Product), you’ll need two different queries. 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 SQL delete statement, for example.
The configuration file includes several sample queries and SQL statements, so you can see exactly where to include your queries and SQL statements and simply overlay the samples with your own. Here are the first lines of the first sample:
<query name="Customer" autocreate="true">
<create>
<!-- Maps to the HTTP POST method -->
INSERT into customer SET customerId='{customerId}'
</create>
We’ve called this query “Customer”—you’ll overlay this with a name meaningful for your forms. Leave the autocreate property true for now; this is discussed below.
The first of the four SQL statements you may include in your first query is the Create statement (aka the C in the SQL CRUD equation). Replace our sample with your own INSERT statement, assuming you will be creating records in your database. If you won’t be creating records, delete the entire create section.
You will be using this query as a template, so in the sample, {customerID} identifies a variable that will come at runtime. The same is true for the second of the four SQL statements—the SQL Retrieve query below.
<retrieve>
<!-- Maps to the HTTP GET method -->
SELECT * FROM customer WHERE customerId='{customerId}'
</retrieve>
In this example, if at runtime the customer’s ID is 123456, the connector will use the SQL statement to fetch customer 123456’s data and display it on the form.
Always be sure the variable names match your SQL column names exactly, including case-sensitivity. Note also that the comments indicate the SQL-to-browser translation—for example, the SQL Retrieve statement maps to the GET method that the browser understands. You’ll need to know this when you set the form’s properties; it is explained in more detail shortly.
To complete the configuration file, simply replace our sample SQL statements with yours. Add as many queries as you need—query names must be unique. If you have several queries, consider configuring just one query and performing a simple test in the browser to make sure the connector is configured properly.
Starting the Connector
To start your database connector:
- For standalone installations type: ant run. This will start the connector on port 8181 on the local machine and automatically set up your classpath. If you want to run without ant, you will need to add all the libraries from WEB-INF/lib as well as the directory WEB-INF/classes to your classpath. See the build.xml file for information.
- On Tomcat, restart the database application via the Tomcat Manager or simply restart Tomcat.
Testing the Connector
You access the connector by typing a URI in your browser. (Your forms will access it via a URI that you will provide in the Forms Designer.) The first part of the URI tells your browser where to find the connector; the rest tells the connector which query to execute and provides (if applicable) any parameters that your SQL statement needs.
In your browser, type a URI that looks like this:
http://<host>/database/<query-name>?<templatename>=.
Replace the items above as follows:
- <host> -- the URL for the web server on which you deployed database.war (or for standalone installations the machine name followed by :8181)
- database –- this identifies the database.war file. Don’t change it unless you renamed the file during installation.
- <query-name> – the query name from your configuration file; this must match exactly
- ?<templatename>= -- the parameter required to execute your SQL statement. You supply the specific value after the =. (If your SQL statement has multiple parameters, string the parameters together with the standard ? syntax.)
Below is an example of a URI that would access the customer query in the sample configuration file.
http://www.mydb.com/database/customer?customerID=123456
Assuming your database has a matching record, you’ll see customer 123456’s information in your browser—and you are officially connected!
Note that you do not have to specify the exact SQL statement—only the query name that includes the statement. The connector uses the Retrieve statement by default. In your forms, you’ll need to specify which of the four SQL statements to use. This is described shortly.
Generating a Database Schema
Once you’re hooked up to your database, retrieve the schema for the query, so you can add the schema to a form and generate controls that align with your database. In our example, this would allow us to generate controls such as Customer Name, Customer ID and so forth—your controls of course will be specific to the query in your configuration file.
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>/database/<queryname>/schema
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.)
http://<host>/database/<queryname>/schema?<template 1>=<value>&<template 2>=<value>…
When your SQL statement requires multiple template variables, you string them together using the ampersand (&) sign. For example, if the query in your configuration file is called “customername” and the SQL Retrieve statement requires first and last name parameters, your URI will look like this.
http://<host>/database/customername/schema?firstname=John&lastname=Doe
When the schema is returned in your browser, do a “Save As” and save the schema as an XSD file. 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.
Setting Your Form’s Document URI Properties
After you’ve generated controls from the elements in your database schema, set your form’s Doc URI property with the URI that identifies the query your form will use. 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.
(insert screenshot)
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 dropdown 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.
Remember that when you construct the URI, you must supply the template variable names if your SQL statement requires them. For example, if the SQL statement from your customer query expects firstname and lastname values, your URI would look like this:
http://www.mydb.com/database/customer?firstname={firstname}&lastname={lastname}
The form now knows which query to access in the configuration file and what variables will be passed for the SQL statement at runtime. 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 reading 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--choose PUT for the SQL Update statement, and so on.
Note that you may choose only one Write Method in the Forms Designer. If your form will be triggering more than one write method (updating existing records and inserting new ones, for example), use frevvo’s autocreate and autodelete features.
When Happens When Users Access Your Form
When users access your form, if the document URI properties for your schema specify a Read Method of GET, your form will be populated with live data based on the SQL statement that was executed. When the user submits your form, if there is a Write Method, the database connector will execute the corresponding SQL statement (again based on the query name in the document URI) and insert, update or delete rows in your database. In addition, your form will perform the autocreate and autodelete functions if these attributes are set to true in the configuration file.
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.
Chaining Forms
You can have one form launch another form and specify which SQL statement should populate the second form. This example consists of two forms. The first form is a New Matter Intake form for a law firm. When the client name and other matter information is entered into the first form, a conflicts check must be run.
Coming Soon...
Image:LegalDemo conflictsform.png
app/_-PpqAeCSEdyckfEkZBXSmw/formtype/_FUC50OCTEdyckfEkZBXSmw?_method=post&embed=true&firstname={FirstName}
Passing Runtime Values
To pass the values needed by your SQL statements to fetch the live data users will see on the form, write simple javascript that appends the values to the URI your Web site will use to access the form.
Recall that before you can publish the form on your web site you must share it. This sharing process creates the URI that frevvo uses to identify and display your particular form.
(replace above with screenshot from Share page)
Your javascript code will append to this URI the values for your SQL Retrieve statement. The URI above will now look like the example below--note the appended information in bold.
http://www.frevvo.com:80/frevvo/web/user/gallery/app/_73zHwep_EduZgK0BUzi1Ug/formtype/__AKE4PgqEdusGKt5_HoCDw?_method=post&embed=true&firstname=John&lastname=Doe
If you don’t want to display live data when users access the form, append the variable names but not the actual values as shown below. (Behind the scenes the SQL statement still will run, but it will return no data.)
http://www.frevvo.com:80/frevvo/web/user/gallery/app/_73zHwep_EduZgK0BUzi1Ug/formtype/__AKE4PgqEdusGKt5_HoCDw?_method=post&embed=true>&firstname=&lastname=
If, after the user has accessed the form, you want to retrieve data, you can pass the values for the SQL statements from a particular control in your form by specifying a type ID for the control and writing a rule.
Auto Create
The configuration file includes an autocreate attribute for each query, which is set to true by default.
<query name="Customer" autocreate="true">
The property applies only when users submit the form and only when you set the form’s Write Method to PUT, meaning you want the connector to run the SQL Update statement. The property tells the database connector to create a new row in the database if one doesn't exist already. Specifically, it instructs the connector to run the Create statement automatically if the Update statement fails, and then try the Update statement again after the Create statement has executed.
Use this when your form lets users update existing records and add new ones. (Your Create statement should simply insert into the database the primary key so the Update can do the heavy lifting.)
- 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). The Connector will then run the Create statement, insert the primary key, and then call the Update statement again to populate the rest of the record.
The autocreate feature is great for repeating items. Say when users access your form it runs a SQL Retrieve query to fetch several mailing labels. These are displayed in frevvo’s repeat control as shown below so the user is free to add more labels as necessary.
(Show screenshot)
Let’s say the user updates three mailing labels and adds two new ones. When the form is submitted, the connector runs the SQL Update statement and applies the three changes successfully but naturally fails to update the two new records. The connector simply runs the create statement two insert these two records into the database, and then updates the records after the primary key has been inserted.
Auto Delete
The autodelete feature follows the same principle as the autocreate feature but automatically runs the SQL Delete statement instead of the SQL Create statement when the form’s Write Method is set to PUT and the SQL Update statement fails. This attribute is not set by default in the configuration.xml file, so if you want to use it, add the property to the file as shown below.
<query name="Customer" autocreate="true" autodelete="true"
This feature comes in handy with repeats or when your form has retrieved several records from the database. Imagine an insurance form that lists multiple cars, for example. Users might update information about one of their cars, or they might no longer own the car and wish to delete it from their insurance policy.
As with the autocreate example, if the user is updating information about an existing car, the SQL Update statement runs successfully and the autodelete feature doesn’t come into play. But if the user has deleted a car from the form, the Delete statement will run, so your database will correctly reflect that the user no longer wishes to insure the car.
Fine-Tuning Your Forms
Overview
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 and updating the schema in frevvo.
The process of updating the schema is described here; but below are some common examples that come into play when using the database connector.
Adding Specific Dropdown Choices
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.
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.
If in your form you want a date control instead of a text control, in the schema change the XSD type from xsd: datetime to xsd:date.
Limiting Potentially Repeating Data
Many times 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="Customer">
<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.
(insert screenshot)
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>
The illustration below is how the control looks when it is no longer a repeating control—notice the Repeat Row heading is gone.
(insert screenshot)
When working with repeat items, it is almost always easier to edit the schema than edit the controls, since the Forms Designer has drag-and-drop limitations on repeating 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
“SSL, or Secure Socket Layer, is a technology which allows web clients and web servers to communicate over a secured connection. This means that 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.”
- 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 next step is to add a queryKey attribute to the frevvo Database Connector's configuration.xml files. If you add this attribute and set it to any string, such as "abc123," the database connector will deny all URI requests that do not contain a parameter queryKey=abc123.
<query name="addressbook" queryKey="abc123">
<create>
<!-- Maps to the HTTP POST method -->
INSERT into customer SET addressId='02', street={street}
</create>
</query>
The queryKey request parameter
The next step is to configure your form to send the queryKey to the frevvo Database Connector. You do this by adding the queryKey request parameter to your form's Doc URIs.
- Edit your form properties
- Select the Advanced tab
- The queryKey request parameter must exactly match the queryKey string set in configuration.xml. If the string does not match, the frevvo Database Connector will reject the request to access your data.
- Use https:// in place of http://
https://myhost//database/addressBook?queryKey=abc123&street={street}
SQL Injection Protection
The frevvo Database Connector automatically protects your data from Injection Attacks. No configuration is required for this security measure.
Internal Database Security
It is also important to consider your own internal security standards. The frevvo Database Connector can be configured to access your database securely via user/password authentication. If your database is not already password protected we encourage you to do so.
Edit the configuration.xml and setup define a user/password created for the frevvo Database Connector as follows. This example uses the SQL server driver.
<database url="jdbc:sqlserver://192.168.1.102;DatabaseName=frevvoTest;"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="yourUser" password="yourPasswd">
