What is the error with following. Can any one tell us the issue with following code with postgresql EDB. We are running this inside the jboss eap 7.0 with the persistence context.
This is the SP and java code used to call that SP.
10:24:01,812 INFO [org.jboss.as.ejb3.timer] (EJB default - 8) WFLYEJB0023: Retrying timeout for timer: [id=51296b84-d2a8-4fdd-a762-bf875b86c5ca timedObjectId=amt_bo_ear-AMT.BO.X.1.11.0.amt_bo_bootstrap.SchedulerSessionBean auto-timer?:true persistent?:false timerService=org.jboss.as.ejb3.timerservice.TimerServiceImpl@5ca061d9 initialExpiration=null intervalDuration(in milli sec)=0 nextExpiration=Wed Jul 22 10:24:41 IST 2020 timerState=IN_TIMEOUT info=Every 120 seconds]
10:24:01,849 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 8) SQL Error: 0, SQLState: 42883
10:24:01,850 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 8) ERROR: function pkg_dc_common.sp_show_range(character varying, character varying, integer, integer, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15
10:24:01,858 INFO [stdout] (EJB default - 8) 159285 [ERROR] MubasherDAO: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
10:24:01,860 INFO [stdout] (EJB default - 8) javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
10:24:01,861 INFO [stdout] (EJB default - 8) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) ~[hibernate-core-5.3.6.Final.jar!/:5.3.6.Final]
10:24:01,864 INFO [stdout] (EJB default - 8) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.6.Final.jar!/:5.3.6.Final]
10:24:01,872 INFO [stdout] (EJB default - 8) at org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:622) ~[hibernate-core-5.3.6.Final.jar!/:5.3.6.Final]
……………………….
…………………………………………..
10:24:02,340 INFO [stdout] (EJB default - 8) Caused by: org.postgresql.util.PSQLException: ERROR: function pkg_dc_common.sp_show_range(character varying, character varying, integer, integer, character varying) does not exist
10:24:02,347 INFO [stdout] (EJB default - 8) Hint: No function matches the given name and argument types. You might need to add explicit type casts.
10:24:02,347 INFO [stdout] (EJB default - 8) Position: 15
10:24:02,349 INFO [stdout] (EJB default - 8) at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) ~[postgresql-42.2.14.jar:42.2.14]
10:24:02,358 INFO [stdout] (EJB default - 8) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) ~[postgresql-42.2.14.jar:42.2.14]
10:24:02,359 INFO [stdout] (EJB default - 8) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) ~[postgresql-42.2.14.jar:42.2.14]
10:24:02,360 INFO [stdout] (EJB default - 8) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.14.jar:42.2.14]
10:24:02,363 INFO [stdout] (EJB default - 8) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.14.jar:42.2.14]
10:24:02,363 INFO [stdout] (EJB default - 8) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) ~[postgresql-42.2.14.jar:42.2.14]
10:24:02,364 INFO [stdout] (EJB default - 8) at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:77) ~[postgresql-42.2.14.jar:42.2.14]
//SP ins EDB after migrate from Oracle
CREATE OR REPLACE PROCEDURE pkg_dc_common.sp_show_range(OUT pview refcursor, ptableorview character varying, psortcolumn character varying, OUT prows integer, pn1 integer, pn2 integer, psearchcriteria character varying)
SECURITY INVOKER
AS $procedure$
s1 VARCHAR2 (4000);
s2 VARCHAR2 (4000);
l_sortcolumn VARCHAR(100);
BEGIN
IF psortcolumn IS NULL
THEN
l_sortcolumn := '1';
ELSE
l_sortcolumn := psortcolumn;
END IF;
IF (psearchcriteria IS NOT NULL)
THEN
s1 := ' WHERE ' || psearchcriteria;
s2 :=
'SELECT count(*) FROM '
|| ptableorview
|| ' WHERE '
|| psearchcriteria;
ELSE
s1 := '';
s2 := 'SELECT count(*) FROM ' || ptableorview;
END IF;
dbms_output.put_line('s1 = '||s1);
dbms_output.put_line('s2 = '||s2);
dbms_output.put_line('l_sortcolumn = '||l_sortcolumn);
dbms_output.put_line('ptableorview = '||ptableorview);
OPEN pview FOR
' SELECT t1.*
FROM (SELECT t3.*, ROW_NUMBER () OVER (ORDER BY '||l_sortcolumn||') rnum
FROM '||ptableorview||' t3
'||s1||') t1
WHERE rnum BETWEEN '||pn1||' AND '||pn2;
DBMS_OUTPUT.put_line (s2);
--IF (pn1 = 1)
--THEN
-- EXECUTE IMMEDIATE s2 INTO prows;
--ELSE
prows := -2;
--END IF;
END$procedure$
LANGUAGE edbspl
Code perfectly work with Oracle
private ListResult<I> executeBaseProcedure(EntityManager em, String viewName, String sortColumn, String filterCriteria, int firstRecordIndex, int lastRecordIndex) {
try {
logger.info("Execute Common SP >> " + em + "|viewName:" + viewName + "|sortColumn:" + sortColumn + "|filterCriteria:" + filterCriteria + "|firstRecordIndex:" + firstRecordIndex + "|lastRecordIndex:" + lastRecordIndex);
StoredProcedureQuery query = em.createStoredProcedureQuery("pkg_dc_common.sp_show_range", beanClass);
if (query == null) {
logger.error("Failed to generate StoredProcedureQuery for Common SP >> pkg_dc_common.sp_show_range");
return new ListResult<>(Collections.emptyList(), -1);
}
logger.info("Define parameters of CommonSP StoredProcedureQuery.");
query.registerStoredProcedureParameter("ptableorview", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("psortcolumn", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("psearchcriteria", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("pn1", Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter("pn2", Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter("prows", Integer.class, ParameterMode.OUT);
query.registerStoredProcedureParameter("pview", beanClass, ParameterMode.REF_CURSOR); //void.class
logger.info("Assign parameter values of CommonSP StoredProcedureQuery.");
query.setParameter("ptableorview", viewName);
query.setParameter("psortcolumn", sortColumn);
query.setParameter("psearchcriteria", filterCriteria);
query.setParameter("pn1", firstRecordIndex);
query.setParameter("pn2", lastRecordIndex);
logger.info("Ready to execute common sp");
query.execute();
logger.info("Executed common sp");
int totalNoOfRows;
Object resultingRowCount = query.getOutputParameterValue("prows");
if (resultingRowCount == null) {
logger.error("prows Out parameter is null");
totalNoOfRows = -1;
} else {
logger.info("Total number of results for query >> " + resultingRowCount);
totalNoOfRows = (int) resultingRowCount;
}
logger.info("Total number of results (integer value) for query >> " + totalNoOfRows);
Object resultView = query.getResultList();
logger.info("Results for query >> " + resultView);
List<I> response = (List<I>) resultView;
logger.info("Results after cast for query >> " + response);
return new ListResult<I>(response, totalNoOfRows);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return new ListResult<>(Collections.emptyList(), -1);
}
}
//Code that try recently with postgressql
private ListResult<I> executeBaseProcedure(EntityManager em, String viewName, String sortColumn, String filterCriteria, int firstRecordIndex, int lastRecordIndex) {
try {
logger.info("Execute Common SP >> " + em + "|viewName:" + viewName + "|sortColumn:" + sortColumn + "|filterCriteria:" + filterCriteria + "|firstRecordIndex:" + firstRecordIndex + "|lastRecordIndex:" + lastRecordIndex);
StoredProcedureQuery query = em.createStoredProcedureQuery("pkg_dc_common.sp_show_range", beanClass);
if (query == null) {
logger.error("Failed to generate StoredProcedureQuery for Common SP >> pkg_dc_common.sp_show_range");
return new ListResult<>(Collections.emptyList(), -1);
}
logger.info("Define parameters of CommonSP StoredProcedureQuery.");
query.registerStoredProcedureParameter(0, beanClass, ParameterMode.REF_CURSOR); //void.class
query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN).setParameter(1,viewName);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN).setParameter(2,sortColumn);
query.registerStoredProcedureParameter(3, Integer.class, ParameterMode.OUT);
query.registerStoredProcedureParameter(4, Integer.class, ParameterMode.IN).setParameter(4,firstRecordIndex);
query.registerStoredProcedureParameter(5, Integer.class, ParameterMode.IN).setParameter(5,lastRecordIndex);
query.registerStoredProcedureParameter(6, String.class, ParameterMode.IN).setParameter(6,filterCriteria);
logger.info("Assign parameter values of CommonSP StoredProcedureQuery.");
/**
query.setParameter(1, viewName);
query.setParameter(2, sortColumn);
query.setParameter(3, firstRecordIndex);
query.setParameter(4, lastRecordIndex);
query.setParameter(5, filterCriteria); **/
logger.info("Ready to execute common sp");
query.execute();
logger.info("Executed common sp");
int totalNoOfRows;
Object resultingRowCount = query.getOutputParameterValue("prows");
if (resultingRowCount == null) {
logger.error("prows Out parameter is null");
totalNoOfRows = -1;
} else {
logger.info("Total number of results for query >> " + resultingRowCount);
totalNoOfRows = (int) resultingRowCount;
}
logger.info("Total number of results (integer value) for query >> " + totalNoOfRows);
Object resultView = query.getResultList();
logger.info("Results for query >> " + resultView);
List<I> response = (List<I>) resultView;
logger.info("Results after cast for query >> " + response);
return new ListResult<I>(response, totalNoOfRows);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return new ListResult<>(Collections.emptyList(), -1);
}
}
select * from your_function(...)in Java