Is that normal I can't commit a transaction when using refcursor in a stored procedure - Mailing list pgsql-jdbc

I all,

I just want to know if it' the way it should be .. and if the way I use
refcursor is correct. I have a stored procedure that return a refcursor.
I don't know if it's relevant but this store procedure creates TEMP table:

If I uncomment the commit below I get the exception (see the end of the
mail). If I just close the ResultSet everithing is oki. Is that the way
it should be?

try {

dbCon = ConnectionFactory.getConnection();
dbCon.startTransaction(Connection.TRANSACTION_READ_COMMITTED); //
Because we use a cursor

String sql = dao.getSqlMap().getMappedStatement("generalLedgerAnalysis."
+ WebOsConstants.DB_GET).getSql(null);
dbCon.prepareCall(sql);
CallableStatement cs = (CallableStatement) dbCon.getPreparedStatement();
cs.registerOutParameter(1, Types.OTHER);

cs.setString(2, (String) parameters.get("companyId"));
cs.setString(3, (String) parameters.get("periodIdFrom"));
cs.setString(4, (String) parameters.get("periodIdTo"));
cs.setString(5, (String) parameters.get("accountIdFrom"));
cs.setString(6, (String) parameters.get("accountIdTo"));

ResultSet results = dbCon.executePreparedStatementQueryCursor();
builder.process(results);
// dbCon.commitTransaction();
//
} catch (SQLException e) {

log.error("Problem with the db : " + e.getMessage(), e);
throw new DefectException(e.getMessage(), e);
} finally {
if (dbCon != null)
dbCon.closeAll();
}

Is that the way it should be? If this exception is not OKI do you have
any idea where the problem can be?

Thanks for your help!! It's really appreciated

/David

I put the exception I got below. And a part of my stored procedure.

CREATE OR REPLACE FUNCTION usp_Comptabilite_AnalyseGL(VARCHAR, VARCHAR,
VARCHAR, VARCHAR, VARCHAR) RETURNS refcursor AS '
DECLARE
companyId ALIAS FOR $1;
periodId1 ALIAS FOR $2;
periodId2 ALIAS FOR $3;
accountId1 ALIAS FOR $4;
accountId2 ALIAS FOR $5;

ref refcursor;
statement varchar(4000);

dateSolde DATE;
dateFinPer2 DATE;

BEGIN

EXECUTE ''
CREATE TEMP TABLE T_AUX (
ANUM INT NOT NULL,
ARRNUM VARCHAR(10) NOT NULL,
ADATE DATE,
AGENUM INT,
AGLNUM VARCHAR(10),
ADEBIT numeric(40, 2),
ACREDIT numeric(40, 2),
ANOM VARCHAR(150),
ADESC varchar(100) NULL
) ON COMMIT DROP'';

-- Comptes impliqu‚s


WARN [http8080-Processor4] (DbConnection.java:218) 2005-03-22
13:51:55,359 : DB: Error commiting Transaction: ERROR: relation 6013057
is still open

ERROR [http8080-Processor4] (GeneralLedgerAnalysisDocument.java:91)
2005-03-22 13:51:55,375 : Problem with the db : ERROR: relation 6013057
is still open

org.postgresql.util.PSQLException: ERROR: relation 6013057 is still open

at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.execSQL(AbstractJdbc1Connection.java:887)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit(AbstractJdbc1Connection.java:1272)
at
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:268)
at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:293)
at
com.davecorp.webos.connectionFactory.DbConnection.commitTransaction(DbConnection.java:216)
at
com.unik.unikommerce.report.accounting.other.GeneralLedgerAnalysisDocument.parse(GeneralLedgerAnalysisDocument.java:87)
at
org.apache.xml.dtm.ref.DTMManagerDefault.getDTM(DTMManagerDefault.java:495)
at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:658)
at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1129)
at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1107)
at
com.davecorp.webos.reportManager.ReportManagerImpl.processReportPdf(ReportManagerImpl.java:278)
at
com.davecorp.webos.reportManager.ReportManager.processReportPdf(ReportManager.java:44)
at com.davecorp.webos.servlet.ReportServlet.service(ReportServlet.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
ERROR [http8080-Processor4] (ReportManagerImpl.java:288) 2005-03-22
13:51:55,375 : ERROR: relation 6013057 is still open

com.davecorp.webos.util.DefectException: ERROR: relation 6013057 is
still open