[Zope] Strange error using DTML with MySQL to build a Select List

[David Washburn]

I have encountered strange behavior building a DTML data entry form.

I built a form that includes a <SELECT> field and I populate the field like
this:
===========================
<tr><td>
 <select size="1" name="company_contact_id" >
   <option value=0 >Unknown          </option>
   <dtml-in ListCompanyStaff>
    <dtml-if "account_id == selected_company_contact_id">
       <option value=<dtml-var account_id> selected><dtml-var
full_name></option>
    <dtml-else>
       <option value=<dtml-var account_id>><dtml-var full_name></option>
    </dtml-if>
   </dtml-in>
 </select>
</td></tr>
===========================
Where "ListCompanyStaff" is a ZSQL Method that looks like this:
===========================
select
   concat(ifnull(a.first_name,''),' ',a.last_name,
        ' (from: ' , a.country_code1,',
         acct id:',a.account_id,')' ) full_name,
   ifnull(a.account_id,0) account_id
from  account as a
where user_type = 'STAFF' and account_status = 'ACTIVE'
order by upper(a.last_name), upper(a.first_name), upper(a.middle_initial)
===========================
And it generates a <SELECT> statement that looks like this (cut from the
source code of the generated page). The logic works, and it successfully
chooses the correct line to include a "selected" option when the value in
the list matches the value in the database.
===========================
<tr>
 <select size="1" name="company_contact_id" >
   <option value=0 >Unknown          </option>
        <option value=22>Test Name22 (from: GB, acct id:22)</option>
        <option value=26>Test Name26 (from: VN, acct id:26)</option>
        <option value=10 selected>Test Name10 (from: ID, acct
id:10)</option>
        <option value=5>Test Name5 (from: SG, acct id:5)</option>
        <option value=25>Test Name25 (from: VN, acct id:25)</option>
        <option value=9>Test Name9 (from: ID, acct id:9)</option>
        <option value=14>Test Name14 (from: PH, acct id:14)</option>
        <option value=15>Test Name15 (from: PH, acct id:15)</option>
    </select>
</td>
</tr>
===========================
When I click on the SUBMIT button on the form (the form actually goes back
to itself for data validation and either error messages or calls a ZSQL
method to update the database) I get a MySQL processing exception. I get it
immediately when the SUBMIT button is pressed.

Here is the strange part -- if I cut the Select statement from the source
code generated by DTML, and place it into the DTML document in place of the
<DTML-IN> clause, the form works fine.

So the question seems to be:

Why does using DTML to generate the SELECT statement cause it to blow up
when the SUBMIT button is pressed -- which is when if happens?

Any ideas would be appreciated.

David

=======================================
David T. Washburn DWashburn@AmInfo.com
Amherst, MA USA
413 256-3103