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 

Difference between interactive SQL and Python

 
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: Sat May 04, 2013 5:20 pm    Post subject: Difference between interactive SQL and Python Reply with quote

There is something going on in the Python Rdb interface.
This works using plain interactive SQL:
Code:

$ sql$
SQL> attach 'file sa001db';
SQL> select BPARTID FROM HV06 WHERE BPARTID = '0967082700';
 BPARTID
 0967082700
 0967082700
 0967082700
 0967082700
 0967082700
 0967082700
 0967082700
 0967082700
 0967082700
 0967082700
10 rows selected
SQL>

This also works OK using embeded SQL from Cobol using the standard Rdb pre-compiler.

The logical name SA001DB is defined as :
Code:

$ sh log sa001db
   "SA001DB" = "/TYPE=ORACLE/USER="xxxxx"/PASS="xxxxx"/NODE=NYASATT/TABLES=(HV06)" (LNM$SYSTEM_TABLE)
$

"/TYPE=ORACLE" shows that the "Rdb Transparent Gateway for Oracle" is used.
The database is an Oracle 11 database on an AIX server.

but the same query using Python (in a file SATT.COM) crashes:
Code:

$ @satt
$ python

import rdb

curs_satt = rdb.Statement("""
   SELECT BPARTID
   FROM HV06
   WHERE BPARTID = '0967082700'""")

rdb.attachDB('SA001DB')
rdb.read_only()
curs_satt.execute()
r = curs_satt.fetchall()
rdb.commit()

for i in r:
  print i[0]

Traceback (most recent call last):
  File "<stdin>", line 11, 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 without a key-preserved table\n', 'RR000')
$

If one google for ORA-01445 one findes:
Quote:

ORA-01445: cannot select ROWID from a join view without a key-preserved table

Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation.
Because the rows selected in the view do not correspond to underlying physical records,
no ROWIDs can be returned.

Action: Remove ROWID from the view selection clause, then re-execute the statement.

Here I'm lost. It seems as the Python interface adds ROWID to the SQL SELECT statement (?)...
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 636

PostPosted: Mon May 06, 2013 7:57 am    Post subject: Reply with quote

No, the rdb module doesn't change anything to the query, it just use SQL dynamic to execute it.

May can you try the sql$sample:sql_dynamic_driver_i.c (with sql_dynamic.c)
to verify that your request can run with this interface.

I don't have any Oracle daatbase to test, so it will be difficult for me to debug the problem Confused


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: Mon May 06, 2013 11:33 am    Post subject: Reply with quote

OK, that's a thought.
I'll take a look at the SQL-dyn sample... Smile

> I don't have any Oracle database to test,

I'm currently downloading "Oracle Database Express Edition 11g Release 2 for Windows x32".
It is a free "development-only" kit of Oracle 11 for 32-bit Windows.

Maybe I can setup something on a spare laptop here are make it available.
If not only for my own tests in-office. If it works, you should be able to do the same,
if I open up a port in my DSL gateway to the laptop.

You also need the "Oracle Transparent Gateway" on you local (Alpha) VMS box.
See the last kit at the bottom of this page:
http://www.oracle.com/technetwork/products/rdb/downloads/index.html
Or contact me if you have problem downloading directly from Oracle...
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