Retrieving RAISERROR from Stored Procedure

Ignition
, ,
1

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.

2

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
3

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.

4

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
5

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

Follow the chain of “caused by” nested exceptions.

7

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

8

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.

9

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.

10

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?

11

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.

12 
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!