Written by

IRIS Developer Advocate, Software developer at CaretDev, Tabcorp
MOD
Question Dmitry Maslennikov · 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
CREATEorREPLACEPROCEDUREtest()
LANGUAGE OBJECTSCRIPT
{
 SET %sqlcontext.%SQLCODE = 400SET %sqlcontext.%ROWCOUNT = -1SET %sqlcontext.%Message = "test error message"
};

CALLtest();

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 there

Obviously, I can raise an exception, but the real message is hardly reachable 

$ cat <<EOF | irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER
CREATEorREPLACEPROCEDUREtest()
RETURNS %StringLANGUAGE PYTHON
{
    return"OK"
};

SELECTtest() result;

CREATEorREPLACEPROCEDUREtest()
RETURNS %StringLANGUAGE PYTHON
{
    raiseException("some error")
    return"OK"
};

SELECTtest() 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

Comments

Bob Kuszewski · Feb 7, 2023

It's a good question.  I had thought raising an irisbuiltins.SQLError would do the trick, but it still returns the wrapped exception you see.  I'll put in for an enhancement.

0