Database Connector

From Frevvodocs

If you have upgraded to Live Forms v2 please see the v2.x documentation.

1 Database Connector

Database Connector

frevvo provides a simple connector that integrates forms with your database. The connector is freely available for download and is licensed under an MIT License.

[edit]

Requirements

Java 5 - note that Java 6 is not yet tested/supported.
Apache Ant for conveniently running in standalone mode.
JDBC driver for your database. If you are using Microsoft SQL Server 2000 or 2005, the driver is included. Please see the list below for some other drivers:
- Oracle
- MySQL
- IBM DB2
- SQL Server 2000/2005 (for documentation purposes)

[edit]

Standalone Installation

Download the database connector, save the zip file to your local disk an unzip it.
Unpack database.war into a new directory (<install-dir>): jar xf database.war
Edit <install-dir>/WEB-INF/etc/configuration.xml according to the instructions in the Usage section below.
Copy the JDBC driver (see above) for your database into <install-dir>/WEB-INF/database/lib. Note that the Microsoft SQL server driver, freely redistributable for sql 2000 & 2005, is already included in database.war.
Type: ant run - this will start the database connector on port 8181 on the local machine. This will automatically setup 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 for information.

[edit]

Tomcat Installation

Drop database.war into Tomcat's webapps directory. Alternatively, you can use the Tomcat Manager to upload it.
Edit <tomcat-webapps-dir>/webapps/database/WEB-INF/etc/configuration.xml according to the instructions in the Usage section below.
Edit <tomcat-webapps-dir>/webapps/database/WEB-INF/web.xml to reflect the location of the configuration.xml file (WEB-INF/etc/configuration.xml)
Copy the JDBC driver (see above) for your database into <tomcat-webapps-dir>/webapps/WEB-INF/database/lib.
Restart the database application via the Tomcat Manager or simply restart Tomcat.

[edit]

Usage

The database connector essentially creates one or more REST services out of CRUD (Create, Retrieve, Update, Delete) operations performed via SQL queries. To use the connector, you must first configure it. The configuration uses an XML configuration file (configuration.xml) where you specify the database parameters. If you are using the connector standalone, you will find it in <install-dir>/WEB-INF/etc. If you are using it in a servlet container such as Tomcat, you will find it in <webapps-dir>/database/WEB-INF/etc. Here is an example file (this is using the MySQL driver).

<database url="jdbc:mysql://localhost/test"
    driver="com.mysql.jdbc.Driver" user="test" password="test">
    <query name="Customer">
        <create>
            INSERT into Customer SET customerId='{customerId}',firstname='{firstname}',lastname='{lastname}',
            email='{email}',street='{street}',city='{city}',state='{state}',zipcode='{zipcode}'
        </create>
        <retrieve>SELECT * FROM Customer WHERE customerId='{customerId}'</retrieve>
        <update>
            UPDATE Customer SET customerId='{customerId}',firstname='{firstname}',lastname='{lastname}',
            email='{email}',street='{street}',city='{city}',state='{state}',zipcode='{zipcode}'
            WHERE customerId='{customerId}'
        </update>
        <delete>DELETE from Customer WHERE customerID='{customerId}'</delete>
    </query>
</database>

Here is an example configuration.xml for Microsoft SQL Server:

<?xml version="1.0" encoding="UTF-8"?>
<database url="jdbc:sqlserver://10.1.10.10;DatabaseName=test"
    driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="test" password="test" dateformat="yyyy-MM-dd">
    <query name="Customer">
       <retrieve>SELECT * FROM Customer WHERE customerId='{customerId}'</retrieve>
    </query>
</database>

The URL, driver, user and password fields relate to configuring the JDBC driver. See the documentation for your driver for more details.

You can create any number of queries. Each query has a name (must be unique within the configuration file) and specifies up to four SQL statements - one each for Create, Retrieve, Update and Delete operations. In the example above, I have created a query called Customer and specified four statements. The queries themselves are templates, the strings enclosed in {} are variables. This follows the URI Templates approach.

The connector will then automatically implement the following URIs:

