Database resource that calls stored procedure

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Jon, Steve, Ian, Dave

Segi
Ebase User
Posts: 649
Joined: Mon Dec 09, 2013 6:37 pm

Database resource that calls stored procedure

#1

Postby Segi » Wed Jun 22, 2016 9:20 pm

i have a database resource that makes a call to a SQL Server stored procedure in the SELECT section of the Native SQL statement.

The statement may not return any results sometimes and this is perfectly normal. In my JavaScript event, the call to fetchTable() is encapsulated with a try catch block so the user never sees an error but the server error log records the error:

Code: Select all

Wed Jun 22 13:54:57: ERROR The executeQuery method must return a result set.
java.sql.SQLException: The executeQuery method must return a result set.
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:488)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
	at org.enhydra.jdbc.core.CorePreparedStatement.executeQuery(CorePreparedStatement.java:92)
	at com.ebasetech.ufs.runtime.resources.RuntimeDBSource.fetchTableUsingPreparedStatement(RuntimeDBSource.java:1228)
	at com.ebasetech.ufs.runtime.resources.RuntimeDBSource.fetchTable(RuntimeDBSource.java:1146)
	at com.ebasetech.ufs.runtime.RuntimeTableField.fetchTable(RuntimeTableField.java:509)
	at com.ebasetech.ufs.runtime.RuntimeTableField.fetchTable(RuntimeTableField.java:461)
	at com.ebasetech.ufs.runtime.external.api.impl.ApiTableBase.fetchTable(ApiTableBase.java:126)
	at com.ebasetech.ufs.runtime.external.api.impl.ApiTableBase.fetchTable(ApiTableBase.java:118)
	at sun.reflect.GeneratedMethodAccessor920.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:126)
	at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:225)
	at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1479)
	at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815)
	at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109)
	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:393)
	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3280)
	at org.mozilla.javascript.Context.callFunctionWithContinuations(Context.java:1284)
	at org.mozilla.javascript.Context.executeScriptWithContinuations(Context.java:1251)
	at com.ebasetech.ufs.runtime.events.rhinojavascript.JavascriptExecutionHandler.execute(JavascriptExecutionHandler.java:270)
	at com.ebasetech.ufs.runtime.events.JVMScriptNode.execute_(JVMScriptNode.java:133)
	at com.ebasetech.ufs.runtime.events.JVMScriptNode.execute(JVMScriptNode.java:99)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.BaseEventNode.execute(BaseEventNode.java:32)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:448)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:417)
	at com.ebasetech.ufs.runtime.Request._execute(Request.java:352)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:317)
	at formservlets.FormProcessingUtil.executeRequest(FormProcessingUtil.java:503)
	at formservlets.EbaseAjaxServlet.process(EbaseAjaxServlet.java:69)
	at formservlets.EbaseAjaxServlet.doPost(EbaseAjaxServlet.java:38)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at formservlets.EbaseFilter.processRequest(EbaseFilter.java:253)
	at formservlets.EbaseFilter.performFilter(EbaseFilter.java:180)
	at formservlets.EbaseFilter.doFilter(EbaseFilter.java:100)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:198)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:176)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Other database resources that don't call a stored procedure and don't return any results don't have this problem. Is this normal behavior ?
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

#2

Postby Jon » Thu Jun 23, 2016 8:22 am

If you issue a fetch() against a database resource, the SQL statement must be a SELECT, or at least it must return a result set. A fetch() will result in Ebase issuing executeQuery() to the JDBC driver - in your case, it's the driver that is throwing the error. When you issue a SELECT that returns no records, the database returns a result set object that is empty.

Why not use a Stored Procedure Resource?
0 x

Segi
Ebase User
Posts: 649
Joined: Mon Dec 09, 2013 6:37 pm

#3

Postby Segi » Thu Jun 23, 2016 3:37 pm

Jon,

This DB resource is a multi row resource so I'm calling fetchTable() not fetch().

According to the docs at /doc/Working_with_stored_procedures.htm, it says "Please note that Ebase Xi does not currently provide support for stored procedures that return multiple result sets."

and my data may return multiple rows so its not practical for me to use a SP resource.
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

#4

Postby Jon » Thu Jun 23, 2016 5:11 pm

Ah yes, good point! I checked our log database and I did come across an example of putting an EXEC statement inside a Database Resource and executing it successfully via fetchTable(). I'm not sure if this is MS SQL Server only. Have you seen this article http://www.sommarskog.se/share%5Fdata.html - discusses how to return result sets from a stored procedure.

If you can't get this to work, you can always do it using Java JDBC code inside a Javascript script - this will give you complete control over what is happening. I don't have an example of this but I could probably create one if it would help.
0 x

Segi
Ebase User
Posts: 649
Joined: Mon Dec 09, 2013 6:37 pm

#5

Postby Segi » Thu Jun 23, 2016 5:22 pm

This is what I am currently doing. I am using a multi row database resource as a native SQL and put the EXEC statement in the SELECT section.

I know how to create odbc code where you run an SQL statement without a database resource but I prefer to avoid using that method whenever possible.

Like I said, the database resource with EXEC works fine when it returns data or it will work fine when it doesn't return data as long as you surround the call to fetchTable with try catch statements. I just wanted to understand if this is normal behavior.
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

#6

Postby Jon » Fri Jun 24, 2016 8:26 am

I don't know why you get a null ResultSet, possibly the code in the stored procedure, possibly a bug in the driver. I think you would be better off using your own JDBC code then you could use:

Code: Select all

var result = stmt.execute(...);
if (result) {
  var rs = stmt.getResultSet();
  while (rs.next()) {
     ..
  }
}
The execute() method returns true if a result set exists.
0 x


Who is online

Users browsing this forum: No registered users and 4 guests