it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java Source code

Java tutorial

Introduction

Here is the source code for it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java

Source

/*******************************************************************************
 * Copyright (c) 2009, 2016 GreenVulcano ESB Open Source Project.
 * All rights reserved.
 *
 * This file is part of GreenVulcano ESB.
 *
 * GreenVulcano ESB is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * GreenVulcano ESB is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with GreenVulcano ESB. If not, see <http://www.gnu.org/licenses/>.
 *******************************************************************************/
package it.greenvulcano.gvesb.datahandling.dbo;

import it.greenvulcano.configuration.XMLConfig;
import it.greenvulcano.configuration.XMLConfigException;
import it.greenvulcano.gvesb.datahandling.DBOException;
import it.greenvulcano.gvesb.datahandling.utils.DiscardCause;
import it.greenvulcano.gvesb.datahandling.utils.ParameterType;
import it.greenvulcano.gvesb.datahandling.utils.exchandler.oracle.OracleError;
import it.greenvulcano.gvesb.datahandling.utils.exchandler.oracle.OracleExceptionHandler;
import it.greenvulcano.util.metadata.PropertiesHandler;
import it.greenvulcano.util.txt.DateUtils;
import it.greenvulcano.util.txt.TextUtils;
import it.greenvulcano.util.xml.XMLUtils;
import it.greenvulcano.util.xml.XMLUtilsException;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.StringReader;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DecimalFormatSymbols;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Base64;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.StringTokenizer;

import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.w3c.dom.Text;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;

/**
 * IDBO Class specialized to parse the input RowSet document and in
 * calling stored procedures in the DB.
 * 
 * @version 3.0.0 Mar 30, 2010
 * @author GreenVulcano Developer Team
 */
public class DBOCallSP extends AbstractDBO {
    /**
     * @version 3.0.0 Mar 30, 2010
     * @author GreenVulcano Developer Team
     */
    public class SPCallDescriptor {
        private final String ROWSET_NAME = "RowSet";

        private final String DATA_NAME = "data";

        /**
         * @version 3.0.0 Mar 30, 2010
         * @author GreenVulcano Developer Team
         * 
         */
        public class SPOutputParam {
            /**
             * Type of parameter.
             */
            private String dbType = null;

            /**
             * Type of parameter.
             */
            private String javaType = null;

            /**
             * Type of parameter.
             */
            private String javaTypeFormat = null;

            /**
             * Position of parameter in the SQL statement.
             */
            private int position = 0;

            /**
             * Precision of numeric parameter.
             */
            private int precision = 0;

            /**
             * Return the value in properties map.
             */
            private boolean returnInProperties = false;

            /**
             * Type property name.
             */
            private String propName = null;

            private boolean returnInUUID;

            private String paramName;

            /**
             * SPOutputParam Constructor
             * 
             * @param node
             *        the configuration node
             * @throws DBOException
             *         if an error occurred
             */
            public SPOutputParam(Node node) throws DBOException {
                try {
                    dbType = XMLConfig.get(node, "@db-type");
                    javaType = XMLConfig.get(node, "@java-type");
                    javaTypeFormat = XMLConfig.get(node, "@java-type-format", "");
                    position = XMLConfig.getInteger(node, "@position");
                    precision = XMLConfig.getInteger(node, "@precision", 0);
                    returnInProperties = XMLConfig.getBoolean(node, "@return-in-prop", false);
                    returnInUUID = XMLConfig.getBoolean(node, "@return-in-uuid", false);
                    propName = XMLConfig.get(node, "@prop-name", "" + position);
                    paramName = XMLConfig.get(node, "@param-name", "");
                } catch (XMLConfigException exc) {
                    throw new DBOException("Error configuring the output parameter: " + exc, exc);
                } catch (Throwable exc) {
                    throw new DBOException("Error initializing the output parameter: " + exc, exc);
                }
            }

            /**
             * Set the dbType.<br/>
             * <br/>
             * 
             * @param dbType
             *        The value to set.
             */
            public void setDBType(String dbType) {
                this.dbType = dbType;
            }

            /**
             * Set the javaType.<br/>
             * <br/>
             * 
             * @param javaType
             *        The value to set.
             */
            public void setJavaType(String javaType) {
                this.javaType = javaType;
            }

            /**
             * Set the javaTypeFormat.<br/>
             * <br/>
             * 
             * @param javaTypeFormat
             *        The value to set.
             */
            public void setJavaTypeFormat(String javaTypeFormat) {
                this.javaTypeFormat = javaTypeFormat;
            }

            /**
             * Set the position of parameter into the SQL Statement.<br/>
             * <br/>
             * 
             * @param position
             *        The value to set.
             */
            public void setPosition(int position) {
                this.position = position;
            }

            /**
             * Set the numeric precision of parameter.<br/>
             * <br/>
             * 
             * @param precision
             *        The value to set.
             */
            public void setPrecision(int precision) {
                this.precision = precision;
            }

            /**
             * Get the dbtype attribute.<br/>
             * <br/>
             * 
             * @return the dbtype
             */
            public String getDBType() {
                return dbType;
            }

            /**
             * Get the java type attribute.<br/>
             * <br/>
             * 
             * @return the java type
             */
            public String getJavaType() {
                return javaType;
            }

            /**
             * Get the java type format attribute.<br/>
             * <br/>
             * 
             * @return the java type format
             */
            public String getJavaTypeFormat() {
                return javaTypeFormat;
            }

            /**
             * Get the precision of numeric parameter.<br/>
             * <br/>
             * 
             * @return the precision
             */
            public int getPrecision() {
                return precision;
            }

            /**
             * Get the position of parameter into the SQL Statement.<br/>
             * <br/>
             * 
             * @return the position
             */
            public int getPosition() {
                return position;
            }

