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 

What does Rdb do different from interactive SQL?

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



Joined: 28 Jan 2008
Posts: 254

PostPosted: Tue Apr 03, 2012 3:16 pm    Post subject: What does Rdb do different from interactive SQL? Reply with quote

I have some trouble reading from an remote Oracle database using the "Transparent Gateway for Oracle".
This works just fine using other interfaces such as interactive SQL or embeded SQL in COBOL.

The problem seems to be related to using a view on the Oracle side.

Example using interactive SQL that works as expected:
Code:

$ sql$
att 'fi sa001db';
select bpartid from hv06 limit to 2 rows;
 BPARTID
 0966838400
 0506271101
2 rows selected
exit;
$!

The same query using Python crashed:
Code:

$ python
import rdb
rdb.attachDB('SA001DB', 'SADB')
rdb.read_only()
curs = rdb.Statement("select bpartid from SADB.HV06 limit to 2 rows")
curs.execute()
r = curs.fetchone()
Traceback (most recent call last):
  File "<stdin>", line 5, in <module>
  File "/python_root/local/rdb/__init__.py", line 93, in execute
    _rdb.Statement.execute(self, *args)
rdb._rdb_exceptions.Error: (-1, '%RDB-F-IO_ERROR, input or output error\r\n-LDRV-E-ORA_DB_SUPP,
ORA-01445: cannot select ROWID from, or sample, a join view with out a key-preserved table\n', 'RR000')

If I change the "table" from a view to a physical table ("hv06" => "bpload"), it works:
Code:

$ sql$
att 'fi sa001db';
select bpartid from bpload limit to 5 rows;
 BPARTID
 0525239201
 0525239201
 0525250601
 0525250601
 0525289201
5 rows selected
exit;

Code:

$ python
import rdb
rdb.attachDB('SA001DB', 'SADB')
rdb.read_only()
curs = rdb.Statement("select bpartid from SADB.bpload limit to 5 rows")
curs.execute()
r = curs.fetchall()
for i in r:
  print i
(u'0525239201              ',)
(u'0525239201              ',)
(u'0525250601              ',)
(u'0525250601              ',)
(u'0525289201              ',)

So something in the way Python runs the SQL makes the Oracle database to see it in differently.
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 636

PostPosted: Fri Apr 06, 2012 2:55 pm    Post subject: Reply with quote

You can try with the latest version of Python (see history), I have fixed a problem with the handling of dbke. I don't know if it is related.

I don't have "Transparent Gateway for Oracle", so I was unable to investigate...


JF
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: Sat Apr 07, 2012 1:46 am    Post subject: Reply with quote

OK, thanks. I'll do that.
ROWID in Oracle could very well be related to DBKEY in Rdb...
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