URI	Method	Description
http://<host>/database/Customer/schema?customerId=xxx	GET	Returns an XML schema for this service. This resolves the template using the HTTP query parameters and runs the Retrieve query. In this example, it will run the query: SELECT * FROM Customer WHERE customerId='xxx'. This URI returns a schema, which may then be used to generate a frevvo form automatically.
http://<host>/database/Customer?customerId=xxx	GET	Returns an XML document for this service. This resolves the template using the HTTP query parameters and runs the Retrieve query. In this example, it will run the query: SELECT * FROM Customer WHERE customerId='xxx'. The XML document that is returned will conform to the schema generated above.
http://<host>/database/Customer	POST	Runs the Create query for this service. Typically, you would use this as the document URI for this document with write method POST. When the form is submitted, frevvo will automatically generate an XML document and POST it to this URI. This XML document is used to resolve the Create query template and the query is executed.
http://<host>/database/Customer	PUT	Runs the Update query for this service. Typically, you would use this as the document URI for this document with write method PUT. When the form is submitted, frevvo will automatically generate an XML document and PUT it to this URI. This XML document is used to resolve the Update query template and the query is executed.
http://<host>/database/Customer?customerId=xxx	DELETE	Runs the Delete query for this service. This resolves the template using the HTTP query parameters and runs the Delete query. In this example, it will run the query: DELETE FROM Customer WHERE customerId='xxx'.

If you have started the database connector, you can test these URIs directly from a browser. For the PUT, POST and DELETE methods, simply add _method=PUT to the end of the URI.

To create a form:

Create a suitable configuration as described above.
Access the schema URI to download an XML schema for the query.
Generate the frevvo form from the schema.
Drag and drop to customize its appearance. You may add other fields from the palette or from other schemas if you require.
Specify the document URIs for the form according to the table above. The URI will be http://<host>/database/<query-name>?<query-parameters>. Choose GET for the Read method or leave it empty and either POST, PUT or DELETE for the Write method depending on which query you wish to execute when the form is submitted. See the Figure below. Note that the URI in the figure below is itself a template, which will be resolved when the form is instantiated.
Use the form.

When the form is used here is the sequence of events:

You access the form via its URI (See Adding Forms to your web page and Using Forms for more information.). In this case, you would add a query parameter: c=xxx to the URI.
When the form is instantiated, frevvo will resolve the URI template from the figure above using this query parameter. It will perform a GET to the URI: http://<host>/database/Customer?customerId=xxx.
This GET is handled by the database connector as described above. It will run the Retrieve query for customerId=xxx and return an XML document for the result.
The XML document is used by the form to pre-populate its fields. The form is now being used to edit the record for customer xxx.
When the user makes changes and submits the form, frevvo will generate an XML document and PUT it to the same URI above. This is handled by the database connector, which will run the Update query (resolved using the data in the XML document) and update the database.

[edit]

Security

The configuration.xml <query> element supports a queryKey attribute. If you add this attribute set to any string, such as "abc123", th e database connector will deny all URI request that do not contains a paramater queryKey=abc123.

This, combined with only accepting SSL connections to the database connector from the web application container, will prevent unauthorized access to your database queries.

[edit]

Auto Create

The configuration.xml <query> element supports an autocreate attribute. If this attribute is set to true, the database connector will create a new row in the database if one doesn't already exist.

<query name="Customer" autocreate="true">

When updating, if the update fails (the row does not exist), the database connector will first automatically first execute your <create> query and then try the <update> again.

This is useful when you want to have a single form that supports:

the ability to display data from an existing record in the database (and update that record)
the ability to create a new record in the database.

Consider the example above. When the form is used here is the sequence of events:

You access the form via its URI (See Adding Forms to your web page and Using Forms for more information.). Since you are creating a new customer, the customerId is not known in advance and not specified in the URL.
When the form is instantiated, frevvo will resolve the URI template from the figure above using this query parameter. It will perform a GET to the URI: http://<host>/database/Customer?customerId=.
This GET is handled by the database connector as described above. It will run the Retrieve query for customerId=, which will return nothing.
An empty form is instantiated and displayed to the user.
When the user makes changes and submits the form, frevvo will generate an XML document and PUT it to the same URI above. This is handled by the database connector, which will run the Update query (resolved using the data in the XML document). Since the customer does not exist, the update query will fail and frevvo will automatically run the create query (since autocreate is true) and re-run the update query. This will create the customer record in the database with the correct data.

