[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2023-01-12 Thread 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.


On Wednesday, January 11, 2023 at 12:08:54 PM UTC+1 Davidiam wrote:

> 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.
>
> On Thursday, June 4, 2020 at 6:30:30 AM UTC+2 Seth J wrote:
>
>> 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 : [(, '172.20.252.61'), 
>> (, 0), (, 
>> '2020-06-04T00:21:23.665000'), (, 
>> '5258b356-2b72-4a24-8868-710222ac0784'), (, 
>> datetime.datetime(2020, 6, 4, 0, 21, 23, 665000)), (> object>, 
>> '\x80\x02cgluon.globals\nSession\nq\x01}q\x02U\x05flashq\x03U\x00s\x85Rq\x04.')]
>> query : 'INSERT INTO 
>> "web2py_session_PubView"("client_ip"...FscwpTZXNzaW9uCnEBfXECVQVmbGFzaHEDVQBzhVJxBC4=\');'
>> self : 
>> table :
>> > unique_key, session_data)>
>>
>> On Wednesday, June 3, 2020 at 11:09:28 PM UTC-4, Dave S wrote:
>>>
>>>
>>>
>>> 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/?
>>> >> 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=, table=>>>web2py_session_PubView (id, locked, clien...me, modified_datetime, 
unique_key, session_data)>, fields=[(, 
'172.20.145.208'), (, 0), 
 (>>>object>, '2020-06-03T00:26:05.296000'), (, 
'affc9c47-0085-45dc-8352-12549c7f93dc'), (, 
datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (>>>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
>>>  
>>>
>>

-- 
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/d0e07c9d-2cde-4b34-81a3-3e4160f4cf78n%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2023-01-11 Thread 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.

On Thursday, June 4, 2020 at 6:30:30 AM UTC+2 Seth J wrote:

> 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 : [(, '172.20.252.61'), 
> (, 0), (, 
> '2020-06-04T00:21:23.665000'), (, 
> '5258b356-2b72-4a24-8868-710222ac0784'), (, 
> datetime.datetime(2020, 6, 4, 0, 21, 23, 665000)), ( object>, 
> '\x80\x02cgluon.globals\nSession\nq\x01}q\x02U\x05flashq\x03U\x00s\x85Rq\x04.')]
> query : 'INSERT INTO 
> "web2py_session_PubView"("client_ip"...FscwpTZXNzaW9uCnEBfXECVQVmbGFzaHEDVQBzhVJxBC4=\');'
> self : 
> table :
>  unique_key, session_data)>
>
> On Wednesday, June 3, 2020 at 11:09:28 PM UTC-4, Dave S wrote:
>>
>>
>>
>> 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/?
>> > 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=, table=>>web2py_session_PubView (id, locked, clien...me, modified_datetime, 
>>>unique_key, session_data)>, fields=[(, 
>>>'172.20.145.208'), (, 0), 
>>> (>>object>, '2020-06-03T00:26:05.296000'), (, 
>>>'affc9c47-0085-45dc-8352-12549c7f93dc'), (, 
>>>datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (>>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
>>  
>>
>

-- 
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/a7015f02-3e03-407e-be45-cf05320e2012n%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2020-06-03 Thread 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 : [(, '172.20.252.61'), 
(, 0), (, 
'2020-06-04T00:21:23.665000'), (, 
'5258b356-2b72-4a24-8868-710222ac0784'), (, 
datetime.datetime(2020, 6, 4, 0, 21, 23, 665000)), (, 
'\x80\x02cgluon.globals\nSession\nq\x01}q\x02U\x05flashq\x03U\x00s\x85Rq\x04.')]
query : 'INSERT INTO 
"web2py_session_PubView"("client_ip"...FscwpTZXNzaW9uCnEBfXECVQVmbGFzaHEDVQBzhVJxBC4=\');'
self : 
table : 

On Wednesday, June 3, 2020 at 11:09:28 PM UTC-4, Dave S wrote:
>
>
>
> 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/?
>  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=, table=>web2py_session_PubView (id, locked, clien...me, modified_datetime, 
>>unique_key, session_data)>, fields=[(, 
>>'172.20.145.208'), (, 0), 
>> (>object>, '2020-06-03T00:26:05.296000'), (, 
>>'affc9c47-0085-45dc-8352-12549c7f93dc'), (, 
>>datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (>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
>  
>

-- 
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/295393b8-2f66-4554-9bd3-3e1529e5dc1c%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2020-06-03 Thread 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/?
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=, table=web2py_session_PubView (id, locked, clien...me, modified_datetime, 
>unique_key, session_data)>, fields=[(, 
>'172.20.145.208'), (, 0), 
> (object>, '2020-06-03T00:26:05.296000'), (, 
>'affc9c47-0085-45dc-8352-12549c7f93dc'), (, 
>datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (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
 

-- 
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/1535949d-38d4-4bd5-9cb4-3a4adfa578b5%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2020-06-03 Thread 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=, table=, fields=[(, 
   '172.20.145.208'), (, 0), (, '2020-06-03T00:26:05.296000'), (, 
   'affc9c47-0085-45dc-8352-12549c7f93dc'), (, 
   datetime.datetime(2020, 6, 3, 0, 26, 5, 296000)), (, 
   
'\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

On Wednesday, June 3, 2020 at 9:14:15 AM UTC-4, Jim S wrote:
>
> 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
>
> On Tuesday, June 2, 2020 at 11:29:27 PM UTC-5, Seth J wrote:
>>
>> 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 [image: help] 
>> 
>>
>> (('22007', '[22007] [Microsoft][ODBC SQL Server 
>> Driver][SQL Server]Conversion failed when converting date and/or time from 
>> character string. (241) (SQLExecDirectW)'))
>>
>> On Tuesday, June 2, 2020 at 1:53:37 AM UTC-4, Clemens wrote:
>>>
>>> Have you already tried the latest version 
>>> 2.20.4-stable+timestamp.2020.05.03.05.18.50 
>>> ?
>>>
>>>
>>> On Monday, June 1, 2020 at 8:12:08 PM UTC+2, Seth J wrote:

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

 [image: error_web2py.png]



-- 
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/5742bfe1-e0ae-4dc8-9455-21a1098572f1%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2020-06-03 Thread 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

On Tuesday, June 2, 2020 at 11:29:27 PM UTC-5, Seth J wrote:
>
> 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 [image: help] 
> 
>
> (('22007', '[22007] [Microsoft][ODBC SQL Server 
> Driver][SQL Server]Conversion failed when converting date and/or time from 
> character string. (241) (SQLExecDirectW)'))
>
> On Tuesday, June 2, 2020 at 1:53:37 AM UTC-4, Clemens wrote:
>>
>> Have you already tried the latest version 
>> 2.20.4-stable+timestamp.2020.05.03.05.18.50 
>> ?
>>
>>
>> On Monday, June 1, 2020 at 8:12:08 PM UTC+2, Seth J wrote:
>>>
>>> I am running IIS connecting to SQL Server.  Upgrade seems to have 
>>> introduced an error to an otherwise functioning app.  Any ideas???
>>>
>>> [image: error_web2py.png]
>>>
>>>

-- 
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/cba0643c-0b9e-4a4f-9e61-457d322fb352%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2020-06-02 Thread 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 [image: help] 


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

On Tuesday, June 2, 2020 at 1:53:37 AM UTC-4, Clemens wrote:
>
> Have you already tried the latest version 
> 2.20.4-stable+timestamp.2020.05.03.05.18.50 
> ?
>
>
> On Monday, June 1, 2020 at 8:12:08 PM UTC+2, Seth J wrote:
>>
>> I am running IIS connecting to SQL Server.  Upgrade seems to have 
>> introduced an error to an otherwise functioning app.  Any ideas???
>>
>> [image: error_web2py.png]
>>
>>

-- 
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/3954861a-1eaa-4795-bcdd-e4d52d18ff78%40googlegroups.com.

[web2py] Re: Upgraded to 2.19 and SQL Server date insert throws an error

2020-06-01 Thread Clemens 
Have you already tried the latest version 
2.20.4-stable+timestamp.2020.05.03.05.18.50 
?


On Monday, June 1, 2020 at 8:12:08 PM UTC+2, Seth J wrote:
>
> I am running IIS connecting to SQL Server.  Upgrade seems to have 
> introduced an error to an otherwise functioning app.  Any ideas???
>
> [image: error_web2py.png]
>
>

-- 
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/b90d2cde-9d15-4c23-82f0-89babe588a95%40googlegroups.com.