Retrieving RAISERROR from Stored Procedure

[thecesrom]

Hello,

I have a stored procedure where input parameters are being validated, in case one has an undesired value I use RAISERROR.

This is the SQL code:

IF @name IS NULL OR NULLIF(RTRIM(LTRIM(@name)), '') IS NULL 
BEGIN
    RAISERROR('Invalid parameter: @name cannot be NULL or empty.', 18, 0)
    RETURN
END

Is it possible to read this error message?

This is my [simplified] code:

try:
    call = system.db.createSProcCall(procedureName='save')
    call.registerInParam('name', system.db.VARCHAR, None)
    system.db.execSProcCall(call)
except java.lang.Exception:
    /* This is where I would like to print the error raised by the stored procedure. */
    print 'RAISERROR'

This is what I get when I run the stored procedure in SQL Server Management Studio:

Msg 50000, Level 18, State 0, Procedure save, Line 29
Invalid parameter: @name cannot be NULL or empty.

Please let me know if this is possible.

Thanks.

[pturmel]

Try this:

import java.lang.Exception
try:
    call = system.db.createSProcCall(procedureName='save')
    call.registerInParam('name', system.db.VARCHAR, None)
    system.db.execSProcCall(call)
except java.lang.Exception, e:
    /* This is where I would like to print the error raised by the stored procedure. */
    print e

[thecesrom]

I may have oversimplified the catch statement.

This is the code I have:

except(java.lang.Exception, Exception):
    print "An unexpected error occurred in %s:\n%s", % (get_function_name(), '\n'.join(traceback.format_exc().splitlines()))

And what I end up getting on the console is:

An unexpected error occurred in save. 
Traceback (most recent call last):
  File "<buffer>", line 59, in save
Exception: java.lang.Exception: Error executing system.db.execSProcCall()

I’m running the save function on the Script Console.

[pturmel]

Use separate catch clauses. One for java exceptions, one for python:

import java.lang.Exception
try:
    call = system.db.createSProcCall(procedureName='save')
    call.registerInParam('name', system.db.VARCHAR, None)
    system.db.execSProcCall(call)
except java.lang.Exception, e:
    /* Print exceptions originating in Java */
    print e
except Exception, e:
    /* Print other exceptions */
    print e

[thecesrom]

Whichever way I do not seem to get the error raised by the stored procedure.

The error reads:

java.lang.Exception: Error executing system.db.execSProcCall()

[pturmel]

Follow the chain of “caused by” nested exceptions.

[pturmel]

Also, consider using a logger instead of print, and one of the logger methods that takes an exception. That’ll print a detailed backtrace.

[thecesrom]

I intend to read the error message from the stored procedure, but I have noticed that system.db.execSProcCall throws java.lang.Exception, and in my test I have yet to find a way to read it.

@pturmel

Follow the chain of "caused by" nested exceptions.

The "cause" is the RAISERROR from the stored procedure, but apparently is doesn't "bubble-up" as I was expecting.

@pturmel

...consider using a logger instead of print...

A logger, in my case, is not a viable option since, as I said, my intention is to present the exception to the user.

I may need to do something else other than catching the java.lang.Exception, but my knowledge is not deep enough.

[pturmel]

@pturmel

Also, consider using a logger instead of print, and one of the logger methods that takes an exception. That'll print a detailed backtrace.

Do this for now so you can show us the whole exception. Or take it out of it's try/except block so that the client will display the exception. Then you can click on "full" on the details pane and paste it here.

[thecesrom]

Now I know what you meant by “caused by”.

This is the full stack trace from the exception:

Java Traceback:

	at org.python.core.Py.JavaError(Py.java:495)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:496)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:429)
	at org.python.core.PyObject.__call__(PyObject.java:387)
	at org.python.core.PyObject.__call__(PyObject.java:391)
	at org.python.pycode._pyx53.save$1(<buffer>:73)
	at org.python.pycode._pyx53.call_function(<buffer>)
	at org.python.core.PyTableCode.call(PyTableCode.java:165)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:120)
	at org.python.core.PyFunction.__call__(PyFunction.java:307)
	at org.python.pycode._pyx53.f$0(<buffer>:109)
	at org.python.pycode._pyx53.call_function(<buffer>)
	at org.python.core.PyTableCode.call(PyTableCode.java:165)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1275)
	at org.python.core.Py.exec(Py.java:1319)
	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:215)
	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:89)
	at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:476)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:464)
	at javax.swing.SwingWorker$1.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at javax.swing.SwingWorker.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.execSProcCall()
	... 32 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Invalid parameter: @sid cannot be NULL.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:894)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:233)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:494)
	... 30 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter: @sid cannot be NULL.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.execute(SRConnectionWrapper.java:937)
	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:248)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:47)
	at sun.reflect.GeneratedMethodAccessor14.invoke(null)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(null)
	at java.lang.reflect.Method.invoke(null)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:208)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:518)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(null)
