[web2py] Re: Update legacy table using primarykey failed

2012-02-10 Thread Omi Chiba 
I'm not sure this happen for postgres but I think it's a bug.
I submit the issue #656.

http://code.google.com/p/web2py/issues/detail?id=656


On Feb 9, 1:58 pm, Niphlod niph...@gmail.com wrote:
 that seems a float vs decimal problem

 bottom line, I didn't get  what is not working right now ?

 PS: post table definition in raw sql, model of the table as in the
 models.py, and controller, so I can at least try to reproduce with sqlite
 or postgres (again, sorry but I don't have DB2 or MSSQL test db available)

[web2py] Re: Update legacy table using primarykey failed

2012-02-09 Thread Niphlod 
can you post the html generated by the form before submitting the record 
and the beautified session ?
In order to avoid race conditions, web2py places some hidden fields in the 
form and store a key in the session when accessing a edit form: seems that 
this fields don't match, hence the traceback.

[web2py] Re: Update legacy table using primarykey failed

2012-02-09 Thread Niphlod 
uhm, this is the relevant part for the form I was asking for

input name=_formkey type=hidden 
value=ce992b3a79f9c18f92ec284b9bd443afinput name=_formname 
type=hidden value=Employee/{#39;EmployeeCode#39;: 
Decimal(#39;61339#39;)}

for the session , just put {{=BEAUTIFY(session)}} into the template and 
watch the keys  

Here the strange part is that it seems that employee code is not an integer 
as defined into the model but a decimal. I don't know if this is the actual 
problem, but it is different from the normal way. 

Waiting for the session data to see if formkey is different or equal, that 
is the problem I was addressing in the previous message.

[web2py] Re: Update legacy table using primarykey failed

2012-02-09 Thread Omi Chiba 
You're right !
I thought it doesn't matter but it does.

I define another table which has a same field type for key and
works !!
But I employee table doesn't work even I changed from 'string' to
'double'

SyntaxError: user is tampering with form's record_id:
{'EmployeeCode': '61339.00'} != {'EmployeeCode': Decimal('61339')}

Model
-
db.define_table('Dept',
Field('DeptCode'),
Field('DeptName'),
primarykey=['DeptCode'])

Controller
-
def index():
form = form=crud.update(db.Dept,db.Dept(db.Dept.DeptCode=='J12'))
return dict(form=form)





On Feb 9, 11:46 am, Niphlod niph...@gmail.com wrote:
 uhm, this is the relevant part for the form I was asking for

 input name=_formkey type=hidden
 value=ce992b3a79f9c18f92ec284b9bd443afinput name=_formname
 type=hidden value=Employee/{#39;EmployeeCode#39;:
 Decimal(#39;61339#39;)}

 for the session , just put {{=BEAUTIFY(session)}} into the template and
 watch the keys 

 Here the strange part is that it seems that employee code is not an integer
 as defined into the model but a decimal. I don't know if this is the actual
 problem, but it is different from the normal way.

 Waiting for the session data to see if formkey is different or equal, that
 is the problem I was addressing in the previous message.

[web2py] Re: Update legacy table using primarykey failed

2012-02-09 Thread Niphlod 
that seems a float vs decimal problem

bottom line, I didn't get  what is not working right now ?

PS: post table definition in raw sql, model of the table as in the 
models.py, and controller, so I can at least try to reproduce with sqlite 
or postgres (again, sorry but I don't have DB2 or MSSQL test db available)

[web2py] Re: Update legacy table using primarykey failed

2012-02-09 Thread Omi Chiba 
Thank you the help.

So far, I found...

- Ver 1.99.2 doesn't support update for primarykey
- Ver 1.99.4 does support update for primarykey if it's string

So this will fail. If it works for posgres and maybe problem with db2/
mssql. (I use DAL: mssql2). From the syntax error, it's ignore the
'integer' from define_table and always tried with string...

Table on MSSQL
---
CREATE TABLE [dbo].[Test](
[mykey] [int] NOT NULL,
[myvalue] [varchar](50) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[mykey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Add Record like

111|AAA

Model
-
db.define_table('Test',
Field('mykey','integer'),
Field('myvalue'),
primarykey=['mykey'])

Controller
-
def index():
form=crud.update(db.Test,db.Test(db.Test.mykey==111))
return dict(form=form)

TRACEBACK
-
SyntaxError: user is tampering with form's record_id: {'mykey':
'111'} != {'mykey': 111}




On Feb 9, 1:58 pm, Niphlod niph...@gmail.com wrote:
 that seems a float vs decimal problem

 bottom line, I didn't get  what is not working right now ?

 PS: post table definition in raw sql, model of the table as in the
 models.py, and controller, so I can at least try to reproduce with sqlite
 or postgres (again, sorry but I don't have DB2 or MSSQL test db available)

[web2py] Re: Update legacy table using primarykey failed

2012-02-08 Thread Omi Chiba 
Oh.. mabybe this ??
http://www.web2py.com/books/default/chapter/29/6?search=primarykey#Legacy-databases-and-keyed-tables

The update_record function is not available for Rows of keyed
tables.

Then what option do I have to update the table ?


On Feb 8, 1:45 pm, Omi Chiba ochib...@gmail.com wrote:
 I have legacy table called IQWAGFTY which doesn't have id field so I
 specify primarkey for the existing field.

 I received KeyError: 'TYPRCD' for crud.updae but not crud.create. What
 am I doing wrong or is it not supported to update with primarykey ?

 Model
 -
 db.define_table('IQWAGFTY',
     Field('TYPRCD', length=15, label=Product Code:),
     Field('TYPRKJ', length=50, label=Part Number:),
     Field('TYTYPE', length=2, label=Type:),
     Field('TYFLAG', length=1, default=),
     Field('TYUUSR', length=128, default = auth.user.username.upper()
 if auth.user else None, label=Updated by:),
     Field('TYUDAT', 'datetime', default=request.now, label=updated
 on:),
     primarykey=['TYPRCD'])

 Contoller (update:NG)
 --
 def test():
     return dict(form=crud.update(db.IQWAGFTY,
 db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))

 TRACEBACK:
 Traceback (most recent call last):
   File D:\web2py\gluon\restricted.py, line 194, in restricted
     exec ccode in environment
   File D:/web2py/applications/Inventory_Reserve_Admin/controllers/
 default.py, line 111, in module
   File D:\web2py\gluon\globals.py, line 149, in lambda
     self._caller = lambda f: f()
   File D:\web2py\gluon\tools.py, line 2456, in f
     return action(*a, **b)
   File D:/web2py/applications/Inventory_Reserve_Admin/controllers/
 default.py, line 68, in test
     return dict(form=crud.update(db.IQWAGFTY,
 db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))
   File D:\web2py\gluon\tools.py, line 2978, in update
     detect_record_change = self.settings.detect_record_change):
   File D:\web2py\gluon\sqlhtml.py, line 1033, in accepts
     record_id = dict((k, request_vars[k]) for k in
 self.table._primarykey)
   File D:\web2py\gluon\sqlhtml.py, line 1033, in genexpr
     record_id = dict((k, request_vars[k]) for k in
 self.table._primarykey)
 KeyError: 'TYPRCD'

 Contoller(create:OK)
 -
 def test():
     return dict(form=crud.create(db.IQWAGFTY))

[web2py] Re: Update legacy table using primarykey failed

2012-02-08 Thread Omi Chiba 
Do you have a compatible DMS?
Yes, I'm using DB2.



On Feb 8, 2:01 pm, Richard Vézina ml.richard.vez...@gmail.com wrote:
 ote that currently this is only available for DB2, MS-SQL, Ingres and
 Informix, but others can be easily added.

 At the time of writing, we cannot guarantee that the primarykey attribute
 works with every existing legacy table and every supported database
 backend. For simplicity, we recommend, if possible, creating a database
 view that has an auto-increment id field.

 Here what the book says... Do you have a compatible DMS?

 Richard







 On Wed, Feb 8, 2012 at 2:45 PM, Omi Chiba ochib...@gmail.com wrote:
  I have legacy table called IQWAGFTY which doesn't have id field so I
  specify primarkey for the existing field.

  I received KeyError: 'TYPRCD' for crud.updae but not crud.create. What
  am I doing wrong or is it not supported to update with primarykey ?

  Model
  -
  db.define_table('IQWAGFTY',
     Field('TYPRCD', length=15, label=Product Code:),
     Field('TYPRKJ', length=50, label=Part Number:),
     Field('TYTYPE', length=2, label=Type:),
     Field('TYFLAG', length=1, default=),
     Field('TYUUSR', length=128, default = auth.user.username.upper()
  if auth.user else None, label=Updated by:),
     Field('TYUDAT', 'datetime', default=request.now, label=updated
  on:),
     primarykey=['TYPRCD'])

  Contoller (update:NG)
  --
  def test():
     return dict(form=crud.update(db.IQWAGFTY,
  db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))

  TRACEBACK:
  Traceback (most recent call last):
   File D:\web2py\gluon\restricted.py, line 194, in restricted
     exec ccode in environment
   File D:/web2py/applications/Inventory_Reserve_Admin/controllers/
  default.py, line 111, in module
   File D:\web2py\gluon\globals.py, line 149, in lambda
     self._caller = lambda f: f()
   File D:\web2py\gluon\tools.py, line 2456, in f
     return action(*a, **b)
   File D:/web2py/applications/Inventory_Reserve_Admin/controllers/
  default.py, line 68, in test
     return dict(form=crud.update(db.IQWAGFTY,
  db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))
   File D:\web2py\gluon\tools.py, line 2978, in update
     detect_record_change = self.settings.detect_record_change):
   File D:\web2py\gluon\sqlhtml.py, line 1033, in accepts
     record_id = dict((k, request_vars[k]) for k in
  self.table._primarykey)
   File D:\web2py\gluon\sqlhtml.py, line 1033, in genexpr
     record_id = dict((k, request_vars[k]) for k in
  self.table._primarykey)
  KeyError: 'TYPRCD'

  Contoller(create:OK)
  -
  def test():
     return dict(form=crud.create(db.IQWAGFTY))

Re: [web2py] Re: Update legacy table using primarykey failed

2012-02-08 Thread Richard Vézina 
Maybe the :

migrate=False


I don't see it in your model so maybe web2py try to modify you database??

I have never use keyed table since web2py is not designed for and strongly
suggest to migrate data into a surrogate refactored database model...

Richard

On Wed, Feb 8, 2012 at 3:07 PM, Omi Chiba ochib...@gmail.com wrote:

 Do you have a compatible DMS?
 Yes, I'm using DB2.



 On Feb 8, 2:01 pm, Richard Vézina ml.richard.vez...@gmail.com wrote:
  ote that currently this is only available for DB2, MS-SQL, Ingres and
  Informix, but others can be easily added.
 
  At the time of writing, we cannot guarantee that the primarykey attribute
  works with every existing legacy table and every supported database
  backend. For simplicity, we recommend, if possible, creating a database
  view that has an auto-increment id field.
 
  Here what the book says... Do you have a compatible DMS?
 
  Richard
 
 
 
 
 
 
 
  On Wed, Feb 8, 2012 at 2:45 PM, Omi Chiba ochib...@gmail.com wrote:
   I have legacy table called IQWAGFTY which doesn't have id field so I
   specify primarkey for the existing field.
 
   I received KeyError: 'TYPRCD' for crud.updae but not crud.create. What
   am I doing wrong or is it not supported to update with primarykey ?
 
   Model
   -
   db.define_table('IQWAGFTY',
  Field('TYPRCD', length=15, label=Product Code:),
  Field('TYPRKJ', length=50, label=Part Number:),
  Field('TYTYPE', length=2, label=Type:),
  Field('TYFLAG', length=1, default=),
  Field('TYUUSR', length=128, default = auth.user.username.upper()
   if auth.user else None, label=Updated by:),
  Field('TYUDAT', 'datetime', default=request.now, label=updated
   on:),
  primarykey=['TYPRCD'])
 
   Contoller (update:NG)
   --
   def test():
  return dict(form=crud.update(db.IQWAGFTY,
   db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))
 
   TRACEBACK:
   Traceback (most recent call last):
File D:\web2py\gluon\restricted.py, line 194, in restricted
  exec ccode in environment
File D:/web2py/applications/Inventory_Reserve_Admin/controllers/
   default.py, line 111, in module
File D:\web2py\gluon\globals.py, line 149, in lambda
  self._caller = lambda f: f()
File D:\web2py\gluon\tools.py, line 2456, in f
  return action(*a, **b)
File D:/web2py/applications/Inventory_Reserve_Admin/controllers/
   default.py, line 68, in test
  return dict(form=crud.update(db.IQWAGFTY,
   db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))
File D:\web2py\gluon\tools.py, line 2978, in update
  detect_record_change = self.settings.detect_record_change):
File D:\web2py\gluon\sqlhtml.py, line 1033, in accepts
  record_id = dict((k, request_vars[k]) for k in
   self.table._primarykey)
File D:\web2py\gluon\sqlhtml.py, line 1033, in genexpr
  record_id = dict((k, request_vars[k]) for k in
   self.table._primarykey)
   KeyError: 'TYPRCD'
 
   Contoller(create:OK)
   -
   def test():
  return dict(form=crud.create(db.IQWAGFTY))

[web2py] Re: Update legacy table using primarykey failed

2012-02-08 Thread Niphlod 
I tried with both sqlite and postgresql and it works as expected. Are you 
sure you are using the last version of web2py ? exceptions and line number 
don't match with 1.99.4 (that is the version I tested for your sample code, 
the only different thing is that I don't have a DB2 instance available, 
sorry)

[web2py] Re: Update legacy table using primarykey failed

2012-02-08 Thread Omi Chiba 
Good point.
I was using ver 1.99.2. I tried with the 1.99.4 and now It shows a
different error for the same contoller. Hm... strange.

Contoller (update:NG)
--
def test():
return dict(form=crud.update(db.IQWAGFTY,
db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))

Traceback:

Traceback (most recent call last):
  File C:\web2py\gluon\restricted.py, line 204, in restricted
exec ccode in environment
  File C:/web2py/applications/Inventory_Reserve_Admin/controllers/
default.py, line 113, in module
  File C:\web2py\gluon\globals.py, line 172, in lambda
self._caller = lambda f: f()
  File C:\web2py\gluon\tools.py, line 2533, in f
return action(*a, **b)
  File C:/web2py/applications/Inventory_Reserve_Admin/controllers/
default.py, line 69, in test
db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))
  File C:\web2py\gluon\tools.py, line 3069, in update
detect_record_change = self.settings.detect_record_change):
  File C:\web2py\gluon\sqlhtml.py, line 1140, in accepts
'%s != %s' % (record_id, self.record_id)
SyntaxError: user is tampering with form's record_id: {'TYPRCD':
None} != {'TYPRCD': '000115126002200'}


