From: | David Gagnon <dgagnon(at)siunik(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Is that normal I can't commit a transaction when using refcursor in a stored procedure |
Date: | 2005-03-22 19:08:07 |
Message-ID: | 42406D17.60104@siunik.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-22 19:28:16 | Re: Is that normal I can't commit a transaction when using refcursor in a stored procedure |
Previous Message | Nico | 2005-03-21 17:00:05 | Re: getting a sequence value |