Can't Execute Query in MSSQL using JDBC in XPages

1
votes

I am using a JDBC Microsoft SQL driver in an xpage to get data from MS SQL server The driver is installed using an update site and it is loaded successfully into the OSGI

I have also added a sqlserver.jdbc containing credentials to the webcontent\webinf\jdbc folder in the application

When I try to access the sql tables using @JdbcDbColumn it is working fine 

var con=@JdbcGetConnection("sqlserver");
@JdbcDbColumn(con,"PrTr","Descr")

But when I try to execute a query like this

var con=@JdbcGetConnection("sqlserver");
var query="SELECT * FROM PrTr";
var rs=@JdbcExecuteQuery(con,query);
....

I get a stack trace error (on line 3) with lots of error lines, the lines that stand out are these

com.ibm.jscript.InterpretException: Script interpreter error, line=4, col=8: Error while executing function '@JdbcExecuteQuery'

java.lang.ArrayIndexOutOfBoundsException

I have also tried to connect a view panel to a JDBCQuery data source but then I only get a 500 error wihtout a stacktrace

The SQL server is not on the same windows server as the xpages

in the stacktrace/tracelog there was one line that looked like this

java.security.AccessController.doPrivileged(AccessController.java:362)

But not sure if it is an error or only informational and if it is related to the query. i.e if there is a security problem.

I need help to understand why I can do the @JDBCDbColumn but not the @JDBCExecuteQuery

btw I just checked the SQLLog and do not see any entries related to my queries

Here is the full stacktrace

