We are using jdbc to connect to postgresql 9.3 database through pgpool. When we are trying to read the data sent from DB in java, sometimes data is read properly and sometimes we are getting this exception.
org.springframework.jdbc.UncategorizedSQLException:
CallableStatementCallback;
uncategorized SQLException for SQL [{call schema.function_name(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}];
SQL state [34000]; error code [0]; ERROR: cursor "<unnamed portal 1>" does not exist; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
We are using this code in our DAO class :
Map<String , Object> outMap = BaseDAO.executeStoredProcedureSpring(
jdbcTemplate,
"schema.function_name",
ProcParams.paramIN(param_1,Types.NUMERIC),
ProcParams.paramCursor(new Map_HistoryVO()),
ProcParams.paramOUT(Types.NUMERIC),
ProcParams.paramOUT(Types.VARCHAR));
Function executeStoredProcedureSpring
try
{
connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
SimpleJdbcCall procedure = new SimpleJdbcCall(jdbcTemplate);
procedure.withProcedureName(procName);
procedure.withoutProcedureColumnMetaDataAccess();
for(int i = 0;i < paramarr.length; i++)
{
int position = i + 1;
String paramName = "param_" + position;
ProcParams procparam = paramarr[i];
SqlParameter sqlParameter = null;
if(procparam.getParamType().equalsIgnoreCase("IN"))
{
sqlParameter = new SqlParameter(paramName , procparam.getType());
inParams.put(paramName, procparam.getValue());
}
parameters.add(sqlParameter);
}
procedure.declareParameters((SqlParameter[]) parameters.toArray(new SqlParameter[0]));
outParams = procedure.execute(inParams);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DataSourceUtils.releaseConnection(connection, jdbcTemplate.getDataSource());
}
Version of pgpool is as follows:
bash-4.1$
-bash-4.1$ ll pgpool
-rwxr-xr-x. 1 enterprisedb enterprisedb 1358832 Nov 5 2013 pgpool
-bash-4.1$ ./pgpool --version
pgpool-II version 3.3.1 (tokakiboshi)
-bash-4.1$
But when we are removing pgpool, and directly hitting the database, we are able to read the data correctly.
Kindly suggest the fix for this issue.