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 -