            /**
             * @return if return object should be set as property
             */
            public boolean isReturnInProperties() {
                return returnInProperties;
            }

            /**
             * @return if return object should be set associated to UUID
             *         specified as row attribute.
             */
            public boolean isReturnInUUID() {
                return returnInUUID;
            }

            /**
             * Get the propName attribute.<br/>
             * <br/>
             * 
             * @return the propName
             */
            public String getPropName() {
                return propName;
            }

            /**
             * @param paramName
             *        the paramName to set
             */
            public void setParamName(String paramName) {
                this.paramName = paramName;
            }

            /**
             * @return the paramName
             */
            public String getParamName() {
                return paramName;
            }

        }

        private final List<SPOutputParam> spOutputParams = new ArrayList<SPOutputParam>();
        private String statement = "";

        private boolean namedParameterMode;

        /**
         * @param node
         * @throws DBOException
         */
        public SPCallDescriptor(Node node) throws DBOException {
            try {
                statement = XMLConfig.get(node, "statement[@type='callsp']", "");
                // Reading stored procedure output parameters
                NodeList nlParameters = XMLConfig.getNodeList(node, "SPOutputParameters/SPOutputParameter");
                int iNumParam = nlParameters.getLength();

                namedParameterMode = XMLConfig.getBoolean(node, "@named-parameter-mode", false);

                for (int i = 0; i < iNumParam; i++) {
                    spOutputParams.add(new SPOutputParam(nlParameters.item(i)));
                }

                if (statement.equals("")) {
                    throw new DBOException(
                            "Empty/misconfigured statements list for stored procedure call descriptor");
                }
            } catch (DBOException exc) {
                throw exc;
            } catch (Exception exc) {
                throw new DBOException("Error initializing the stored procedure call descriptor", exc);
            }
        }

        /**
         * @return the statement
         */
        public String getStatement() {
            return statement;
        }

