11 Replies Latest reply: Oct 10, 2012 8:26 AM by 967064 RSS

    Problem with getMetadata from PrepareStatement

    967064

      Hi,
      i would take metadata from preparestatement directly without execute a query, but when i do
      preparedstatement.getMetaData()

      i receive an handling error of this type:
      handle of the instruction not executed: getMetaData
      +(this is my translation from italian message: handle dell'istruzione non eseguito: getMetaData)+

      In Internet i found that may be an error due to oracle that requires an open connection, so include the invoke of the open method in this way:
      Method method = preparedstatement.getClass().getSuperclass().getSuperclass().getDeclaredMethod("open");
      method .setAccessible(true);
      method .invoke(preparedstatement,new Object[0]);

      and now the previous error is disappeared, but now when i get metadata:
      preparedstatement.getMetaData()
      i have this error:
      Error: ORA-01009: missing mandatory parameter

      What is wrong?
      Thanks in advance

        • 1. Re: Problem with getMetadata from PrepareStatement
          Tolls
          Why are you trying to get the meta data without actually executing the PreparedStatement?
          Especially in such a round about way.

          Do you not think that that looks rather hacky?
          • 2. Re: Problem with getMetadata from PrepareStatement
            967064
            Becouse in this step i need only or the name and type of column or check the syntax of query, and so i woldn't wait the execution and fetch phases.
            • 3. Re: Problem with getMetadata from PrepareStatement
              Tolls
              Gah.
              I was thinking of something else entirely.
              My bad.

              Right, let's try this again.
              I don't suppose you have the full exception available (possibly including stack trace)?
              I can't understand that translation.
              It should say what the actual exception class is, as well.

              What driver are you using, and what version of Oracle?
              • 4. Re: Problem with getMetadata from PrepareStatement
                rp0428
                >
                i would take metadata from preparestatement directly without execute a query
                >
                And what method did you use to create and compile the PreparedStatement before you tried to get the metadata?

                Did you use Connection.prepareStatement method?

                You have to create a compiled prepared statement before you can get metadata. The second error seems to indicate that you haven't set all of the parameters in the query.

                Post the code you are using to create the prepared statement.
                • 5. Re: Problem with getMetadata from PrepareStatement
                  Joe Weinstein-Oracle
                  A Statement is just an object that allows you to use a connection to talk to the DBMS,
                  and the statement and connection must be open. The metadata a statement may provide
                  has to come from the DBMS usually. The information accompanies the return data for the
                  statement execution.
                  • 6. Re: Problem with getMetadata from PrepareStatement
                    967064
                    Hi all,
                    first of all thanks for your prompt reply.
                    I use Oracle 11
                    and Oracle JDBC Driver version - 10.1.0.2.0

                    With this code:

                    java.sql.Connection Connection connection = java.sql.DriverManager.getConnection(_DBUrl, User, Password)
                    PreparedStatement preparedstatement= connection.prepareStatement("select 1 as num, 'ppp' as desc from dual");
                    ResultSetMetaData rsmd = preparedstatement.getMetaData();

                    i receive this error (but the error message is in italian):

                    java.sql.SQLException: handle dell'istruzione non eseguito: getMetaData
                         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
                         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
                         at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:861)
                         at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:130)
                         at oracle.jdbc.driver.OraclePreparedStatement.getMetaData(OraclePreparedStatement.java:3500)
                         at it.sword.dei.server.command.ValidateSqlReportDBCommand.handleExecute(ValidateSqlReportDBCommand.java:95)
                         at it.sword.dei.kernel.command.DBCommand.handleExecute(DBCommand.java:68)
                         at it.sword.dei.client.action.ValidateSqlAction.internalProcess(ValidateSqlAction.java:95)
                         at it.sword.dei.client.function.AbstractFunctionManager.executeAction(AbstractFunctionManager.java:62)
                         at it.sword.dei.client.function.AdminNewAnalisiFunctionManager.executeFunction(AdminNewAnalisiFunctionManager.java:308)
                         at it.sword.dei.client.function.AbstractFunctionManager.manageFunction(AbstractFunctionManager.java:151)
                         at it.sword.dei.client.model.jsp.ModelManagerServlet.invokeFunction(ModelManagerServlet.java:57)
                         at it.sword.dei.client.model.jsp.ModelManagerServlet.performTask(ModelManagerServlet.java:92)
                         at it.sword.dei.jsp.DeiServlet.service(DeiServlet.java:1124)
                         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
                         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
                         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
                         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
                         at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:432)
                         at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:386)
                         at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:534)
                         at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:530)
                         at java.lang.Thread.run(Unknown Source)


                    If i modify the code in this way, as read in this forum:

                    java.sql.Connection Connection connection = java.sql.DriverManager.getConnection(_DBUrl, User, Password)
                    PreparedStatement preparedstatement= connection.prepareStatement("select 1 as num, 'ppp' as desc from dual");

                    String className = preparedstatement.getClass().getName();
                    if(className.contains("T4CPreparedStatement") || className.contains("T2CPreparedStatement")){
                         try{
                         Method[] methods = preparedstatement.getClass().getSuperclass().getSuperclass().getDeclaredMethods();
                    for (int m=0; m<methods.length; m++){
                         Method met= methods[m];
                    if(!connection.isClosed()){
                         if("open".equals(met.getName())){
                         met.setAccessible(true);
                    met.invoke(preparedstatement,new Object[0]);
                                             break;
                    }     
                    }
                    }
                    }catch(Exception e){
                         throw new RuntimeException(e);
                    }
                    }     
                    ResultSetMetaData rsmd = preparedstatement.getMetaData();

                    the first error disappeared, but i receive this error:

                    java.sql.SQLException: ORA-01009: missing mandatory parameter

                         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
                         at oracle.jdbc.driver.T4C8Odscrarr.receive(T4C8Odscrarr.java:203)
                         at oracle.jdbc.driver.T4CPreparedStatement.do_describe(T4CPreparedStatement.java:364)
                         at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:3181)
                         at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:63)
                         at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:133)
                         at oracle.jdbc.driver.OraclePreparedStatement.getMetaData(OraclePreparedStatement.java:3500)
                         at it.sword.dei.server.command.ValidateSqlReportDBCommand.handleExecute(ValidateSqlReportDBCommand.java:116)
                         at it.sword.dei.kernel.command.DBCommand.handleExecute(DBCommand.java:68)
                         at it.sword.dei.client.action.ValidateSqlAction.internalProcess(ValidateSqlAction.java:95)
                         at it.sword.dei.client.function.AbstractFunctionManager.executeAction(AbstractFunctionManager.java:62)
                         at it.sword.dei.client.function.AdminNewAnalisiFunctionManager.executeFunction(AdminNewAnalisiFunctionManager.java:308)
                         at it.sword.dei.client.function.AbstractFunctionManager.manageFunction(AbstractFunctionManager.java:151)
                         at it.sword.dei.client.model.jsp.ModelManagerServlet.invokeFunction(ModelManagerServlet.java:57)
                         at it.sword.dei.client.model.jsp.ModelManagerServlet.performTask(ModelManagerServlet.java:92)
                         at it.sword.dei.jsp.DeiServlet.service(DeiServlet.java:1124)
                         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
                         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
                         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
                         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
                         at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                         at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                         at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                         at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
                         at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:432)
                         at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:386)
                         at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:534)
                         at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:530)
                         at java.lang.Thread.run(Unknown Source)

                    my doubt is in this line:
                    met.invoke(preparedstatement,new Object[0]);
                    for the second parameter, i put an empty object array because i see that the method open of oracle.jdbc.driver.OracleStatement is without parameter.
                    Thanks in advance
                    • 7. Re: Problem with getMetadata from PrepareStatement
                      EJP
                      You haven't executed the prepared statement yet, so you can't get any metadata from it until you do.
                      • 8. Re: Problem with getMetadata from PrepareStatement
                        Tolls
                        EJP wrote:
                        You haven't executed the prepared statement yet, so you can't get any metadata from it until you do.
                        That's what I thought, but reading the API it implies (well, says actually) that you don't need to execute it (hence my original posts):
                        http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#getMetaData()

                        "
                        Because a PreparedStatement object is precompiled, it is possible to know about the ResultSet object that it will return without having to execute it. Consequently, it is possible to invoke the method getMetaData on a PreparedStatement object rather than waiting to execute it and then invoking the ResultSet.getMetaData method on the ResultSet object that is returned.
                        "

                        Unless I'm misinterpreting what that says.

                        Then again, that error seems to be saying it can't find the meta data.

                        Digging around a bit and it seems ojdbc14 and earlier do not support the getMetaData method, but I can't find anything quickly from the horses mouth (so to speak).
                        Or whether a later driver would work.
                        Though I would suggest the OP try a later driver version (assuming it doesn't clash with the version of Java being used).
                        • 9. Re: Problem with getMetadata from PrepareStatement
                          Tolls
                          And just to talk to myself:
                          http://docs.oracle.com/cd/E14072_01/java.112/e10589/whatsnew.htm#CJACGJHA

                          "
                          Starting from Oracle Database 11g Release 2 (11.2), JDBC drivers support obtaining the metadata of a SELECT statement without executing the PreparedStatement.
                          "

                          So you need the newer driver.
                          • 10. Re: Problem with getMetadata from PrepareStatement
                            gimbal2
                            Tolls wrote:
                            So you need the newer driver.
                            Good advice, regardless of added features. The driver used was built for Oracle 9 and JDK 1.4, not Oracle 11 and any recent maintained version of Java.
                            • 11. Re: Problem with getMetadata from PrepareStatement
                              967064
                              Ok,
                              i use the latest version of jdbc driver and work fine; now with:

                              ResultSetMetaData rsmd = preparedstatement.getMetaData();

                              i save a lot of time!!!

                              Thanks to everybody for the help