[SQLObject] Problem With UPDATE

[CLIFFORD I. <cli...@di...>]

Hello,

Since this question did not elicit any response on the Webware list and 
since it could well be a SQLObject problem, I shall ask here as well.

I am running WebWare 0.8.1, SQLObject 0.6, mod_webkit2, and PostgresSQL 
7.4.1 on Mandrake 10. I have a form that uses a JavaScript library to make 
an xmlrpc call to WebWare. When the user enters a new item and hits the 
Submit button, an xmlprc call is made which does an INSERT into the 
appropriate PostgreSQL table via SQLObject and another xmlrpc call is made 
which does a SELECT * on that same table so that the list of items can be 
updated. The the new item is inserted and it appears on the list of items 
displayed in a table on the form, no problem. Keep this in mind.

I have a simple JavaScript routine that sorts the columns in the table when 
the user clicks on a link representing the columns. I noticed that the 
first item was not in the correct sort order. Upon closer examination, I 
noticed it had a leading space. I went to a shell where I had psql running 
and did a "UPDATE my_table set name = 'No Leading Space Name' where id = 
99". I refreshed the browser but saw the first row in the table was still 
the item with the leading space. I double checked from psql to make sure I 
had not made a mistake and sure enough, there was no leading space and 
things sorted properly. I inserted another row using the form described 
above. Again, the new item was added to the db and to the table on the form 
but the problematic data remained *unchanged*. In order for the new item to 
be added to the table, it had to have come from the result of the xmlrpc 
request. If I restart the AppServer and refresh the browser, the data in 
the table on the form reflects what is in the db.

To eliminate the JavaScript libraries, I started a Python shell and hit the 
xmlrpc server from there. The output of psql and the Python shell are below.

devdb=# select * from grp_type;
  id  | description | mod_user_id |        mod_date
-----+-------------+-------------+------------------------
  145 | Test 1      | cilkay      | 2004-09-23 01:53:17-04
  146 | Test 2      | cilkay      | 2004-09-23 01:53:17-04
  147 | Test 3      | cilkay      | 2004-09-23 01:53:17-04
  148 | Test 4      | cilkay      | 2004-09-23 01:53:17-04
  150 | Test 6      | cilkay      | 2004-09-27 04:10:53-04
  151 | Test 7      | cilkay      | 2004-09-27 04:10:53-04
  149 |  Test 5     | cilkay      | 2004-09-27 00:13:21-04
(7 rows)

 >>> import xmlrpclib
 >>> server = xmlrpclib.Server('http://localhost/wk/ww/grpTypeMethods')
 >>> groups = server.getAll()
 >>> print groups
{'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 
'Test 6', '148': 'Test 4', '149': ' Test 5'}
 >>>

devdb=# update grp_type set description = 'Test 5' where id = 149;
UPDATE 1
devdb=# select * from grp_type;
  id  | description | mod_user_id |        mod_date
-----+-------------+-------------+------------------------
  145 | Test 1      | cilkay      | 2004-09-23 01:53:17-04
  146 | Test 2      | cilkay      | 2004-09-23 01:53:17-04
  147 | Test 3      | cilkay      | 2004-09-23 01:53:17-04
  148 | Test 4      | cilkay      | 2004-09-23 01:53:17-04
  150 | Test 6      | cilkay      | 2004-09-27 04:10:53-04
  151 | Test 7      | cilkay      | 2004-09-27 04:10:53-04
  149 | Test 5      | cilkay      | 2004-09-27 00:13:21-04
(7 rows)

 >>> groups = server.getAll()
 >>> print groups
{'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 
'Test 6', '148': 'Test 4', '149': ' Test 5'}
 >>>

Note the leading space in ' Test 5' in the Python results. Note that the db 
has changed. Now I restart the AppServer and execute server.getAll() again.

 >>> groups = server.getAll()
 >>> print groups
{'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 
'Test 6', '148': 'Test 4', '149': 'Test 5'}
 >>>

Python now reflects the state of the database. If I do an INSERT or a 
DELETE on grp_type from psql and execute server.getAll(), the changes will 
be reflected in "groups". It is only on UPDATE that it does not work as it 
should. Why is it behaving like this? More importantly, how can I make this 
work as it should? The xmlrpc code is below.

from WebKit.XMLRPCServlet import XMLRPCServlet
from devdb import *
from psycopg import * # needed to trap for exceptions

class grpTypeMethods(XMLRPCServlet):

     def exposedMethods(self):
         return ['listMethods','insert','getOne','getAll']

     def listMethods(self):
         return ['listMethods','insert','getOne','getAll']

     def insert(self,theDescription):
         try:
             newGrpType = GrpType(
                 description=theDescription,
                 modUserId='cilkay'
                 )
             return newGrpType.id
         except IntegrityError:
             return 0

     def remove(self,theID):
         theGrpType = GrpType.get(theID)
         theGrpType.destroySelf()

     def getOne(self,theID):
         groupType = GrpType.get(theID)
         return [groupType.id,groupType.description]

     def getAll(self):
         result = {}
         grpTypes = None
         grpTypes = GrpType.select(orderBy=GrpType.q.description)
         for theGroupType in grpTypes:
             result[str(theGroupType.id)] = theGroupType.description
         return result


Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326