        /**
         * Specify the output parameter from stored procedure
         * 
         * @param callStmt
         *        the statement
         * @throws DBOException
         *         if an error occurred
         */
        public void specifyOutputParameter(CallableStatement callStmt) throws DBOException {
            try {
                for (int i = 0; i < spOutputParams.size(); i++) {
                    SPOutputParam outp = spOutputParams.get(i);
                    String type = outp.getDBType().trim();
                    int iPos = outp.getPosition();
                    int iPrec = outp.getPrecision();
                    logger.debug("Parameter Output[" + (i + 1) + "] Type [" + type + "] Position [" + iPos
                            + "] Precision [" + iPrec + "]...");

                    if (type.equalsIgnoreCase(ParameterType.ORACLE_STRING)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.VARCHAR);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_INT)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.INTEGER);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_LONG)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.BIGINT);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_NUM)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.NUMERIC, iPrec);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_DATE)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.DATE);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_TIMESTAMP)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.TIMESTAMP);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_TIMESTAMP_WITH_TZ)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_LONG_RAW)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.LONGVARBINARY);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_CLOB)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.CLOB);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_BLOB)) {
                        callStmt.registerOutParameter(iPos, java.sql.Types.BLOB);
                    } else if (type.equalsIgnoreCase(ParameterType.ORACLE_CURSOR)) {
                        callStmt.registerOutParameter(iPos, -10); // OracleTypes.CURSOR
                    } else {
                        logger.error("specifyOutputParameter - "
                                + "Error while registring parameters for CallableStatement: "
                                + "parameter type not supported " + type);

                        throw new DBOException(
                                "Error while registring output parameters for CallableStatement: parameter type not supported "
                                        + type);
                    }
                }
            } catch (DBOException exc) {
                throw exc;
            } catch (SQLException exc) {
                throw new DBOException("Error while registering output parameters: " + exc, exc);
            }
        }

        /**
         * Specify the output parameter from store procedure
         * 
         * @param callStmt
         *        the statement
         * @param props
         * @throws DBOException
         *         if an error occurred
         */
        public void setOutputParameterValuesInMap(CallableStatement callStmt, Map<String, Object> props)
                throws DBOException {
            try {
                for (int i = 0; i < spOutputParams.size(); i++) {
                    SPOutputParam outp = spOutputParams.get(i);
                    if (outp.isReturnInProperties() || outp.isReturnInUUID()) {
                        String dbType = outp.getDBType().trim();
                        String javaType = outp.getJavaType().trim();
                        String propName = outp.getPropName();
                        int iPos = outp.getPosition();
                        String paramName = outp.getParamName();
                        Object value = null;

                        if (javaType.equalsIgnoreCase(ParameterType.JAVA_STRING)) {
                            if (dbType.equalsIgnoreCase(ParameterType.ORACLE_DATE)) {
                                String format = outp.getJavaTypeFormat();
                                if (format.equals("")) {
                                    format = DEFAULT_DATE_FORMAT;
                                }
                                Timestamp ts = namedParameterMode ? callStmt.getTimestamp(paramName)
                                        : callStmt.getTimestamp(iPos);
                                value = DateUtils.dateToString(new Date(ts.getTime()), format);
                            } else if (dbType.equalsIgnoreCase(ParameterType.ORACLE_CLOB)) {
                                Clob clob = namedParameterMode ? callStmt.getClob(paramName)
                                        : callStmt.getClob(iPos);
                                if (clob != null) {
                                    InputStream is = clob.getAsciiStream();
                                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                    IOUtils.copy(is, baos);
                                    is.close();
                                    try {
                                        value = new String(baos.toByteArray(), 0, (int) clob.length());
                                    } catch (SQLFeatureNotSupportedException exc) {
                                        value = baos.toString();
                                    }
                                } else {
                                    value = "";
                                }
                            } else if (dbType.equalsIgnoreCase(ParameterType.ORACLE_BLOB)) {
                                Blob blob = namedParameterMode ? callStmt.getBlob(paramName)
                                        : callStmt.getBlob(iPos);
                                if (blob != null) {
                                    InputStream is = blob.getBinaryStream();
                                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                    IOUtils.copy(is, baos);
                                    is.close();
                                    try {
                                        byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                                        value = Base64.getEncoder().encodeToString(buffer);
                                    } catch (SQLFeatureNotSupportedException exc) {
                                        value = Base64.getEncoder().encodeToString(baos.toByteArray());
                                    }
                                } else {
                                    value = "";
                                }
                            } else {
                                value = namedParameterMode ? callStmt.getString(paramName)
                                        : callStmt.getString(iPos);
                            }
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_INT)) {
                            int v = namedParameterMode ? callStmt.getInt(paramName) : callStmt.getInt(iPos);
                            value = new Integer(v);
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_LONG)) {
                            long v = namedParameterMode ? callStmt.getLong(paramName) : callStmt.getLong(iPos);
                            value = new Long(v);
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_DATE)) {
                            Timestamp ts = namedParameterMode ? callStmt.getTimestamp(paramName)
                                    : callStmt.getTimestamp(iPos);
                            value = new Date(ts.getTime());
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_RESULTSET)) {
                            // ResultSet not returned in properties
                        } else {
                            throw new DBOException(
                                    "Error while extracting the CallableStatement output parameters: parameter type not supported ("
                                            + javaType + ")");
                        }
                        if (!javaType.equalsIgnoreCase(ParameterType.JAVA_RESULTSET)) {
                            if (outp.isReturnInProperties()) {
                                logger.debug(
                                        "Setting out parameter " + propName + " = " + value + " in properties");
                                props.put(propName, value);
                            }
                            if (outp.isReturnInUUID()) {
                                Object uuid = currentRowFields.get(iPos);
                                if (uuid != null) {
                                    String uuidStr = uuid.toString();
                                    logger.debug("Setting out parameter " + propName + " = " + value + " in UUID "
                                            + uuidStr);
                                    uuids.put(uuidStr, value.toString());
                                }
                            }
                        }
                    }
                }
            } catch (DBOException exc) {
                throw exc;
            } catch (IOException exc) {
                throw new DBOException("Error while extracting the CallableStatement output parameters: " + exc,
                        exc);
            } catch (SQLException exc) {
                throw new DBOException("Error while extracting the CallableStatement output parameters: " + exc,
                        exc);
            }
        }

        /**
         * Specifies the output parameters from store procedure
         * 
         * @param callStmt
         *        the statement
         * @param xmlOut
         * @param statementId
         * @throws DBOException
         *         if an error occurred
         */
        public void buildOutXml(CallableStatement callStmt, Document xmlOut, String statementId)
                throws DBOException {
            XMLUtils xml = null;
            try {
                xml = XMLUtils.getParserInstance();
                Element docRoot = xmlOut.getDocumentElement();
                if (docRoot == null) {
                    xmlOut.appendChild(xml.createElement(xmlOut, ROWSET_NAME));
                    docRoot = xmlOut.getDocumentElement();
                }
                Element data = xml.createElement(xmlOut, DATA_NAME);
                xml.setAttribute(data, ID_NAME, statementId);
                docRoot.appendChild(data);

                Element row = xml.createElement(xmlOut, ROW_NAME);
                xml.setAttribute(row, ID_NAME, SP_RESULT);
                data.appendChild(row);
                for (int i = 0; i < spOutputParams.size(); i++) {
                    SPOutputParam outp = spOutputParams.get(i);
                    String dbType = outp.getDBType().trim();
                    String javaType = outp.getJavaType().trim();
                    String propName = outp.getPropName();
                    String paramName = outp.getParamName();
                    int iPos = outp.getPosition();
                    String value = null;
                    ResultSet resultSet = null;

                    if (javaType.equalsIgnoreCase(ParameterType.JAVA_RESULTSET)) {
                        Object obj = null;
                        try {
                            obj = namedParameterMode ? callStmt.getObject(paramName) : callStmt.getObject(iPos);
                        } catch (SQLException exc) {
                            // closed cursor?
                            logger.warn("Error reading Cursor output parameter... Closed cursor?", exc);
                        }
                        value = null;
                        if ((obj != null) && (obj instanceof ResultSet)) {
                            resultSet = (ResultSet) obj;
                        }
                    } else {
                        Element col = xml.createElement(xmlOut, COL_NAME);
                        xml.setAttribute(col, ID_NAME, propName);
                        if (javaType.equalsIgnoreCase(ParameterType.JAVA_STRING)) {
                            if (dbType.equalsIgnoreCase(ParameterType.ORACLE_DATE)) {
                                String format = outp.getJavaTypeFormat();
                                if (format.equals("")) {
                                    format = DEFAULT_DATE_FORMAT;
                                }
                                Timestamp ts = namedParameterMode ? callStmt.getTimestamp(paramName)
                                        : callStmt.getTimestamp(iPos);
                                value = DateUtils.dateToString(new Date(ts.getTime()), format);
                                xml.setAttribute(col, TYPE_NAME, TIMESTAMP_TYPE);
                                xml.setAttribute(col, FORMAT_NAME, format);
                            } else if (dbType.equalsIgnoreCase(ParameterType.ORACLE_CLOB)) {
                                xml.setAttribute(col, TYPE_NAME, LONG_STRING_TYPE);
                                Clob clob = namedParameterMode ? callStmt.getClob(paramName)
                                        : callStmt.getClob(iPos);
                                if (clob != null) {
                                    InputStream is = clob.getAsciiStream();
                                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                    IOUtils.copy(is, baos);
                                    is.close();
                                    try {
                                        value = new String(baos.toByteArray(), 0, (int) clob.length());
                                    } catch (SQLFeatureNotSupportedException exc) {
                                        value = baos.toString();
                                    }
                                } else {
                                    value = "";
                                }
                            } else if (dbType.equalsIgnoreCase(ParameterType.ORACLE_BLOB)) {
                                xml.setAttribute(col, TYPE_NAME, BASE64_TYPE);
                                Blob blob = namedParameterMode ? callStmt.getBlob(paramName)
                                        : callStmt.getBlob(iPos);
                                if (blob != null) {
                                    InputStream is = blob.getBinaryStream();
                                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                    IOUtils.copy(is, baos);
                                    is.close();
                                    try {
                                        byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                                        value = Base64.getEncoder().encodeToString(buffer);
                                    } catch (SQLFeatureNotSupportedException exc) {
                                        value = Base64.getEncoder().encodeToString(baos.toByteArray());
                                    }
                                } else {
                                    value = "";
                                }
                            } else {
                                xml.setAttribute(col, TYPE_NAME, STRING_TYPE);
                                value = namedParameterMode ? callStmt.getString(paramName)
                                        : callStmt.getString(iPos);
                            }
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_INT)) {
                            int v = namedParameterMode ? callStmt.getInt(paramName) : callStmt.getInt(iPos);
                            value = Integer.toString(v);
                            xml.setAttribute(col, TYPE_NAME, NUMERIC_TYPE);
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_LONG)) {
                            long v = namedParameterMode ? callStmt.getLong(paramName) : callStmt.getLong(iPos);
                            value = Long.toString(v);
                            xml.setAttribute(col, TYPE_NAME, NUMERIC_TYPE);
                        } else if (javaType.equalsIgnoreCase(ParameterType.JAVA_DATE)) {
                            String format = outp.getJavaTypeFormat();
                            if (format.equals("")) {
                                format = DEFAULT_DATE_FORMAT;
                            }
                            Timestamp ts = namedParameterMode ? callStmt.getTimestamp(paramName)
                                    : callStmt.getTimestamp(iPos);
                            value = DateUtils.dateToString(new Date(ts.getTime()), format);
                            xml.setAttribute(col, TYPE_NAME, TIMESTAMP_TYPE);
                            xml.setAttribute(col, FORMAT_NAME, format);
                        } else {
                            throw new DBOException(
                                    "Error while extracting the CallableStatement output parameters: parameter type not supported ("
                                            + javaType + ")");
                        }
                        if (value != null) {
                            Text text = xmlOut.createTextNode(value);
                            col.appendChild(text);
                        }
                        row.appendChild(col);
                    }
                    if (resultSet != null) {
                        try {
                            ResultSetMetaData metadata = resultSet.getMetaData();
                            boolean firstItr = true;
                            Set<Integer> keyField = keysMap.get(statementId);
                            boolean noKey = ((keyField == null) || keyField.isEmpty());
                            Map<String, String> keyAttr = new HashMap<String, String>();
                            String colKey = null;
                            String precKey = null;
                            while (resultSet.next()) {
                                if (!firstItr) {
                                    row = xml.createElement(xmlOut, ROW_NAME);
                                    xml.setAttribute(row, ID_NAME, SP_RESULT);
                                }
                                for (int j = 1; j <= metadata.getColumnCount(); j++) {
                                    Element col = xml.createElement(xmlOut, "col");
                                    xml.setAttribute(col, ID_NAME, propName + "[" + j + "]");
                                    switch (metadata.getColumnType(j)) {
                                    case Types.CLOB: {
                                        Clob clob = resultSet.getClob(j);
                                        if (clob != null) {
                                            InputStream is = clob.getAsciiStream();
                                            byte[] buffer = new byte[2048];
                                            ByteArrayOutputStream baos = new ByteArrayOutputStream(2048);
                                            int size;
                                            while ((size = is.read(buffer)) != -1) {
                                                baos.write(buffer, 0, size);
                                            }
                                            is.close();
                                            value = baos.toString();
                                        }
                                    }
                                        break;
                                    case Types.BLOB: {
                                        Blob blob = resultSet.getBlob(j);
                                        if (blob != null) {
                                            InputStream is = blob.getBinaryStream();
                                            byte[] buffer = new byte[2048];
                                            ByteArrayOutputStream baos = new ByteArrayOutputStream(2048);
                                            int size;
                                            while ((size = is.read(buffer)) != -1) {
                                                baos.write(buffer, 0, size);
                                            }
                                            is.close();
                                            value = Base64.getEncoder().encodeToString(baos.toByteArray());
                                        }
                                    }
                                        break;
                                    default: {
                                        value = resultSet.getString(j);
                                        if (value == null) {
                                            value = "";
                                        }
                                    }
                                    }
                                    if (value != null) {
                                        col.appendChild(xmlOut.createTextNode(value));
                                    }
                                    if (!noKey && keyField.contains(new Integer(j))) {
                                        if (value != null) {
                                            if (colKey == null) {
                                                colKey = value;
                                            } else {
                                                colKey += "##" + value;
                                            }
                                            keyAttr.put("key_" + j, value);
                                        }
                                    } else {
                                        row.appendChild(col);
                                    }
                                }
                                if (!noKey && (colKey != null) && !colKey.equals(precKey)) {
                                    if (!firstItr) {
                                        data = xml.createElement(xmlOut, DATA_NAME);
                                        xml.setAttribute(data, ID_NAME, statementId);
                                        docRoot.appendChild(data);
                                    }
                                    for (Entry<String, String> keyAttrEntry : keyAttr.entrySet()) {
                                        xml.setAttribute(data, keyAttrEntry.getKey(), keyAttrEntry.getValue());
                                    }
                                    keyAttr.clear();
                                    precKey = colKey;
                                }
                                if (firstItr) {
                                    firstItr = false;
                                }
                                colKey = null;
                                data.appendChild(row);
                            }
                        } finally {
                            resultSet.close();
                        }
                    }
                }
            } catch (DBOException exc) {
                throw exc;
            } catch (Exception exc) {
                throw new DBOException("Generic error", exc);
            } finally {
                XMLUtils.releaseParserInstance(xml);
            }
        }
    }

    /**
     * Current <code>SPCallDescriptor</code> to invoke the stored procedure.
     */
    SPCallDescriptor spCallDescriptor;

    /**
     * Call descriptors cache of stored procedure calls configured and
     * identified by an ID.
     */
    private final Map<String, SPCallDescriptor> spCallDescriptors;

    private Document xmlOut;

    /**
     * Private <i>logger</i> instance.
     */
    private static final Logger logger = org.slf4j.LoggerFactory.getLogger(DBOCallSP.class);

    private final Map<String, Set<Integer>> keysMap;

    /**
     * Default constructor.
     * 
     */
    public DBOCallSP() {
        super();
        spCallDescriptors = new HashMap<String, SPCallDescriptor>();
        keysMap = new HashMap<String, Set<Integer>>();
    }

    /**
     * @see it.greenvulcano.gvesb.datahandling.dbo.AbstractDBO#init(org.w3c.dom.Node)
     */
    @Override
    public void init(Node config) throws DBOException {
        super.init(config);
        try {
            forcedMode = XMLConfig.get(config, "@force-mode", MODE_CALL);
            isReturnData = XMLConfig.getBoolean(config, "@return-data", true);
            NodeList callds = XMLConfig.getNodeList(config, "CallDescriptor");
            String id = null;
            String keys = null;
            for (int i = 0; i < callds.getLength(); i++) {
                Node calld = callds.item(i);
                id = XMLConfig.get(calld, "@id", Integer.toString(i));
                keys = XMLConfig.get(calld, "statement[@id='" + id + "']/@keys");
                spCallDescriptors.put(id, new SPCallDescriptor(calld));
                if (keys != null) {
                    Set<Integer> s = new HashSet<Integer>();
                    StringTokenizer sTok = new StringTokenizer(keys, ",");
                    while (sTok.hasMoreTokens()) {
                        String str = sTok.nextToken();
                        s.add(new Integer(str.trim()));
                    }
                    keysMap.put(id, s);
                }
            }
        } catch (Exception exc) {
            logger.error("Error reading configuration of [" + dboclass + "]", exc);
            throw new DBOException("Error reading configuration of [" + dboclass + "]", exc);
        }
    }

    /**
     * @see it.greenvulcano.gvesb.datahandling.dbo.AbstractDBO#execute(java.lang.Object,
     *      java.sql.Connection, java.util.Map)
     */
    @Override
    public void execute(Object input, Connection conn, Map<String, Object> props)
            throws DBOException, InterruptedException {
        dataOut = new ByteArrayOutputStream();
        try {
            createOutXML();
            super.execute(input, conn, props);
            storeResult();
        } finally {
            dhr.setRead(0);
            dhr.setTotal(0);
            dhr.setInsert(0);
            dhr.setUpdate(0);
            dhr.setDiscard(0);
        }
    }

    /**
     * @see it.greenvulcano.gvesb.datahandling.dbo.AbstractDBO#execute(java.io.OutputStream,
     *      java.sql.Connection, java.util.Map)
     */
    @Override
    public void execute(OutputStream data, Connection conn, Map<String, Object> props)
            throws DBOException, InterruptedException {
        dataOut = data;
        try {
            createOutXML();
            super.execute((OutputStream) null, conn, props);
            storeResult();
        } finally {
            dhr.setRead(0);
            dhr.setTotal(0);
            dhr.setInsert(0);
            dhr.setUpdate(0);
            dhr.setDiscard(0);
        }
    }

    /**
     * @see it.greenvulcano.gvesb.datahandling.dbo.AbstractDBO#execute(java.lang.Object,
     *      java.io.OutputStream, java.sql.Connection, java.util.Map)
     */
    @Override
    public void execute(Object dataIn, OutputStream dataOut, Connection conn, Map<String, Object> props)
            throws DBOException, InterruptedException {
        this.dataOut = dataOut;
        try {
            createOutXML();
            super.execute(dataIn, conn, props);
            storeResult();
        } finally {
            dhr.setRead(0);
            dhr.setTotal(0);
            dhr.setInsert(0);
            dhr.setUpdate(0);
            dhr.setDiscard(0);
        }
    }

    /**
     * @throws DBOException
     * 
     */
    private void storeResult() throws DBOException {
        if (dataOut != null) {
            XMLUtils xml = null;
            try {
                xml = XMLUtils.getParserInstance();
                byte[] dataDOM = xml.serializeDOMToByteArray(xmlOut);
                dataOut.write(dataDOM);
            } catch (Exception exc) {
                throw new DBOException("Cannot store DBOCallSP result.", exc);
            } finally {
                XMLUtils.releaseParserInstance(xml);
            }
        }
        xmlOut = null;
    }

    /**
     * @throws DBOException
     * 
     */
    private void createOutXML() throws DBOException {
        XMLUtils xml = null;
        try {
            xml = XMLUtils.getParserInstance();
            this.xmlOut = xml.newDocument();
        } catch (XMLUtilsException exc) {
            throw new DBOException("Cannot instantiate XMLUtils.", exc);
        } finally {
            XMLUtils.releaseParserInstance(xml);
        }
    }

    private void handleOutput() throws DBOException {
        try {
            CallableStatement sqlStatement = (CallableStatement) sqlStatementInfo.getStatement();
            spCallDescriptor.setOutputParameterValuesInMap(sqlStatement, getCurrentProps());
            spCallDescriptor.buildOutXml(sqlStatement, xmlOut, sqlStatementInfo.getId());
        } catch (DBOException exc) {
            throw exc;
        } catch (Exception exc) {
            throw new DBOException("Error processing output parameters: " + exc.getMessage(), exc);
        }
    }

    private final String SP_RESULT = "sp_result";

    /**
     *
     */
    protected static final String OUTONLY_ATTR = "out-only";

    private int colIdx = 0;

    private String currType;

    private String currDateFormat;

    private String currNumberFormat;

    private String currGroupSeparator;

    private String currDecSeparator;

    private StringBuffer textBuffer;

    private boolean colDataExpecting;

    private OutputStream dataOut;

    private String currentUUID;

    private boolean outOnly;

    private String currName;

    private boolean useName;

    /**
     * @see it.greenvulcano.gvesb.datahandling.dbo.AbstractDBO#getStatement(java.lang.String)
     */
    @Override
    protected void getStatement(String id) throws SAXException {
        if (id == null) {
            id = "0";
        }
        if ((sqlStatementInfo == null) || !getCurrentId().equals(id)) {
            try {
                if (sqlStatementInfo != null) {
                    sqlStatementInfo.close();
                }
                spCallDescriptor = spCallDescriptors.get(id);
                if (spCallDescriptor == null) {
                    logger.error("SQL Call descriptor with id " + id + " not found.");
                    throw new SAXException("SQL Call descriptor with id " + id + " not found.");
                }
                String expandedSQL = PropertiesHandler.expand(spCallDescriptor.getStatement(), getCurrentProps(),
                        null, getInternalConn());
                logger.debug("expandedSQL stmt: " + expandedSQL);
                Statement statement = getInternalConn().prepareCall(expandedSQL);
                sqlStatementInfo = new StatementInfo(id, expandedSQL, statement);
                spCallDescriptor.specifyOutputParameter((CallableStatement) statement);
                setCurrentId(id);
            } catch (SAXException exc) {
                throw exc;
            } catch (SQLException exc) {
                OracleError oerr = OracleExceptionHandler.handleSQLException(exc);
                oerr.printLoggerInfo();
                throw new SAXException(exc);
            } catch (Exception exc) {
                throw new SAXException(exc);
            }
        }
    }

    /**
     * 
     * @see org.xml.sax.ContentHandler#startElement(java.lang.String,
     *      java.lang.String, java.lang.String, org.xml.sax.Attributes)
     */
    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes)
            throws SAXException {
        if (ROW_NAME.equals(localName)) {
            currentRowFields.clear();
            currentRowFields.add(null);
            colDataExpecting = false;
            colIdx = 0;
            String id = attributes.getValue(uri, ID_NAME);
            getStatement(id);
            currentXSLMessage = attributes.getValue(uri, XSL_MSG_NAME);
            String statsOn = attributes.getValue(uri, STATS_ON_NAME);
            statsOnInsert = !(STATS_UPD_MODE.equals(statsOn));
            String incrDisc = attributes.getValue(uri, INCR_DISC_NAME);
            incrDiscIfUpdKO = !(INCR_DISC_N_MODE.equals(incrDisc));
            currCriticalError = "true".equalsIgnoreCase(attributes.getValue(uri, CRITICAL_ERROR));
        } else if (COL_NAME.equals(localName)) {
            currType = attributes.getValue(uri, TYPE_NAME);
            currName = attributes.getValue(uri, NAME_ATTR);
            useName = (currName != null) && (currName.trim().length() > 0);
            currentUUID = attributes.getValue(uri, UUID_NAME);
            String outOnlyStr = attributes.getValue(uri, OUTONLY_ATTR);
            outOnly = outOnlyStr != null ? outOnlyStr.equalsIgnoreCase("true") : false;
            if (!outOnly) {
                if (TIMESTAMP_TYPE.equals(currType) || DATE_TYPE.equals(currType) || TIME_TYPE.equals(currType)) {
                    currDateFormat = attributes.getValue(uri, FORMAT_NAME);
                    if (currDateFormat == null) {
                        currDateFormat = DEFAULT_DATE_FORMAT;
                    }
                } else if (DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType)
                        || FLOAT_TYPE.equals(currType) || DOUBLE_TYPE.equals(currType)) {
                    currNumberFormat = attributes.getValue(uri, FORMAT_NAME);
                    if (currNumberFormat == null) {
                        currNumberFormat = call_DEFAULT_NUMBER_FORMAT;
                    }
                    currGroupSeparator = attributes.getValue(uri, GRP_SEPARATOR_NAME);
                    if (currGroupSeparator == null) {
                        currGroupSeparator = call_DEFAULT_GRP_SEPARATOR;
                    }
                    currDecSeparator = attributes.getValue(uri, DEC_SEPARATOR_NAME);
                    if (currDecSeparator == null) {
                        currDecSeparator = call_DEFAULT_DEC_SEPARATOR;
                    }
                }
                colDataExpecting = true;
                textBuffer = new StringBuffer();
            }
            colIdx++;
        }
    }

    /**
     * @see org.xml.sax.ContentHandler#characters(char[], int, int)
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        if (colDataExpecting) {
            textBuffer.append(ch, start, length);
        }
    }

    /**
     * @see org.xml.sax.ContentHandler#endElement(java.lang.String,
     *      java.lang.String, java.lang.String)
     */
    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        if (ROW_NAME.equals(localName)) {
            if (!currCriticalError) {
                executeStatement();
            } else {
                rowDisc++;
                // aggiunta DiscardCause al dhr...
                String msg = currentXSLMessage;

                dhr.addDiscardCause(new DiscardCause(rowCounter, msg));

                resultMessage.append("Data error on row ").append(rowCounter).append(": ").append(msg);
                resultMessage.append("SQL Statement Informations:\n").append(sqlStatementInfo);
                resultMessage.append("Record parameters:\n").append(dumpCurrentRowFields());
                resultStatus = STATUS_PARTIAL;
            }
        } else if (COL_NAME.equals(localName)) {
            CallableStatement cs = (CallableStatement) sqlStatementInfo.getStatement();
            try {
                if (!outOnly) {
                    colDataExpecting = false;
                    String text = textBuffer.toString();
                    if ((currentUUID != null) && (currentUUID.trim().length() > 0) && (text.length() == 0)) {
                        text = uuids.get(currentUUID);
                        if (text == null) {
                            text = currentUUID;
                        }
                    }
                    if (TIMESTAMP_TYPE.equals(currType) || DATE_TYPE.equals(currType)
                            || TIME_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            if (TIMESTAMP_TYPE.equals(currType))
                                setNull(cs, Types.TIMESTAMP);
                            else if (DATE_TYPE.equals(currType))
                                setNull(cs, Types.DATE);
                            else
                                setNull(cs, Types.TIME);
                            currentRowFields.add(null);
                        } else {
                            dateFormatter.applyPattern(currDateFormat);
                            Date formattedDate = dateFormatter.parse(text);
                            if (TIMESTAMP_TYPE.equals(currType)) {
                                Timestamp ts = new Timestamp(formattedDate.getTime());
                                setTimestamp(cs, ts);
                                currentRowFields.add(ts);
                            } else if (DATE_TYPE.equals(currType)) {
                                java.sql.Date d = new java.sql.Date(formattedDate.getTime());
                                setDate(cs, d);
                                currentRowFields.add(d);
                            } else {
                                java.sql.Time t = new java.sql.Time(formattedDate.getTime());
                                setTime(cs, t);
                                currentRowFields.add(t);
                            }
                        }
                    } else if (INTEGER_TYPE.equals(currType) || SMALLINT_TYPE.equals(currType)
                            || BIGINT_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            if (INTEGER_TYPE.equals(currType))
                                setNull(cs, Types.INTEGER);
                            else if (SMALLINT_TYPE.equals(currType))
                                setNull(cs, Types.SMALLINT);
                            else
                                setNull(cs, Types.BIGINT);
                            currentRowFields.add(null);
                        } else {
                            if (INTEGER_TYPE.equals(currType))
                                setInt(cs, Integer.parseInt(text, 10));
                            else if (SMALLINT_TYPE.equals(currType))
                                setShort(cs, Short.parseShort(text, 10));
                            else
                                setLong(cs, Long.parseLong(text, 10));
                            currentRowFields.add(text);
                        }
                    } else if (FLOAT_TYPE.equals(currType) || DOUBLE_TYPE.equals(currType)
                            || DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            if (DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType))
                                setNull(cs, Types.NUMERIC);
                            else if (FLOAT_TYPE.equals(currType))
                                setNull(cs, Types.FLOAT);
                            else
                                setNull(cs, Types.DOUBLE);
                            currentRowFields.add(null);
                        } else {
                            DecimalFormatSymbols dfs = numberFormatter.getDecimalFormatSymbols();
                            dfs.setDecimalSeparator(currDecSeparator.charAt(0));
                            dfs.setGroupingSeparator(currGroupSeparator.charAt(0));
                            numberFormatter.setDecimalFormatSymbols(dfs);
                            numberFormatter.applyPattern(currNumberFormat);
                            boolean isBigDecimal = numberFormatter.isParseBigDecimal();
                            try {
                                numberFormatter.setParseBigDecimal(true);
                                BigDecimal formattedNumber = (BigDecimal) numberFormatter.parse(text);
                                if (DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType)) {
                                    setBigDecimal(cs, formattedNumber);
                                    currentRowFields.add(formattedNumber);
                                } else if (FLOAT_TYPE.equals(currType)) {
                                    setFloat(cs, formattedNumber.floatValue());
                                    currentRowFields.add(formattedNumber.floatValue());
                                } else {
                                    setDouble(cs, formattedNumber.doubleValue());
                                    currentRowFields.add(formattedNumber.doubleValue());
                                }
                            } finally {
                                numberFormatter.setParseBigDecimal(isBigDecimal);
                            }
                        }
                    } else if (LONG_STRING_TYPE.equals(currType) || LONG_NSTRING_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            if (LONG_STRING_TYPE.equals(currType))
                                setNull(cs, Types.CLOB);
                            else
                                setNull(cs, Types.NCLOB);
                            currentRowFields.add(null);
                        } else {
                            if (LONG_STRING_TYPE.equals(currType)) {
                                setCharacterStream(cs, new StringReader(text));
                                currentRowFields.add(text);
                            } else {
                                setNCharacterStream(cs, new StringReader(text));
                                currentRowFields.add(text);
                            }
                        }
                    } else if (BASE64_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            setNull(cs, Types.BLOB);
                            currentRowFields.add(null);
                        } else {
                            byte[] data = text.getBytes();
                            data = Base64.getDecoder().decode(data);
                            ByteArrayInputStream bais = new ByteArrayInputStream(data);
                            setBinaryStream(cs, bais, data.length);
                            currentRowFields.add(text);
                        }
                    } else if (BINARY_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            setNull(cs, Types.BLOB);
                            currentRowFields.add(null);
                        } else {
                            byte[] data = text.getBytes();
                            ByteArrayInputStream bais = new ByteArrayInputStream(data);
                            setBinaryStream(cs, bais, data.length);
                            currentRowFields.add(text);
                        }
                    } else if (BOOLEAN_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            setNull(cs, Types.BOOLEAN);
                            currentRowFields.add(null);
                        } else {
                            setBoolean(cs, TextUtils.parseBoolean(text));
                            currentRowFields.add(text);
                        }
                    } else if (XML_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            setNull(cs, Types.SQLXML);
                            currentRowFields.add(null);
                        } else {
                            SQLXML xml = cs.getConnection().createSQLXML();
                            xml.setString(text);
                            setSQLXML(cs, xml);
                            currentRowFields.add(text);
                        }
                    } else if (NSTRING_TYPE.equals(currType)) {
                        if (text.equals("")) {
                            setNull(cs, Types.NVARCHAR);
                            currentRowFields.add(null);
                        } else {
                            setNString(cs, text);
                            currentRowFields.add(text);
                        }
                    } else {
                        if (text.equals("")) {
                            setNull(cs, Types.VARCHAR);
                            currentRowFields.add(null);
                        } else {
                            setString(cs, text);
                            currentRowFields.add(text);
                        }
                    }
                } else {
                    currentRowFields.add(currentUUID);
                }
            } catch (ParseException exc) {
                throw new SAXException(exc);
            } catch (SQLException exc) {
                OracleExceptionHandler.handleSQLException(exc);
                throw new SAXException(exc);
            }
        }
    }

    /**
     * @param cs
     * @param ts
     * @throws SQLException
     */
    private void setTimestamp(CallableStatement cs, Timestamp ts) throws SQLException {
        if (useName) {
            cs.setTimestamp(currName, ts);
        } else {
            cs.setTimestamp(colIdx, ts);
        }
    }

    private void setTime(CallableStatement cs, Time t) throws SQLException {
        if (useName) {
            cs.setTime(currName, t);
        } else {
            cs.setTime(colIdx, t);
        }
    }

    private void setDate(CallableStatement cs, java.sql.Date d) throws SQLException {
        if (useName) {
            cs.setDate(currName, d);
        } else {
            cs.setDate(colIdx, d);
        }
    }

    /**
     * @param cs
     * @param num
     * @throws SQLException
     */
    private void setInt(CallableStatement cs, int num) throws SQLException {
        if (useName) {
            cs.setInt(currName, num);
        } else {
            cs.setInt(colIdx, num);
        }
    }

    private void setLong(CallableStatement cs, long num) throws SQLException {
        if (useName) {
            cs.setLong(currName, num);
        } else {
            cs.setLong(colIdx, num);
        }
    }

    private void setShort(CallableStatement cs, short num) throws SQLException {
        if (useName) {
            cs.setShort(currName, num);
        } else {
            cs.setShort(colIdx, num);
        }
    }

    /**
     * @param cs
     * @param num
     * @throws SQLException
     */
    private void setFloat(CallableStatement cs, float num) throws SQLException {
        if (useName) {
            cs.setFloat(currName, num);
        } else {
            cs.setFloat(colIdx, num);
        }
    }

    private void setDouble(CallableStatement cs, double num) throws SQLException {
        if (useName) {
            cs.setDouble(currName, num);
        } else {
            cs.setDouble(colIdx, num);
        }
    }

    private void setBigDecimal(CallableStatement cs, BigDecimal num) throws SQLException {
        if (useName) {
            cs.setBigDecimal(currName, num);
        } else {
            cs.setBigDecimal(colIdx, num);
        }
    }

    /**
     * @param cs
     * @param bais
     * @param length
     * @throws SQLException
     */
    private void setBinaryStream(CallableStatement cs, ByteArrayInputStream bais, int length) throws SQLException {
        if (useName) {
            cs.setBinaryStream(currName, bais, length);
        } else {
            cs.setBinaryStream(colIdx, bais, length);
        }
    }

    private void setNCharacterStream(CallableStatement cs, StringReader sr) throws SQLException {
        if (useName) {
            cs.setNCharacterStream(currName, sr);
        } else {
            cs.setNCharacterStream(colIdx, sr);
        }
    }

    private void setCharacterStream(CallableStatement cs, StringReader sr) throws SQLException {
        if (useName) {
            cs.setCharacterStream(currName, sr);
        } else {
            cs.setCharacterStream(colIdx, sr);
        }
    }

    private void setSQLXML(CallableStatement cs, SQLXML xml) throws SQLException {
        if (useName) {
            cs.setSQLXML(currName, xml);
        } else {
            cs.setSQLXML(colIdx, xml);
        }
    }

    private void setBoolean(CallableStatement cs, boolean b) throws SQLException {
        if (useName) {
            cs.setBoolean(currName, b);
        } else {
            cs.setBoolean(colIdx, b);
        }
    }

    private void setNString(CallableStatement cs, String text) throws SQLException {
        if (useName) {
            cs.setNString(currName, text);
        } else {
            cs.setNString(colIdx, text);
        }
    }

    /**
     * @param cs
     * @param text
     * @throws SQLException
     */
    private void setString(CallableStatement cs, String text) throws SQLException {
        if (useName) {
            cs.setString(currName, text);
        } else {
            cs.setString(colIdx, text);
        }
    }

    /**
     * @param cs
     * @throws SQLException
     */
    private void setNull(CallableStatement cs, int type) throws SQLException {
        if (useName) {
            cs.setNull(currName, type);
        } else {
            cs.setNull(colIdx, type);
        }
    }

    /**
     * @throws SAXException
     */
    @Override
    protected void executeStatement() throws SAXException {
        try {
            if (sqlStatementInfo != null) {
                super.executeStatement();
                handleOutput();
            }
        } catch (DBOException exc) {
            logger.error("Record parameters:\n" + dumpCurrentRowFields());
            logger.error("SQL Statement Informations:\n" + sqlStatementInfo);
            logger.error("DBOException error on row " + getRowCounter() + ": " + exc.getMessage());
            throw new SAXException(exc);
        }
    }
}