Announcement Announcement Module
Collapse
No announcement yet.
Using optional/default values in a Stored Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using optional/default values in a Stored Procedure

    Hi,

    I am using org.springframework.jdbc.object.StoredProcedure(Sp ring 2.5) class to call a Stored Procedure defined in the DATABASE.

    This Stored Procedure that has been defined in the database has got a couple of in parameters which have a default value ...

    Code:
    Procedure XYZ (
     .......
       retId OUT NUMBER,     
      flag IN NUMBER DEFAULT 0 
    ) .....
    IS
    ....
    retId := flag;
    END;
    Code:
    public class XYZ extends StoredProcedure {
    
               public XYZ(DataSource dataSource) {
    		setDataSource(dataSource);
    		setSql("xyz");
    		......
                   //declareParameter(new SqlParameter("flag", Types.INTEGER));
                  declareParameter(new SqlOutParameter("retId", Types.INTEGER));
    
    	}
    
    	public int callingStoredProcedureXYZ() {
    		Map<String,Object> inParameters = new HashMap<String,Object>();
    		
    ........
    	    inParameters.put("flag", 1);
    	    Map returnParameters = execute(inParameters);
    	    int id = (Integer)returnParameters.get("retId");
    	    return id;
    	}
    ....
    }
    The callingStoredProcedureXYZ method uses the default value of flag as defined in the stored procedure in the Database when I do not pass any flag parameter as part of the inParameters in my java class.

    However in the above case if I do pass the flag parameter as part of inParameters then it makes not use of it and in the procedure flag always takes the default value 0.

    I want the procedure to use the value of flag that I give for flag if I define it in the inParameters else just use the default value. How can I do that?

    If I declare the flag parameter in the constructor of XYZ class (declareParameter(new SqlParameter("flag", Types.INTEGER)) then I get the following exception when I call the procedure ....
    Code:
    org.springframework.dao.InvalidDataAccessApiUsageException: 12 parameters were supplied, but 13 in parameters were declared in class [XYZ]
            at org.springframework.jdbc.object.RdbmsOperation.validateParameterCount(RdbmsOperation.java:436)
            at org.springframework.jdbc.object.RdbmsOperation.validateParameters(RdbmsOperation.java:394)
            at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:112)

    So declaring the parameter is not fixing the problem. Is there a way in which I can do this. i.e use the parameter value only if it is defined as part of the input parameters that are being sent to the Stored Procedure.
    Last edited by apekshad; Jun 18th, 2009, 07:21 AM.

  • #2
    Can the Spring Team please reply ?

    Thanks in advance.

    Comment


    • #3
      Using optional/default values in a Stored Procedure

      I need to be able to do the same thing. I found examples in the Spring doc that supposedly address this issue by using SimpleJdbcCall, but I cannot get it to work.

      Here is my test case.
      Using 10g Enterprise Edition Release 10.2.0.3.0
      Spring 2.5.0

      First I create a package and a pl/sql test to show how it behaves

      Code:
      CREATE OR REPLACE PACKAGE test_package IS
         PROCEDURE test_procedure (param1                       VARCHAR2,
                                   param2_default               VARCHAR2 DEFAULT 'BAR' ,
                                   param3_out               OUT VARCHAR2,
                                   param4_value_of_param2   OUT VARCHAR2);
      END test_package;
      /
      show error
      
      CREATE OR REPLACE PACKAGE BODY test_package IS
         PROCEDURE test_procedure (param1                       VARCHAR2,
                                   param2_default               VARCHAR2 DEFAULT 'BAR' ,
                                   param3_out               OUT VARCHAR2,
                                   param4_value_of_param2   OUT VARCHAR2) IS
         BEGIN
            param3_out := param1;
            param4_value_of_param2 := param2_default;
         END test_procedure;
      END test_package;
      /
      show error
      Now here is a simple test. Note that I am NOT passing the second parameter at all. This allows the procedure to apply the default value, which is passed back out as the last parameter to show that the default was applied

      Code:
      DECLARE
         lv_out   VARCHAR2 (10);
         lv_parm2_val VARCHAR2 (10);
      BEGIN
         test_package.test_procedure (param1=>'Foo',
              param3_out=> lv_out,
              param4_value_of_param2=> lv_parm2_val);
         DBMS_OUTPUT.put_line ('out param value = '||lv_out);
         DBMS_OUTPUT.put_line ('Default param value = '||lv_parm2_val);
      END;
      /
      The output from this is:

      out param value = Foo
      Default param value = BAR

      This is what I expect, the value BAR is applied by the DEFAULT operation.
      If I pass anything for that parameter, including NULL, the DEFAULT is not triggered:

      Code:
      DECLARE
         lv_out   VARCHAR2 (10);
         lv_parm2_val VARCHAR2 (10);
      BEGIN
         test_package.test_procedure (param1=>'Foo',
              param2_default=> null,
              param3_out=> lv_out,
              param4_value_of_param2=> lv_parm2_val);
         DBMS_OUTPUT.put_line ('out param value = '||lv_out);
         DBMS_OUTPUT.put_line ('Default param value = '||lv_parm2_val);
      END;
      /
      out param value = Foo
      Default param value =

      Now, the java equivalent I have looks like this:

      Code:
      import org.apache.commons.dbcp.BasicDataSource;
      import org.springframework.jdbc.core.JdbcTemplate;
      import org.springframework.jdbc.core.SqlOutParameter;
      import org.springframework.jdbc.core.SqlParameter;
      import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
      import org.springframework.jdbc.core.namedparam.SqlParameterSource;
      import org.springframework.jdbc.core.simple.SimpleJdbcCall;
      
      import java.sql.Types;
      import java.util.Map;
      
      public class JdbcTest {
          public static void main(String[] args) {
              
              final String PARAM1 = "param1"; // apparently case does not matter
              final String PARAM2_DEFAULT = "PARAM2_DEFAULT";
              final String PARAM3_OUT = "PARAM3_OUT";
              final String PARAM4_VALUE_OF_PARAM2 = "PARAM4_VALUE_OF_PARAM2";
              oracle.jdbc.driver.OracleLog.setTrace(true); // enable logging
              BasicDataSource ds = new BasicDataSource();
              ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
              ds.setUsername("test");
              ds.setPassword("test");
              ds.setUrl("jdbc:oracle:thin:@//localhost:1521/local");
              JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      
              System.out.println("configuring SimpleJdbcCall call");
              SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
                      .withSchemaName("test_package")
                      .withCatalogName("test")
                      .withProcedureName("test_procedure")
                      .withoutProcedureColumnMetaDataAccess() // I want to control the parameters
                      .useInParameterNames(PARAM1,PARAM3_OUT,PARAM4_VALUE_OF_PARAM2 ) // not sure what affect this has
                      .declareParameters(
                              new SqlParameter( PARAM1, Types.VARCHAR )
                              ,new SqlOutParameter( PARAM2_DEFAULT, Types.VARCHAR ) // this is the one I do not want to pass to the call
                              ,new SqlOutParameter( PARAM3_OUT, Types.VARCHAR ) 
                              ,new SqlOutParameter( PARAM4_VALUE_OF_PARAM2, Types.VARCHAR ) 
                              )
                      ;
              
              SqlParameterSource inputParameters = new MapSqlParameterSource().addValue( PARAM1, "Foobar");
         
              System.out.println("calling procedure");
              Map out = call.execute(inputParameters);
              System.out.println("Done, output value="+out.get( PARAM3_OUT ));
              System.out.println("Done, defaulted value="+out.get( PARAM4_VALUE_OF_PARAM2));
              
          }
      }
      When run, we see the same output, since I am passing that second parameter:
      configuring SimpleJdbcCall call
      calling procedure
      Done, output value=Foobar
      Done, defaulted value=null

      Now, everthing I try to make a valid call without passing that second parameter gets the same error. I have variously added and removed the calls to
      .withoutProcedureColumnMetaDataAccess() and .useInParameterNames
      and have removed the declaration of the second parameter to no avail:

      Removing the declaration of PARAM2_DEFAULT returns
      Code:
      Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call TEST.TEST_PACKAGE.TEST_PROCEDURE(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
      PLS-00306: wrong number or types of arguments in call to 'TEST_PROCEDURE'
      Basically, it seems like .withoutProcedureColumnMetatDataAccess is having no affect. Even though it generates a statement with 3 parameters call TEST.TEST_PACKAGE.TEST_PROCEDURE(?, ?, ?), it appears to not be matching up the parameters correctly.

      ANY ideas would be greatly appreciated.

      Comment


      • #4
        There is currently no good way of supporting in parameter defaults. This would require supporting named parameters for callable statements. We have plans to add this, probably for Spring 3.1 - see http://jira.springframework.org/browse/SPR-4406

        Comment


        • #5
          Using optional/default values in a Stored Procedure

          Thanks for the reply. I'm sorry this functionality is not available yet, but I'm glad it's on the list.

          In the mean time, could you help me clarify the following that I found at

          Spring Framework, Chapter 11. Data access using JDBC at SpringSource.org

          We can opt to declare one, some or all of the parameters explicitly. The parameter metadata is still being used. By calling the method withoutProcedureColumnMetaDataAccess we can specify that we would like to bypass any processing of the metadata lookups for potential parameters and only use the declared ones. Another situation that can arise is that one or more in parameters have default values and we would like to leave them out of the call. To do that we will just call the useInParameterNames to specify the list of in parameter names to include.
          This last sentence is the one that I believe addresses my issue.
          It appears that the parameters given to .useInParameterNames() do in fact determine the number of bind variables generated, but the number of actual values required to be bound remains whatever the db metadata calls for, even if you specify .withoutProcedureColumnMetaDataAccess()

          I guess what I'm wondering is: is this documentation incorrect, is it just that it's partially implemented, or am I not understanding this correctly?

          thanks again

          Ed

          Comment


          • #6
            Good question - I'm fairly certain that this is inaccurate and it would only be useful in combination with a function/procedure that has multiple signatures. I can't see how it could work with default values specified on some parameter, unless the defaulted parameter is at the end of the parameter list. In that case it might work. I'll do some additional testing and will revise that paragraph for the 3.0 RC1 release.

            I have created a JIRA issue to track this.
            Last edited by trisberg; Jul 15th, 2009, 09:33 AM. Reason: Added JIRA link

            Comment


            • #7
              Thanks for your reply, I look forward seeing this addressed.

              I would like to also say that if it is possible to actually make this work, it would be a huge deal. I don't think I'm the only one working with legacy databases where we cannot control the parameter modes, parameter order, or the database side defaulting of parameter values.

              If we are to minimise the the impact of database side changes we have to be able ignore parameters that we don't want. Our database group has a policy of always adding parameters to the end, rather than changing the order, so as to not break any calling programs. Well, that works for PL/Sql callers, but Java calls must *always* be modified to deal with new parameters, even if they have defaults.

              thanks!
              ed

              Comment


              • #8
                As a workaround you have to fall back on the Oracle specific named parameter notation and also decide upfront what parameters you would like to default and which ones you would like to provide. In this case I recommend using the StoredProcedure class where you can specify the JDBC call string explicitly by setting "sqlReadyForUse" to true.

                Here is an example of a proc that has 3 in parameters and one out parameter where I let the param2 use the default.

                Code:
                CREATE OR REPLACE PROCEDURE TEST_DEFAULT ( 
                    param1 IN VARCHAR2, 
                    param2 IN VARCHAR2 DEFAULT 'DEFAULT', 
                    param3 IN VARCHAR2, 
                    param4 OUT VARCHAR2) AS
                BEGIN
                  param4 := param1 || ' ' || param2 || ' ' || param3;
                END TEST_DEFAULT;
                Code:
                    public class ProcWithDefaults extends StoredProcedure {
                
                        private static final String SQL = "{call test_default (param1 => ?, param3 => ?, param4 => ?)}";
                
                        public ProcWithDefaults(DataSource dataSource) {
                
                            super(dataSource, SQL);
                            setSqlReadyForUse(true);
                            declareParameter(new SqlParameter("param1", Types.VARCHAR));
                            declareParameter(new SqlParameter("param3", Types.VARCHAR));
                            declareParameter(new SqlOutParameter("param4", Types.VARCHAR));
                        }
                        
                    }

                Comment


                • #9
                  Hi
                  I did a workaround using JdbcTemplate call method

                  Code:
                  public Map call(CallableStatementCreator csc,   List declaredParameters) throws DataAccessException
                  which basically does the same thing, but StoredProcedure is simpler, I'll try that.
                  thanks

                  Comment

                  Working...
                  X