MeringueMail Database Support

This page is mainly about how you can store MeringueMail forms in a database. The examples on this page assume that the database management system (DBMS) being used is Microsoft Access but the database support has also been tested with MySQL (as described in the documentation) and should work with other DBMS's as well. If you haven't used MeringueMail, you should probably look at this page before reading the following sections.

1. Introduction
Most company web sites will have their own web server that receives forms submitted by visitors. These forms are normally stored (by means of server-side code) in a database on a database server. Once the data is in the database it can easily be processed, i.e. one can extract useful information from the data and identify patterns. If you're using e-mail to receive forms, getting a form's contents into a database can be a pain; in practice one would usually have to enter the data manually. Manual entry is pretty inefficient and error-prone. MeringueMail version 1.2 can store the data in a form in a database table rather than requiring the user to type it all in.

2. Create a Form
To demonstrate MeringueMail's form handling capabilities we'll create a form to ask people whether they believe the economy is getting better, deteriorating or is flat. The following HTML creates the form. The form's name is "economy" and it contains three radio buttons in a radio button group called "state" (for "state of the economy"). The button selected can take on the value "better", "worse" or "same".



Poll




In six months, will the economy be performing

Better?

Worse?

About the same?






The figure below shows how the form appears in a browser.

If we use Response-O-Matic to process the form, we'll see something similar in our browser to what's shown below. There are some things worth noting:

3. Create a Database
To create a database that MeringueMail can use, you'll have to start your DBMS. In Access, it's very easy. Select "New..." on the "Files" menu.

Next you specify that you want to create a new database.

Finally, you give the database a name (e.g. meringue).

The database is now ready to be populated with tables. You may want to create tables of your own but MeringueMail can create and populate the tables it uses.

4. Connecting to the Database using ODBC or JDBC
To connect the MeringueMail client to the database, the DBMS must support either Open Database Connectivity (ODBC) or JDBC (Java Database Connectivity). How to proceed now depends on whether you want to use ODBC or JDBC. If you're using MySQL you can use either ODBC or JDBC. With MS Access you can connect only using ODBC (technically, you connect to Access using JDBC but by using the JDBC-ODBC bridge).

If you want an example of connecting to a database using a pure JDBC driver, see the documentation.

To connect to a database using ODBC, select ODBC Data Sources from the Windows Control Panel.

In the "ODBC Data Source Administrator", choose to "add" a new data source.

You then select the driver for the ODBC data source. Since we're using MS Access, we choose Microsoft Access Driver.

We then choose a name for our database and supply a description. Next we need to select the database on our computer.

After this procedure, our database is registered as an ODBC data source ("meringuemails").

5. MeringueMail and Database Management
If you want to store decrypted forms in your database, you have to register the database with MeringueMail by selecting "New database" from the "Databases" menu. This registration is shown below. You must specify the database name ("meringuemails"), the DBMS being used and the protocol and driver needed to communicate with the database. In an ideal world, one wouldn't need to specify the DBMS since all DBMS's would conform to the ANSI SQL standards. However Access implements SQL particularly poorly and other databases have proprietary but useful data types (or domains, to use the SQL term).

We're now ready to save MeringueMail forms in the registered database. The following two screenshots show four MeringueMail forms in the Inbox and a decrypted form. To save the form in the database, choose the "Database" option on the "File" menu of the MeringueMail message.

After choosing to store the form in a database, you are required to logon to the database. You may have chosen to require a logon name and password to access the database when you created it. Supply the logon name and password if required, otherwise just click "OK".

If the form was successfully written, you'll get a message like the one shown below. Otherwise you'll get some warning message (e.g. if the form already appears to exist in the database) or an error message (e.g. if MeringueMail couldn't connect to the database or couldn't write the form data into a table).

6. Accessing the form in the Database
If everything worked in the previous section, you should now be able to find a new table in the database called "economy", i.e. the table has the same name as the form. If you don't give form a name, the table gets the default name of "MeringueMail".

Viewing the table will reveal a table with six columns (or attributes). The first five attributes always exist in any table created by MeringueMail. The sixth column (and subsequent columns, if your form had more than one field) has the name of the form field and the attribute values are what people voted for.

The MeringueIndex field is effectively a counter that indicates the order in which the forms were written into the database.

The MeringueId field contains the first five bytes of the encrypted form. The first and third records in the table both reflect a vote for the economy getting worse, however their MeringueId values are different as these were two separate votes. The MeringueMail client uses the MeringueId field to check quickly whether a form has already been stored in the table. In practice you would need to receive about one million forms to have a reasonable chance of encountering two different forms with the same identifier.

The MeringueDate field contains the date and time that the form was submitted.

The MeringueName and MeringueEmail fields are unlikely to be useful if you use the Bravenet or Response-O-Matic web form processors. However if you're using MeringueMail with a "mailto:" action, these fields contain the name and e-mail address of the person who submitted the form.

The advantage of storing the decrypted forms in the database is that you can issue SQL quieries to the database, to obtain information. Obviously with our very limited form our ability to extract information is rather constrained. However, the following shows an SQL query on the table that determines how many people think that the economy is deteriorating.

Executing this query we learn that two people believe that the economy is getting worse (see below). Obviously this was an artificially trivial use of SQL. With a more sophisticated form you could issue more complex queries and learn more about the visitors to your website.