Calling a postgres stored procedure from spring boot using jdbc template

Question

I am migrating from Oracle DB to Postgres. I was using stored procedures in oracle and accessing them in my spring boot code using jdbc template. I am getting an error when running my code using postgres stored procedures. Here is a snippet of my code:

@Repository
 public class DatabaseImpl implements RegistrationDao {
            @Autowired
            DataSource dataSource;
            private JdbcTemplate jdbcTemplate;
            private SimpleJdbcCall spStoreTempData;

            @PostConstruct
            private void postConstruct() throws SQLException {
                jdbcTemplate = new JdbcTemplate(dataSource);
                spStoreTempData = new SimpleJdbcCall(jdbcTemplate).withProcedureName("stmt_temp_store").withSchemaName("my_schema");
            }

            @Override
            public Map<String, Object> storeTempData(String v_user_type, String v_refid, String v_phone, String v_scode, String v_id_number, String v_email,
                                                     String v_tnc, String v_channel, String v_temp_uuid, String v_totp, String v_totp_expperiod, String v_svc_type, String v_reason, String v_max_retries) {

                SqlParameterSource in = new MapSqlParameterSource()
                        .addValue("v_user_type", v_user_type)
                        .addValue("v_refid", v_refid)
                        .addValue("v_phone", v_phone)
                        .addValue("v_scode", v_scode)
                        .addValue("v_id_number", v_id_number)
                        .addValue("v_email", v_email)
                        .addValue("v_tnc", v_tnc)
                        .addValue("v_channel", v_channel)
                        .addValue("v_temp_uuid", v_temp_uuid)
                        .addValue("v_totp", v_totp)
                        .addValue("v_totp_expperiod", v_totp_expperiod)
                        .addValue("v_svc_type", v_svc_type)
                        .addValue("v_reason", v_reason)
                        .addValue("v_max_retries", v_max_retries)
                        .addValue("v_code", "")
                        .addValue("v_msg", "");

                Map<String, Object> result = null;
                try {
                    result = spStoreTempData.execute(in);
                } catch (Exception e) {
                    System.out.println(e.getLocalizedMessage());
                }
                return result;
            }

}

When this code executes, I get the following error:

CallableStatementCallback; bad SQL grammar [{? = call my_schema.stmt_temp_store(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is org.postgresql.util.PSQLException: ERROR: my_schema.stmt_temp_store(numeric, character varying, numeric, numeric, character varying, character varying, character varying, character varying, character varying, numeric, character varying, numeric, character varying, numeric, character varying, character varying) is a procedure
    Hint: To call a procedure, use CALL.
    Position: 15

What am I doing wrong? Here is a link of how the code works when am using an oracle db

Possible workaround: write a function that ```CALL```s the stored procedure and returns something. Or just make the procedure a function (they are the same in PostgreSQL but a procedure cannot return a value and must only appear in a ```CALL``` statement and not in a ```SELECT```). — Islingre, Oct 20 '19 at 01:16

score 0 · Answer 1 · answered Nov 10 '19 at 02:24

0

Check if it is a Procedure or Function, if it is a function you should use

spStoreTempData = new SimpleJdbcCall (jdbcTemplate) .withFunctionName (" name_you_function "). WithSchemaName (" my_schema ");

answered Nov 10 '19 at 02:24

Victor Marrerp

157
1
4

Calling a postgres stored procedure from spring boot using jdbc template

1 Answers1