[edit]

Controlling Form Field Validation

The schema auto-generated when you access the schema URI for the query, often times may not provide the validation you desire for your form. For instance, you may have a database column named email which is a VARCHAR(50). The generated schema element for this field will be of type xsd:string with a restriction of 50 characters maximum length. This will allow any string up to 50 characters long to be entered into the form you create using this schema.

You can easily tell frevvo to enforce that this form field must be a valid email address format, for instance, by adding a simple restriction to the autogenerated schema. To do this:

Open the auto-generated schema in a text editor
Find the xsd element corresponding to the database column email. Here's an example:

  <xsd:element minOccurs="0" name="email">
     <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="50"/>
        </xsd:restriction>
     </xsd:simpleType>
  </xsd:element>

Replace this with your desired restriction. Here's an example:

  <xsd:element minOccurs="0" name="email">
     <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:pattern value="[a-zA-Z\-_][a-zA-Z0-9\-_]*(\.[a-zA-Z0-9\-_]+)*@([a-zA-Z0-9\-_]+\.)+[a-zA-Z]{2,6}"/>
        </xsd:restriction>
     </xsd:simpleType>
  </xsd:element>

If you have already used the original schema to create your form fields, all you need to do is to update that schema in your frevvo application with this new version. Now use your form again and it will immediately begin validating the email address field as desired.

[edit]

Formatting Data

Sometimes database columns contain sensitive data that needs special formatting when displayed in a web form. Here is a SQL query that ensures that the form containing a credit card information only displays the last 4 digits of the credit card number.

SELECT concat('***********', substring(creditCard,-4, 4)) as creditCard, name, address from customer where accountId='{accountId}'

[edit]

Running the Sample

Upload AshishDatabaseGallery.zip to your running frevvo Live Forms server.
Edit the properties of the application - set the port to be the same as the server running frevvo Live Forms. It's currently set to http://localhost:8080, which reflects a frevvo Live Forms server running locally on port 8080. If you are using frevvo's hosted site, simply change this to http://www.frevvo.com (no trailing slash). If you are using a downloaded copy of frevvo Live Forms, change this to http://<server-running-frevvo>:<port> (no trailing slash). See Image below.
Edit the Update Customer form; in the edit container, set the port to that of the server running the database connector. Replace localhost:8181 with <server-running-database-connector>:<port>. See Image below.
Run the contacts.sql script against your database. This will create a table named customer.
The database connector comes with a configuration.xml that is preconfigured with the Customer query described above.

Note that if you are using frevvo's hosted site for your forms 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.

[edit]

Create a new customer

Navigate to the Application in your browser and Click 'Use Form' for the Update Customer form.
Enter a set of values (make sure you enter a customerId that does not already exist in the database) and click Submit.
frevvo will perform a PUT to the database connector. You will see this in the database connector log.
The database connector will attempt to execute the Update query, which will fail.
Since autocreate is set to true, this will cause the Create query to execute, which will create the new customer record.
The database connector will then re-run the Update query which sets the values of the other fields.

[edit]

Update an existing customer

Use the Update Customer form with a query parameter on the URI. To do this, navigate to the Application in your browser and Right Click 'Use Form' for the Update Customer form. Copy the URL, paste it into your browser and add &customerId=xxx. A record must exist in the database for customerId xxx.
frevvo will perform a GET to the database connector. The connector will return an XML document representing the database record.
The form will be displayed with the fields pre-populated from the database.
Edit the values (do not change the customerId) and click Submit.
frevvo will perform a PUT to the database connector. You will see this in the database connector log.
The database connector will execute the Update query, which will update the record in the database.

Retrieved from "http://docs.frevvo.com/docs/index.php/Database_Connector"

Database Connector

From Frevvodocs

Contents