0

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.

Abhraneel
  • 1
  • 1
  • Do you switch connections during the session? Looks like the cursor is create on a different connection. – Frank Heikens Dec 02 '14 at 08:54
  • We create a connection every time we make a transaction to DB `connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());` – Abhraneel Dec 02 '14 at 10:04
  • pgpool is being used as a load balancer between master n slave DB. Not sure if pgpool switches connection internally.. – Abhraneel Dec 02 '14 at 10:12

0 Answers0