Announcement Announcement Module
Collapse
No announcement yet.
Unable to call stored procedures which declare nothing but INOUT parameters Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Unable to call stored procedures which declare nothing but INOUT parameters

    Hi,

    At work we have several SQL Server 2005 stored procedures that declare nothing but output parameters.

    Example:

    Code:
    ALTER PROCEDURE [foo].[bar]
    (
        @foo type OUTPUT
        @bar type OUTPUT
        ... etc
    )
    
    procedure body
    I'm using Velocity and Spring 2.0.5 to generate DataAccessObjects (DAO) using the meta data returned from the database.

    For procedures as defined above all the parameters are being returned as type INOUT so they get declared in my DAO as following:

    Code:
    declareParameter(new org.springframework.jdbc.core.SqlInOutParameter("foo", type));
    declareParameter(new org.springframework.jdbc.core.SqlInOutParameter("bar", type));
    ... etc
    
    compile();
    When I try and execute this DAO I get the following Exception:

    Code:
    org.springframework.dao.InvalidDataAccessApiUsageException: Didn't expect any parameters: none were declared
    As the parameters where defined as input and output this seems like it might be a bug? Is there any work around for this or am I just using the API incorrectly?

    Thanks in advance,
    Hal


    Edit:

    The problem appears to be line 368 of the method validateParameters in the class RdbmsOperation, if (!(param instanceof ResultSetSupportingSqlParameter)), because SqlInOutParameter extends SqlOutParameter which in turn extends ResultSetSupportingSqlParameter.
    Last edited by Gentleman Hal; Oct 8th, 2008, 05:33 AM.

  • #2
    Your procedure only supports output params not input params, so why doyou declare them in/out params? Should they only be out params?

    Also how do you call your procedure?

    Comment


    • #3
      Hi,

      I've been assured by the Database guys that despite the fact they are declared as OUTPUT in the procedure they are in fact input and output parameters.

      This is backed up by the fact the database meta data method getProcedureColumns() returns java.sql.DatabaseMetaData.procedureColumnInOut as the column type.

      At the moment I'm calling the DAO from a Unit test that extends the Spring AbstractTransactionalDataSourceSpringContextTests class.


      Code:
      public class FooBarTest extends org.springframework.test.AbstractTransactionalDataSourceSpringContextTests {
      	/** Test instance */
          private FooBar testInstance;
      
          protected String[] getConfigLocations() {
              return new String[] { "classpath:spring-dao-edm.xml" };
          }
          
          public void onSetUpInTransaction() throws Exception {
              super.onSetUpInTransaction();
              
              testInstance = new FoorBar(jdbcTemplate.getDataSource());
          }
          
          /**
           * Tests the stored procedure foo.bar'.
           */
          public void testStoredProcedure() {
              java.util.Map inParams = new java.util.HashMap();
              
      	inParams.put(FooBar.FOO, value);
              inParams.put(FooBar.BAR, value);
              ... etc
      	        
              try {
                  System.out.println(testInstance.execute(inParams));
                  
              } catch (org.springframework.jdbc.BadSqlGrammarException e) {
                  fail(e.getMessage());
              }
          }
          
      }
      Thanks,
      Hal

      Comment


      • #4
        Can you post your StoredProcedure code and specify which version spring etc. you are using.

        Comment


        • #5
          Unfortunately I don't think I can post the actual code as it is work code, however the [hopefully] most important parts were reproduced in my first post.

          The stored procedure declares all the parameters with the OUTPUT keyword (the first code snippet above) and my generated DAO declares all the parameters in it's constructor (2nd code snippet).

          I'm using Spring 2.0.5 and the stored procedure is on SQL Server 2005.


          Edit:
          Database: Microsoft SQL Server 9.00.3042
          Driver: Microsoft SQL Server 2005 JDBC Driver 1.2.2828.100


          Thanks,
          Hal


          DAO example:
          Code:
          package foo.bar;
          
          public class FooBar extends StoredProcedure {
              public static final String STORED_PROCEDURE_NAME = "foo.bar";
          	
              public static final String FOO = "foo";
              public static final String BAR = "bar";
          
              public FooBar(javax.sql.DataSource ds) {
                  super(ds, STORED_PROCEDURE_NAME);
          		
          	setFunction(false);
          		
          	declareParameter(new org.springframework.jdbc.core.SqlInOutParameter(FOO, java.sql.Types.TYPE);
                  declareParameter(new org.springframework.jdbc.core.SqlInOutParameter(BAR, java.sql.Types.TYPE));
          		
                  compile();
              }
          
          }

          Comment


          • #6
            Still looking for a resolution to this problem, I'm also more sure than ever it's actually a bug in Spring.

            How do I go about reporting bugs?

            Comment

            Working...
            X