CODEWITHSUNDEEP
stored-proceduresjdbcspring-jdbc
Guillaume
Guillaume

Reputation: 5557

how to pass a function as an argument value in jdbc preparedStatement, CallableStatement or Spring-jdbc StoredProcedure?

I'm using Spring Jdbc to perform some stored procedures calls using the StoredProcedure class.

Some of the procedures are taking a date as an input parameter.
(I can't change the procedures: they are outside my project)

In my business logic, I have some rules about date:
in some case, for the same proc, I can have to provide an arbitrary date OR the current system date of the database (the ORACLE sysdate keyword)

How should I provide this kind of parameter when calling the proc ?
If I pass "sysdate", spring and jdbc resolve that as a VARCHAR type and the call fails.

I don't want to call the db for retrieving the sysdate first as I have seen in some documentation.

More generally, how should I do that when using all flavor of JDBC using '?' as a placeholder for sql query arguments ? I've spend some time trying to find a way to doing this, but I did not manage to find an answer.

Test DDL: 

CREATE TABLE "AAATEST" ("A_TEXT" VARCHAR2(20 BYTE), "A_DATE" DATE, "A_NUMBER" NUMBER) ;

create or replace PROCEDURE TEST_PROC ( someText in VARCHAR2, someNumber in NUMERIC, someDate in date) IS
BEGIN
    insert into AAATEST(a_text, a_number, a_date) values (someText, someNumber, someDate);
END;

Test class in Java:

public class Test {
  public static class MyStoredProc extends StoredProcedure {
    protected MyStoredProc(DataSource ds) {
      super(ds, "test_proc");
      declareParameter(new SqlParameter("someText", Types.VARCHAR));
      declareParameter(new SqlParameter("someNumber", Types.NUMERIC));
      declareParameter(new SqlParameter("someDate", Types.DATE));
    }
  }

  public static void main(String[] args) {
    DataSource ds = new SingleConnectionDataSource("driver", "url", "username","password",false);
    MyStoredProc myStoredProc = new MyStoredProc(ds);

    //this one is ok
    myStoredProc.execute(new HashMap(){{
      put("someText", "hello world");
      put("someNumber", 3.14);
      put("someDate", new Date());
    }});

    //this one fails with 'Exception in thread "main" java.lang.IllegalArgumentException at java.sql.Date.valueOf(Date.java:140)'
    myStoredProc.execute(new HashMap(){{
      put("someText", "abc");
      put("someNumber", 123);
      put("someDate", "sysdate");
    }});            
  }
}

Upvotes: 2

Views: 2214

Answers (1)

skaffman
skaffman

Reputation: 403461

I don't think StoredProcedure (or the RdbmsOperation hierarchy in general) are going to help you here. Their logic around the generation of the call string is not written in such a way as to allow extension and customisation.

My suggestion would be to abandon the StoredProcedure and use JdbcTemplate directly. It's a bit more of a faff, but you get full control over the call string. Then, when you need to call your procedure with sysdate, you can just: add it to the call string, e.g.

{call TEST_PROC(?, ?, sysdate)}

You'd need to dynamically decide whther to use the call string with the sysdate, or one without, and decide which parameters to add accordingly.

Upvotes: 2

Related Questions