details: https://code.openbravo.com/erp/devel/pi/rev/39a08f4bf306
changeset: 17738:39a08f4bf306
user: Antonio Moreno <antonio.moreno <at> openbravo.com>
date: Wed Aug 22 10:05:16 2012 +0200
summary: Fixed issue 21277. Added call method to CallStoredProcedure which
supports calling of procedures in Oracle.
diffstat:
src/org/openbravo/costing/CostingAlgorithm.java |
11 +-
src/org/openbravo/erpCommon/ad_actionButton/MRPManufacturingPlanProcess.java |
97 +---------
src/org/openbravo/service/db/CallStoredProcedure.java |
21 +-
3 files changed, 25 insertions(+), 104 deletions(-)
diffs (239 lines):
diff -r 422f36b87a28 -r 39a08f4bf306
src/org/openbravo/costing/CostingAlgorithm.java
--- a/src/org/openbravo/costing/CostingAlgorithm.java Wed Aug 22 09:55:31
2012 +0200
+++ b/src/org/openbravo/costing/CostingAlgorithm.java Wed Aug 22 10:05:16
2012 +0200
@@ -20,7 +20,6 @@
import java.math.BigDecimal;
import java.math.RoundingMode;
-import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
@@ -31,7 +30,6 @@
import org.openbravo.base.structure.BaseOBObject;
import org.openbravo.costing.CostingServer.TrxType;
import org.openbravo.dal.core.DalUtil;
-import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.erpCommon.utility.OBDateUtils;
import org.openbravo.financial.FinancialUtils;
@@ -46,7 +44,7 @@
import org.openbravo.model.materialmgmt.transaction.ProductionTransaction;
import org.openbravo.model.pricing.pricelist.PriceList;
import org.openbravo.model.pricing.pricelist.ProductPrice;
-import org.openbravo.service.db.DalConnectionProvider;
+import org.openbravo.service.db.CallStoredProcedure;
public abstract class CostingAlgorithm {
protected MaterialTransaction transaction;
@@ -518,11 +516,8 @@
private void calculateWorkEffortCost(ProductionTransaction production) {
try {
- // first get a connection
- final Connection connection = OBDal.getInstance().getConnection();
-
- CostingData.calculateWorkEffortCost(connection, new
DalConnectionProvider(),
- production.getId(), (String)
DalUtil.getId(OBContext.getOBContext().getUser()));
+ List<Object> params = new ArrayList<Object>();
+ CallStoredProcedure.getInstance().call("MA_PRODUCTION_COST", params,
null, true, false);
} catch (Exception e) {
OBDal.getInstance().rollbackAndClose();
diff -r 422f36b87a28 -r 39a08f4bf306
src/org/openbravo/erpCommon/ad_actionButton/MRPManufacturingPlanProcess.java
---
a/src/org/openbravo/erpCommon/ad_actionButton/MRPManufacturingPlanProcess.java
Wed Aug 22 09:55:31 2012 +0200
+++
b/src/org/openbravo/erpCommon/ad_actionButton/MRPManufacturingPlanProcess.java
Wed Aug 22 10:05:16 2012 +0200
@@ -18,22 +18,14 @@
*/
package org.openbravo.erpCommon.ad_actionButton;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.Timestamp;
-import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
-import java.util.Properties;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
-import org.openbravo.base.session.OBPropertiesProvider;
-import org.openbravo.base.structure.BaseOBObject;
import org.openbravo.dal.core.DalUtil;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
@@ -43,6 +35,7 @@
import org.openbravo.model.mrp.ProductionRun;
import org.openbravo.model.mrp.ProductionRunLine;
import org.openbravo.scheduling.ProcessBundle;
+import org.openbravo.service.db.CallStoredProcedure;
import org.openbravo.service.db.DalBaseProcess;
public class MRPManufacturingPlanProcess extends DalBaseProcess {
@@ -112,7 +105,7 @@
parameters.add(timeHorizon);
parameters.add(docDate);
parameters.add("Y");
- call("MRP_RUN_INITIALIZE", parameters, null);
+ CallStoredProcedure.getInstance().call("MRP_RUN_INITIALIZE", parameters,
null, true, false);
OBDal.getInstance().commitAndClose();
OBDal.getInstance().getSession().clear();
@@ -130,7 +123,7 @@
parameters.add(timeHorizon);
parameters.add(docDate);
parameters.add(safetyLeadTime);
- call("MRP_RUN_EXPLODE", parameters, null);
+ CallStoredProcedure.getInstance().call("MRP_RUN_EXPLODE", parameters,
null, true, false);
OBDal.getInstance().commitAndClose();
OBDal.getInstance().getSession().clear();
@@ -147,7 +140,7 @@
parameters.add(timeHorizon);
parameters.add(docDate);
parameters.add(safetyLeadTime);
- call("MRP_PROCESSPLAN", parameters, null);
+ CallStoredProcedure.getInstance().call("MRP_PROCESSPLAN", parameters,
null, true, false);
OBDal.getInstance().commitAndClose();
OBDal.getInstance().getSession().clear();
@@ -188,86 +181,4 @@
return prlQry.scroll(ScrollMode.FORWARD_ONLY);
}
- private void call(String name, List<Object> parameters, List<Class<?>>
types) {
- final StringBuilder sb = new StringBuilder();
-
- final Properties obProps =
OBPropertiesProvider.getInstance().getOpenbravoProperties();
- if (obProps.getProperty("bbdd.rdbms") != null
- && obProps.getProperty("bbdd.rdbms").equals("POSTGRE")) {
- sb.append("SELECT * FROM ");
- } else {
- sb.append(" CALL ");
- }
-
- sb.append(name);
- for (int i = 0; i < parameters.size(); i++) {
- if (i == 0) {
- sb.append("(");
- } else {
- sb.append(",");
- }
- sb.append("?");
- }
- if (parameters.size() > 0) {
- sb.append(")");
- }
- final Connection conn = OBDal.getInstance().getConnection(true);
- try {
- final PreparedStatement ps = conn.prepareStatement(sb.toString(),
- ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
- int index = 0;
-
- for (Object parameter : parameters) {
- final int sqlIndex = index + 1;
- if (parameter == null) {
- if (types == null || types.size() < index) {
- ps.setNull(sqlIndex, Types.NULL);
- } else {
- ps.setNull(sqlIndex, getSqlType(types.get(index)));
- }
- } else if (parameter instanceof String &&
parameter.toString().equals("")) {
- ps.setNull(sqlIndex, Types.VARCHAR);
- } else if (parameter instanceof Boolean) {
- ps.setObject(sqlIndex, ((Boolean) parameter) ? "Y" : "N");
- } else if (parameter instanceof BaseOBObject) {
- ps.setObject(sqlIndex, ((BaseOBObject) parameter).getId());
- } else if (parameter instanceof Timestamp) {
- ps.setTimestamp(sqlIndex, (Timestamp) parameter);
- } else if (parameter instanceof Date) {
- ps.setDate(sqlIndex, new java.sql.Date(((Date)
parameter).getTime()));
- } else {
- ps.setObject(sqlIndex, parameter);
- }
- index++;
- }
- final ResultSet resultSet = ps.executeQuery();
- resultSet.close();
- ps.close();
- } catch (Exception e) {
- throw new IllegalStateException(e);
- }
- }
-
- private int getSqlType(Class<?> clz) {
- if (clz == null) {
- return Types.VARCHAR;
- }
- if (clz == Boolean.class) {
- return Types.VARCHAR;
- } else if (clz == String.class) {
- return Types.VARCHAR;
- } else if (clz == BaseOBObject.class) {
- return Types.VARCHAR;
- } else if (Number.class.isAssignableFrom(clz)) {
- return Types.NUMERIC;
- } else if (clz == Timestamp.class) {
- return Types.TIMESTAMP;
- } else if (Date.class.isAssignableFrom(clz)) {
- return Types.DATE;
- } else if (BaseOBObject.class.isAssignableFrom(clz)) {
- return Types.VARCHAR;
- } else {
- throw new IllegalStateException("Type not supported, please add it here
" + clz.getName());
- }
- }
}
diff -r 422f36b87a28 -r 39a08f4bf306
src/org/openbravo/service/db/CallStoredProcedure.java
--- a/src/org/openbravo/service/db/CallStoredProcedure.java Wed Aug 22
09:55:31 2012 +0200
+++ b/src/org/openbravo/service/db/CallStoredProcedure.java Wed Aug 22
10:05:16 2012 +0200
@@ -64,11 +64,24 @@
* if the null value is something else than a String (which is
handled as a default type)
* @param doFlush
* do flush before calling stored procedure
+ * @param returnResults
+ * whether a fetch for results should be done after the call to the
stored procedure
+ * (essentially describes whether the PL object is a procedure or
function)
+ *
* @return the stored procedure result.
*/
public Object call(String name, List<Object> parameters, List<Class<?>>
types, boolean doFlush) {
+ return call(name, parameters, types, doFlush, true);
+ }
+
+ public Object call(String name, List<Object> parameters, List<Class<?>>
types, boolean doFlush,
+ boolean returnResults) {
final StringBuilder sb = new StringBuilder();
- sb.append("SELECT " + name);
+ if (new DalConnectionProvider(false).getRDBMS().equalsIgnoreCase("ORACLE")
&& !returnResults) {
+ sb.append("CALL " + name);
+ } else {
+ sb.append("SELECT " + name);
+ }
for (int i = 0; i < parameters.size(); i++) {
if (i == 0) {
sb.append("(");
@@ -80,7 +93,9 @@
if (parameters.size() > 0) {
sb.append(")");
}
- sb.append(" AS RESULT FROM DUAL");
+ if (returnResults || !new
DalConnectionProvider(false).getRDBMS().equalsIgnoreCase("ORACLE")) {
+ sb.append(" AS RESULT FROM DUAL");
+ }
final Connection conn = OBDal.getInstance().getConnection(doFlush);
try {
final PreparedStatement ps = conn.prepareStatement(sb.toString(),
@@ -112,7 +127,7 @@
}
final ResultSet resultSet = ps.executeQuery();
Object resultValue = null;
- if (resultSet.next()) {
+ if (returnResults && resultSet.next()) {
resultValue = resultSet.getObject("RESULT");
if (resultSet.wasNull()) {
resultValue = null;
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
