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 

How to run stored procedures?

 
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: Tue May 17, 2011 8:10 pm    Post subject: How to run stored procedures? Reply with quote

How can I run a SQL stored procedure in python with an Rdb database? Where can I find more information on this topic? Also, out of curiosity, how can I run a DCL COM file from python, if possible? Thank you for your input. -ojr
Back to top
View user's profile Send private message
jescab



Joined: 28 Jan 2008
Posts: 254

PostPosted: Tue May 17, 2011 10:40 pm    Post subject: Reply with quote

Code:

$! Create a 1-line COM file.
$!
$ create show_time.com
$ SHOW TIME Exit
$!
$! Run Python and call the COM file "as usual".
$!
$ python
Python 2.5.4 (r254:67916, May 15 2011, 08:21:54) [DECC] on OpenVMS
Type "help", "copyright", "credits" or "license" for more information.
>>> import os
>>> status = os.system("@show_time")
  17-MAY-2011 22:11:56
>>>


Create a simple test PROC and table and insert a dummy value:
Code:

SQL> cre tab test_tab1 (x integer, y integer);
SQL> commit;
SQL> create mod test1 language sql
cont> procedure test_proc1 (:id integer);
cont> begin
cont> update test_tab1 set x = 1 where y = :id;
cont> end;
cont> end module;

SQL> insert into test_tab1 values(0,3);
1 row inserted
SQL> commit;
SQL> select * from test_tab1;
           X             Y   
           0             3   
1 row selected
SQL> commit;
SQL> commit;


Now run the PROC from inside of Python with 3 as parameter:
Code:

 python
Python 2.5.4 (r254:67916, May 15 2011, 08:21:54) [DECC] on OpenVMS
Type "help", "copyright", "credits" or "license" for more information.
>>> import rdb
>>> rdb.attachDB("trad_db");
>>> ex = rdb.statement("call test_proc1(3)")
>>> rdb.read_write()
>>> ex.execute()
>>> rdb.commit();

And finaly re-check the value in the table:
Code:

QL> select * from test_tab1;
           X             Y   
           1             3   
1 row selected
SQL> commit;

Works ! Smile

> How can I run a SQL stored procedure in python with an Rdb database?

The answer is, just as you'd runed any other SQL statement.
Back to top
View user's profile Send private message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Tue May 17, 2011 10:45 pm    Post subject: Reply with quote

Ah.... I see. Sorry for the newbie question. Thank you jescab for your helpful examples. -ojr
Back to top
View user's profile Send private message
jescab



Joined: 28 Jan 2008
Posts: 254

PostPosted: Wed May 18, 2011 12:05 pm    Post subject: Reply with quote

> Where can I find more information on this topic?

Not sure what "topic" refers to here. If it is Rdb stored procs in general,
check "Guide to SQL Programming" from the standard Rdb doc set.

Note also that my examples lacks all error checks and so on.
Back to top
View user's profile Send private message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Tue Dec 20, 2011 11:32 pm    Post subject: Reply with quote

Thank you for your reply.

From your SHOW TIME procedure example, how can I store the results of a COM file in a variable so that I may evaluate the results. Thanks again. ojr
Back to top
View user's profile Send private message
jescab



Joined: 28 Jan 2008
Posts: 254

PostPosted: Wed Dec 21, 2011 12:49 am    Post subject: Reply with quote

What is the "results of a COM file" ? It could be some large file or something.
So what "results" are you talking about ? Single numeric values ? Strings ? Something else ?

Anyway... Smile

Easiest is to let the COM file create symbols or (probably better)
logical names that you later read from your Python code.

If you know VMS this is a no-brainer using the examples.
If you don't know VMS, this is probably not the right forum... Smile

> From your SHOW TIME procedure example,

Say a COM file with one line (note /job, important!):
Code:

$ define/nolog/job my_log "''f$time()'"

Then a PY file with :
Code:

$ python

import os
from vms.rtl.lib import delete_logical, get_logical, set_logical

status = os.system("@my_com_file")

try:
  tmp1, my_variable, tmp2 = get_logical('MY_LOG','LNM$FILE_DEV')
except:
  print "Logical TRAD_APPID not found."
  raise

print "The variable is:", my_variable
$
$ @ py_ex.com
The variable is: 19-DEC-2011 23:47:29.40
$

Python calls the COM file, the COM file sets the logical (in the job-logical table)
and the Python code then reads the logical and prints the value.
Back to top
View user's profile Send private message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Wed Dec 21, 2011 1:08 am    Post subject: Reply with quote

The COM file returns True or False.
I was thinking that if I did...
status = os.system("@test.com")

The status value would be True or False, instead it was some number.

I am fairly new to python and VMS, but I do understand your example. I will try that. Thank you, ojr
Back to top
View user's profile Send private message
RonPeters



Joined: 18 Sep 2008
Posts: 9

PostPosted: Wed Dec 21, 2011 5:46 am    Post subject: Reply with quote

Hi ojr,

Popen will give you more control over the spawned process.

something like this should work.
Code:

import os

cmd = '@test' 
r,w = os.popen4(cmd)
w.close()  # I've had some issues when not closing w before doing a read
results = r.readlines() # creates a list of the output
r.close()
print results  # find out which index your output is in and strip etc what data you want



Usually I just compile a regex to search in the results
Back to top
View user's profile Send private message
ojr



Joined: 01 Dec 2010
Posts: 17

PostPosted: Wed Dec 21, 2011 4:09 pm    Post subject: Reply with quote

Ok, I'll try this one too. Thanks RonPeters
Back to top
View user's profile Send private message
jescab



Joined: 28 Jan 2008
Posts: 254

PostPosted: Thu Dec 22, 2011 1:33 am    Post subject: Reply with quote

> The COM file returns True or False.

Does it ?? How ?

> The status value would be True or False, instead it was some number.

I would *guess* (you can test yourself) that the return value
is the exit code from the COM file.

So if you specify "$ EXIT 1234", you would se 1234 as the return value. Probably...

There is no bolean values or constants in VMS such as Pythons True and False.

Now, a numeric value with some specific properties (even/odd and so on) can be interpreted as "true" or "false".
Or as "-S-uccess", "-E-rror", "-F-ault", "-I-nformation" and so on.
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