Calling postgres sql function with spring boot

[rajesh mandalapu]

I have a sample demo spring boot mybatis project where i call sql procedure. But, I always get Exception as unnamed portal 1 does not exist

2018-10-30 15:24:18.991  INFO 32549 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-10-30 15:24:21.162  INFO 32549 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-10-30 15:24:21.893  INFO 32549 --- [nio-8080-exec-1] o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2018-10-30 15:24:22.374 ERROR 32549 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
### The error may exist in com/example/demo/Mapper.xml
### The error may involve com.example.demo.Mapper.getData-Inline
### The error occurred while setting parameters
### SQL: {               call MV_METADATA.MV_GET_UI_REPORTS_BY_PAGEC(139440,         1000572,         ?,         ?,         0,         25,         ?,         ?,         ?,         ?,         ?,         ?,         ?)                 }
### Cause: org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
; uncategorized SQLException; 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] with root cause

org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgConnection.execSQLQuery(PgConnection.java:417) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:250) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2572) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:126) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132) ~[postgresql-42.2.5.jar:42.2.5]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-2.7.9.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java) ~[HikariCP-2.7.9.jar:na]
    at org.apache.ibatis.executor.statement.CallableStatementHandler.query(CallableStatementHandler.java:67) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)

. You can clone my project from Demo Github project.

I do not know where i am making mistake. Can you guys help me on this?

[Guy Rouillier]

I haven't looked at your code, but searched for "cursor "<unnamed portal 1>" does not exist" and found some relevant results.  Here's one:

http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure

Apparently this happens when you obtain a ref cursor with autocommit on, which immediately terminates the transaction after each statement.  So the ref cursor becomes invalid.  The solution proposed is to turn autocommit off.  Have you tried that?

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rajesh mandalapu]

Thanks for your reply.
I know that is possible solution. But, I am using Spring Boot with latest Mybatis Starter and exactly not sure where i can explictly open an SqlSession with autocommit turned off. I am following this tutorial from mybatis http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/ and it says it uses a default 

MyBatis-Spring-Boot-Starter will:

• Autodetect an existing DataSource.
• Will create and register an instance of a SqlSessionFactory passing that DataSource as an input using the SqlSessionFactoryBean.
• Will create and register an instance of a SqlSessionTemplate got out of the SqlSessionFactory.
• Autoscan your mappers, link them to the SqlSessionTemplate and register them to Spring context so they can be injected into your beans.
  
  
  And the  SqlSessionFactory  uses ans SqlSession with the following options where it says autocommit is by default turned off. I am not sure weather i understand it in correct way.
  
  From http://www.mybatis.org/mybatis-3/java-api.html
  
  

  The set of overloaded openSession() method signatures allow you to choose any combination of these options that makes sense.

  SqlSession openSession()
  SqlSession openSession(boolean autoCommit)
  SqlSession openSession(Connection connection)
  SqlSession openSession(TransactionIsolationLevel level)
  SqlSession openSession(ExecutorType execType,TransactionIsolationLevel level)
  SqlSession openSession(ExecutorType execType)
  SqlSession openSession(ExecutorType execType, boolean autoCommit)
  SqlSession openSession(ExecutorType execType, Connection connection)
  Configuration getConfiguration();

  The default openSession() method that takes no parameters will create a SqlSession with the following characteristics:

  • A transaction scope will be started (i.e. NOT auto-commit).
  • A Connection object will be acquired from the DataSource instance configured by the active environment.
  • The transaction isolation level will be the default used by the driver or data source.
  • No PreparedStatements will be reused, and no updates will be batched.
  
  Thanks
  Rajesh M