Issues in reading data from postgres DB 9.3 using pgpool version 3.3.1

928 views Asked by At

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.

0

There are 0 answers