Retrieving RAISERROR from Stored Procedure

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.

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

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.

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
1 Like

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

Follow the chain of “caused by” nested exceptions.

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

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.

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

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.

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.

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?

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.

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!