Unable to locate the corresponding parameter value for 'in_id' within the parameter values provided

Question

I am trying to implement following tutorial https://www.tutorialspoint.com/spring/calling_stored_procedure.htm for simplejdbccall with postgres (need to migrate existing application from Oracle to Postgres which using simplejdbccall) but getting following exception

 INFO: Unable to locate the corresponding parameter value for 'in_id' within the parameter values provided: [inID]
 Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'in_id' is missing

I am using following code to pull information:

 public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
 //   this.jdbcCall =  new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
    this.jdbcCall =  new SimpleJdbcCall(dataSource).withCatalogName("public").withFunctionName("getrecord1");
}

public Student getStudent(Integer id) {
    //SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
    System.out.println("----In getStudent-----"+id );
    SqlParameterSource in = new MapSqlParameterSource().addValue("inID", id);
    /* SqlParameterSource in = new MapSqlParameterSource().addValue("id", id, Types.INTEGER)
            .addValue("name",Types.VARCHAR)
            .addValue("age", Types.INTEGER);


    */

    Map<String, Object> out = jdbcCall.execute(in);

    Student student = new Student();
    student.setId(id);
    student.setName((String) out.get("out_name"));
    student.setAge((Integer) out.get("out_age"));
    return student;
}

and I have following function in postgres database:

 CREATE function getrecord1 (in_id INTEGER, OUT out_name character, 
                        OUT out_age Integer)
  as

  $$SELECT name, age from student where id=in_id$$
   LANGUAGE sql VOLATILE
   COST 100;

I have tried Receiving Message Unable to locate the Corresponding Parameter when calling Stored Procedure but using below code as well I am getting issue

   SqlParameterSource in = new MapSqlParameterSource().addValue("id", id, Types.INTEGER)
            .addValue("name",Types.VARCHAR)
            .addValue("age", Types.INTEGER);

spelling `in_id` in the java code exactly as it is written in the function is required — JGH, Nov 27 '19 at 12:21
The column name `in_id` is not the same as the column name `inID` you used in your Java code. — Mark Rotteveel, Nov 27 '19 at 16:18

score 0 · Answer 1 · answered Dec 02 '19 at 13:42

I have followed steps to create function from https://www.mkyong.com/jdbc/jdbc-callablestatement-postgresql-stored-function/ as shown below

public class getConnectionDetails {

    public static void main(String[] args) {

        String createFunction = "CREATE OR REPLACE FUNCTION environment_management.get_connection_details(p_application_name VARCHAR , OUT lv_conn_record refcursor) "
            + " RETURNS  refcursor "
            + " AS $$ "
            + " BEGIN "
            + " OPEN lv_conn_record FOR SELECT CONNECTION_ID, CONNECTION_TYPE, CONNECTION_NAME, DESCRIPTION, MAX_RETRY_ATTEMPT, RETRY_DELAY_IN_SECONDS,ENABLE_DBMS_OUTPUT from  public.CONNECTION_SPECIFICATION "
            + " where APPLICATION_NAME=p_application_name"
            + " AND ACTIVE_FLAG = 'Y';"
            + " END; "
            + " $$ "
            + " LANGUAGE plpgsql";




    try (Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://a302-3354-7677.ldn.swissbank.com:5453/pg_emea_at28400_dev_177", "pgdbo", "X9u9CZmgW3b%6AcYHtfESFBf");

         Statement statement = conn.createStatement();

    ) {



        // create function
        statement.execute(createFunction);




    } catch (SQLException e) {
        System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

}

and call function as shown below:

   public final Collection<ConnectionDto> getApplicationConnections(String applicationName) {

        System.out.println("Fetch Connections from the database for applicationName: " + applicationName);

  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);

    System.out.println("----3333333333333333333 in getApplicationConnections-----" +applicationConnectionsResultMap);

    return createApplicationConnectionsFromResultMap(applicationConnectionsResultMap);
}

My Bean.xml look like:

    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name = "url" value = "jdbc:postgresql://a302-3354-7677.ldn:5453/pg"/>
    <property name = "username" value = "pgdbo"/>
    <property name = "password" value = "abcde"/>

    <property name="initialSize" value="10"/>
    <property name="maxActive" value="5"/>
    <!-- added for postgres -->
    <property name="defaultAutoCommit" value="false" />

</bean>

<bean id = "studentJDBCTemplate1"
      class = "com.tutorialspoint.StudentJDBCTemplate1">
    <property name = "dataSource" ref = "dataSource" />
</bean>

Unable to locate the corresponding parameter value for 'in_id' within the parameter values provided

1 Answers1