com.ibm.xsp.exception.EvaluationExceptionEx: Error while executing JavaScript computed expression com.ibm.xsp.binding.javascript.JavaScriptValueBinding.getValue(JavaScriptValueBinding.java:132) javax.faces.component.UIOutput.getValue(UIOutput.java:159) com.ibm.xsp.util.FacesUtil.convertValue(FacesUtil.java:1122) com.ibm.xsp.renderkit.html_basic.OutputTextRenderer.encodeEnd(OutputTextRenderer.java:97) com.ibm.xsp.renderkit.ReadOnlyAdapterRenderer.encodeEnd(ReadOnlyAdapterRenderer.java:180) javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:1005) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:858) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.component.UIViewRootEx._renderView(UIViewRootEx.java:1317) com.ibm.xsp.component.UIViewRootEx.renderView(UIViewRootEx.java:1255) com.ibm.xsp.application.ViewHandlerExImpl.doRender(ViewHandlerExImpl.java:651) com.ibm.xsp.application.ViewHandlerExImpl._renderView(ViewHandlerExImpl.java:321) com.ibm.xsp.application.ViewHandlerExImpl.renderView(ViewHandlerExImpl.java:336) com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:103) com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210) com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:120) com.ibm.xsp.controller.FacesControllerImpl.render(FacesControllerImpl.java:270) com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:261) com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:157) com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160) com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:138) com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103) com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576) com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1335) com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:853) com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796) com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565) com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1319) com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:662) com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:482) com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:350) com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:306) com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272) com.ibm.jscript.InterpretException: Script interpreter error, line=3, col=10: Error while executing function '@JdbcExecuteQuery' com.ibm.xsp.extlib.javascript.JdbcFunctions$NotesFunction.call(JdbcFunctions.java:389) com.ibm.jscript.types.FBSObject.call(FBSObject.java:161) com.ibm.jscript.ASTTree.ASTCall.interpret(ASTCall.java:197) com.ibm.jscript.ASTTree.ASTVariableDecl.interpret(ASTVariableDecl.java:82) com.ibm.jscript.ASTTree.ASTProgram.interpret(ASTProgram.java:119) com.ibm.jscript.ASTTree.ASTProgram.interpretEx(ASTProgram.java:139) com.ibm.jscript.JSExpression._interpretExpression(JSExpression.java:435) com.ibm.jscript.JSExpression.access$1(JSExpression.java:424) com.ibm.jscript.JSExpression$2.run(JSExpression.java:414) java.security.AccessController.doPrivileged(AccessController.java:362) com.ibm.jscript.JSExpression.interpretExpression(JSExpression.java:410) com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:251) com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:234) com.ibm.xsp.javascript.JavaScriptInterpreter.interpret(JavaScriptInterpreter.java:222) com.ibm.xsp.javascript.JavaScriptInterpreter.interpret(JavaScriptInterpreter.java:194) com.ibm.xsp.binding.javascript.JavaScriptValueBinding.getValue(JavaScriptValueBinding.java:78) javax.faces.component.UIOutput.getValue(UIOutput.java:159) com.ibm.xsp.util.FacesUtil.convertValue(FacesUtil.java:1122) com.ibm.xsp.renderkit.html_basic.OutputTextRenderer.encodeEnd(OutputTextRenderer.java:97) com.ibm.xsp.renderkit.ReadOnlyAdapterRenderer.encodeEnd(ReadOnlyAdapterRenderer.java:180) javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:1005) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:858) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.component.UIViewRootEx._renderView(UIViewRootEx.java:1317) com.ibm.xsp.component.UIViewRootEx.renderView(UIViewRootEx.java:1255) com.ibm.xsp.application.ViewHandlerExImpl.doRender(ViewHandlerExImpl.java:651) com.ibm.xsp.application.ViewHandlerExImpl._renderView(ViewHandlerExImpl.java:321) com.ibm.xsp.application.ViewHandlerExImpl.renderView(ViewHandlerExImpl.java:336) com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:103) com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210) com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:120) com.ibm.xsp.controller.FacesControllerImpl.render(FacesControllerImpl.java:270) com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:261) com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:157) com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160) com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:138) com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103) com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576) com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1335) com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:853) com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796) com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565) com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1319) com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:662) com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:482) com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:350) com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:306) com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272) java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 2 com.ibm.jscript.types.FBSValueVector.get(FBSValueVector.java:76) com.ibm.xsp.extlib.javascript.JdbcFunctions$NotesFunction.call(JdbcFunctions.java:267) com.ibm.jscript.types.FBSObject.call(FBSObject.java:161) com.ibm.jscript.ASTTree.ASTCall.interpret(ASTCall.java:197) com.ibm.jscript.ASTTree.ASTVariableDecl.interpret(ASTVariableDecl.java:82) com.ibm.jscript.ASTTree.ASTProgram.interpret(ASTProgram.java:119) com.ibm.jscript.ASTTree.ASTProgram.interpretEx(ASTProgram.java:139) com.ibm.jscript.JSExpression._interpretExpression(JSExpression.java:435) com.ibm.jscript.JSExpression.access$1(JSExpression.java:424) com.ibm.jscript.JSExpression$2.run(JSExpression.java:414) java.security.AccessController.doPrivileged(AccessController.java:362) com.ibm.jscript.JSExpression.interpretExpression(JSExpression.java:410) com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:251) com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:234) com.ibm.xsp.javascript.JavaScriptInterpreter.interpret(JavaScriptInterpreter.java:222) com.ibm.xsp.javascript.JavaScriptInterpreter.interpret(JavaScriptInterpreter.java:194) com.ibm.xsp.binding.javascript.JavaScriptValueBinding.getValue(JavaScriptValueBinding.java:78) javax.faces.component.UIOutput.getValue(UIOutput.java:159) com.ibm.xsp.util.FacesUtil.convertValue(FacesUtil.java:1122) com.ibm.xsp.renderkit.html_basic.OutputTextRenderer.encodeEnd(OutputTextRenderer.java:97) com.ibm.xsp.renderkit.ReadOnlyAdapterRenderer.encodeEnd(ReadOnlyAdapterRenderer.java:180) javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:1005) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:858) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.util.FacesUtil.renderComponent(FacesUtil.java:853) com.ibm.xsp.component.UIViewRootEx._renderView(UIViewRootEx.java:1317) com.ibm.xsp.component.UIViewRootEx.renderView(UIViewRootEx.java:1255) com.ibm.xsp.application.ViewHandlerExImpl.doRender(ViewHandlerExImpl.java:651) com.ibm.xsp.application.ViewHandlerExImpl._renderView(ViewHandlerExImpl.java:321) com.ibm.xsp.application.ViewHandlerExImpl.renderView(ViewHandlerExImpl.java:336) com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:103) com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210) com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:120) com.ibm.xsp.controller.FacesControllerImpl.render(FacesControllerImpl.java:270) com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:261) com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:157) com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160) com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:138) com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103) com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576) com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1335) com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:853) com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796) com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565) com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1319) com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:662) com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:482) com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:350) com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:306) com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)

2
sql-serverjdbcxpages
Can you post more of the stacktraces? and code? - Toby Samples
I have added the stacktrace - Thomas Adrian
Some more info can be found in the defect tab here extlib.openntf.org/?r=project/XPages%20Extension%20Library/… - Thomas Adrian

2 Answers

0
votes

I came across this problem some time ago with column names being upper cased for a method in Extension Library code -> How to get XPages and JSON to not put variable names in Uppercase

I'm not sure if it's the same problem, but may be worth investigating.

0
votes

To find out if it's a security issue you could allow all permissions to Java. Create a java.pol file in the jvm/lib/ext/ folder, and add this:

grant {
  permission java.security.AllPermission;
};

(don't forget to restart the server when you're done)

I've done custom JDBC connections by including the correct driver Jar file to the database and I needed to do this to get it work.