To process a form, you simply call dp_process. This procedure reads all the variables that begin with "dp" from [ns_getform], and generates an insert or update statement as appropriate.

Arguments to dp_process (using ad_proc):

• -db: Specify a database handle. If no database handle is missing, data pipeline gets one from the subquery pool.

• -db_op: Specify the database operation. The default operation is "update_or_insert" which first tries to update the row in question, and if it fails, tries to insert the row. Other valid values for this argument are "update" to only update the row, or "insert" to only insert the row.

• -where_clause: specify the where_clause to use when updating. This argument is optional only when db_op is insert.

• -form_index: If you need to serialize updates to multiple tables (for example, insert a user before inserting a row into user_group_map), you can use form_index which specifies an identifier to use with dp in the html form element name. This will be clear in the example that follows.

• int: Returns an error if the specified value has non-digits in it.
• money: Like int, except money allows for commas.
• year: Value must be exactly 4 digits
• date: Uses ns_buildsqldate to validate the date. Value must be in YYYY-MM-DD format.
• expr: An expression is a string that does not have spaces and that can be inserted into the database without single quotes. A common case is inserting sysdate where sysdate is much different from 'sysdate'.
• clob: Data pipeline gracefully handles clobs so you never have to worry about the format of the update/insert statement.
• phone: Returns an error if phone number has fewer than 10 characters. This is obviously very limited but works well for the US.
• email: Returns an error if ![philg_email_valid_p $value]

Example - a signup form

create table inquiry_contact (
       contact_id             integer primary key,
       name		      varchar(100),
       phone		      varchar(100),
       email  	              varchar(200)
);

create table inquiries (
       inquiry_id             integer primary key,
       contact_id             not null references inquiry_contact,
       comments		      clob
);

<form method=post action=signup-2.tcl>
<input type=hidden name=dp.inquiries.inquiry_id.int value=1003>
<input type=hidden name=dp_c.inquiry_contact.contact_id.int value=123>
<p>1 Your name:
<br><dd><input type=text name=dp_c.inquiry_contact.name size=45>

<p>2 Your email address:
<br><dd><input type=text name=dp_c.inquiry_contact.email.email size=45>

<p>3 Your phone number:
<br><dd><input type=text name=dp_c.inquiry_contact.phone.phone size=45>

<p>4 Do you have any comments for us?
<br><dd><textarea name=dp.inquiries.comments.clob cols=45 rows=5 wrap=soft></textarea>

<p><dd><input type=submit></form>

Now you want to process the data. Let's say you just want to make sure name is not empty. Here's the tcl script to do your error checking and to process the form:

set_form_variables 0
# dp variables: contact_id, inquiry_id, name, email, phone, comments

set exception_count 0
set exception_text ""

if { ![exists_and_not_null dp_c.inquiry_contact.name] } {
    append exception_text "  <li> Please enter your name"
    incr exception_count
}

if { $exception_count > 0 } {
    ad_return_complaint $exception_count $exception_text
    return
}

set form_setid [ns_getform]

# Need to create the relation
ns_set put $form_setid dp.inquiries.contact_id ${dp_c.inquiry_contact.contact_id.int}

set db [ns_db gethandle]

ns_db dml $db "begin transaction"

# update or insert the record in inquiry_contact 
dp_process -db $db -form_index "_c" -where_clause "contact_id=${dp_c.inquiry_contact.contact_id.int}"

# update or insert the record in inquiries
dp_process -db $db -where_clause "inquiry_id=${dp.inquiries.inquiry_id.int}"

ns_db dml $db "end transaction"

ns_returnredirect /

The sql trace generated would look like:

    select count(1) from inquiry_contact where contact_id=123
    update inquiry_contact set
        contact_id=123,
        name='Mike',
        email='mbryzek@arsdigita.com',
        phone='555-555-5555'
    where contact_id=123 
    insert into inquiry_contact (
        contact_id,name,email,phone 
    ) values (
        123,'Mike','mbryzek@arsdigita.com','555-555-5555' 
    ) 
    
    select count(1) from inquiries where inquiry_id=1003
    update inquiries set
        inquiry_id=1003,
        comments='Nothing to say',
        contact_id='123'
    where inquiry_id=1003 
    insert into inquiries (
        inquiry_id,comments,contact_id 
    ) values (
        1003,'Nothing to say','123' 
    ) 

Things to note: we only use ns_ora clob_dml if the clob you're inserting is indeed longer than 4000 characters. Note that the "select count(1) ...." seems redundant when we could use ns_ora resultrows. Unfortunately, resultrows won't work after [ns_ora clob_dml ...].

Future Enhancements