0

I have created an alias for the package name : 

CREATE ALIAS IF NOT EXISTS PKG_DATA_INGESTION.F_GET_CONFIGURATION_COMPONENTS FOR
"com.db.aminet.cucumbertests.Orchestrator.H2databaseProc.selectComponentsConfig";

The java class for the given package : 

public class H2databaseProc {

    public static ResultSet selectComponentsConfig(final String componentConfig, final Date executionDate) throws SQLException {
        String h2Pwd=EncrypterFactory.getEncrypter().decrypt("ENC(bZO5WqcDhckxAFZhcZD5+6g3vbMMOZuwTHmtNKuIhBe4+oFdaDwintZvbNqDQYGj)");
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", h2Pwd);
        PreparedStatement controlComponent = conn.prepareStatement("select PROPERTY_KEY, PROPERTY_VALUE from component_config where COMPONENT_ID = ? and INIT_DATE <= TO_DATE(?, 'YYYY-MM-DD') and (END_DATE is NULL OR END_DATE >= TO_DATE(?, 'YYYY-MM-DD')) and IS_LIST = 0; ");
        controlComponent.setString(1, componentConfig);
        controlComponent.setDate(2, executionDate);
        ResultSet rs = controlComponent.executeQuery();
        return rs;
    }
}

calling the package in java :

conn = DriverManager.getConnection(this.jdbcUrlDB, this.usernameDB, this.passwordDB);
            cstmt = conn.prepareCall("{? = call PKG_DATA_INGESTION.F_GET_CONFIGURATION_COMPONENTS(?, ?)}");
            cstmt.registerOutParameter(1, -10);
            cstmt.setString(2, this.componentID);
            cstmt.setDate(3, new java.sql.Date(this.date.getTime()));
            cstmt.execute();

Error I am getting : 

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " ? = CALL PKG_DATA_INGESTION.F_GET_CONFIGURATION_COMPONENTS([*]?, ?)  "; SQL statement:
 ? = call PKG_DATA_INGESTION.F_GET_CONFIGURATION_COMPONENTS(?, ?)  [42000-176]

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
Jochen Reinhardt
  • 833
  • 5
  • 14
Vikas
  • 107
  • 1
  • 10
  • Are you sure you are invoking the stored procedure correctly? I am not an expert for H2, but should the call just be `call `? Have you seen this question? https://stackoverflow.com/questions/11718865/stored-procedure-in-h2-database Seems the connection can be passed into the store procedure method. – Jochen Reinhardt Nov 21 '19 at 07:40
  • Here's another complete, complex example: https://medium.com/@callkalpa/user-defined-functions-and-stored-procedures-with-h2-database-a1cbfa510559 – Jochen Reinhardt Nov 21 '19 at 07:43

0 Answers0