Traceback (most recent call last):
  File "<buffer>", line 105, in <module>
  File "<buffer>", line 62, in save
  File "<buffer>", line 62, in save
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:496)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

Buried in the Traceback I am getting the error message from RAISERROR:

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Invalid parameter: @sid cannot be NULL.

…and…

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter: @sid cannot be NULL.

But all I’m reading is:

java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

Suggestions?

[pturmel]

Look at the Docs for java.lang.Exception. Notice the getCause() method. Follow that from outermost to innermost to get what you need. Also note that a SQLException has additional detail.

[thecesrom]

try:
    ....
except java.lang.Exception, e:
    print e.getCause()

In this case it prints:

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Invalid parameter: @sid cannot be NULL.

Which is a little bit clearer than the bare java.lang.Exception.

Thanks!

[Ryan_Shaw]

My google search led me here. The topic of this post is relevant to my problem, but the discussion isn't helpful because the user was just struggling with passing in bad arguments. I am also using 8.1, not 7.9. But since other people will end up reading this post when they have my problem I'll go ahead and revive this.

My MSSQL stored procedure explicitly raises an error, but this error is not raised by the gateway. Here is my MSSQL code snippet

IF EXISTS (SELECT 1 FROM @errorMessages) BEGIN
		
	SELECT @errorMessage = STRING_AGG(errorMessage, ',') FROM @errorMessages
	RAISERROR (@errorMessage, 16, 1)
	RETURN

END

Here is the procedure called from SSMS

EXEC test_proc 
    @p1 = -1,
    @p2 = NULL,
    @p3 = NULL;

and the expected output when no parameters are passsed in

Msg 50000, Level 16, State 1, Procedure test_proc, Line 56 [Batch Start Line 0]
p1 was not provided.,p2 was not provided.,p3 was not provided.

Here is my Ignition code called from the script console

    call = system.db.createSProcCall('test_proc', 'Ignition')

    call.registerInParam('p1', system.db.INTEGER, -1)
    call.registerInParam('p2', system.db.INTEGER, None)
    call.registerInParam('p3', system.db.INTEGER, None)

    system.db.execSProcCall(call)

the output in the script console is empty (>>>), but I'm expecting a traceback error with my RAISERROR message.

[pturmel]

Ignition only does vanilla JDBC, as a consequence of its multi-vendor connection pool design. I'm pretty sure you can only get the output of RAISEERROR via SQL Server extensions in their customized driver.

Don't use vendor-specific JDBC extensions in Ignition.

[YF129701]

What happens when you create a named query, then for the query body you put your procedure call

EXEC test_proc :p1, :p2, :p3

The :p1, :p2, :p3 are your named query parameters. Then, test it out with a script console system.db.runNamedQuery("test_proc_query", parameters = {...}).

Where I work, we use stored procedures heavily, but never using the system.db.create/execSProcCall() interface - just using named queries. I don't know whether or not this is explicitly supported by the JDBC driver, but its worked for me for hundreds of procedures and many, many projects with this setup.

[Again, I'm not saying this is what you should do - I'm just curious if you'll get the exception you're expecting - I'm suspecting you might]

[Ryan_Shaw]

query = """
EXEC test_proc
	@p1 = -1,
	@p2 = NULL,
	@p3 = NULL
"""
system.db.runUpdateQuery(query, 'Ignition')

Does in fact produce the error message I'm expecting. Another alternative is to have @errorMessage be an output parameter in my stored procedure, and then I can retrieve it and check if it is NULL or an empty string. I like that idea. It was also suggested to me that Ignition might catch the error if I use THROW instead of RAISERROR. I didn't look into that, but someone else having this problem might try that.

Thank you for your replies, @pturmel @YF129701

[lrose]

If you insist on using the executeSPCall() (I wouldn’t I find it finicky and infuriating) you’ll probably have to, at a minimum, define a return parameter.

For what it’s worth I would just use a Named query.