Announcement Announcement Module
Collapse
No announcement yet.
Oracle/SQL Server stored procedure genericity Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle/SQL Server stored procedure genericity

    Hello all,

    I would like to call stored procedures in a generic fashion. The procedures have identical signatures except for the additional oracle OUT parameter of type REF CURSOR.

    Before I begin my real question, does spring already facilitate this sort of behavior?

    In the case that it does not, I would like to extend the StoredProcedure class to utilize an object to set the parameters as necessary. For instance, an OracleParameterSetter (implementing a ParameterSetter interface) would set the explicit parameters as usual, and automatically register an additional OUT parameter.

    The result would hopefully work similar to the following pseudocode:


    Code:
    public class MyFunctionCallDao extends StoredProcedureX {
    
        // MyFunction takes one  parameter called myId, but 
        // requires an OUT parameter for the oracle resultset cursor
        private static final String PROC_CALL = "MyFunction";
    
        public GetUdsExtensionsDao(DataSource dataSource, ParameterSetter ps) {
            super(dataSource, CALL_SQL);
            ps.doWork(); // calls declareParameter as necessary
            compile();
        }
        ...
    }
    Then, when I configure my application for a particular environment, I can set the parameterSetter property of the StoredProcedure subclass to any class that implements the ParameterSetter interface.

    Code:
        <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName"><value>java&#58;comp/env/jdbc/its</value></property>
        </bean>
    
        <bean id="oracleParameterSetter" class="OracleParameterSetter">
        </bean>
    
        <bean id="myFunctionCallDao" class="net.xyz.dao.MyFunctionCallDao">
            <constructor-arg><ref local="dataSource"/></constructor-arg>
            <constructor-arg><ref local="oracleParameterSetter"/></constructor-arg>
        </bean>
    Of course, it may do more than set parameters. Perhaps it sets package names, etc. I certainly don't have all the details worked out. I'd appreciate comments, suggestions, or a good beating for being ignorant Specifically: is it useful? practical? too niche or too generic?

    Regards...

  • #2
    There should be no reason why a compatible stored procedure from Oracle and SQL Server won't work with the same StoredProcedure class. Ive never tried it, but I can't see no reasons it couldn't work.

    Rob

    Comment


    • #3
      They actually do pass back the resultset using different semantics, so there would be an additional SqlOutParameter to declare in the Oracle case versus an SqlReturnResultSet in the MS SQL Server case. Once you use/compile the StoredProcedure class you can't add parameters though. I would use some kind of switch in the constructor to deal with the additional parameter or just use the JdbcTemplate directly.

      Comment


      • #4
        Originally posted by robh
        There should be no reason why a compatible stored procedure from Oracle and SQL Server won't work with the same StoredProcedure class. Ive never tried it, but I can't see no reasons it couldn't work.

        Rob
        Thanks for the speedy reply, Rob.

        Indeed, StoredProcedure works in either case. However, what I am trying to do is avoid writing different subclasses for different calling conventions. In my situation, I have a procedure that returns a resultset. The oracle procedure returns it through an explicit OUT parameter, while the SQL Server procedure returns it implicitly. Therefore, the signatures differ, e.g.

        Oracle: SprocX(id IN NUMBER, rs OUT refcur_t)
        SQL Server: SprocX(@id int)

        Both do exactly the same thing, but they return the resultset differently.

        Comment


        • #5
          Originally posted by trisberg
          They actually do pass back the resultset using different semantics, so there would be an additional SqlOutParameter to declare in the Oracle case versus an SqlReturnResultSet in the MS SQL Server case. Once you use/compile the StoredProcedure class you can't add parameters though. I would use some kind of switch in the constructor to deal with the additional parameter or just use the JdbcTemplate directly.
          That's the short version of my question. However, in the OO spirit of things, is it unreasonable to try push this concern into a separate object? For example, when I want to support a new database that that does things slightly differently, I code new implementation of the ParameterSetter. Then, for x stored procedures on y databases, I write x StoredProcedure subclasses plus y ParameterSetters, rather than xy StoredProcedure subclasses.

          Supposing I use a constructor switch, I then have to modify the constructor for the x StoredProcedure subclasses, or create another layer between StoredProcedure and my StoredProcedure subclasses.

          Comment


          • #6
            Couldn't you have an abstract base class that defines the execute() signature you want. Then have a separate OracleImpl and a JdbcReturnResultSetImpl that you could switch between depending on the database. You might need a specific implementation for each database that doesn't support the standard JDBC syntax. I don't see how the ParameterSetter would give you any benefit.

            Comment


            • #7
              Unless you have a lot of these types of procedures. Then you could device some custom scheme - I wouldn't call it ParameterSetter though - that did confuse me since it is used to declare the parameters - I would call it SqlReturnResultSetParameterDeclarer or something similar .

              Comment


              • #8
                Yeah, the name could use some work... But I have many of these procedures, as I am trying to clean up a legacy system. [It's got a ton of stuff like: String myCall = "{call " + procedureName + "(" + someParam + ... + ")}", very ugly.] It needs an overhaul, but the business logic is implemented in the stored procedures, none of which may be changed. Now the client is afraid of the effort in converting all the string concatenation, which led me down the road of minimizing class hierarchy.... that's my rant... you have my aplogies.

                Comment

                Working...
                X