On Feb 8, 2:35 pm, Niphlod niph...@gmail.com wrote:
 I tried with both sqlite and postgresql and it works as expected. Are you
 sure you are using the last version of web2py ? exceptions and line number
 don't match with 1.99.4 (that is the version I tested for your sample code,
 the only different thing is that I don't have a DB2 instance available,
 sorry)

[web2py] Re: Update legacy table using primarykey failed

2012-02-08 Thread Omi Chiba 
I mean on version 1.99.2, I got error when I access the
function(page). On version 1.99.4, I can access the function(page) but
got the error when I submit.

On Feb 8, 3:50 pm, Omi Chiba ochib...@gmail.com wrote:
 Good point.
 I was using ver 1.99.2. I tried with the 1.99.4 and now It shows a
 different error for the same contoller. Hm... strange.

 Contoller (update:NG)
 --
 def test():
     return dict(form=crud.update(db.IQWAGFTY,
 db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))

 Traceback:
 
 Traceback (most recent call last):
   File C:\web2py\gluon\restricted.py, line 204, in restricted
     exec ccode in environment
   File C:/web2py/applications/Inventory_Reserve_Admin/controllers/
 default.py, line 113, in module
   File C:\web2py\gluon\globals.py, line 172, in lambda
     self._caller = lambda f: f()
   File C:\web2py\gluon\tools.py, line 2533, in f
     return action(*a, **b)
   File C:/web2py/applications/Inventory_Reserve_Admin/controllers/
 default.py, line 69, in test
     db.IQWAGFTY(db.IQWAGFTY.TYPRCD=='000115126002200')))
   File C:\web2py\gluon\tools.py, line 3069, in update
     detect_record_change = self.settings.detect_record_change):
   File C:\web2py\gluon\sqlhtml.py, line 1140, in accepts
     '%s != %s' % (record_id, self.record_id)
 SyntaxError: user is tampering with form's record_id: {'TYPRCD':
 None} != {'TYPRCD': '000115126002200'}

 On Feb 8, 2:35 pm, Niphlod niph...@gmail.com wrote:







  I tried with both sqlite and postgresql and it works as expected. Are you
  sure you are using the last version of web2py ? exceptions and line number
  don't match with 1.99.4 (that is the version I tested for your sample code,
  the only different thing is that I don't have a DB2 instance available,
  sorry)