java.sql.SQLException: Could not execute sql command - Original message: null: URGENT HELP

I am having problems when running some query in servlets. I have this webapp that adds a device and after it add it displays the message saying device added successfully and it also displays the device that was added in the table below this message. But after displaying ths message it goes to the error page that we have in our exception block and displays the error page. I am using WSAS and Websphere 5.0. I looked at the output in the console and the error says:

java.sql.SQLException: Could not execute sql command - Original message: null.

I dont why its doing that. Because sometime it works fine with no problem. And once every 2 times I get this error. I am copying my console output and if someone and look at it and see what might be the problem. I can also post the code where the problem occurs:

Console Output:

[4/5/06 12:34:20:179 EDT] 5391e072 WebGroup      I SRVE0180I: [MSOURCE-webapp] [/mobilesource2] [Servlet.LOG]: SearchDeviceServlet: init
[4/5/06 12:34:20:179 EDT] 5391e072 SystemOut     O Survey Datasource Name =java:comp/env/MS/SurveyDB
[4/5/06 12:34:20:179 EDT] 5391e072 SystemOut     O surveyschema=surveydb
[4/5/06 12:34:20:179 EDT] 5391e072 SystemOut     O router Datasource Name =java:comp/env/MS/RouterDB
[4/5/06 12:34:20:179 EDT] 5391e072 SystemOut     O routerschema=surveydb
[4/5/06 12:34:20:219 EDT] 5391e072 SystemOut     O Inside the SearcheDeviceServlet connection object is : com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@6f9a206c
[4/5/06 12:34:20:219 EDT] 5391e072 SystemOut     O getAllDeviceQuery :  select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from surveydb.systemroutingdata srd left join(surveydb.deviceidtranslation dit join surveydb.techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid)  where SystemType='H' and srd.SystemName like ?  order by SystemName
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O Debug parameter that is passed is com.ccfna.mobilesource.server.common.DebugLevel@4947607c
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O I am in prepared.getstatement
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O The con is com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@6f9a206c
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O The stmt is  select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from surveydb.systemroutingdata srd left join(surveydb.deviceidtranslation dit join surveydb.techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid)  where SystemType='H' and srd.SystemName like ?  order by SystemName
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O The debuglevel is com.ccfna.mobilesource.server.common.DebugLevel@4947607c
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O Default Formatter is com.ccfna.mobilesource.server.common.DefaultSqlFormatter@6a31a07c
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O I am in statementfactory.getstatement 4 parameters method
[4/5/06 12:34:20:269 EDT] 5391e072 SystemOut     O I am after this.connection
[4/5/06 12:34:21:121 EDT] 5391e072 SystemOut     O Prepared Statement is com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement@6222e06d
[4/5/06 12:34:21:121 EDT] 5391e072 SystemOut     O SQL is   select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from surveydb.systemroutingdata srd left join(surveydb.deviceidtranslation dit join surveydb.techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid)  where SystemType='H' and srd.SystemName like ?  order by SystemName
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O DebugLevel is com.ccfna.mobilesource.server.common.DebugLevel@4947607c
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O Formatter is  com.ccfna.mobilesource.server.common.DefaultSqlFormatter@6a31a07c
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O I am after StatementFactory.getStatement
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O Prepared Statement is  select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from surveydb.systemroutingdata srd left join(surveydb.deviceidtranslation dit join surveydb.techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid)  where SystemType='H' and srd.SystemName like ? (missing variable # 1 )   order by SystemName
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O I am after setDevicePerpareStatement
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O Prepared Statement is  select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from surveydb.systemroutingdata srd left join(surveydb.deviceidtranslation dit join surveydb.techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid)  where SystemType='H' and srd.SystemName like 'MOBILESOU200%'  order by SystemName
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O deviceQuery =  select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from surveydb.systemroutingdata srd left join(surveydb.deviceidtranslation dit join surveydb.techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid)  where SystemType='H' and srd.SystemName like 'MOBILESOU200%'  order by SystemName
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O I am in executeQuery
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O methodName is executeQuery
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O Class parameters is null
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O I am in executeVerbose Query
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O Method m is public java.sql.ResultSet com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery() throws java.sql.SQLException
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O m.inovke ps is com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement@6222e06d
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O m.inovke parameters is null
[4/5/06 12:34:21:131 EDT] 5391e072 SystemOut     O I am in executeQuery catch statement
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R java.sql.SQLException: Could not execute sql command - Original message: null
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.server.common.DebuggableStatement.executeQuery(DebuggableStatement.java:255)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.server.message.DataAccess.getAllDevice(DataAccess.java:8489)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doGet(SearchDeviceServlet.java:151)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doPost(SearchDeviceServlet.java:182)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
[4/5/06 12:34:21:131 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:872)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R com.ccfna.mobilesource.server.message.MobileSourceException
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.server.message.DataAccess.getAllDevice(DataAccess.java:8577)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doGet(SearchDeviceServlet.java:151)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doPost(SearchDeviceServlet.java:182)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:872)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)[4/5/06 12:34:21:141 EDT] 5391e072 SystemOut     O I am in Catch exception
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
[4/5/06 12:34:21:141 EDT] 5391e072 SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)

