Odd problem updating a PostgreSQL DB
Hi everyone, I've been working on a project off and on for a while now and I've run into a bug that has me stumped. The project is a PostgreSQL-driven event database for my school district. I've built a user interface to the database, but there's a problem when I try to update the results of an athletic event. 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. Any ideas? I've looked carefully at the code and don't see anything obvious. I can certainly post it if anyone is interested in looking more closely, but I wonder if this little problem is a common one and I'm just missing something between my ears. -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
What database adapter are you using? Perhaps 0 is being interpreted as 0 / false / null, rather than as the integer "0". What datatype is the score stored as? Adam At 03:47 PM 9/13/02, Tim Wilson wrote:
Hi everyone,
I've been working on a project off and on for a while now and I've run into a bug that has me stumped. The project is a PostgreSQL-driven event database for my school district. I've built a user interface to the database, but there's a problem when I try to update the results of an athletic event.
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.
Any ideas? I've looked carefully at the code and don't see anything obvious. I can certainly post it if anyone is interested in looking more closely, but I wonder if this little problem is a common one and I'm just missing something between my ears.
-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
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Fri, Sep 13, 2002 at 02:47:59PM -0500, Tim Wilson wrote:
Any ideas? I've looked carefully at the code and don't see anything obvious. I can certainly post it if anyone is interested in looking more closely, but I wonder if this little problem is a common one and I'm just missing something between my ears.
Please could you check if the same requests in psql procuce the same result ? The same with the Python module the database adapter relies on. This way you would be able to find if the bug is in PostgreSQL, the PostgreSQL Python module you use, or in its Zope DB Adapter. Alternatively you could test with another Zope DB Adapter for PostgreSQL, there are several of them hth Jerome Alet
On Fri, Sep 13, 2002 at 10:38:24PM +0200, Jerome Alet wrote:
On Fri, Sep 13, 2002 at 02:47:59PM -0500, Tim Wilson wrote:
Any ideas? I've looked carefully at the code and don't see anything obvious. I can certainly post it if anyone is interested in looking more closely, but I wonder if this little problem is a common one and I'm just missing something between my ears.
Please could you check if the same requests in psql procuce the same result ? The same with the Python module the database adapter relies on.
Doing the SQL manually with psql works perfectly. I'm using Psycopg and the ZPsycopgDA, BTW. I will post the relevant code in a message to follow this one. Thanks to all who have responded to my question. -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
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. Cheers, Evan @ 4-am
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> </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
Tim Wilson wrote:
On Fri, Sep 13, 2002 at 04:08:00PM -0500, Evan Simpson wrote:
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>
Yah, sure enough:
<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>
I know it sounds weird, but leave off the ":int" marshalling flag on the input fields. That way, zero will get passed to the code above as the string "0", which tests as true, rather than the integer 0, which tests as false. An absent or blank string value will test false, skipping the update (which is why I presume those dtml-ifs are there in the first place). You'll get the same level of input validation out of the type="int" attributes on your dtml-sqlvars as you would from the marshaller, but with the happy added bonus of being able to capture errors in context with a standard_error_message. Cheers, Evan @ 4-am
participants (4)
-
Adam Manock -
Evan Simpson -
Jerome Alet -
Tim Wilson