[Zope] Re: Odd problem updating a PostgreSQL DB

Tim Wilson wilson@visi.com
Fri, 13 Sep 2002 22:09:59 -0500


On Fri, Sep 13, 2002 at 04:08:00PM -0500, Evan Simpson wrote:
> Tim Wilson wrote:
> >When I attempt to update an event score, it works perfectly unless one
> >of the scores is 0. The SQL update completely ignores the zero value and
> >leaves it as NULL. If I go back and enter two non-zero numbers, things
> >work fine. If I then go back and change one of the scores to a 0, that
> >change is ignored and the old non-zero score remains.
> 
> My first reaction to these symptoms is that you've got a Z SQL Method 
> with roughly the following in it somewhere:
> 
> <dtml-if score>
> UPDATE ....
> </dtml-if>
> 
> Hard to tell without more info, tho.

OK Evan. You asked for it. :-) I'm going to paste in the offending code.
As always, any feedback is greatly appreciated. The formatting will
likely get messed up in the pasting process.

Here's the DTML method that contains the event edit form. Note that the
scores are stored as integers.

editEventForm (DTML Method)
===========================

<dtml-var standard_html_header>
<dtml-in expr="qry_getEventDetails(event_id=event_id)" size="1">
<form action="doUpdateEvent" method="post">
<table cellpadding="5">
<tr class="table-header"><th colspan="2" align="left">Edit an
event</th></tr>
<tr>
 <th align="right">ID</th>
 <td><dtml-var event_id><input type="hidden" name="event_id:int"
value="&dtml-event_id;"></td>
</tr>
<tr>
 <th align="right">Name/Opp</th>
 <td><input type="text" name="event_name" value="&dtml-event_name;"
size="30"></td>
</tr>
<tr>
 <th align="right">Location</th>
 <td>
  <dtml-call expr="REQUEST.set('loc_id', location_id)">
  <select name="location_id:int">
   <dtml-in expr="qry_getlocations(location_id='', location_name='',
city='')">
    <dtml-if expr="location_id == loc_id">
    <option value="&dtml-location_id;" selected>
    <dtml-else>
    <option value="&dtml-location_id;">
    </dtml-if>
     <dtml-if room><dtml-var room>, </dtml-if>
     <dtml-var location_name>, <dtml-var city>
    </option>
   </dtml-in>
  </select>
 </td>
</tr>
<tr>
 <th align="right">Participant(s)</th>
 <td>
  <select name="participant_ids:list:int" size="5" multiple>
   <dtml-in qry_getParticipantsByPermission>
   <dtml-call expr="REQUEST.set('part_id', participant_id)">
    <dtml-in expr="qry_getParticipants(event_id=event_id)">
     <dtml-if expr="participant_id == part_id">
      <option value="&dtml-participant_id;" selected><dtml-var
participant></option>
     </dtml-if>
    </dtml-in>
    <option value="&dtml-participant_id;"><dtml-var
participant></option>   
   </dtml-in>
  </select>
 </td>
</tr>
<tr>
 <th align="right">Start time</th>
 <td><input type="text" name="start_time:datetime" 
value="<dtml-var start_time fmt="%Y-%m-%d %H:%M" null="">"
size="30"></td>
</tr>
<tr>
 <th align="right">End Time</th>
 <td><input type="text" name="end_time:datetime" 
value="<dtml-var end_time fmt="%Y-%m-%d %H:%M" null="">" size="30"></td>
</tr>
<tr>
 <th align="right">Description</th>
 <td><input type="text" name="description" value="<dtml-var description
null="">" size="30"></td>
</tr>

<dtml-if expr="event_type == 'meeting'">      <!--  Options for
meeting-type events -->
<tr>
 <th align="right">Agenda URL</th>
 <td><input type="text" name="agenda_url" value="<dtml-var agenda_url
null="">" size="40"></td>
</tr>
<tr>
 <th align="right">Minutes URL</th>
 <td><input type="text" name="minutes_url" value="<dtml-var minutes_url
null="">" size="40"></td>
</tr>