[4/5/06 12:34:21:141 EDT] 5391e072 SystemOut     O After Displaying Search.jsp
[4/5/06 12:34:21:171 EDT] 5391e072 WebGroup      I SRVE0180I: [MSOURCE-webapp] [/mobilesource2] [Servlet.LOG]: /AdminWeb/ErrorPage.jsp: init


Code where the query runs:

public ResultSet executeQuery() throws SQLException{
        //execute query
        ResultSet results = null;
       
       
        try{
              System.out.println("I am in executeQuery");
            results = (ResultSet)executeVerboseQuery("executeQuery",null);
            System.out.println("Resultset results is " + results);
        }catch(Exception e){
              System.out.println("I am in executeQuery catch statement");
            throw new SQLException("Could not execute sql command - Original message: " + e.getMessage());
        }
       
        return results;
    }


private Object executeVerboseQuery(String methodName,Class[] parameters)
                                throws SQLException,NoSuchMethodException,
                                       InvocationTargetException,IllegalAccessException{
        //determine which method we have
        System.out.println("methodName is " + methodName);
        System.out.println("Class parameters is " + parameters);
        Method m = ps.getClass().getDeclaredMethod(methodName,parameters);
            System.out.println("I am in executeVerbose Query");
            System.out.println("Method m is " + m);
        //debug is set to on, so no times are calculated
       if (debugLevel == DebugLevel.ON)
        {
              System.out.println("m.inovke ps is " + ps);
              System.out.println("m.inovke parameters is " + parameters);
          return m.invoke(ps,parameters);
        }
       
        System.out.println("I am in executeVerbose Query before start()");

        //calculate execution time for verbose debugging
        start();
        System.out.println("I am in executeVerbose Query after start()");
        Object returnObject = m.invoke(ps,parameters);
        System.out.println("I am in executeVerbose Query after returnobject");
        end();
        System.out.println("I am in executeVerbose Query after end");
       
        System.out.println("I am in executeVerbose Query and returnobject is " + returnObject);

        //return the executions return type
        return returnObject;
    }

If you look at the last code executeVerboseQuery and look at the if statement that is where the code is getting back null value I think not sure. Please help me out and let me know what else one might need. This is URGENT.

Thanks
Suketu
suketu9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BogoJokerCommented:
Hi suketu9,

>  results = (ResultSet)executeVerboseQuery("executeQuery",null);
Your executing a null query here right?

