grid edit

[T.R.Rajkumar]

I call this 127.0.0.1:8000/web_ocms/amc/newamcmaster/edit/amc_master/3 by clicking the edit button of the recor in the grid, and the edit form for the record is displayed. After editing when I submit error ticket is issued stating id column cannot be updated. Pl. help. Thanks for your time.
The ticket is

Error ticket for "web_ocms"

Ticket ID

127.0.0.1.2017-05-23.17-15-24.8a493eb8-5c35-4dd1-8ade-97ef1e190c7c

<class 'pyodbc.ProgrammingError'> ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'id'. (8102) (SQLExecDirectW)")

Version

web2py™	Version 2.14.6-stable+timestamp.2016.05.10.00.21.47
Python	Python 2.7.5: D:\Python\python.exe (prefix: D:\Python)

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24.

Traceback (most recent call last): File "D:\web2py\gluon\restricted.py", line 227, in restricted exec ccode in environment File "D:/web2py/applications/web_ocms/controllers/amc.py", line 49, in <module> File "D:\web2py\gluon\globals.py", line 417, in <lambda> self._caller = lambda f: f() File "D:/web2py/applications/web_ocms/models/db.py", line 140, in wrapper return callee() File "D:/web2py/applications/web_ocms/controllers/amc.py", line 42, in newamcmaster grid = SQLFORM.grid(db.amc_master,user_signature=False,fields=list_of_fields,oncreate=create_new_amc) File "D:\web2py\gluon\sqlhtml.py", line 2352, in grid next=referrer) File "D:\web2py\gluon\html.py", line 2298, in process self.validate(**kwargs) File "D:\web2py\gluon\html.py", line 2236, in validate if self.accepts(**kwargs): File "D:\web2py\gluon\sqlhtml.py", line 1744, in accepts self.id_field_name]).update(**fields) File "D:\web2py\gluon\packages\dal\pydal\objects.py", line 2054, in update ret = db._adapter.update("%s" % table._tablename, self.query, fields) File "D:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1031, in update raise e ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'id'. (8102) (SQLExecDirectW)")

Error snapshot

<class 'pyodbc.ProgrammingError'>(('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'id'. (8102) (SQLExecDirectW)"))

inspect attributes

Frames

• File D:\web2py\gluon\restricted.py in restricted at line 227 code arguments variables

• File D:\web2py\applications\web_ocms\controllers\amc.py in <module> at line 49 code arguments variables

• File D:\web2py\gluon\globals.py in <lambda> at line 417 code arguments variables

• File D:\web2py\applications\web_ocms\models\db.py in wrapper at line 140 code arguments variables

• File D:\web2py\applications\web_ocms\controllers\amc.py in newamcmaster at line 42 code arguments variables

• File D:\web2py\gluon\sqlhtml.py in grid at line 2352 code arguments variables

• File D:\web2py\gluon\html.py in process at line 2298 code arguments variables

• File D:\web2py\gluon\html.py in validate at line 2236 code arguments variables

• File D:\web2py\gluon\sqlhtml.py in accepts at line 1744 code arguments variables

• File D:\web2py\gluon\packages\dal\pydal\objects.py in update at line 2054 code arguments variables

• File D:\web2py\gluon\packages\dal\pydal\adapters\base.py in update at line 1031 code arguments variables

  Function argument list

  (self=<pydal.adapters.mssql.MSSQL2Adapter object>, tablename='amc_master', query=<Query (amc_master.id = 3)>, fields=[(<pydal.objects.Field object>, None), (<pydal.objects.Field object>, 'bid'), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, 3L), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, datetime.datetime(2017, 5, 23, 16, 59, 23)), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), (<pydal.objects.Field object>, None), ...])

  Code listing

  1026.
  1027.
  1028.
  1029.
  1030.
  1031.
  
  1032.
  1033.
  1034.
  1035.
          except Exception:
              e = sys.exc_info()[1]
              table = self.db[tablename]
              if hasattr(table,'_on_update_error'):
                  return table._on_update_error(table,query,fields,e)
              raise e
  
          try:
              return self.cursor.rowcount
          except:
              return None

  Variables

  e	ProgrammingError('42000', "[42000] [Microsoft][O...e identity column 'id'. (8102) (SQLExecDirectW)")

[T.R.Rajkumar]

Why is DAL trying to update id column in grid edit form submission.

[Dave S]

On Tuesday, May 23, 2017 at 11:28:29 PM UTC-7, T.R.Rajkumar wrote:

Why is DAL trying to update id column in grid edit form submission.

I think we need your model file to have much idea.

/dps

 

[T.R.Rajkumar]

