Aargh...
You asked to check _date_ type of field. But it works ok too:
-> import pdb;pdb.set_trace()
(Pdb) l 1
1 # -*- coding: utf-8 -*-
2
3 exec('from applications.%s.modules import cfg' %
request.application)
4 db = DAL('oracle://%s/%...@%s'%(cfg.oralogin,cfg.orapassword,cfg.oradb),
pool_size=10)
5
6 db.define_table('mytable',
7 Field('myfield','string'),
8 Field('mydt','datetime'),
9 Field('mydt2','date'),
10 )
11
(Pdb)
12 -> import pdb;pdb.set_trace()
[EOF]
(Pdb) db.mytable.insert(myfield='ccc',mydt2='2010-04-22')
3
(Pdb) db.mytable.insert(myfield='ddd',mydt2='2010-04-23')
4
(Pdb) db.commit()
(Pdb) db(db.mytable.id>0).select()
<gluon.sql.Rows object at 0xb28330c>
(Pdb) print db(db.mytable.id>0).select()
mytable.id,mytable.myfield,mytable.mydt,mytable.mydt2
1,aaa,2010-04-22 00:00:00,<NULL>
2,bbb,2010-04-22 10:00:00,<NULL>
3,ccc,<NULL>,2010-04-22
4,ddd,<NULL>,2010-04-23
(Pdb) print db(db.mytable.mydt2>'2010-04-22').select()
mytable.id,mytable.myfield,mytable.mydt,mytable.mydt2
4,ddd,<NULL>,2010-04-23
(Pdb)
Regards
Alexey
On Thu, Apr 22, 2010 at 2:43 PM, Alexey Nezhdanov <snak...@gmail.com> wrote:
> Sure.
> In short: everything works ok.
>
> In detail - see below.
>
>
> (Pdb) l 1
> 1 # -*- coding: utf-8 -*-
> 2
> 3 exec('from applications.%s.modules import cfg' %
> request.application)
> 4 db =
> DAL('oracle://%s/%...@%s'%(cfg.oralogin,cfg.orapassword,cfg.oradb),
> pool_size=10)
> 5
> 6 db.define_table('mytable',
> 7 Field('myfield','string'),
> 8 Field('mydt','datetime'),
> 9 )
> 10
> 11 -> import pdb;pdb.set_trace()
> (Pdb) db.mytable.insert(myfield='aaa',mydt='2010-04-22 00:00:00')
> 1
> (Pdb) db.mytable.insert(myfield='bbb',mydt='2010-04-22 10:00:00')
> 2
> (Pdb) db.commit()
> (Pdb) print db(db.mytable.mydt>'2010-04-22 09:00:00').select()
> mytable.id,mytable.myfield,mytable.mydt
> 2,bbb,2010-04-22 10:00:00
>
> (Pdb) print db(db.id>0).select()
> *** KeyError: 'id'
> (Pdb) print db(db.mytable.id>0).select()
> mytable.id,mytable.myfield,mytable.mydt
> 1,aaa,2010-04-22 00:00:00
> 2,bbb,2010-04-22 10:00:00
>
> (Pdb)
>
>
>
> On Tue, Apr 20, 2010 at 6:20 PM, mdipierro <mdipie...@cs.depaul.edu>wrote:
>
>> I think I understand better the problem. Oracle uses the same time for
>> DATE and DATETIME, i.e. DATE.
>> I made in trunk the change you suggested. Can you please check this
>> does not break insert and select of Field('somename','date')?
>>
>> On Apr 20, 12:37 am, Alexey Nezhdanov <snak...@gmail.com> wrote:
>> > Oh, well.
>> > May be that's an Oracle bug then. I use my custom 'console.py' script
>> here,
>> > but output should be clear enough:
>> >
>> > BTW - time_start column was created by web2py with this line:
>> > Field('time_start','datetime'),
>> >
>> > sn...@nezhdanov:~/VTC/pinger/elixir$ ./console.py
>> > select time_start from pinger_results where id=1;
>> > [('TIME_START', <type 'cx_Oracle.DATETIME'>, 23, 7, 0, 0, 1)]
>> > select time_start from pinger_results where time_start='2010-04-20
>> > 15:16:17';
>> > ORA-01861: литерал не соответствует формату строки
>> > ALTER SESSION set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
>> > ok
>> > select time_start from pinger_results where time_start='2010-04-20
>> > 15:16:17';
>> > [('TIME_START', <type 'cx_Oracle.DATETIME'>, 23, 7, 0, 0, 1)]
>> > ==========
>> > no comments
>> >
>> > Alexey
>> >
>> > On Mon, Apr 19, 2010 at 8:27 PM, mdipierro <mdipie...@cs.depaul.edu>
>> wrote:
>> > > taking a second look at the source code
>> >
>> > > self._execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-
>> > > MM-DD';")
>> > > self._execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT =
>> > > 'YYYY-MM-DD HH24:MI:SS';")
>> >
>> > > Now you propose adding:
>> >
>> > > self._execute("ALTER SESSION set NLS_DATE_FORMAT = 'YYYY-
>> > > MM-DD HH24:MI:SS';")
>> >
>> > > But date does not have HH224, MI, SS, only timestamp does.
>> >
>> > > On Apr 19, 11:19 am, Alexey Nezhdanov <snak...@gmail.com> wrote:
>> > > > No, I'm all for it. Actually it sounds more like bugfix since atm
>> web2py
>> > > > can't make datetime queries on Oracle.
>> >
>> > > > On Mon, Apr 19, 2010 at 6:16 PM, mdipierro <mdipie...@cs.depaul.edu
>> >
>> > > wrote:
>> > > > > There is no hook for this, but should this not be always the
>> default?
>> > > > > If so I am happy to add it to trunk. Any counterindication?
>> >
>> > > > > On Apr 19, 12:32 am, Alexey Nezhdanov <snak...@gmail.com> wrote:
>> > > > > > Hi.
>> > > > > > Sometimes I have to execute this line prior to making a query:
>> > > > > > oradb.executesql("ALTER SESSION set NLS_DATE_FORMAT =
>> 'YYYY-MM-DD
>> > > > > > HH24:MI:SS';")
>> >
>> > > > > > That is because Oracle by default uses different date format
>> that
>> > > causes
>> > > > > my
>> > > > > > queries to fail.
>> > > > > > The problem is that doing that in model is incorrect - this
>> should be
>> > > > > > executed just once for each db connection.
>> > > > > > Doing that just prior to query is inconvenient and still
>> incorrect -
>> > > I am
>> > > > > > probably reusing same connection.
>> > > > > > Can I somehow tell web2py to execute this sql right after
>> calling
>> > > > > connect()?
>> >
>> > > > > > Regards
>> > > > > > Alexey
>> >
>> > > > > > --
>> > > > > > Subscription settings:
>> > > > >http://groups.google.com/group/web2py/subscribe?hl=en
>>
>
>
