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
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.