Map a web form to a database table

Wiki Tools

When a web form is created in tropos (with silva forms) the data can be saved to a database when the user submits the form.  This document describes how to accomplish this.

To start, a group creates a web form using silva forms.  For this example we will say the group created a class registration form.  This form is a silva document.  This silva document is called index and is accessed by going to their website: http://website/register/index.

This form needs to be linked to a database table. 

Create the sql for the database table creation

Go to http://website/register/manage. Add a new DTML document and call it xxx.sql.  In this document we will add code to create a new database table.

/* oracle tables in banweb schema */
create sequence banweb.wf_caps_acc_app_seq;
CREATE TABLE banweb.wf_caps_acc_app (
  id NUMBER,
  entry_status varchar2(6) default 'new',
  entry_creationdate date,
  fname varchar2(64) default NULL,
  mname varchar2(64) default NULL,
  lname varchar2(64) default NULL,
  bethelid varchar2(64),
  ssn varchar2(64),
  birthdate varchar2(64),
  course_number varchar2(64),
  course_title varchar2(64),
  course_credits varchar2(64),
  start_date varchar2(64),
  signature varchar2(64),
  sign_date varchar2(64),
  certify_true varchar2(64),
CONSTRAINT wf_caps_acc_app_pkidx primary key (id),
CONSTRAINT wf_caps_acc_app_es CHECK (entry_status in ('old','new','hidden'))
);

In the above code we have the database name: wf_caps_acc_app  (wf=web form). The create sequence statemenet allows tropos to auto increment the id when adding a new entry.  The first three columns of the table are required by tropos.  The constraints are also required by tropos.  The other columns match the columns from the form.

The easiest way to create the database connection is to map the database table column names to the form field names as then tropos will automatically match them for you (as seen later in this document).  Go to the url for the form and view the source (browser view source option or use firebug) and make sure each field name maps to the database table column name.  Also make sure the columns are in the correct order.  Save the file.

Create the database table

Open the sql developer application.  Connect to the PROD8 instance.  Copy your sql create table statement from above and execute it within sql developer.  Now your table should be created.

Update the form

Go to http://website/register/index/edit.  Go to the form configuration (i.e. create a new version, edit the tropos form, and dive down to the HTML Form Editor configuration).  Add a new Basic Database Form Action and edit it.  For the database name enter

banner_forms_db

This is the id of a zope database connector object that will be used to connect.  For the database name enter the name of the database you created

wf_caps_acc_app

Save and edit.  Now you should see a list of field names and a list of db column names to select from for each field name.  The db column names should already be mapped for you as long as you mapped the field and db column names as described above.  Verify these are mapped correctly and then save and publish.

Test the form

Go to the form: http://website/register/index and enter data.  Submit the form.  In sql developer check to ensure your data has been entered and looks correct.  Delete your data.