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

  • Help executing callable query

    Hello,

    Can anybody help me execute following callable query:

    Code:
    BEGIN
    	? := case MY_FUNC(?, ?) when true then '1' else '0' end;
    END;
    I'm using oracle database, and MY_FUNC function is returning boolean, so this is my attempt to workaround oracle jdbc driver's inability to work with booleans (creating wrapper function is currently not an option). Query executes ok when run in my sql console...

    I tried the following but it fails with message: java.sql.SQLException: Invalid SQL type.
    Code:
    List params = new ArrayList();
    params.add(new SqlOutParameter("out", Types.VARCHAR));
    
    Map results = getJdbcTemplate().call(new CallableStatementCreator() {
    	public CallableStatement createCallableStatement(Connection connection) throws SQLException {
    		CallableStatement cs = connection.prepareCall("? := case MY_FUNC(?, ?) when true then '1' else '0' end");
    		cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    		cs.setString(2, "param1");
    		cs.setString(3, "param2");
    		return cs;
    	}
    }, params);
    I expect that I should be able to execute this callable query using jdbcTemplate, but I don't have much knowledge on how to do this. Please help.

    Regards,
    Igor.

  • #2
    I think you are close to a solution. Try:

    Code:
    CallableStatement cs = connection.prepareCall("begin ? := case MY_FUNC(?, ?) when true then '1' else '0' end; end;");

    Comment


    • #3
      Or, you could use this syntax:
      Code:
      "{ ? = call case MY_FUNC(?, ?) when true then '1' else '0' end }"
      which I think is a bit "prettier"
      Last edited by trisberg; Jun 26th, 2007, 11:37 AM.

      Comment


      • #4
        Oh, thank you very much, that worked like a charm My head hurts a little less now.

        Regards,
        Igor.

        Comment

        Working...
        X