forum.vmspython.org Forum Index forum.vmspython.org
Forum system
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Trouble updating TIMESTAMP/DATE fields Rdb

 
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> Python for OpenVMS
View previous topic :: View next topic  
Author Message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Sun Nov 20, 2011 12:40 am    Post subject: Trouble updating TIMESTAMP/DATE fields Rdb Reply with quote

Hello All,
I have attempting to use an sql update statement in python with Rdb, but I cannot get the syntax correct or continue to get invalid date or time error messages.

I am using prepared statements. tmdate is a timestamp field.
update table set tmdate = ? where test = ?;

I have tried...
update table set tmdate = (TIMESTAMP ? ) where test = ?;
Where tmdate is '2011-11-18 00:00:00'.
I get..
syntax error error message

Tried...
update table set tmdate = ? where test = ?;
Where tmdate is a string I create, '(TIMESTAMP '2011-11-18 00:00:00')'.
I get...
COSI-F-IVTIME, invalid date or time error message

Tried...
update table set tmdate = ? where test = ?;
Where tmdate is '2011-11-18 00:00:00'.
I get...
sqlstate 02000

I would like your insight. What am I doing wrong here? Thank you.
Back to top
View user's profile Send private message
jescab



Joined: 28 Jan 2008
Posts: 254

PostPosted: Sun Nov 20, 2011 1:37 am    Post subject: Reply with quote

This works :
Code:

SQL> sh tab t1
Information for table T1
 
Columns for table T1:
Column Name                     Data Type        Domain
-----------                     ---------        ------
F1                              VARCHAR(10)     
F2                              VARCHAR(10)     
F3                              TIMESTAMP(0)     
 

$ python
Python 2.7.2+ (default, Oct  4 2011, 21:13:00) [DECC] on OpenVMS
Type "help", "copyright", "credits" or "license" for more information.
>>> import rdb
>>> rdb.attachDB('test')
>>> rdb.read_write()
>>> stat = rdb.statement('insert into t1 (f3) values (?)')
>>> stat.execute('2011-11-19 11:22:33')
>>> rdb.commit()
>>>  Exit

$ sql$     
SQL> att 'fi test';
SQL> select f3 from t1;
 F3                   
...
...
 2011-11-19 11:22:33   
...
14 rows selected
SQL>
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 633

PostPosted: Sun Nov 20, 2011 9:13 am    Post subject: Reply with quote

You can also use a datetime.datetime object:
Code:
$ python
Python 2.7.2+ (default, Oct  4 2011, 21:13:00) [DECC] on OpenVMS
Type "help", "copyright", "credits" or "license" for more information.
>>> import rdb
>>> from datetime import datetime
>>> rdb.attachDB('test')
>>> rdb.read_write()
>>> stat = rdb.statement('insert into t1 (f3) values (?)')
>>> stat.execute(datetime(2011, 11, 19, 22, 33))
>>> rdb.commit()
>>>  Exit
...


JF
Edit 20-NOV-2011, fix example...


Last edited by jfp on Mon Nov 21, 2011 9:46 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jescab



Joined: 28 Jan 2008
Posts: 254

PostPosted: Sun Nov 20, 2011 1:11 pm    Post subject: Reply with quote

And *this* code actualy works Smile
Code:

>>> import rdb
>>> import datetime
>>> rdb.attachDB('test')
>>> rdb.read_write()
>>> stat = rdb.statement('insert into t1 (f3) values (?)')
>>> stat.execute(datetime.datetime(2011, 11, 19, 11, 22, 33))
>>> rdb.commit()

or this:
Code:

>>> import rdb
>>> from datetime import datetime
>>> rdb.attachDB('test')
>>> rdb.read_write()
>>> stat = rdb.statement('insert into t1 (f3) values (?)')
>>> stat.execute(datetime(2011, 11, 19, 11, 22, 33))
>>> rdb.commit()
Back to top
View user's profile Send private message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Tue Nov 22, 2011 4:38 pm    Post subject: Reply with quote

I am attempting to update an existing record. How do I make it work in a UPDATE statement? Thank you for your replies.
Back to top
View user's profile Send private message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Tue Nov 22, 2011 4:39 pm    Post subject: Reply with quote

I am attempting to update an existing record. How do I make it work in a UPDATE statement? Thank you for your replies.
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 633

PostPosted: Tue Nov 22, 2011 5:10 pm    Post subject: Reply with quote

Work for me:
Code:
mc sql$
SQL> sh table(col) foo
Information for table FOO

Columns for table FOO:
Column Name                     Data Type        Domain
-----------                     ---------        ------
X                               TIMESTAMP(0)

SQL> sel * from foo;
 X
 2000-01-01 00:00:00
1 row selected
SQL> Exit
$ python
Python 2.7.2+ (default, Nov  6 2011, 14:54:07) [DECC] on OpenVMS
Type "help", "copyright", "credits" or "license" for more information.
>>> import rdb
>>> from datetime import datetime
>>> rdb.attachDB('foo')
>>> rdb.read_write()
>>> stat = rdb.statement('update foo set x=? where x=?')
>>> stat.execute(datetime(2000,2,1),datetime(2000,1,1))
>>> rdb.commit()
>>>  Exit
$ mc sql$ sel * from foo;
 X
 2000-02-01 00:00:00
1 row selected
$


JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Tue Nov 22, 2011 7:56 pm    Post subject: Reply with quote

Oh, duh... Sorry, I don't know what I thinking. Thank you for your help. Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> Python for OpenVMS All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group