Re: [Sqlrelay-discussion] Error messages are not visible

[Firstworks/4access <dav...@fi...>]

I wonder if the problem is specific to the python API.  Try it from
sqlrsh and see if it works.  This, for example works for me:

0> create table testtable (col1 varchar2(20));         Rows Returned   :
0
        Fields Returned : 0
        System time     : 400000

0> create or replace function test(var1 in varchar) return varchar is
begin insert into testtable values (var1); return 'hello'; end;;
        Rows Returned   : 0
        Fields Returned : 0
        System time     : 410000

0> select test('hi') from dual; ORA-14551: cannot perform a DML
operation inside a query
ORA-06512: at "TESTUSER.TEST", line 1

Attempted Query:
select test('hi') from dual

        Rows Returned   : 0
        Fields Returned : 0
        System time     : 420000

0>


By "works", I mean, I get the error.

You should be able to run the function and get it's result without using
a select, like this:

from SQLRelay import PySQLRClient
from SQLRelay import PySQLRDB
con=PySQLRClient.sqlrconnection('localhost',9000,'','test','test',0,1)
cur=PySQLRClient.sqlrcursor(con)
res = cur.prepareQuery('begin :out=PCK_TESTY_STEROWNIKA.PROC_3(:in1, :in2); end;')
cur.inputBind('in1', 'jkkkak', 10, 0)
cur.inputBind('in2', 1, 10,2)
cur.defineOutputBind('out',20)
res = cur.executeQuery()
out = cur.getOutputBind('out')

Dave


On Thu, 2005-09-08 at 14:08 +0200, Maciej Wisniowski wrote:
> Hi!
> 
> When I try to execute Oracle function from python with code like this:
> 
> # 
> ----------------------------------------------------------------------------------------------
> from SQLRelay import PySQLRClient
> from SQLRelay import PySQLRDB
> con=PySQLRClient.sqlrconnection('localhost',9000,'','test','test',0,1)
> cur=PySQLRClient.sqlrcursor(con)
> res = cur.prepareQuery('select PCK_TESTY_STEROWNIKA.PROC_3(:in1, :in2) 
> from dual')
> cur.inputBind('in1', 'jkkkak', 10, 0)
> cur.inputBind('in2', 1, 10,2)
> res = cur.executeQuery()
>    
> print 'res:', res, cur.errorMessage()
> print 'val:', cur.getField(0,0)
> print 'end'
> # 
> ----------------------------------------------------------------------------------------------
> 
> then if there is a DML operation in my function Oracle returns error like:
> 
> Ora 14551 Cannot Perform A Dml Operation Inside A Query
> 
> that says it's not possible to use this function with select xxx from
> dual clause.
> 
> The problem is that SQLRelay doesn't return any errors.
> 
> Output is simply:
> 
> res: 1 None
> val: None
> end
> 
> I'm not sure whether other errors from functions called in this way are
> visible... but it seems to be a bug. We're using SQLRelay 0.36.4
>