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

  • Accessing PostgreSQL Functions

    The following correctly calls the function demoschema.demo() on a PostgreSQL server:

    Code:
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            SimpleJdbcCall simpleGetCall = new SimpleJdbcCall(jdbcTemplate);
            simpleGetCall.withProcedureName("demo");
            simpleGetCall.withCatalogName("demoschema");
            Map<String, Object> execute = simpleGetCall.execute();
    However I have been unable to call any functions with parameters. I've tried combinations of the following:

    Code:
            Set s = new HashSet<String>();
            s.add("v_int");
            simpleGetCall.setInParameterNames(s);
            MapSqlParameterSource in = new MapSqlParameterSource();
            in.addValue("v_int", 1);
            Map<String, Object> execute = simpleGetCall.execute(in);
    But get the same error about the fucntion demoschema.demo() not existing. What am I doing wrong?

    As a follow on question, can Spring pass array parameters to the Postgre driver?

  • #2
    I've figured out the code required:

    Code:
            simpleGetCall.withoutProcedureColumnMetaDataAccess();
            simpleGetCall.declareParameters(new SqlParameter("v_int", java.sql.Types.ARRAY));
            MapSqlParameterSource in = new MapSqlParameterSource();
            Array createArrayOf = connection.createArrayOf("int4", new Object[]{1, 2, 3, 4, 5});
            in.addValue("v_int", createArrayOf);
            Map<String, Object> execute = simpleGetCall.execute(in);
    Not sure why I have to declare "withoutProcedureColumnMetaDataAccess", but presumably Spring can't handle function names with signatures in them.

    The array stuff above is a seaperate issue, use java.sql.Types.INTEGER etc when declaring parameters of more simpler types.

    Comment

    Working...
    X