Oracle datetime problem (ORA-01830)

[Gabriele Alberti]

Hello web2py users,
I defined a table with one of the fields as datetime type, and when I
try to insert a raw it fails with this

ORA-01830: date format picture ends before converting entire input
string

with a raw query looking like this

INSERT INTO vals(timestamp, value, type, number) VALUES ('2009-09-07
12:00:00', 24, 11, 1);

which works with a MySQL backend.
Googling a bit it turns out its a problem of datetime conversion in
oracle.
Im still investingating about this, but what I get so far is the
default Oracle DATE format is something weird (kind of YYYY-MM-DD, no
hour and so).
To fix the problem you can either do this to change it to our (python
datetime) format

alter session set NLS_DATE_FORMAT='<my_format>';

but this lasts just for this session, or use something like

INSERT INTO vals(timestamp, value, type, number) VALUES (to_date
('2009-09-07 12:00:00', 'yyyy-mm-dd hh:mi:ss'), 24, 11, 1);

using the to_date() oracle function.

I did not yet try anything of this solutions, and anyway I guess we
need a web2py fix.. any other hint ?

Thanks,

[mdipierro]

I think something is wrong in your model. If a field is declared of
type 'date' or 'datetime', web2py generates the SQL with the to_date
(..) function. I think you may have incorrectly defined the Field
('timestamp') as a string. BTW. I do not think 'timestamp' is a valid
field name since it is a reserved keyword.

On Nov 19, 5:47 am, Gabriele Alberti <gabriele.albe...@gmail.com>
wrote:

[Gabriele Alberti]

Hello,
my model is ok.. I tried to debug a bit, dont know exactly what is
happening but you probably do:
what happens in sql_represent() method is that among the many "if
fieldtype == '<type>'" there is this code:

elif isinstance(obj, str):
try:
obj.decode('utf-8')
except:
obj = obj.decode('latin1').encode('utf8')

which catches up my datetime field (dunno why), and as it is an "elif"
clause it ends up not being catched by the following
"elif fieldtype == 'datetime':" code, which fixes the to_date() issue.

Hope this helps understanding what is going on..

Regards,

G.

[mdipierro]

You are right. there is a problem. I assumed the datetime was
datetime.datetime format and not a string.
I am uploading a fix in trunk, please give it a try.

On Nov 19, 8:57 am, Gabriele Alberti <gabriele.albe...@gmail.com>

[Gabriele Alberti]

Hello,
it does with this patch

Index: gluon/sql.py
===================================================================
--- gluon/sql.py (revision 1441)
+++ gluon/sql.py (working copy)
@@ -521,7 +521,7 @@
obj = obj.isoformat()[:10]
else:
obj = str(obj)
- if not instance(obj,str):
+ if not isinstance(obj,str):
obj = str(obj)
try:
obj.decode('utf-8')

Damn typos ;)

Thank you very much for your help!

Best regards