Found it. I had a "timestamp" column included in each table def that
generated the error. Also, I gave you a poor example of the table
def'n. The following table def reproduced the error:
db.define_table('dateTest',
SQLField('modDate','smalldatetime'),
SQLField('date2','datetime'),
SQLField('date3','datetime',default=now),
SQLField('timestamp','datetime'),
migrate=False)
BTW, I used the auto.py code modified by David Lypka and added some of
my own code to generate the web2py model. I'm still working on the
auto.py code to make it more generic, but I've got it doing most of
the prep and conversion of a full mssql CREATE script (for our legacy
database). Also, I added logic to convert the foreign key constraints
to their equivalent "requires" statements and to maintain default
values for each column as specified in the database. Hopefully, I can
offer up the code in a week or so. Maybe it'll help others working
with legacy data.
-Michael
On Jul 25, 10:19 am, CS_teach <[email protected]> wrote:
> Thanks Massimo. I changed my Windows settings (control panel/regional)
> to the correct format and that worked for a new test table I created.
> But I still can't view the legacy tables with datetime data. I'll keep
> testing and let you know what I find.
>
> -Michael
>
> On Jul 25, 9:09 am, mdipierro <[email protected]> wrote:
>
> > This is a config issue on MSSQL side. The datatime fields have to be
> > in ISO format.
>
> > Massimo
>
> > On Jul 25, 7:50 am, Michael <[email protected]> wrote:
>
> > > Using a modified auto.py, I created a db.py model for a legacy SQL
> > > Server db and just successfully connected to the database using
> > > web2py. However, when I query any table that contains a datetime
> > > datatype, I get the following error:
>
> > > Traceback (most recent call last):
> > > File "C:\web2py\gluon\restricted.py", line 176, in restricted
> > > exec ccode in environment
> > > File "C:/web2py/applications/fcs/views/appadmin.html", line 122, in
> > > <module>
> > > File "C:\web2py\gluon\sqlhtml.py", line 918, in __init__
> > > for (rc, record) in enumerate(sqlrows):
> > > File "C:\web2py\gluon\sql.py", line 2433, in __iter__
> > > yield self[i]
> > > File "C:\web2py\gluon\sql.py", line 2373, in __getitem__
> > > str(value)[:10].strip().split('-')]
> > > ValueError: invalid literal for int() with base 10: ''
>
> > > In sql.py it seems that web2py expects to see datetime in the format
> > > YYYY-MM-DD HH:MM:SS. SQL Server presents the data in the format MM/DD/
> > > YYYY HH:MM:SS. I'm brand new to web2py so I don't know whether this
> > > is a bug in data handling or a config. problem on my end. My
> > > understanding is that SQL Server stores a datetime in its own internal
> > > format but formats the data based on locale so it cannot be modified
> > > globally.
>
> > > Here's a sample of one table def in db.py:
>
> > > db.define_table('Announcement',
> > > SQLField('startTime','datetime'),
> > > SQLField('endTime','datetime'),
> > > SQLField('announcementDate','datetime'),
> > > SQLField('announcementText','string'),
> > > SQLField('announcementType','nchar'),
> > > SQLField('modifyUser','char'),
> > > SQLField('modifyDate','datetime',default=now),
> > > migrate=False)
>
> > > Announcement_labels={'startTime':T('startTime'),'endTime':T
> > > ('endTime'),'AnnouncementDate':T
> > > ('AnnouncementDate'),'AnnouncementText':T
> > > ('AnnouncementText'),'AnnouncementType':T
> > > ('AnnouncementType'),'ModifyUser':T('ModifyUser'),'ModifyDate':T
> > > ('ModifyDate')}
>
> > > Any ideas about how to address this problem?
>
> > > -Michael
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
