[Zope] Inserting into databases

[Andrew M. Kuchling]

Bradford Hull [mailto:brad@tera.com] writes:
>anywhere.  ZSQL methods seemed to clearly only allow "select" statements;
>when I put in an update, insert or delete, they made a big ugly mess with
>error returns.  After I asked for help, a couple kind souls told me they
>work anyway.  In the 2.0 beta, if I refer to one from dtml, they put up
>their little generated dtml screen after mine and re-require the data I
>already gave them, and then I have not been able to verify that it actually
>got in the database.  Thus, I have spent 3 times as long as I have ever had

I find the mention of "big ugly mess with error returns" suspicious,
because I have some (still unreleased) pages which do SQL inserts, and
getting them working was uneventful; perhaps the Database Adapter is
buggy, or there's some other problem.  The body of the query looks
like this:

insert into registered_users 
(id, prefix, first_name, last_name, suffix, 
 street1, street2, street3, city, state, zip,
 country, email, phone, fax, timezone,
 allow_mailing, password, date_added) values (
<!--#sqlvar userid type=string-->,
<!--#sqlvar prefix type=string-->,
<!--#sqlvar first_name type=string-->,
  ... etc ... , CURTIME() )
 
When I test it from the management screen, Zope displays the generated
SQL and a message: "There was no data matching this Insert a New User
Record query."  (Maybe this message should be reworded, to 'The
<dtml-title> query did not return any rows of data.')  I'm not sure if
this is the error return being described.  

The input form is checked by an ExternalMethod; there seems no other
practical way to do all the checks required for such a large form.
(This is why I don't think ExternalMethods are so advanced; once a
form contains more than about 3 fields, the sanity checking is too
complex to be sanely implemented in DTML.)

The ExternalMethod looks like this (sorry for the wacky indentation;
I'm cutting irrelevant bits out):

def register_new(self, REQUEST=None, RESPONSE=None):
        valid = isDataValid(REQUEST)
        if valid:
            self.sql.insert_user(REQUEST=REQUEST)
            return self.register_OK_html(self, REQUEST=REQUEST,
                                 message = "Your registration is complete.  Welcome, " +
                                 userid + '.')
	else:
            # Return the original form, with the entered values and 
	    # error messages included in the HTML.
            return self.register_form_html(self, REQUEST=REQUEST)


def isDataValid(REQUEST):
    fields_ok = 1
    # Check that various critical fields aren't blank.
    for field in ['userid', 'first_name', 'last_name',
                  'street1', 'city', 'zip', 'email']:
        if REQUEST[field] == "":
            REQUEST.set('err_'+field, 'You must fill in this field.')
            fields_ok = 0

    # Check that the state is a legal 2-character code
    REQUEST['state'] = string.upper( REQUEST['state'] )
    if REQUEST['state'] == "":
        fields_ok = 0
        REQUEST.set('err_state', 'You must fill in this field.')
    elif REQUEST['state'] not in STATES:
        fields_ok = 0
        REQUEST.set('err_state', 
	            'That is not a legal 2-character code for a US state.')

    ... etc ...

    return fields_ok


-- 
A.M. Kuchling			http://starship.python.net/crew/amk/
For the skeptic there remains only one consolation: if there should be such a
thing as superhuman law it is administered with subhuman inefficiency.
    -- Eric Ambler