0

We are trying to migrate Oracle to Postgres and in the process I have changed database connection parameter to Postgres.

But I am trying to run the same code then I am getting below exception:

Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: CallableStatementCallback; SQL [{? = call environment_management.get_connection_details_1()}]; No value specified for parameter 1.; nested exception is org.postgresql.util.PSQLException: No value specified for parameter 1.

    static final String GET_APPLICATION_CONNECTIONS_FUNCTION = "get_connection_details";
    static final String APPLICATION_CONNECTIONS_RESULT_SET = "lv_conn_record";

    this.getApplicationConnectionsFunction = createSimpleJdbcCallForFunction(ENVIRONMENT_MANAGEMENT_CATALOG, GET_APPLICATION_CONNECTIONS_FUNCTION);

    this.getApplicationConnectionsFunction.returningResultSet(APPLICATION_CONNECTIONS_RESULT_SET,
            new ConnectionRowMapper());




    Map<String, Object> applicationConnectionsResultMap =  
    getApplicationConnectionsFunction.execute();

If I am providing wrong function name too then also it is throwing same error. It means, it is not reaching Postgres database at all and throwing error before that . In postgres function, we don't have input parameter but having refcursor as output.

sachin
  • 159
  • 1
  • 11
  • I can find the similar issue stated in https://www.postgresql.org/message-id/20060225030913.23189.qmail@web31813.mail.mud.yahoo.com .. it seems, it is postgres bug .. not sure what could be fix in when using simplejdbccall with postgresql-42.2.8.jar driver – sachin Nov 25 '19 at 11:44

1 Answers1

0

I used following and it worked

   this.jdbcCall = new SimpleJdbcCall(dataSource).withSchemaName("environment_management").withFunctionName("get_connection_details").withoutProcedureColumnMetaDataAccess()
            .declareParameters(
                    new SqlOutParameter("lv_conn_record",  Types.OTHER),
                    new SqlParameter("p_application_name", Types.VARCHAR));


    SqlParameterSource in = new MapSqlParameterSource().addValue("lv_conn_record", Types.REF_CURSOR)
         .addValue("p_application_name", applicationName);

    Map<String, Object> applicationConnectionsResultMap = jdbcCall.execute(in);
sachin
  • 159
  • 1
  • 11
  • Why don't you simply run a `select * from environment_management.get_connection_details(?)` ? and then use `executeQuery()`? –  Dec 02 '19 at 15:20