<dtml-elif expr="event_type == 'game'">       <!-- Event is a game -->
<tr>
 <th align="right">Our score</th>
 <td><input type="text" name="our_score:int" value="<dtml-var our_score
null="">" size="5"></td>
</tr>
<tr>
 <th align="right">Their score</th>
 <td><input type="text" name="their_score:int" value="<dtml-var
their_score null="">" size="5"></td>
</tr>

<dtml-elif expr="event_type == 'tournament'">  <!-- It's a tournament
-->
<tr>
 <th align="right">Finish</th>
 <td><input type="text" name="finish" value="<dtml-var  finish null="">"
size="20"></td>
</tr>

</dtml-if>

<tr>
 <td>&nbsp;</td>
 <td><input type="submit" value=" Submit edits "><input type="reset"
value=" Clear form "></td>
</tr>
</table>
<input type="hidden" name="username" value="<dtml-var
expr="AUTHENTICATED_USER.getUserName()">">
<input type="hidden" name="event_type" value="&dtml-event_type;">
</form>
</dtml-in>
<dtml-var standard_html_footer>


doUpdateEvent (DTML Method) (Sorry about the formatting)
===========================

<dtml-var standard_html_header>

<dtml-if expr="event_type == 'game'">
<dtml-call expr="qry_updateEvent(event_id=event_id,
event_name=event_name, location_id=location_id,
                                 start_time=start_time,
end_time=end_time, description=description,
                                 our_score=our_score,
their_score=their_score, username=username,
                                 participant_ids=participant_ids)">

<dtml-elif expr="event_type == 'tournament'">
<dtml-call expr="qry_updateEvent(event_id=event_id,
event_name=event_name, location_id=location_id,
                                 start_time=start_time,
end_time=end_time, description=description,
                                 finish=finish, username=username,
participant_ids=participant_ids)">

<dtml-elif expr="event_type == 'meeting'">
<dtml-call expr="qry_updateEvent(event_id=event_id,
event_name=event_name, location_id=location_id,
                                 start_time=start_time,
end_time=end_time, description=description,
                                 agenda_url=agenda_url,
minutes_url=minutes_url, username=username,
                                 participant_ids=participant_ids)">
</dtml-if>

<p class="hilite-text"><dtml-var event_name> updated.</p>
<dtml-var standard_html_footer>


qry_updateEvent (Z SQL Method)
==============================

UPDATE event SET
  event_name = <dtml-sqlvar event_name type="nb">,
  location_id = <dtml-sqlvar location_id type="int">,
  start_time = <dtml-sqlvar start_time type="nb">,
  end_time = <dtml-sqlvar end_time type="nb" optional="true">,
  description = <dtml-sqlvar description type="nb" optional="true">,
  <dtml-if our_score>our_score = <dtml-sqlvar our_score type="int"
optional="true">,</dtml-if>
  <dtml-if their_score>their_score = <dtml-sqlvar their_score
type="int" optional="true">,</dtml-if>
  <dtml-if finish>finish = <dtml-sqlvar finish type="nb"
optional="true">,</dtml-if>
  <dtml-if agenda_url>agenda_url = <dtml-sqlvar agenda_url type="nb"
optional="true">,</dtml-if>
  <dtml-if minutes_url>minutes_url = <dtml-sqlvar minutes_url type="nb"
optional="true">,</dtml-if>
  username = <dtml-sqlvar username type="nb">

  WHERE <dtml-sqltest event_id op="eq" type="int">

<dtml-var sql_delimiter>

DELETE FROM event_participant WHERE <dtml-sqltest event_id op="eq"
type="int">  /* Remove references to event */

<dtml-var sql_delimiter>

<dtml-in participant_ids>  /* Reinsert references to events */
INSERT INTO event_participant (participant_id, event_id)
  VALUES (
          <dtml-sqlvar sequence-item type="int">,
          <dtml-sqlvar event_id type="int">
         )
<dtml-var sql_delimiter>


-Tim

-- 
Tim Wilson      |   Visit Sibley online:   | Check out:
Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
W. St. Paul, MN |                          | http://slashdot.org
wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com