Announcement Announcement Module
Collapse
No announcement yet.
SqlFunction Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SqlFunction

    I'm having trouble invoking an SQL function in Oracle using the Spring
    SqlFunction wrapper. Here is a snippet from the JDBC invocation.
    Code:
      call = conn.prepareCall("{? = call pkg_product.f_id_from_upc(?)}");
      call.registerOutParameter(1, OracleTypes.NUMERIC);
      call.setString(2, upcNo);
      call.execute();
      uniqueProductId = (long) call.getLong(1);			
      call.execute();
    I've tried various combinations the SQL string and parameter declarations
    to no avail. I was attempting to subclass SqlFunction with a constructor like this.
    Code:
    public MySqlFunction(DataSource ds) {
       super(ds, 
                "call pkg_product.f_id_from_upc(?)",
                new int[] {Types.VARCHAR}
                );
       compile();
    }
    But my invocation
    Code:
    MySqlFunction sf = new MySqlFunction(ds);
    Object o = sf.runGeneric(new String[] {"xyz"});
    I get the error
    Code:
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [call pkg_product.f_id_from_upc(?)]; nested exception is java.sql.SQLException: ORA-06576: not a valid function or procedure name
    But I'm pretty sure the function name is correct. If I add the leading question mark to the SQL statement:
    Code:
    ? = call pkg_product.f_id_from_upc(?)
    it will complain that I have not passed in enough parameters. But the first parameter would be an OUT parameter. I'm not sure how to do that with SqlFunction.

  • #2
    SqlFunction is a wrapper class for a query that returns a single value. It is not intended to wrap a CallableStatement. Use the Stored Procedure class for that.

    See http://static.springframework.org/sp...api/index.html for more info.

    Comment


    • #3
      Indeed, StoredProcedure seems to do the trick. The principal thing I missed was the
      Code:
      setFunction(true);
      This, apparently, is what tells StoredProcedure to place the "? = call " before the name of the function when building the SQL string.

      Comment

      Working...
      X