I have this in model.
db.define_table('amc_master',
                Field('amcno', type='string',length=10,

required='True',notnull='True',requires=IS_LENGTH(10)),
                Field('unitid',type='string',length=3,notnull='True'),
                Field('amcdesc', type='string',length=512,required='True',notnull='True'),
                Field('estval', type='decimal(18,2)'),
                Field('supcflg', type='string', length=1),
                Field('supcval',type='decimal(18,2)'),
                Field('netestval',type='decimal(18,2)'),
                Field('bidmethod',type='string',length=3),
                Field('capbud',type='string',length=1),
                Field('amcmod',type='string',length=3),
                Field('lrrflg',type='string',length=1),
                Field('insflg',type='string',length=1),
                Field('stflg',type='string',length=1),
                Field('phwflg',type='string',length=1),
                Field('contyp',type='string',length=3),
                Field('rfact',type='decimal(18,2)'),
                Field('qfact',type='decimal(18,2)'),
                Field('emdamt',type='decimal(18,2)'),
                Field('doccharge',type='decimal(18,2)'),
                Field('divn',type='string',length=3),
                Field('subdivn',type='string',length=2),
                Field('cflg',type='string',length=1),
                Field('statflg',type='string',length=3),
                Field('appauthority',type='string',length=16),
                Field('accappddt',type='datetime'),
                Field('iewappddt',type='datetime'),
                Field('complndays',type='integer'),
                Field('examcno',type='string',length=10),
                Field('consoldamcstr',type='string',length=70),
                Field('isconsold',type='string',length=1),
                Field('estonprovn',type='decimal(18,2)'),
                Field('totest',type='decimal(18,2)'),
                Field('update_uid','string',length=6,required='True',notnull='True'),
                Field('update_dt','datetime',required='True',notnull='True')
)

in controller i have this

def newamcmaster():
    list_of_fields = [db.amc_master.amcdesc,db.amc_master.amcmod,db.amc_master.complndays]
    [setattr(f, 'readable', False) for f in db.amc_master if f.name not in ['amcdesc','amcmod','complndays']]
    [setattr(f, 'writable', False) for f in db.amc_master if f.name not in ['amcdesc','amcmod','complndays']]
    grid = SQLFORM.grid(db.amc_master,user_signature=False,fields=list_of_fields,oncreate=create_new_amc)
    return locals()

[T.R.Rajkumar]

When I connect to postgresql the same model the grid edit form submits without error. Is this to do with pyodbc?

[T.R.Rajkumar]

I googled and saw this https://www.drupal.org/node/2342699 where it is stated that SqlContentEntityStorage::mapToStorageRecord sends update statements that includes IDENTITY fields that need to be updated, such as: UPDATE xxx SET nid = 1, property0 = :value0 WHERE nid = 1;. This is not supported in SQL Server because although there is no actual update happening it is explicitly telling the database engine that you wish to perform an update.

and here https://www.drupal.org/node/2090251  it is said The underlying problem is that MySQL accepts identity updates (when there are no changes) but Sql Server being properly designed complains with this bad practice.

Is this the issue in web2py also.
 

[T.R.Rajkumar]

I don't know why there is no response from anyone in the group. Is the post not clear? Thanks in advance.

[isi_jca]

T.R.Rajkumar

Try setting this value:

field_id must be the field of the table to be used as ID, for example db.mytable.id. This is useful when the grid query is a join of several tables. Any action button on the grid(add record, view, edit, delete) will work over db.mytable.


Regards.

[Anthony]

The "id" field is not writable in SQLFORMs, so the grid/SQLFORM do not include that field in update SQL queries. Does the "id" field actually appear as writable in your forms? What happens if you just do SQLFORM.grid(db.amc_master) without specifying the fields list or setting any of the readable/writable attributes?

[T.R.Rajkumar]

When I do SQLFORM.grid(db.amc_master) without setting any of the readable/writable attributes the grid edit works fine. I am able to edit and the form submits without errors.
Even with specifying the fields list grid edit works fine as the edit from has all the fields of the table.
But when I set the  readable/writable attributes to filter the fields in edit form the the error 42000 listed above occurs.
Thank you Anthony for the test guidance.
How to go about now?

[Jim S]

Have you tried adding 'id' to your editable field list?

[setattr(f, 'writable', False) for f in db.amc_master if f.name not in ['amcdesc','amcmod','complndays', 'id']]

-Jim

[Anthony]

On Wednesday, May 31, 2017 at 5:39:11 AM UTC-4, T.R.Rajkumar wrote:

When I do SQLFORM.grid(db.amc_master) without setting any of the readable/writable attributes the grid edit works fine. I am able to edit and the form submits without errors.
Even with specifying the fields list grid edit works fine as the edit from has all the fields of the table.
But when I set the  readable/writable attributes to filter the fields in edit form the the error 42000 listed above occurs.

I see the problem. By default, the "id" field is actually set to writable=True. It is excluded from SQLFORM database writes not because of its "writable" attribute but because the SQLFORM.accepts() code explicitly excludes the "id" field. However, the code at this line ends up including fields that are in the record by have writable=False. So, because your code sets writable=False for all fields except the three special fields you have identified, the "id" field ends up getting writable=False, which forces it to be included in the database update. To fix this, the following should work:

[setattr(f, 'writable', False) for f in db.

amc_master
 if f.name not in ['id', 'amcdesc', 'amcmod', 'complndays']]

Anthony

[T.R.Rajkumar]

Yes Anthony, with this the grid edit is OK.

[setattr(f, 'writable', False) for f in db.

amc_master
 if f.name not in ['id', 'amcdesc', 'amcmod', 'complndays'

]]

But how come it worked fine with postgresql without the id field setting?

Anyway thanks a lot for resolving the issue.