Announcement Announcement Module
Collapse
No announcement yet.
How to Access In/Out Parameters of Stored Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to Access In/Out Parameters of Stored Procedure

    I want to run access DB2 store procedure:
    db2info.MD_MESSAGE(operation IN, metadata IN/OUT, response OUT)
    all parameters are of type CLOB.

    I read other articles of this forum, I can read this output of "response",
    but how to define "metadata" as In/Out in SpringFramework?

    the following is my code(result is OK).
    Code:
    /*
     * Created on 2004-11-16
     *
     */
    
    
    import java.util.*;
    
    import org.apache.log4j.Logger;
    import org.springframework.context.*;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.core.SqlReturnType;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.jdbc.object.*;
    import javax.sql.*;
    
    import java.io.IOException;
    import java.io.Reader;
    import java.sql.*;
    
    /**
     * @author Daniel
     *
     */
    public class DB2Test {
        /**
         * Logger for this class
         */
        private static final Logger logger = Logger.getLogger(DB2Test.class);
        
        private class MySP extends StoredProcedure {
            public static final String SQL = "db2info.MD_MESSAGE(?,?,?)";
        		public static final String request = 
        		    "<olap&#58;request xmlns&#58;olap=\"http&#58;//www.ibm.com/olap\" "
        		    + "xmlns&#58;xsi=\"http&#58;//www.w3.org/2001/XMLSchema-instance\" "
        		    + "xmlns&#58;xsd=\"http&#58;//www.w3.org/2001/XMLSchema\" version=\"8.1.2.1.0\">"
        		    + "<describe objectType=\"all\" recurse=\"no\" />"
        		    + "</olap&#58;request>";
        		public Clob metaData = null;
            
            public MySP&#40;DataSource dataSource&#41; &#123;
                setDataSource&#40;dataSource&#41;;
                setFunction&#40;true&#41;;
                setSql&#40;SQL&#41;;
                declareParameter&#40;new SqlOutParameter&#40;"warn", Types.INTEGER&#41;&#41;;
                declareParameter&#40;new SqlParameter&#40;"operation", Types.VARCHAR&#41;&#41;;
                declareParameter&#40;new SqlParameter&#40;"metadata", Types.CLOB&#41;&#41;;
    //            declareParameter&#40;new SqlOutParameter&#40;"response", Types.CLOB&#41;&#41;;
                declareParameter&#40;new SqlOutParameter&#40;"response", Types.CLOB, null, 
                        new SqlReturnType&#40;&#41; 
                        &#123; 
    
                            public Object getTypeValue&#40;CallableStatement cs, 
                                    int paramIndex, int sqlType, String typeName&#41; 
                                    throws SQLException 
                            &#123; 
                                try 
                                &#123; 
                                    final Clob aClob = cs.getClob&#40;paramIndex&#41;; 
                                    final Reader clobReader = aClob.getCharacterStream&#40;&#41;; 
                                    int length = &#40;int&#41; aClob.length&#40;&#41;; 
                                    char&#91;&#93; inputBuffer = new char&#91;1024&#93;; 
                                    final StringBuffer outputBuffer = new StringBuffer&#40;&#41;; 
                                    while &#40;&#40;length = clobReader.read&#40;inputBuffer&#41;&#41; != -1&#41; 
                                    &#123; 
                                        outputBuffer.append&#40;inputBuffer, 0, length&#41;; 
                                    &#125; 
                                    return outputBuffer.toString&#40;&#41;; 
                                &#125; catch &#40;IOException e&#41; 
                                &#123; 
                                    throw new SQLException&#40;e.toString&#40;&#41;&#41;; 
                                &#125; 
                            &#125; 
                        &#125;&#41;&#41;; 
                compile&#40;&#41;;
            &#125;
            
            public Map execute&#40;&#41; &#123;
                Map inParam = new HashMap&#40;&#41;;
                inParam.put&#40;"operation", request&#41;;
                inParam.put&#40;"metadata", metaData&#41;;
                Map out = execute&#40;inParam&#41;;
    //            Clob  warn = &#40;Clob&#41;out.get&#40;"response"&#41;;
    //            try &#123;
    //                Reader r = warn.getCharacterStream&#40;&#41;;
    //                logger.info&#40;"No"&#41;;
    //                logger.info&#40;r.toString&#40;&#41;&#41;;
    //            &#125; catch &#40;SQLException e&#41; &#123;
    //                logger.info&#40;e&#41;;
    //            &#125;
    //            logger.info&#40;metaData&#41;;
                return out;
            &#125;
        &#125;
    
        void test&#40;&#41; &#123;
            ApplicationContext appContext = new ClassPathXmlApplicationContext&#40;"applicationContext.xml"&#41;;
            DataSource dataSource = &#40;DriverManagerDataSource&#41;appContext.getBean&#40;"dataSource"&#41;;
            MySP sp = new MySP&#40;dataSource&#41;;
            Map res = sp.execute&#40;&#41;;
            Integer s = &#40;Integer&#41; res.get&#40;"warn"&#41;;
            logger.info&#40;s&#41;;
            String  warn = &#40;String&#41;res.get&#40;"response"&#41;;
            logger.info&#40;warn&#41;;
            Clob  metaData = &#40;Clob&#41;res.get&#40;"metadata"&#41;;
            logger.info&#40;metaData&#41;;
            appContext = null;
            
        &#125;
        
        
        public static void main&#40;String&#91;&#93; args&#41; &#123;
            DB2Test t = new DB2Test&#40;&#41;;
            t.test&#40;&#41;;
        &#125;
    &#125;
    result is:
    Code:
    2004-11-17 09&#58;42&#58;47,428 INFO &#91;DB2Test$MySP&#93; - <RdbmsOperation with SQL &#91;db2info.MD_MESSAGE&#40;?,?,?&#41;&#93; compiled>
    2004-11-17 09&#58;42&#58;49,140 INFO &#91;DB2Test&#93; - <-727>
    2004-11-17 09&#58;42&#58;49,140 INFO &#91;DB2Test&#93; - <<olap&#58;response xmlns&#58;olap="http&#58;//www.ibm.com/olap" xmlns&#58;xsi="http&#58;//www.w3.org/2001/XMLSchema-instance" xmlns&#58;xsd="http&#58;//www.w3.org/2001/XMLSchema" version="8.1.2.1.0">
    <describe>
    <status id="0" text="Operation completed successfully. No errors were encountered." type="informational"/>
    </describe>
    </olap&#58;response>
    >
    2004-11-17 09&#58;42&#58;49,140 INFO &#91;DB2Test&#93; - <>

  • #2
    Hope this helps: [url]http://forum.springframework.org/showthread.php?t=10363[url]
    Originally posted by trisberg
    Yes, declare it as an SqlOutParameter and provide a value in the Map used for input parameters. Both in and out parameters are accessed by name from the map, and if there is a value then it is set before the procedure is executed.
    Last edited by robyn; May 14th, 2006, 04:53 PM.

    Comment


    • #3
      How to set provide a Clob value to simulate In/out parameter

      Thank you!
      I get the idea
      Code:
      Yes, declare it as an SqlOutParameter and provide a value in the Map used for input parameters. Both in and out parameters are accessed by name from the map, and if there is a value then it is set before the procedure is executed.
      but "metadata" parameter is of type Clob, I should set its intial value as "", and read the result after stored procedure executed.
      I try to intial Clob to null, I still can't get the result, on the other hand, I try JDBC successfully.
      Code:
      import org.apache.log4j.Logger;
      import org.springframework.context.*;
      import org.springframework.context.support.ClassPathXmlApplicationContext;
      import org.springframework.jdbc.datasource.DriverManagerDataSource;
      
      import javax.sql.*;
      
      import java.sql.*;
      
      /**
       * @author Daniel
       *
       */
      public class DB2Test1 &#123;
          /**
           * Logger for this class
           */
          private static final Logger logger = Logger.getLogger&#40;DB2Test1.class&#41;;
          public static final String SQL = "Call db2info.MD_MESSAGE&#40;?,?,?&#41;";
      		public static final String request = 
      		    "<olap&#58;request xmlns&#58;olap=\"http&#58;//www.ibm.com/olap\" "
      		    + "xmlns&#58;xsi=\"http&#58;//www.w3.org/2001/XMLSchema-instance\" "
      		    + "xmlns&#58;xsd=\"http&#58;//www.w3.org/2001/XMLSchema\" version=\"8.1.2.1.0\">"
      		    + "<describe objectType=\"all\" recurse=\"no\" />"
      		    + "</olap&#58;request>";
      
          
      
          void test&#40;&#41; &#123;
              ApplicationContext appContext = new ClassPathXmlApplicationContext&#40;"applicationContext.xml"&#41;;
              DataSource dataSource = &#40;DriverManagerDataSource&#41;appContext.getBean&#40;"dataSource"&#41;;
              String response = null;
              String xmlMetadataResponse = null;
              CallableStatement stmt = null;
              Connection connection = null;
              
              try &#123;
                  connection = dataSource.getConnection&#40;&#41;;
                  stmt = connection.prepareCall&#40;SQL&#41;;
                  stmt.setString&#40;1, request&#41;;
                  stmt.setString&#40;2, ""&#41;;
                  stmt.registerOutParameter&#40;2, Types.VARCHAR&#41;;
                  stmt.registerOutParameter&#40;3, Types.VARCHAR&#41;;
                  stmt.execute&#40;&#41;;
            			SQLWarning warning = stmt.getWarnings&#40;&#41;;
            			if &#40;warning != null&#41;
            			&#123;
            			  logger.info&#40;"Returned a warning&#58;"&#41;;
                          if &#40;logger.isDebugEnabled&#40;&#41;&#41; &#123;
                              logger.debug&#40;"test&#40;&#41; - SQL State&#58; "
                                      + warning.getSQLState&#40;&#41;&#41;;
                          &#125;
                          if &#40;logger.isDebugEnabled&#40;&#41;&#41; &#123;
                              logger.debug&#40;"test&#40;&#41; - SQL Error&#58; "
                                      + warning.getErrorCode&#40;&#41;&#41;;
                          &#125;
                          if &#40;logger.isDebugEnabled&#40;&#41;&#41; &#123;
                              logger.debug&#40;"test&#40;&#41; - SQL Message&#58;"
                                      + warning.getMessage&#40;&#41;&#41;;
                          &#125;
            			&#125;
                  logger.info&#40;stmt.getString&#40;2&#41;&#41;;
                  logger.info&#40;stmt.getString&#40;3&#41;&#41;;
                  
              &#125; catch &#40;SQLException e&#41; &#123;
                  logger.error&#40;"test&#40;&#41;", e&#41;;
              &#125; finally &#123;
                  try &#123;
                      stmt.close&#40;&#41;;
                  &#125; catch &#40;SQLException e1&#41; &#123;
                      logger.error&#40;"test&#40;&#41;", e1&#41;;
                  &#125;
                  try &#123;
                      connection.close&#40;&#41;;
                  &#125; catch &#40;SQLException e2&#41; &#123;
                      logger.error&#40;"test&#40;&#41;", e2&#41;;
                  &#125;
                  
              &#125;
              appContext = null;
              
          &#125;
          
          
          public static void main&#40;String&#91;&#93; args&#41; &#123;
              DB2Test1 t = new DB2Test1&#40;&#41;;
              t.test&#40;&#41;;
          &#125;
      &#125;

      Comment


      • #4
        help me please

        I try to use springframework as following :
        Code:
        /*
         * Created on 2004-11-16
         *
         */
        
        
        import java.util.*;
        
        import org.apache.log4j.Logger;
        import org.springframework.context.*;
        import org.springframework.context.support.ClassPathXmlApplicationContext;
        import org.springframework.jdbc.core.SqlOutParameter;
        import org.springframework.jdbc.core.SqlParameter;
        import org.springframework.jdbc.core.SqlReturnType;
        import org.springframework.jdbc.datasource.DriverManagerDataSource;
        import org.springframework.jdbc.object.*;
        
        import javax.sql.*;
        
        import java.io.IOException;
        import java.io.Reader;
        import java.sql.*;
        
        /**
         * @author Daniel
         *
         */
        public class DB2Test &#123;
            /**
             * Logger for this class
             */
            private static final Logger logger = Logger.getLogger&#40;DB2Test.class&#41;;
            
            private class MySP extends StoredProcedure &#123;
                public static final String SQL = "db2info.MD_MESSAGE&#40;?,?,?&#41;";
            		public static final String request = 
            		    "<olap&#58;request xmlns&#58;olap=\"http&#58;//www.ibm.com/olap\" "
            		    + "xmlns&#58;xsi=\"http&#58;//www.w3.org/2001/XMLSchema-instance\" "
            		    + "xmlns&#58;xsd=\"http&#58;//www.w3.org/2001/XMLSchema\" version=\"8.1.2.1.0\">"
            		    + "<describe objectType=\"all\" recurse=\"no\" />"
            		    + "</olap&#58;request>";
            		public String metaData = "";
        
                
                public MySP&#40;DataSource dataSource&#41; &#123;
                    setDataSource&#40;dataSource&#41;;
                    setFunction&#40;true&#41;;
                    setSql&#40;SQL&#41;;
                    declareParameter&#40;new SqlOutParameter&#40;"warn", Types.INTEGER&#41;&#41;;
                    declareParameter&#40;new SqlParameter&#40;"operation", Types.VARCHAR&#41;&#41;;
                    declareParameter&#40;new SqlOutParameter&#40;"metadata", Types.VARCHAR&#41;&#41;;
        //            declareParameter&#40;new SqlOutParameter&#40;"metadata", Types.CLOB, null, 
        //                    new SqlReturnType&#40;&#41; 
        //                    &#123; 
        //
        //                        public Object getTypeValue&#40;CallableStatement cs, 
        //                                int paramIndex, int sqlType, String typeName&#41; 
        //                                throws SQLException 
        //                        &#123; 
        //                            try 
        //                            &#123; 
        //                                final Clob aClob = cs.getClob&#40;paramIndex&#41;; 
        //                                final Reader clobReader = aClob.getCharacterStream&#40;&#41;; 
        //                                int length = &#40;int&#41; aClob.length&#40;&#41;; 
        //                                char&#91;&#93; inputBuffer = new char&#91;1024&#93;; 
        //                                final StringBuffer outputBuffer = new StringBuffer&#40;&#41;; 
        //                                while &#40;&#40;length = clobReader.read&#40;inputBuffer&#41;&#41; != -1&#41; 
        //                                &#123; 
        //                                    outputBuffer.append&#40;inputBuffer, 0, length&#41;; 
        //                                &#125; 
        //                                return outputBuffer.toString&#40;&#41;; 
        //                            &#125; catch &#40;IOException e&#41; 
        //                            &#123; 
        ////                                throw new SQLException&#40;e.toString&#40;&#41;&#41;; 
        //                            &#125; 
        //                            return "";
        //                        &#125; 
        //                    &#125;&#41;&#41;; 
        //            declareParameter&#40;new SqlOutParameter&#40;"response", Types.CLOB&#41;&#41;;
                    declareParameter&#40;new SqlOutParameter&#40;"response", Types.CLOB, null, 
                            new SqlReturnType&#40;&#41; 
                            &#123; 
        
                                public Object getTypeValue&#40;CallableStatement cs, 
                                        int paramIndex, int sqlType, String typeName&#41; 
                                        throws SQLException 
                                &#123; 
                                    try 
                                    &#123; 
                                        final Clob aClob = cs.getClob&#40;paramIndex&#41;; 
                                        final Reader clobReader = aClob.getCharacterStream&#40;&#41;; 
                                        int length = &#40;int&#41; aClob.length&#40;&#41;; 
                                        char&#91;&#93; inputBuffer = new char&#91;1024&#93;; 
                                        final StringBuffer outputBuffer = new StringBuffer&#40;&#41;; 
                                        while &#40;&#40;length = clobReader.read&#40;inputBuffer&#41;&#41; != -1&#41; 
                                        &#123; 
                                            outputBuffer.append&#40;inputBuffer, 0, length&#41;; 
                                        &#125; 
                                        return outputBuffer.toString&#40;&#41;; 
                                    &#125; catch &#40;IOException e&#41; 
                                    &#123; 
                                        throw new SQLException&#40;e.toString&#40;&#41;&#41;; 
                                    &#125; 
                                &#125; 
                            &#125;&#41;&#41;; 
                    compile&#40;&#41;;
                &#125;
                
                public Map execute&#40;&#41; &#123;
                    Map inParam = new HashMap&#40;&#41;;
                    inParam.put&#40;"operation", request&#41;;
                    inParam.put&#40;"metadata", ""&#41;;
                    Map out = execute&#40;inParam&#41;;
                    logger.info&#40;metaData&#41;;
                    return out;
                &#125;
            &#125;
        
            void test&#40;&#41; &#123;
                ApplicationContext appContext = new ClassPathXmlApplicationContext&#40;"applicationContext.xml"&#41;;
                DataSource dataSource = &#40;DriverManagerDataSource&#41;appContext.getBean&#40;"dataSource"&#41;;
                MySP sp = new MySP&#40;dataSource&#41;;
                Map res = sp.execute&#40;&#41;;
                Integer s = &#40;Integer&#41; res.get&#40;"warn"&#41;;
                logger.info&#40;s&#41;;
                String  warn = &#40;String&#41;res.get&#40;"response"&#41;;
                logger.info&#40;warn&#41;;
        //        Clob  metaData = &#40;Clob&#41;res.get&#40;"metadata"&#41;;
        //        logger.info&#40;metaData&#41;;
                appContext = null;
                
            &#125;
            
            
            public static void main&#40;String&#91;&#93; args&#41; &#123;
                DB2Test t = new DB2Test&#40;&#41;;
                t.test&#40;&#41;;
            &#125;
        &#125;
        but the error occures:

        2004-11-17 15:40:05,878 INFO [DB2Test$MySP] - <Compiled stored procedure. Call string is [{? = call db2info.MD_MESSAGE(?,?,?)(?, ?, ?)}]>
        2004-11-17 15:40:05,878 INFO [DB2Test$MySP] - <RdbmsOperation with SQL [db2info.MD_MESSAGE(?,?,?)] compiled>
        2004-11-17 15:40:05,958 WARN [org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator] - <Unable to translate SQLException with errorCode '-104', will now try the fallback translator>
        2004-11-17 15:40:05,968 INFO [org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator] - <Translating SQLException with SQLState '42601' and errorCode '-104' and message [DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );NFO.MD_MESSAGE(?,?,?;,]; SQL was [{? = call db2info.MD_MESSAGE(?,?,?)(?, ?, ?)}] for task [executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call db2info.MD_MESSAGE(?,?,?)(?, ?, ?)}]: params=[{metadata=, operation=<olap:request xmlns:olap="http://www.ibm.com/olap" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="8.1.2.1.0"><describe objectType="all" recurse="no" /></olap:request>}]]]>
        Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [{? = call db2info.MD_MESSAGE(?,?,?)(?, ?, ?)}] in task 'executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call db2info.MD_MESSAGE(?,?,?)(?, ?, ?)}]: params=[{metadata=, operation=<olap:request xmlns:olap="http://www.ibm.com/olap" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="8.1.2.1.0"><describe objectType="all" recurse="no" /></olap:request>}]]'; nested exception is com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );NFO.MD_MESSAGE(?,?,?;,
        com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );NFO.MD_MESSAGE(?,?,?;,
        at com.ibm.db2.jcc.b.zc.e(zc.java:1606)
        at com.ibm.db2.jcc.b.zc.a(zc.java:1206)
        at com.ibm.db2.jcc.a.db.h(db.java:149)
        at com.ibm.db2.jcc.a.db.d(db.java:71)
        at com.ibm.db2.jcc.a.r.d(r.java:54)
        at com.ibm.db2.jcc.a.sb.j(sb.java:199)
        at com.ibm.db2.jcc.b.zc.o(zc.java:1203)
        at com.ibm.db2.jcc.b.ad.fb(ad.java:1776)
        at com.ibm.db2.jcc.b.ad.d(ad.java:2201)
        at com.ibm.db2.jcc.b.bd.sb(bd.java:159)
        at com.ibm.db2.jcc.b.bd.execute(bd.java:142)
        at org.springframework.jdbc.core.JdbcTemplate$5.doInC allableStatement(JdbcTemplate.java:661)
        at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:636)
        at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:659)
        at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:100)
        at DB2Test$MySP.execute(DB2Test.java:113)
        at DB2Test.test(DB2Test.java:123)
        at DB2Test.main(DB2Test.java:137)

        Comment


        • #5
          does anybody has idea about it

          How to apply springframwork to do the same work as JDBC?

          Comment

          Working...
          X