Question
· Feb 6, 2023

SQL PROCEDURE %sqlcontext for LANGUAGE PYTHON

So, I know that I can return a SQL Error message from my SQL Procedure written in ObjectScript, with code like this

$ cat <<EOF | irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER
CREATE or REPLACE PROCEDURE test()
LANGUAGE OBJECTSCRIPT
{
 SET %sqlcontext.%SQLCODE = 400
 SET %sqlcontext.%ROWCOUNT = -1
 SET %sqlcontext.%Message = "test error message"
};

CALL test();

EOF
[SQLCODE: <-400>:<Fatal error occurred>]
[Location: <SPFunction>]
[%msg: <test error message>]

But I did not find how to do it with Python. I can't find %sqlcontext variable available thereObviously, I can raise an exception, but the real message is hardly reachable 

$ cat <<EOF | irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER
CREATE or REPLACE PROCEDURE test()
RETURNS %String
LANGUAGE PYTHON
{
    return "OK"
};

SELECT test() result;

CREATE or REPLACE PROCEDURE test()
RETURNS %String
LANGUAGE PYTHON
{
    raise Exception("some error")
    return "OK"
};

SELECT test() result;

EOF
result
OK
[SQLCODE: <-149>:<SQL Function encountered an error>]
[Location: <ServerLoop - Query Open()>]
[%msg: <SQL Function SQLUSER.TEST failed with error:  SQLCODE=-400,%msg=ERROR #5002: ObjectScript error: %0AmBm3l0tudf^%sqlcq.USER.cls309.1^1^ *<class 'Exception'>: some error - >]
Product version: IRIS 2022.3
Discussion (1)1
Log in or sign up to continue