Failed to pass a custom type from java as parameter of a postgres procedure

652 Views Asked by At

https://docs.spring.io/spring-data/jdbc/old-docs/current/reference/html/orcl.datatypes.html

DB : Postgres Trying to pass a custom type from java as parameter of a procedure following above url.

Custom Type:

CREATE TYPE schm.TESTTYPE AS
(
    tranProdCode character varying(20),
    particulars character varying(200),
    totalAmount numeric(18,4)
);

Procedure Def:

create or replace procedure schm.post (
   IN request schm.TESTTYPE
)
language plpgsql    
as $$

DECLARE

--xxxxxxxxxxxxxxxxxxxx

begin
-- xxxxxxxxxxxxxxxxxxxx        
end;$$
class SqlPostTransaction extends PostTransaction implements SQLData {

    @Override
    public String getSQLTypeName() throws SQLException {
        return "testtype";
    }

    @Override
    public void readSQL(SQLInput sqlInput, String s) throws SQLException {
        setTranProdCode(sqlInput.readString());
        setParticulars(sqlInput.readString());
        setTotalAmount(new BigDecimal(sqlInput.readString()));
    }

    @Override
    public void writeSQL(SQLOutput sqlOutput) throws SQLException {
        sqlOutput.writeString(getTranProdCode());
        sqlOutput.writeString(getParticulars());
        sqlOutput.writeBigDecimal(getTotalAmount());
    }
}
    @PersistenceContext
    private EntityManager entityManager;

    public DataSource getDataSourceFromHibernateEntityManager() {
        EntityManagerFactoryInfo info = (EntityManagerFactoryInfo)  entityManager.getEntityManagerFactory();
        return info.getDataSource();
    }

        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getDataSourceFromHibernateEntityManager())
                .withSchemaName("schm").withProcedureName("post")
                .declareParameters(
                        new SqlParameter("request", Types.STRUCT, "testtype"));

/*SqlPostTransaction sp - object*/
            Map in = Collections.singletonMap("request", sp);
            simpleJdbcCall.execute(in);

Error message :

If Types.OTHER is used

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call schm.post(?)}]; nested exception is org.postgresql.util.PSQLException: ERROR: schm.post(unknown) is a procedure Hint: To call a procedure, use CALL. Position: 15

If Types.STRUCT is used

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call schm.post(?)}]; nested exception is org.postgresql.util.PSQLException: Unsupported Types value: 2,002

Also tried but no luck - escapeSyntaxCallMod=callIfNoReturn

https://github.com/pgjdbc/pgjdbc

Other helpful links -

https://www.highgo.ca/2020/11/25/calling-a-stored-procedure-in-postgrsql-from-java-and-its-current-limitations/

0

There are 0 best solutions below