...but this error is not clear with me
Error ticket for "ITAsset"Ticket ID
127.0.0.1.2020-08-21.09-54-23.77579ea3-20b8-49ca-aed5-76c10960c92f
<class 'pyodbc.IntegrityError'> ('23000', u"[23000] [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot insert the value NULL into column 'id',
table 'itassetdb.dbo.asset_archive'; column does not allow nulls. INSERT
fails. (515) (SQLExecDirectW); [23000] [Microsoft][ODBC SQL Server
Driver][SQL Server]The statement has been terminated. (3621)")
insert a NULL value into culumn'id' on table asset_archive? Why?
Il giorno venerdì 21 agosto 2020 10:04:15 UTC+2, Andrea Fae' ha scritto:
>
> I think the problem with archiving is when I defined archive table in
> mssql I defined a field named 'current_record" type int. Maybe type int is
> not correct, isn't it?
> Thank you
>
> Il giorno venerdì 21 agosto 2020 09:43:14 UTC+2, Andrea Fae' ha scritto:
>>
>> Hello, some updates.
>> In the databases I can see only this (see attachment).
>> I'm trying to manage record versionin (table archiving). I create for
>> example asset.archive in mssql but web2py doesn't "see" the table. In fact
>> the table is not listend in the appadmin page.
>> The question is that tables definitions are not created in the databases
>> folder, even if I put migrate=false in appconfig.ini and in db.py I typed
>> migrate_fake=true... ???
>>
>>
>>
>> Il giorno giovedì 20 agosto 2020 16:57:26 UTC+2, Andrea Fae' ha scritto:
>>>
>>> UPDATES....
>>>
>>> in appconfig.ini I changed from migrate= true to migrate=false.
>>>
>>> tha system doesn't create me the sql.log and .table file definitions in
>>> the folder databases, but the application seems start to work! How is
>>> posisble?
>>>
>>>
>>> Il giorno giovedì 20 agosto 2020 16:45:35 UTC+2, Andrea Fae' ha scritto:
>>>>
>>>> Hello Villas, first al all...thank you thank you thank you! But I
>>>> didn't arrived to the end of the game yet. but I think I'm near with your
>>>> suggestions.
>>>>
>>>> I followed the "easier solution" and, with a lot of effort, now I have
>>>> my MSSQL database filled of my data.
>>>> But when I start tha application web2py I have this error:
>>>>
>>>> Error ticket for "ITAsset"Ticket ID
>>>>
>>>> 127.0.0.1.2020-08-20.16-37-26.91c5b362-833d-4bdc-afef-3b727fde37a8
>>>> <class 'pyodbc.ProgrammingError'> ('42S01', u"[42S01] [Microsoft][ODBC
>>>> SQL Server Driver][SQL Server]There is already an object named 'sede' in
>>>> the database. (2714) (SQLExecDirectW)")Versione
>>>> web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47
>>>> Python Python 2.7.15: C:\Python27\python.exe (prefix: C:\Python27)
>>>> Traceback
>>>>
>>>> 1.
>>>> 2.
>>>> 3.
>>>> 4.
>>>> 5.
>>>> 6.
>>>> 7.
>>>> 8.
>>>> 9.
>>>> 10.
>>>> 11.
>>>> 12.
>>>> 13.
>>>> 14.
>>>> 15.
>>>> 16.
>>>> 17.
>>>> 18.
>>>> 19.
>>>>
>>>> Traceback (most recent call last):
>>>> File "C:\web2py\gluon\restricted.py", line 227, in restricted
>>>> exec ccode in environment
>>>> File "c:/web2py/applications/ITAsset/models/db_asset.py"
>>>> <http://127.0.0.1:8000/admin/default/edit/ITAsset/models/db_asset.py>,
>>>> line 12, in <module>
>>>> format='%(nome)s')
>>>> File "C:\web2py\gluon\packages\dal\pydal\base.py", line 834, in
>>>> define_table
>>>> table = self.lazy_define_table(tablename,*fields,**args)
>>>> File "C:\web2py\gluon\packages\dal\pydal\base.py", line 873, in
>>>> lazy_define_table
>>>> polymodel=polymodel)
>>>> File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 482, in
>>>> create_table
>>>> self.create_sequence_and_triggers(query, table)
>>>> File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1369,
>>>> in create_sequence_and_triggers
>>>> self.execute(query)
>>>> File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1388,
>>>> in execute
>>>> return self.log_execute(*a, **b)
>>>> File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1382,
>>>> in log_execute
>>>> ret = self.get_cursor().execute(command, *a[1:], **b)
>>>> ProgrammingError: ('42S01', u"[42S01] [Microsoft][ODBC SQL Server
>>>> Driver][SQL Server]There is already an object named 'sede' in the
>>>> database. (2714) (SQLExecDirectW)")
>>>>
>>>>
>>>> I will attach you db.py and dbasset.py, my models and the sql.log
>>>> generated...
>>>> I tested with all combination of "migrate" and "fake_migrate" but
>>>> web2py try every time to create the table "sede and so the error.
>>>> Can you have another suggestion? when I will finish I inveted you yo a
>>>> dinner in the restaurant! :-)
>>>>
>>>> thank you
>>>>
>>>>
>>>> Il giorno mercoledì 19 agosto 2020 12:32:20 UTC+2, villas ha scritto:
>>>>>
>>>>> *An easier solution for you could be this!*
>>>>> Simply specify your created_by/modified_by as extra fields (which
>>>>> should side-step self-referencing probem).
>>>>> I didn't test it, but it seems like a good idea to me. You can then
>>>>> use normal migrations etc.
>>>>>
>>>>> auth.settings.extra_fields['auth_user']= [
>>>>>
>>>>> Field('is_active', 'boolean', default=True),
>>>>> Field('created_on', 'datetime', default=request.now),
>>>>> Field('created_by', 'integer', default=auth.user_id),
>>>>> Field('modified_on', 'datetime', update=request.now),
>>>>> Field('modified_by', 'integer', update=auth.user_id)
>>>>>
>>>>> ]
>>>>>
>>>>> auth.define_tables(... signature=False )
>>>>>
>>>>> *Otherwise, in answer to your questions...*
>>>>> migrate='asset_archive.table'
>>>>> Personally, I do not specify the table definition name as the one
>>>>> given automatically by web2py is very good. I just use
>>>>> migrate=True/False.
>>>>> If you use migrate='asset_archive.table', you are also specifying
>>>>> migrate=True
>>>>>
>>>>> fake_migrate=True
>>>>> This is used to recreate the .table file without touching your DB.
>>>>> If you manually create and edit the fields in your DB, you may wish
>>>>> to recreate the .table definition file (hence fake_migrate).
>>>>>
>>>>> I am not sure how you are using the archiving etc, but I do know
>>>>> this:
>>>>>
>>>>> - you can manually create your own DB on-disk structure with
>>>>> whatever triggers, indexes etc you require.
>>>>> - in web2py you can use define_table to create a web2py meta
>>>>> definition (which must be compatible with what is on disk!)
>>>>> - you can use fake_migrate if you wish to create a .table
>>>>> definition file (which will reflect your web2py define_table)
>>>>> - you can use migrate=False to prevent web2py from attempting to
>>>>> migrate the physical DB on-disk structure
>>>>>
>>>>> Using the above, the book explanation should make a little more
>>>>> sense.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wednesday, 19 August 2020 09:43:42 UTC+1, Andrea Fae' wrote:
>>>>>
>>>>>> Hello Villas, unfortunately other problems to this game...
>>>>>> I used archiving, and so this type of table definitions
>>>>>>
>>>>>> db.define_table('asset_archive',
>>>>>> Field('current_record', db.asset),
>>>>>> db.asset,migrate='asset_archive.table')
>>>>>>
>>>>>> but I have the same problem regarding FOREIGN KEYS...no way to
>>>>>> recreate the tables.
>>>>>>
>>>>>> Do I have to give up the record archiving? Waht do you think?
>>>>>>
>>>>>> If you can, please can you explain the meaning of fake_migrate? I
>>>>>> never used and in the book is not very well explained.
>>>>>>
>>>>>> Thank you
>>>>>>
>>>>>>
>>>>>>
>>>>>> Il giorno martedì 18 agosto 2020 22:38:50 UTC+2, villas ha scritto:
>>>>>>>
>>>>>>> I presume therefore that you need those fields. This is the kind of
>>>>>>> thing I do to get things working. Please forgive me if I've missed
>>>>>>> something.
>>>>>>>
>>>>>>> - I would create those required fields manually in your database
>>>>>>> (without the constraint that causes the problem).
>>>>>>> - Set auth.define_tables(migrate = True, fake_migrate=True)
>>>>>>> - Run the app. This will create the .table definition file in
>>>>>>> the databases dir. You may have had to delete the old .table file.
>>>>>>> If
>>>>>>> necessary delete that.
>>>>>>> - Set auth.define_tables(migrate =False) again.
>>>>>>>
>>>>>>>
>>>>>>> Sorry if this seems strange, but it is a work around solution.
>>>>>>> I am hoping that someone will eventually fix this contraint problem
>>>>>>> in pyDal.
>>>>>>>
>>>>>>>
>>>>>>> On Tuesday, 18 August 2020 20:51:57 UTC+1, Andrea Fae' wrote:
>>>>>>>>
>>>>>>>> Thank you Villas. I'm using option 2 and now I have the tables
>>>>>>>> without "created_by" and "modified_by".
>>>>>>>> Now I will try to reset signature=true and see what will happen. If
>>>>>>>> it causes the same error I could add but they will not managed
>>>>>>>> automatically by web2py, or I'm wrong?
>>>>>>>> What do you suggest?
>>>>>>>> Thank you for your precious information.
>>>>>>>>
>>>>>>>> Il giorno lunedì 17 agosto 2020 14:24:25 UTC+2, villas ha scritto:
>>>>>>>>>
>>>>>>>>> Hi Andrea
>>>>>>>>> I was pleased to see you are making progress with the DB
>>>>>>>>> connection etc.
>>>>>>>>> I can see why mssql does not like the cascade from the same
>>>>>>>>> auth_user table. This may be an issue which needs fixing...
>>>>>>>>> To get things moving, and this might not be ideal, but I propose
>>>>>>>>> you consider either of these options:
>>>>>>>>>
>>>>>>>>> 1. Create manually your own tables without those contraints.
>>>>>>>>> You then set this: auth.define_tables(... migrate=False ) so
>>>>>>>>> that pyDal skips creation.
>>>>>>>>> 2. Do not include the signature fields with your auth.
>>>>>>>>> auth.define_tables(... signature=False )
>>>>>>>>>
>>>>>>>>> Incidentally, I use option 2 and I therefore do not benefit from
>>>>>>>>> the created and modified info, but I could easily remedy this by
>>>>>>>>> adding
>>>>>>>>> the fields if I wished.
>>>>>>>>> Hope this helps.
>>>>>>>>>
>>>>>>>>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/ec7b498f-9c61-47f8-9970-b93a573c403fo%40googlegroups.com.
