5 Replies Latest reply on Mar 24, 2010 1:15 PM by Steven Hawkins Branched from an earlier discussion.

    Error with in/out parameter

    Andeep Toor Newbie

      I am having a similar problem to the original poster, as I am trying to execute a stored procedure via Teiid. I would not get this to work with version 6.3, so I tried 7.0.0-SNAPSHOT, as suggested in the thread. The stored procedure I am trying to use has two parameters: one in parameter and one in-out parameter. It seems like teiid is not recognizing the second parameter. Any ideas?

       

      Here is the code:

       

      return (String) new JdbcTemplate(new SingleConnectionDataSource(connection, false)).execute("{ call MY_VL.DB.MY_PROC(?,?) }", new CallableStatementCallback() {

                     public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {

                          cs.setString(1, rolelist);

                          cs.registerOutParameter(2, Types.VARCHAR);

                          cs.execute();

                          return cs.getString(2);

                     }

                });

       

      Here is the stack trace

       

       

      --- Cause: java.lang.IllegalArgumentException: Parameter is not found at index 2.:
      java.lang.IllegalArgumentException: Parameter is not found at index 2.
      at com.metamatrix.jdbc.MMCallableStatement.getObject(MMCallableStatement.java:261)
      at com.metamatrix.jdbc.MMCallableStatement.getString(MMCallableStatement.java:286)
      at foo.bar.MyProcDaoJdbcImpl$1.doInCallableStatement(MyProcDaoJdbcImpl.java:48)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:936)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:964)

      ...

        • 1. Re: Error with in/out parameter
          Steven Hawkins Master

          Hi Andeep,

           

          This is a different issue than the other thread.  Is your procedure a source procedure or a virtual procedure?  If it's a virtual, we don't yet support in/out, out, or return parameters in virtual procedures.  The typical workaround is to change the in/out parameter to an in parameter and add the out value to the result set.  I'm not sure if there is an issue currently open on supporting in/out, out, and return parameters in virtual procedures - I'll create one if not.

           

          If it's a source procedure, then can you double check the metadata?  If it was imported via a dyanmic vdb, then you can query System.ProcedureParams or this was imported via the designer you can look at the model.

           

          Steve

          • 2. Re: Error with in/out parameter
            Andeep Toor Newbie

            Steven,

             

            The procedure I am using is a virtual procedure that is mapped to a source procedure. The source procedure, as it turns out, has one in parameter and one out parameter. I had thought that there was an in / out parameter involved here, but it turns out that there is not. The virtual procedure lists the in parameter and on the result set, it has a column for the out parameter.

             

            If I read your post correctly then you seem to be saying that this still will not work as Teiid does not support out parameters for virtual procedures? If this is incorrect and this should work, how should I call this stored procedure via SQL and get the out value? Can you provide an example that should work with a single in parameter and single out parameter in a virtual procedure?

            • 3. Re: Error with in/out parameter
              Steven Hawkins Master

              Hi Andeep,

               

              If you have a source procedure 'proc' that only returns in/out, out, or return parameters - and does not have have a resultset, then you're vitrual procedure should look like:

               

              create virtual procedure

              begin

                   exec proc(MY_PROC.param1)

              end

               

              This will return the out parameter as a single row in a resultset (in designer the resultset should automatically be created for you with a single column).

               

              From your client you would then execute '{ call MY_VL.DB.MY_PROC(?) }' and get the out parameter from the resultset:

               

              ResulSet rs = cs.getResultSet();

              rs.next();

              String result = rs.getString(1);

               

              Hope this helps,

              Steve

              • 4. Re: Error with in/out parameter
                Andeep Toor Newbie

                Steven,

                 

                Thanks, the example you provided cleared up my issue. Two quick followup questions:

                1) Is there documentation that you can point to about calling stored procedures in this way?

                2) When will there be a stable release of the 7.0.0 version of Teiid? We are looking to use this in a project that will be completed within the next month and wanted to know if there will be other major changes / fixes occurring between now and then.

                 

                Thanks again for your help!

                • 5. Re: Error with in/out parameter
                  Steven Hawkins Master

                  Andeep,

                   

                  Glad to hear that it worked.  All the docbook docs are posted at http://www.jboss.org/teiid/docs.html.  In particular you may be interested in the language reference covering the exec command http://docs.jboss.org/teiid/6.2/reference/en-US/html/sql_support.html#exec_command.

                   

                  The next 7.0 milestone will be available shortly, but will contain major changes to the runtime/deployment model - we will run inside JBoss AS.  7.0 final won't probably be until May.

                   

                  Steve