Joe P
0
suketu9Author Commented:
see this executeQuery() is perparedStatement method but this class
public class DebuggableStatement implements PreparedStatement{

overrides the method with the code listed above for it.

results = (ResultSet)executeVerboseQuery("executeQuery",null);
Above statement calls this executeVerboseQuery method which take following parameters(String methodName,Class[] parameters)

then code does this:
//determine which method we have
        System.out.println("methodName is " + methodName);
        System.out.println("Class parameters is " + parameters);
        Method m = ps.getClass().getDeclaredMethod(methodName,parameters);
          System.out.println("I am in executeVerbose Query");
          System.out.println("Method m is " + m);
        //debug is set to on, so no times are calculated
       if (debugLevel == DebugLevel.ON)
        {
             System.out.println("m.inovke ps is " + ps);
             System.out.println("m.inovke parameters is " + parameters);
          return m.invoke(ps,parameters);
        }

But after gong into the if statement I think it return null value.  I did not write this code. I am doing production support. Apparently this works sometimes most of the time it never works. So what should I pass in parameters instead of null.

Their is one method getDeviec which calls the executeQuery():
ResultSet rs = ps.executeQuery();

I dont know what to put in null , please let me know what should i put their.

Thanks


0
CEHJCommented:
Try

return m.invoke(ps,(Object)parameters);
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mrigankCommented:
Not ssure if that is the problem, but you need to close the ResultSets as well.
Try doing that.
0
mrigankCommented:
catch(Exception e){
             System.out.println("I am in executeQuery catch statement");
            throw new SQLException("Could not execute sql command - Original message: " + e.getMessage());
        }

can you add a e.printStackTrace() in this catchblock and post what the trace is.
0
Siva Prasanna KumarPrincipal Solutions ArchitectCommented:
I think its the problem withn the result set Just do a results.close before exiting the function.
Thank You.
0
suketu9Author Commented:
Following is the output in the console after e.printStackTrace(). Looks like it says statement is closed. Please anyone look at it and tell me what the trace says and how to fix this.

[4/5/06 14:12:42:329 EDT] 6b152bfb SystemOut     O Method m is public java.sql.ResultSet com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery() throws java.sql.SQLException
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemOut     O m.inovke ps is com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement@30222bfb
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemOut     O m.inovke parameters is null
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemOut     O I am in executeQuery catch statement
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R java.lang.reflect.InvocationTargetException: com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed.
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.runtimeXIfNotClosed(WSJdbcStatement.java:879)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:532)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at java.lang.reflect.Method.invoke(Native Method)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.server.common.DebuggableStatement.executeVerboseQuery(DebuggableStatement.java:864)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.server.common.DebuggableStatement.executeQuery(DebuggableStatement.java:251)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.server.message.DataAccess.getAllDevice(DataAccess.java:8490)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doGet(SearchDeviceServlet.java:154)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doPost(SearchDeviceServlet.java:185)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:872)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:187)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R java.sql.SQLException: Could not execute sql command - Original message: null
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.server.common.DebuggableStatement.executeQuery(DebuggableStatement.java:256)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.server.message.DataAccess.getAllDevice(DataAccess.java:8490)
[4/5/06 14:12:42:329 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doGet(SearchDeviceServlet.java:154)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doPost(SearchDeviceServlet.java:185)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:872)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:187)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R com.ccfna.mobilesource.server.message.MobileSourceException
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.server.message.DataAccess.getAllDevice(DataAccess.java:8578)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doGet(SearchDeviceServlet.java:154)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ccfna.mobilesource.AdminWeb.servlets.SearchDeviceServlet.doPost(SearchDeviceServlet.java:185)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:872)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:187)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R [4/5/06 14:12:42:359 EDT] 6b152bfb SystemOut     O I am in Catch exception
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemOut     O After Displaying Search.jsp
      at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
[4/5/06 14:12:42:359 EDT] 6b152bfb SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
0
mrigankCommented:
Prepared Statement is closed.
Create a new Prepared Statement and execute the query on it.
0
mrigankCommented:
java.lang.reflect.InvocationTargetException: com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed.

The Prepared Statement you are using has been closed.
ps is a class level variable for you.
Make it a method level variable

or

