?
Solved

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

Posted on 2006-04-05
14
Medium Priority
?
3,358 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:suketu9
14 Comments
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16383619
Hi suketu9,

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

Joe P
0
 

Author Comment

by:suketu9
ID: 16383738
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 16383747
Try

return m.invoke(ps,(Object)parameters);
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:mrigank
ID: 16383906
Not ssure if that is the problem, but you need to close the ResultSets as well.
Try doing that.
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16383951
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
 
LVL 23

Expert Comment

by:Siva Prasanna Kumar
ID: 16384118
I think its the problem withn the result set Just do a results.close before exiting the function.
Thank You.
0
 

Author Comment

by:suketu9
ID: 16384583
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
 
LVL 5

Expert Comment

by:mrigank
ID: 16384802
Prepared Statement is closed.
Create a new Prepared Statement and execute the query on it.
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16384816
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
 

Author Comment

by:suketu9
ID: 16385033
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
 
LVL 5

Expert Comment

by:mrigank
ID: 16385173
where is your ps initialised ?

ps = conn.createStatement() or something like that ?
0
 

Author Comment

by:suketu9
ID: 16385693
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
 
LVL 5

Accepted Solution

by:
mrigank earned 1500 total points
ID: 16385863
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
 

Author Comment

by:suketu9
ID: 16386117
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month9 days, 8 hours left to enroll

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question