We are working with a new deployment and are attempting to get our first app installed on Voice Portal where our users can start calling and testing it. Early in the app we have a database query step which works perfectly in our simulations inside Eclipse, but we get a Runtime Error when we verify the app on Voice Portal:

javax.servlet.ServletException: EXCEPTION> com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ". ClientConnectionId:d9b6ef58...and it goes on for quite a while. I can send a copy of the full error if it would help.

I have confirmed access to our SQL database with the same user id and password on the Tomcat server where we are deploying and, like I said, this all works beautifully within Eclipse. Any thoughts or suggestions for identifying the problem and getting it resolved?

Can you post more of the stack trace. You should verify that the data base driver jar file is also deployed to your application server.

If you find the exception happen after some idling and goes away after restarting the app server, you have to set up some extra parameters on Tomcat as the following post suggest:

https://devconnect.avaya.com/public/forum/d_forum_3.jsp?t=8432&f=6

Here is the rest of the trace. Interestingly, when I go through the verify process today it is a slightly different error. Note the first part of it is different than my earlier post.

javax.servlet.ServletException: EXCEPTION> com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed. at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667) at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654) at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4844) at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at com.avaya.sce.runtime.connectivity.db.DbQuery.executeStatement(DbQuery.java:289) at com.avaya.sce.runtime.connectivity.db.DbQuery.executeSQL(DbQuery.java:198) at com.avaya.sce.runtime.connectivity.db.DbQuery.execute(DbQuery.java:134) at com.avaya.sce.runtime.Data.evaluateActions(Data.java:165) at flow.chkEnterLoan.executeDataActions(chkEnterLoan.java:66) at com.avaya.sce.runtime.Data.handleRequest(Data.java:81) at com.avaya.sce.runtime.AppServlet.processRequest(AppServlet.java:78) at com.avaya.sce.runtime.SCEServlet.requestHandler(SCEServlet.java:267) at com.avaya.sce.runtime.SCEServlet.doPost(SCEServlet.java:184) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:291) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Unknown Source)

Sorry for the double post...going to check jar files and reading through that other thread now.

The exception indicates stale connections. Do try the extra setting suggested in the other threads.

I can do that. I had just finished looking through that other thread. As we're new to this platform, can you guide me on where to look to edit those settings?

As for jar files, I have sqljdbc4.jar in both my local tomcat lib and in the server's tomcat lib directories.

You add or modify the parameters in the <appname>xml file in Tomcat/conf/catalina/localhost directory. There are couple parameters - testOnBorrow and validationQuery - pre-added. You need to configure them and add other ones if necessary.

Very good. Thanks.

I have a couple of additional questions before I make the change.

Do I edit the xml locally and then it carries over as I deploy or do I need to edit the xml directly on the tomcat server?

Also, in the other thread you recommended the following:

validateQuery="select 1 from dual"

for Oracle, but mine currently says:

validateQuery="select 1 from AnyTable"

Do I need to adjust this and then add the following two parameters after that?

testOnReturn=true
testWhileIdle=true

You need to edit the file after the deployment.

The query needs to be a valid query that runs on the database. For sql server, "select getdate()" would run.

You can add the 2 additional parameters. They would help.

I have tried a few things, but anytime I edit that file at all my app immediately crashes and doesn't even answer. I just get the default "your application is having a problem" voice played.

Of the three queries I am getting to when I do get my app to run, the first and last give this:

The statement did not return a result set.

...and the second gives this:

SQL Server did not return a response. The connection has been closed.

Is this still consistent with a stale connection or do we need to look elsewhere? As for editing the xml file for the app, do I need to use any specific editor or will Notepad work?

Thanks for your help.

First, I am not sure how all the apps "immediate crashes" provided that you only edit the <appname>.xml on one app. Make sure you edit the file with Tomcat stopped; then start it back up after you save the changes.
Any text editor should be fine.

I'm curious, not to hijack this thread but now that my applications are encountering the same need to include the

testOnReturn=true
testWhileIdle=true

settings and I read this exchange: Why aren't these defaulted already with these values? Or at least in a location where we could set the properties in DD/OD and forget them? Having support employees make these changes while deploying developed applications is a hard sell to my management (even when the alternative is DB call failures). Please consider this a formal request if that is what is needed. Thanks!

In our design, we didn't find these 2 parameters crucial, and they are part of the Apache DBCP component which is external to DD/OD. We assume customers would have to fine tune the Tomcat/DBCP after deployment anyway. Now it looks like quite a few folks depend on these parameters when deploying on Tomcat. It will probably be good to add them in our design environment for our customers. We will consider it as an enhancement.