recreate a new PreparedStatement in you method.
0
suketu9Author Commented:
How do I make it a method level. This is what my code does.
In my getdevice method i have this statements:

getAllDeviceQuery += " order by SystemName";
                  System.out.println("getAllDeviceQuery : " + getAllDeviceQuery);
                  System.out.println("Debug parameter that is passed is " + debug);
                  ps = StatementFactory.getStatement(connection,getAllDeviceQuery,debug);
                  System.out.println("I am after StatementFactory.getStatement");
                  System.out.println("Prepared Statement is " + ps);
                  ps = setDevicePrepareStatement(ps,firstElement,deviceSearchParamBO);
                  System.out.println("I am after setDevicePerpareStatement");
                  System.out.println("Prepared Statement is " + ps);
                  System.out.println("deviceQuery = "+ps.toString());

                  ResultSet rs = ps.executeQuery();
And ps.executeQuery() call this class
public class DebuggableStatement implements PreparedStatement

which overrides executeQuery().
executeQuery() method code is :
public ResultSet executeQuery() throws SQLException{
        //execute query
        ResultSet results = null;    
                try{         
              System.out.println("I am in executeQuery");
              results = (ResultSet)executeVerboseQuery("executeQuery",null);
            System.out.println("Resultset results is " + results);
        }catch(Exception e){
              System.out.println("I am in executeQuery catch statement");
          e.printStackTrace();
          throw new SQLException("Could not execute sql command - Original message: " + e.getMessage());
        }
        return results;
    }

executeQuery() calls this executeVerboseQuery(), Code for executeVerboseQuery()
private Object executeVerboseQuery(String methodName,Class[] parameters)
                                throws SQLException,NoSuchMethodException,
                                       InvocationTargetException,IllegalAccessException{
        //determine which method we have
        System.out.println("methodName is " + methodName);
        System.out.println("Class parameters is " + parameters);
        Method m = ps.getClass().getDeclaredMethod(methodName,parameters);
            System.out.println("I am in executeVerbose Query");
            System.out.println("Method m is " + m);
        //debug is set to on, so no times are calculated
       if (debugLevel == DebugLevel.ON)
        {
              System.out.println("m.inovke ps is " + ps);
              System.out.println("m.inovke parameters is " + parameters);
          return m.invoke(ps,parameters);
        }
       
        System.out.println("I am in executeVerbose Query before start()");

        //calculate execution time for verbose debugging
        start();
        System.out.println("I am in executeVerbose Query after start()");
        Object returnObject = m.invoke(ps,parameters);
        System.out.println("I am in executeVerbose Query after returnobject");
        end();
        System.out.println("I am in executeVerbose Query after end");
       
        System.out.println("I am in executeVerbose Query and returnobject is " + returnObject);

        //return the executions return type
        return returnObject;
    }

Where do I create a new preparedstatement based on this code and how do i create it. Or how I do make is method level. Please provide me with code, I am new to JDBC and have no clue how to do this. Please help. Thanks
0
mrigankCommented:
where is your ps initialised ?

ps = conn.createStatement() or something like that ?
0
suketu9Author Commented:
This is my getAllDevice method: Their is a statement in this method: ps = connection.prepareStatement(getAllDeviceQuery); it was commented before I uncommented it and it still does not work.

Also their are  method calls ps.StatementFactory.getStatement which in turns call bunch of other method. I really dont know what they do.  Please help and let me know what else you need.


