details: https://code.openbravo.com/erp/devel/pi/rev/4eceadac7f74 changeset: 27820:4eceadac7f74 user: Asier Lostalé <asier.lostale <at> openbravo.com> date: Fri Oct 30 11:22:17 2015 +0100 summary: fixed bug 30891: sqlc transactional callable statemts not released in Oracle
Working with Oracle transactional callable statements are not closed after invokation, causing the number of open cursors to increase unnecessarily. There was already code in sqlc to release the statement, but there was an extra incorrect check not to do it in case of callable when in transactional mode. This check has been removed, being now transactional and non transactional cases handled in the same way. details: https://code.openbravo.com/erp/devel/pi/rev/bf8fd604e551 changeset: 27821:bf8fd604e551 user: Asier Lostalé <asier.lostale <at> openbravo.com> date: Fri Oct 30 11:41:50 2015 +0100 summary: related to bug 30891: added test case details: https://code.openbravo.com/erp/devel/pi/rev/bf568b95d85e changeset: 27822:bf568b95d85e user: Asier Lostalé <asier.lostale <at> openbravo.com> date: Fri Oct 30 12:03:10 2015 +0100 summary: related to bug 30891: added test case details: https://code.openbravo.com/erp/devel/pi/rev/6af281d22ea0 changeset: 27823:6af281d22ea0 user: Asier Lostalé <asier.lostale <at> openbravo.com> date: Mon Nov 02 08:33:06 2015 +0100 summary: related to bug 30891: query open cursors as DBA diffstat: src-core/src/org/openbravo/data/Sqlc.java | 4 +- src-test/src/org/openbravo/test/AllAntTaskTests.java | 8 +- src-test/src/org/openbravo/test/AntTaskTests.java | 4 + src-test/src/org/openbravo/test/db/model/functions/SqlCallableStatement.java | 103 ++++++++++ 4 files changed, 115 insertions(+), 4 deletions(-) diffs (181 lines): diff -r 543f652781a2 -r 6af281d22ea0 src-core/src/org/openbravo/data/Sqlc.java --- a/src-core/src/org/openbravo/data/Sqlc.java Fri Oct 30 13:15:08 2015 +0100 +++ b/src-core/src/org/openbravo/data/Sqlc.java Mon Nov 02 08:33:06 2015 +0100 @@ -1,6 +1,6 @@ /* ************************************************************************************ - * Copyright (C) 2001-2014 Openbravo S.L.U. + * Copyright (C) 2001-2015 Openbravo S.L.U. * Licensed under the Apache Software License version 2.0 * You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 * Unless required by applicable law or agreed to in writing, software distributed @@ -1481,7 +1481,7 @@ } else { if (sql.sqlType.equals("statement")) out2.append(" connectionProvider.releaseTransactionalStatement(st);\n"); - else if (sql.sqlType.equalsIgnoreCase("preparedstatement")) + else out2.append(" connectionProvider.releaseTransactionalPreparedStatement(st);\n"); } out2.append(" } catch(Exception ignore){\n"); diff -r 543f652781a2 -r 6af281d22ea0 src-test/src/org/openbravo/test/AllAntTaskTests.java --- a/src-test/src/org/openbravo/test/AllAntTaskTests.java Fri Oct 30 13:15:08 2015 +0100 +++ b/src-test/src/org/openbravo/test/AllAntTaskTests.java Mon Nov 02 08:33:06 2015 +0100 @@ -57,6 +57,7 @@ import org.openbravo.test.dal.ReadByNameTest; import org.openbravo.test.dal.ValidationTest; import org.openbravo.test.dal.ViewTest; +import org.openbravo.test.db.model.functions.SqlCallableStatement; import org.openbravo.test.expression.EvaluationTest; import org.openbravo.test.model.ClassLoaderTest; import org.openbravo.test.model.IndexesTest; @@ -103,7 +104,7 @@ @RunWith(Suite.class) @Suite.SuiteClasses({ - // dal +// dal DalComplexQueryRequisitionTest.class, // DalComplexQueryTestOrderLine.class, // DalPerformanceInventoryLineTest.class, // @@ -205,7 +206,10 @@ ModuleScriptsVersions.class, // // role inheritance - RoleInheritanceTestSuite.class + RoleInheritanceTestSuite.class, // + + // db + SqlCallableStatement.class }) public class AllAntTaskTests { diff -r 543f652781a2 -r 6af281d22ea0 src-test/src/org/openbravo/test/AntTaskTests.java --- a/src-test/src/org/openbravo/test/AntTaskTests.java Fri Oct 30 13:15:08 2015 +0100 +++ b/src-test/src/org/openbravo/test/AntTaskTests.java Mon Nov 02 08:33:06 2015 +0100 @@ -41,6 +41,7 @@ import org.openbravo.test.dal.OBContextTest; import org.openbravo.test.dal.ReadByNameTest; import org.openbravo.test.dal.ValidationTest; +import org.openbravo.test.db.model.functions.SqlCallableStatement; import org.openbravo.test.expression.EvaluationTest; import org.openbravo.test.model.ClassLoaderTest; import org.openbravo.test.model.OneToManyTest; @@ -144,6 +145,9 @@ // cdi CdiInfrastructure.class, + // db + SqlCallableStatement.class + }) public class AntTaskTests { } \ No newline at end of file diff -r 543f652781a2 -r 6af281d22ea0 src-test/src/org/openbravo/test/db/model/functions/SqlCallableStatement.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-test/src/org/openbravo/test/db/model/functions/SqlCallableStatement.java Mon Nov 02 08:33:06 2015 +0100 @@ -0,0 +1,103 @@ +/* + ************************************************************************* + * The contents of this file are subject to the Openbravo Public License + * Version 1.1 (the "License"), being the Mozilla Public License + * Version 1.1 with a permitted attribution clause; you may not use this + * file except in compliance with the License. You may obtain a copy of + * the License at http://www.openbravo.com/legal/license.html + * Software distributed under the License is distributed on an "AS IS" + * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the + * License for the specific language governing rights and limitations + * under the License. + * The Original Code is Openbravo ERP. + * The Initial Developer of the Original Code is Openbravo SLU + * All portions are Copyright (C) 2015 Openbravo SLU + * All Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ + */ + +package org.openbravo.test.db.model.functions; + +import static org.hamcrest.CoreMatchers.is; +import static org.hamcrest.Matchers.equalTo; +import static org.hamcrest.Matchers.lessThanOrEqualTo; +import static org.junit.Assert.assertThat; +import static org.junit.Assume.assumeThat; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Properties; + +import org.junit.Test; +import org.openbravo.base.session.OBPropertiesProvider; +import org.openbravo.dal.service.OBDal; +import org.openbravo.erpCommon.utility.DocumentNoData; +import org.openbravo.service.db.DalConnectionProvider; +import org.openbravo.test.base.OBBaseTest; + +/** + * Test cases for sqlc callable statements. + * + * @author alostale + * + */ +public class SqlCallableStatement extends OBBaseTest { + + /** + * Asserts that sqlc transactional callable statements are closed. See issue #30891 + */ + @Test + public void transactionalCallableStatemetsShouldBeClosed() throws Exception { + DalConnectionProvider cp = new DalConnectionProvider(false); + assumeThat("Executing only in Oracle", cp.getRDBMS(), is(equalTo("ORACLE"))); + + Connection conn = OBDal.getInstance().getConnection(false); + for (int i = 0; i < 200; i++) { + DocumentNoData.nextDocTypeConnection(conn, cp, "466AF4B0136A4A3F9F84129711DA8BD3", + "23C59575B9CF467C9620760EB255B389", "Y"); + } + + assertOpenCursors(); + } + + /** + * Asserts that sqlc non transactional callable statements are closed. See issue #30891 + */ + @Test + public void noTransactionalCallableStatemetsShouldBeClosedCallableStatemetsShouldBeClosed() + throws Exception { + DalConnectionProvider cp = new DalConnectionProvider(false); + assumeThat("Executing only in Oracle", cp.getRDBMS(), is(equalTo("ORACLE"))); + + for (int i = 0; i < 200; i++) { + DocumentNoData.nextDocType(cp, "466AF4B0136A4A3F9F84129711DA8BD3", + "23C59575B9CF467C9620760EB255B389", "Y"); + } + + assertOpenCursors(); + } + + private void assertOpenCursors() throws Exception, SQLException { + // getting a direct jdbc connection to system DB user because DBA privileges are required to + // query open_cursor view + Properties props = OBPropertiesProvider.getInstance().getOpenbravoProperties(); + Connection con = DriverManager.getConnection(props.getProperty("bbdd.url"), + props.getProperty("bbdd.systemUser"), props.getProperty("bbdd.systemPassword")); + + String query = "select count(*) from v$open_cursor where sql_text like 'CALL AD_Sequence_DocType%'"; + PreparedStatement st = con.prepareStatement(query); + ResultSet rs = st.executeQuery(); + rs.next(); + int openCursors = rs.getInt(1); + rs.close(); + st.close(); + con.close(); + + assertThat("# of open cursors for the statement", openCursors, is(lessThanOrEqualTo(1))); + } + +} ------------------------------------------------------------------------------ _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
