Upgraded to 2.19 and SQL Server date insert throws an error

[Seth J]

I am running IIS connecting to SQL Server.  Upgrade seems to have introduced an error to an otherwise functioning app.  Any ideas???

[Clemens]

Have you already tried the latest version 2.20.4-stable+timestamp.2020.05.03.05.18.50 ?

[Seth J]

Yes, same error. 🤷🏼‍♂️

Error ticket for "PubView"

Ticket ID

172.20.145.208.2020-06-03.00-26-05.a903caa0-1d7d-4612-a5f9-104967995098

Version

web2py™	Version 2.20.4-stable+timestamp.2020.05.03.05.18.50
Python	Python 2.7.10: C:\Python27\python.exe (prefix: C:\Python27)

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.

Traceback (most recent call last): File ".\gluon\main.py", line 456, in wsgibase session._try_store_in_db(request, response) File ".\gluon\globals.py", line 1254, in _try_store_in_db record_id = table.insert(**dd) File "C:\inetpub\wwwroot\gluon\packages\dal\pydal\objects.py", line 888, in insert ret = self._db._adapter.insert(self, row.op_values()) File "C:\inetpub\wwwroot\gluon\packages\dal\pydal\adapters\base.py", line 539, in insert raise e DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

Error snapshot 

<class 'pyodbc.DataError'>(('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)'))

[Jim S]

Can you pinpoint the line in your code that is causing the error?

Can you see the SQL statement that is being sent to the server?

-Jim

[Seth J]

I am not sure how to view the query sent to MSSQL.  Here's a little more from the error record:

• File C:\inetpub\wwwroot\gluon\packages\dal\pydal\adapters\base.py in insert at line 539 code arguments variables

  Function argument list

  (self=<pydal.adapters.mssql.MSSQL4 object>, table=<Table web2py_session_PubView (id, locked, clien...me, modified_datetime, unique_key, session_data)>, fields=[(<pydal.objects.Field object>, '172.20.145.208'), (<pydal.objects.Field object>, 0), (<pydal.objects.Field object>, '2020-06-03T00:26:05.296000'), (<pydal.objects.Field object>, 'affc9c47-0085-45dc-8352-12549c7f93dc'), (<pydal.objects.Field object>, datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (<pydal.objects.Field object>, '\x80\x02cgluon.globals\nSession\nq\x01}q\x02U\x05flashq\x03U\x00s\x85Rq\x04.')])

  Code listing

  534.
  535.
  536.
  537.
  538.
  539.
  540.
  541.
  542.
  543.
              self.execute(query)
          except:
              e = sys.exc_info()[1]
              if hasattr(table, "_on_insert_error"):
                  return table._on_insert_error(table, fields, e)
              raise e
          if hasattr(table, "_primarykey"):
              pkdict = dict(
                  [(k[0].name, k[1]) for k in fields if k[0].name in table._primarykey]
              )

  Variables

  e	DataError('22007', '[22007] [Microsoft][ODBC SQL...e from character string. (241) (SQLExecDirectW)')

Context

[Dave S]

On Wednesday, June 3, 2020 at 2:04:02 PM UTC-7, Seth J wrote:

I am not sure how to view the query sent to MSSQL. 

Does this help/?

<URL:https://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#_lastsql>

 

Here's a little more from the error record:

• File C:\inetpub\wwwroot\gluon\packages\dal\pydal\adapters\base.py in insert at line 539 code arguments variables

  Function argument list

  (self=<pydal.adapters.mssql.MSSQL4 object>, table=<Table web2py_session_PubView (id, locked, clien...me, modified_datetime, unique_key, session_data)>, fields=[(<pydal.objects.Field object>, '172.20.145.208'), (<pydal.objects.Field object>, 0), (<pydal.objects.Field object>, '2020-06-03T00:26:05.296000'), (<pydal.objects.Field object>, 'affc9c47-0085-45dc-8352-12549c7f93dc'), (<pydal.objects.Field object>, datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (<pydal.objects.Field object>, '\x80\x02cgluon.globals\nSession\nq\x01}q\x02U\x05flashq\x03U\x00s\x85Rq\x04.')])

You've got a date conversion error.  Do you need to specify a format string?  Is the ISO 'T' causing problems with a format that expects a space?  How have you defined the field?

/dps

 

[Seth J]

Thanks for pointing the function.  But it looks like the error is happening on the Session insert. See below query block:

• Variables

  e	DataError('22007', '[22007] [Microsoft][ODBC SQL...e from character string. (241) (SQLExecDirectW)')

Context

locals request session response

locals

e

:

DataError('22007', '[22007] [Microsoft][ODBC SQL...e from character string. (241) (SQLExecDirectW)')

fields	:	[(<pydal.objects.Field object>, '172.20.252.61'), (<pydal.objects.Field object>, 0), (<pydal.objects.Field object>, '2020-06-04T00:21:23.665000'), (<pydal.objects.Field object>, '5258b356-2b72-4a24-8868-710222ac0784'), (<pydal.objects.Field object>, datetime.datetime(2020, 6, 4, 0, 21, 23, 665000)), (<pydal.objects.Field object>, '\x80\x02cgluon.globals\nSession\nq\x01}q\x02U\x05flashq\x03U\x00s\x85Rq\x04.')]
query	:	'INSERT INTO "web2py_session_PubView"("client_ip"...FscwpTZXNzaW9uCnEBfXECVQVmbGFzaHEDVQBzhVJxBC4=\');'
self	:	<pydal.adapters.mssql.MSSQL4 object>
table	:

<Table web2py_session_PubView (id, locked, clien...me, modified_datetime, unique_key, session_data)>

[Davidiam]

Hi,

Has anyone found a solution for this?  We are experiencing the same issue with 2.22.5 and storing the session in the db. 

As a work-around, we have removed the ".isoformat" from the line 1247 of the gluon\globals.py:

modified_datetime=request.now, #.isoformat(),

What would be the impact of our work-around?  If we put isoformat in comment, the modified_datetime will be a string instead of a datetime object.

[Davidiam]

Just for clarity on this issue: if you save your sessions to MSSQL you will get an error because the MS SQL driver is expecting the timestamp to be a string, but instead it is a datetime object due to the ".isoformat()" added in version 2.19.

By modifying the line 1247 in globals.py, putting the .isoformat in comment, the timestamp is again a string and it works again.