public static Collection getAllDevice(DeviceSearchParamBO deviceSearchParamBO)throws MobileSourceException
      {
            Connection connection = null;

            PreparedStatement ps = null;

            Collection devicesList = null;

            try
            {

                  String firstElement = null;

                  connection = routerPool.getConnection();
                  
                  System.out.println("Inside the SearcheDeviceServlet connection object is : " + connection);

                  DebugLevel debug = DebugLevel.ON;

                  String getAllDeviceQuery = " select SystemName,QMName,SystemStatus,td.TechId as TechnicianId,FirstName,LastName, AppRelease from "+routerSchemaName+".systemroutingdata srd left join("+routerSchemaName+".deviceidtranslation dit"+
                                                         " join "+routerSchemaName+".techdata td on (dit.techid=td.techid))on( srd.systemname=dit.deviceid) "+
                                                         " where SystemType='H'";

                  if((deviceSearchParamBO.getDeviceId()!=null)&&(!("").equals(deviceSearchParamBO.getDeviceId())))
                  {
                        firstElement = "DeviceId";

                        getAllDeviceQuery = getAllDeviceQuery + " and srd.SystemName like ? ";

                        getAllDeviceQuery = getRestOfDeviceQuery(getAllDeviceQuery,"DeviceId",deviceSearchParamBO);
                  }
                else if((deviceSearchParamBO.getTechId()!=null)&&(!("").equals(deviceSearchParamBO.getTechId())))
                  {
                        firstElement = "TechId";

                        getAllDeviceQuery = getAllDeviceQuery + " and td.TechId = ? ";

                        getAllDeviceQuery = getRestOfDeviceQuery(getAllDeviceQuery,"TechId",deviceSearchParamBO);
                  }
                  else if((deviceSearchParamBO.getFirstName()!=null)&&(!("").equals(deviceSearchParamBO.getFirstName())))
                  {
                        firstElement = "FirstName";

                        getAllDeviceQuery = getAllDeviceQuery + " and UPPER(FirstName) like ? ";

                        getAllDeviceQuery = getRestOfDeviceQuery(getAllDeviceQuery,"FirstName",deviceSearchParamBO);
                  }
                  else if((deviceSearchParamBO.getLastName()!=null)&&(!("").equals(deviceSearchParamBO.getLastName())))
                  {
                        firstElement = "LastName";

                        getAllDeviceQuery = getAllDeviceQuery + " and UPPER(LastName) like ? ";

                        getAllDeviceQuery = getRestOfDeviceQuery(getAllDeviceQuery,"LastName",deviceSearchParamBO);
                  }
                  else if((deviceSearchParamBO.getAppRelease()!=null)&&(!("").equals(deviceSearchParamBO.getAppRelease())))
                  {
                        firstElement = "AppRelease";

                        getAllDeviceQuery = getAllDeviceQuery + " and UPPER(AppRelease) like ? ";

                        getAllDeviceQuery = getRestOfDeviceQuery(getAllDeviceQuery,"AppRelease",deviceSearchParamBO);
                  }

                  //ps = connection.prepareStatement(getAllDeviceQuery);
                  
                  getAllDeviceQuery += " order by SystemName";
                  System.out.println("getAllDeviceQuery : " + getAllDeviceQuery);
                  System.out.println("Debug parameter that is passed is " + debug);
                  ps = StatementFactory.getStatement(connection,getAllDeviceQuery,debug);
                  System.out.println("I am after StatementFactory.getStatement");
                  System.out.println("Prepared Statement is " + ps);
                  ps = setDevicePrepareStatement(ps,firstElement,deviceSearchParamBO);
                  System.out.println("I am after setDevicePerpareStatement");
                  System.out.println("Prepared Statement is " + ps);
                  System.out.println("deviceQuery = "+ps.toString());
//                  Statement stmt = connection.(getAllDeviceQuery);

                  ResultSet rs = ps.executeQuery();
                  System.out.println("Resultset rs is " + rs);
                  System.out.println("After executeQuery");

                  String lastDevice="";

                  String currentDevice="";

                  Collection userCollection = null;

                  DeviceBO deviceBO = null;

                  TechDataBO techDataBO = null;

                  devicesList = new ArrayList();
0
mrigankCommented:
ps = connection.prepareStatement(getAllDeviceQuery);

needs to be  uncommented.
Did you compile the code after uncommenting it ;) .

Is there a place where ps.close() or conn.close() is getting called ?
               
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suketu9Author Commented:
Its working now, their is this method that ps calls and in that method ps.close() is called. so i changed that and now its working. Thanks everyone for help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.