Application: gvSIG desktop

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2008 Infrastructures and Transports Department

 * of the Valencian Government (CIT)

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 2

 * of the License, or (at your option) any later version.

 *

 * This program 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 General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 */

/*

 * AUTHORS (In addition to CIT):

 * 2009 Prodevelop S.L. main development

 */

package org.gvsig.fmap.dal.store.oracle;

import java.security.InvalidParameterException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import oracle.sql.STRUCT;

import oracle.sql.TIMESTAMP;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.exception.OpenException;

import org.gvsig.fmap.dal.exception.ReadException;

import org.gvsig.fmap.dal.exception.ValidateDataParametersException;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureIndexes;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.exception.CreateFeatureException;

import org.gvsig.fmap.dal.feature.impl.DefaultFeatureIndexes;

import org.gvsig.fmap.dal.feature.impl.DefaultFeatureStore;

import org.gvsig.fmap.dal.feature.spi.AbstractFeatureStoreProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices;

import org.gvsig.fmap.dal.feature.spi.FeatureSetProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureStoreProviderServices;

import org.gvsig.fmap.dal.resource.ResourceAction;

import org.gvsig.fmap.dal.resource.exception.ResourceExecuteException;

import org.gvsig.fmap.dal.spi.DataStoreProviderServices;

import org.gvsig.fmap.dal.store.db.DBHelper;

import org.gvsig.fmap.dal.store.jdbc.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreProviderWriter;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecutePreparedSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCPreparingSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCUpdateWithoutChangesException;

import org.gvsig.fmap.dal.store.oracle.index.OracleFeatureIndex;

import org.gvsig.fmap.dal.store.oracle.index.OracleRemoteFeatureIndexProvider;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.Geometry.SUBTYPES;

import org.gvsig.fmap.geom.operation.towkb.ToWKB;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dataTypes.CoercionException;

import org.gvsig.tools.dataTypes.DataTypesManager;

import org.gvsig.tools.dataTypes.impl.coercion.CoerceToDateTime;

import org.gvsig.tools.dynobject.DynObject;

import org.gvsig.tools.exception.BaseException;

/**

 * Oracle Provider

 * 

 * @author vsanjaime

 * 

 */

public class OracleStoreProvider extends JDBCStoreProviderWriter {

        public final static Logger logger = LoggerFactory

                        .getLogger(OracleStoreProvider.class);

        public static String NAME = "Oracle";

        public static String DESCRIPTION = "Oracle source";

        public static final String METADATA_DEFINITION_NAME = NAME;

        private boolean oracleIndexSet = false;

        private Map selectedFields = null;

        /*

        protected static void registerDynClass() {

                DynObjectManager dynman = ToolsLocator.getDynObjectManager();

                DynClass dynClass;

                if (DYNCLASS == null) {

                        dynClass = dynman.add(DYNCLASS_NAME, DESCRIPTION);

                        // dynClass.extend(dynman.get(FeatureStore.DYNCLASS_NAME));

                        DYNCLASS = dynClass;

                }

        }

        */

        public OracleStoreProvider(OracleStoreParameters params,

                        DataStoreProviderServices storeServices) throws InitializeException {

                super(params, storeServices,

                                DBHelper.newMetadataContainer(METADATA_DEFINITION_NAME));

                String defaultGeometryAttributeName;

                try {

                        defaultGeometryAttributeName = this.getFeatureStore()

                                .getDefaultFeatureType()

                                        .getDefaultGeometryAttributeName();

                } catch (DataException e) {

                        throw new InitializeException(e);

                }

                String ora_srid =

                        getOraHelper().getOraTableSrid(params, defaultGeometryAttributeName);

                this.setDynValue(OracleStoreParameters.ORA_TABLE_SRID_KEY, ora_srid);

                String sFields = (String)getParameters().getDynValue("fields");

                if (sFields != null){

                    selectedFields = new HashMap();

                    String[] fields = sFields.split(",");

                    for (int i=0 ; i<fields.length ; i++){

                        selectedFields.put(fields[i], Boolean.TRUE);

                    }

                }

        }

        /**

         * Default Constructor.

         *

         * @param params

         * @param storeServices

         * @param metadata

         */

        protected OracleStoreProvider(DataStoreParameters params,

                        DataStoreProviderServices storeServices, DynObject metadata) throws InitializeException {

                super((JDBCStoreParameters) params, storeServices, metadata);

        }

        /**

         * Constructor when cannot create metada in constrution time. <br>

         * <br>

         * <strong>Note: </strong> Don't use it if not is necesary. Set metada

         * <strong>as soon as posible</strong> by

         * {@link AbstractFeatureStoreProvider#setMetadata(DynObject)}

         * 

         * @param params

         * @param storeServices

         */

        protected OracleStoreProvider(DataStoreParameters params,

                        DataStoreProviderServices storeServices) throws InitializeException {

                this((JDBCStoreParameters) params, storeServices, null);

        }

        private OracleStoreParameters getOracleStoreParameters() {

                return (OracleStoreParameters) this.getParameters();

        }

        protected JDBCHelper createHelper() throws InitializeException {

                return new OracleHelper(this, getOracleStoreParameters());

        }

        public void open() throws OpenException {

                super.open();

                // =============== add remote oracle geometry index to prevent default index

                if (!oracleIndexSet) {

                        DefaultFeatureStore dfs = null;

                        if (!(getFeatureStore() instanceof DefaultFeatureStore)) {

                                logger.warn("Feature store not DefaultFeatureStore: " + dfs.getClass().getName());

                                return;

                        }

                        dfs = (DefaultFeatureStore) getFeatureStore();

                        FeatureType ft = null;

            try {

                ft = dfs.getDefaultFeatureType();

            } catch (DataException e) {

                throw new OpenException(getFeatureStore().getName(), e);

            }                        

            //It there is not a geometry the index is not created

                        if (ft.getDefaultGeometryAttributeName() != null){

                            FeatureIndexes findexes = dfs.getIndexes();

                            if (findexes instanceof DefaultFeatureIndexes) {

                                    DefaultFeatureIndexes dfi = (DefaultFeatureIndexes) findexes;                                   

                                    dfi.addIndex(

                                                    new OracleFeatureIndex(

                                                                    (FeatureStoreProviderServices) getFeatureStore(),

                                                                    ft,

                                                                    new OracleRemoteFeatureIndexProvider(dfs),

                                                                    ft.getDefaultGeometryAttributeName(),

                                                                    OracleRemoteFeatureIndexProvider.NAME));

                            }

                        }

                        oracleIndexSet = true;

                }

                // ===============                 

        }

        protected String fixFilter(String filter) {

                // filter was created by a class in oracle plugin, should not need fix

                return filter;

        }

        public String getName() {

                return NAME;

        }

        public int getOIDType() {

                return DataTypes.LONG;

        }

        private static long ora_oid = 100000000;

        public Object createNewOID() {

                return new Long(ora_oid++);

        }

        public FeatureSetProvider createSet(FeatureQuery query,

                        FeatureType featureType) throws DataException {

                return new OracleSetProvider(this, query, featureType);

        }

        public DataServerExplorer getExplorer() throws ReadException {

                DataManager manager = DALLocator.getDataManager();

                OracleServerExplorerParameters exParams;

                OracleStoreParameters params = getOracleStoreParameters();

                try {

                        exParams = (OracleServerExplorerParameters) manager

                                        .createServerExplorerParameters(OracleServerExplorer.NAME);

                        exParams.setUrl(params.getUrl());

                        exParams.setHost(params.getHost());

                        exParams.setPort(params.getPort());

                        exParams.setDBName(params.getDBName());

                        exParams.setUser(params.getUser());

                        exParams.setPassword(params.getPassword());

                        exParams.setCatalog(params.getCatalog());

                        exParams.setSchema(params.getSchema());

                        exParams.setJDBCDriverClassName(params.getJDBCDriverClassName());

                        exParams.setUseSSL(params.getUseSSL());

                        exParams.setOraDriverType(params.getOraDriverType());

                        return manager.openServerExplorer(OracleServerExplorer.NAME, exParams);

                } catch (DataException e) {

                        throw new ReadException(this.getName(), e);

                } catch (ValidateDataParametersException e) {

                        throw new ReadException(this.getName(), e);

                }

        }

        public boolean allowAutomaticValues() {

                return true;

        }

        public boolean hasGeometrySupport() {

                return true;

        }

        protected OracleHelper getOraHelper() {

                return (OracleHelper) getHelper();

        }

        public boolean canWriteGeometry(int geometryType, int geometrySubtype)

                        throws DataException {

                FeatureType type = getFeatureStore().getDefaultFeatureType();

                FeatureAttributeDescriptor geomAttr = type.getAttributeDescriptor(type

                                .getDefaultGeometryAttributeName());

                if (geomAttr == null) {

                        return false;

                }

                if (geometrySubtype != geomAttr.getGeometrySubType()) {

                        return false;

                }

                switch (geomAttr.getGeometryType()) {

                case Geometry.TYPES.GEOMETRY:

                        return true;

                case Geometry.TYPES.MULTISURFACE:

                        return geometryType == Geometry.TYPES.MULTISURFACE;

                case Geometry.TYPES.MULTIPOINT:

                        return geometryType == Geometry.TYPES.MULTIPOINT;

                case Geometry.TYPES.MULTICURVE:

                        return geometryType == Geometry.TYPES.MULTICURVE;

                case Geometry.TYPES.MULTISOLID:

                        return geometryType == Geometry.TYPES.MULTISOLID;

                case Geometry.TYPES.SURFACE:

                        return geometryType == Geometry.TYPES.SURFACE;

                case Geometry.TYPES.POINT:

                        return geometryType == Geometry.TYPES.POINT;

                case Geometry.TYPES.CURVE:

                        return geometryType == Geometry.TYPES.CURVE;

                case Geometry.TYPES.SOLID:

                        return geometryType == Geometry.TYPES.SOLID;

                default:

                        return geometryType == geomAttr.getGeometryType();

                }

        }

        protected void addToListFeatureValues(FeatureProvider featureProvider,

                        FeatureAttributeDescriptor attrOfList,

                        FeatureAttributeDescriptor attr, List values) throws DataException {

                // geometry

                if (attr.getDataType().getType() == DataTypes.GEOMETRY) {

                        Geometry geom = (Geometry) featureProvider.get(attr.getIndex());

                        String ora_srid_str = null;

                        boolean is_geo = false;

                        boolean has_srid = false;

                        Object ora_srid = getDynValue("ora_table_srid");

                        if (ora_srid != null) {

                            if (ora_srid instanceof String) {

                                ora_srid_str = (String) ora_srid; 

                            } else {

                        if (ora_srid instanceof Integer) {

                            ora_srid_str = "" + ((Integer) ora_srid).intValue(); 

                        }

                            }

                        }

                        has_srid = ora_srid_str != null && ora_srid_str.compareToIgnoreCase("0") != 0; 

                        is_geo = OracleUtils.getIsGCS(

                            ora_srid_str, has_srid);

                        // OracleUtils.is

                        STRUCT stru = OracleUtils.buildSTRUCT(

                            geom, 0, helper.getConnection(),

                            ora_srid_str, has_srid, false, is_geo);

                        values.add(stru);

                } else {

                        super.addToListFeatureValues(featureProvider, attrOfList, attr, values);

                }

        }

        protected String getSqlStatementAddField(FeatureAttributeDescriptor attr,

                        List additionalStatement) throws DataException {

                if (attr.getDataType().getType() == DataTypes.GEOMETRY) {

                        OracleStoreParameters params = getOracleStoreParameters();

                        additionalStatement.add(((OracleHelper) helper)

                                        .getSqlGeometryFieldAdd(attr, params.getTable(), params

                                                        .getSchema()));

                }

                return super.getSqlStatementAddField(attr, additionalStatement);

        }

        protected String getSqlStatementDropField(FeatureAttributeDescriptor attr,

                        List additionalStatement) {

                String result = super.getSqlStatementDropField(attr,

                                additionalStatement);

                if (attr.getDataType().getType() == DataTypes.GEOMETRY) {

                        additionalStatement.add(getSqlGeometryFieldDrop(attr));

                }

                return result;

        }

        protected List<String> getSqlStatementAlterField(

                        FeatureAttributeDescriptor attrOrg,

                        FeatureAttributeDescriptor attrTrg, List additionalStatement)

                        throws DataException {

                List<String> actions = new ArrayList<String>();

                StringBuilder strb;

                OracleStoreParameters params = getOracleStoreParameters();

                // diferent column type

                if (attrOrg.getDataType() != attrTrg.getDataType()) {

                        strb = new StringBuilder();

                        strb.append("MODIFY (");

                        strb.append(helper.escapeFieldName(attrTrg.getName()));

                        strb.append(" ");

                        strb.append(helper.getSqlColumnTypeDescription(attrTrg));

                        strb.append(")");

                        if (attrOrg.getDataType().getType() == DataTypes.GEOMETRY) {

                                additionalStatement.add(getSqlGeometryFieldDrop(attrOrg));

                        }

                        if (attrTrg.getDataType().getType() == DataTypes.GEOMETRY) {

                                additionalStatement.addAll(((OracleHelper) helper)

                                                .getSqlGeometryFieldAdd(attrTrg, params.getTable(),

                                                                params.getSchema()));

                        }

                        actions.add(strb.toString());

                }

                if (attrOrg.allowNull() != attrTrg.allowNull()) {

                        strb = new StringBuilder();

                        strb.append("MODIFY (");

                        strb.append(helper.escapeFieldName(attrTrg.getName()));

                        strb.append(" ");

                        if (attrTrg.allowNull()) {

                                strb.append("SET ");

                        } else {

                                strb.append("DROP ");

                        }

                        strb.append("NOT NULL)");

                        actions.add(strb.toString());

                }

                if (attrOrg.getDefaultValue() != attrTrg.getDefaultValue()) {

                        if (attrTrg.getDefaultValue() == null) {

                                strb = new StringBuilder();

                                strb.append("MODIFY (");

                                strb.append(helper.escapeFieldName(attrTrg.getName()));

                                strb.append(" DROP DEFAULT)");

                                actions.add(strb.toString());

                        } else if (!attrTrg.getDefaultValue().equals(

                                        attrOrg.getDefaultValue())) {

                                // ALTER [ COLUMN ] column DROP DEFAULT

                                strb = new StringBuilder();

                                strb.append("MODIFY (");

                                strb.append(helper.escapeFieldName(attrTrg.getName()));

                                strb.append(" SET DEFAULT ");

                                strb.append(helper.dalValueToJDBC(attrTrg, attrTrg

                                                .getDefaultValue()));

                                strb.append(")");

                                actions.add(strb.toString());

                        }

                }

                if (attrOrg.getDataType() == attrTrg.getDataType()

                                && attrTrg.getDataType().getType() == DataTypes.GEOMETRY) {

                        // TODO Checks SRS and GeomType/Subtype

                }

                return actions;

        }

        private Object getSqlGeometryFieldDrop(FeatureAttributeDescriptor attr) {

                StringBuilder strb = new StringBuilder();

                OracleStoreParameters params = getOracleStoreParameters();

                strb.append("DELETE FROM ");

                strb.append(OracleValues.USER_ORACLE_GEOMETADATA_VIEW);

                strb.append(" WHERE ");

                strb.append(OracleValues.USER_ORACLE_GEOMETADATA_VIEW_TABLE_NAME);

                strb.append(" = '");

                strb.append(params.getTable());

                strb.append("' AND ");

                strb.append(OracleValues.USER_ORACLE_GEOMETADATA_VIEW_COLUMN_NAME);

                strb.append(" = '");

                strb.append(attr.getName());

                strb.append("'");

                return strb.toString();

        }

        protected void loadFeatureProviderValue(FeatureProvider data, ResultSet rs,

                        FeatureAttributeDescriptor attr) throws DataException {

                if (attr.getDataType().getType() == DataTypes.GEOMETRY) {

                        try {

                                Object geo_str_obj = rs.getObject(attr.getIndex() + 1);

                                if (geo_str_obj == null) {

                                        data.set(attr.getIndex(), OracleUtils.createNullGeometry(SUBTYPES.GEOM2D));

                                } else {

                                        STRUCT geo_str = (STRUCT) geo_str_obj;

                                        // IProjection proj = attr.getSRS();

                                        // OracleUtils.

                                        Geometry geom = OracleUtils.getGeometry(

                                                        geo_str,

                                                        false,

                                                        false,

                                                        "",

                                                        helper.getConnection());

                                        data.set(attr.getIndex(), geom);

                                }

                        } catch (SQLException e) {

                                throw new JDBCSQLException(e);

                        } catch (BaseException e) {

                                throw new ReadException(getName(), e);

                        }

                } else {

                        try {

                                Object obj_data = rs.getObject(attr.getName());

                                if (obj_data != null) {

                                    obj_data = coerce(attr, obj_data);

                                }

                                data.set(attr.getName(), obj_data);

                                if (attr.isPrimaryKey()) {

                                        try {

                                                obj_data = getDataTypesManager().coerce(DataTypes.LONG, obj_data);

                                                data.setOID(obj_data);

                                        } catch (Exception ex) {

                                                logger.warn("Unable to convert OID to Long. Auto-increm will be used.");

                                        }

                                }

                        } catch (Exception e) {

                                throw new CreateFeatureException(e, NAME);

                        }

                }

        }

        /**

         * Load data form a resulset.<br>

         *

         * <strong>Note:</strong><br>

         * this method have to perform <code>resouceBegin</code> at the begining and

         * <code>resourceEnd</code> at the end of execution.

         *

         *

         * @param data

         * @param resulsetID

         *

         * @return

         * @throws DataException

         */

        public void loadFeatureProvider(final FeatureProvider data, final int resultsetID)

                        throws DataException {

                getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                ResultSet rs = getResultSet(resultsetID);

                                FeatureAttributeDescriptor attr;

                                FeatureAttributeDescriptor[] atts = data.getType().getAttributeDescriptors();

                                int len = atts.length;

                                if (selectedFields == null){

                                    for (int i=0; i<len; i++) {

                                            attr = atts[i];

                                            loadFeatureProviderValue(data, rs, attr);

                                    }

                                }else{

                                    for (int i=0; i<len; i++) {

                        attr = atts[i];

                        if (selectedFields.containsKey(attr.getName())){

                            loadFeatureProviderValue(data, rs, attr);

                        }

                    }

                                }

                                // this should not happen, loadFeatureProviderValue checks PK

                                if (data.getOID() == null) {

                                        data.setOID(createNewOID());

                                }

                                return null;

                        }

                });

        }

        protected ResultSet createNewResultSet(final String sql,

                        final Object[] values, final int fetchSize)

                        throws DataException {

                this.open();

                return (ResultSet) getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                Connection conn = null;

                                PreparedStatement st = null;

                                ResultSet rs = null;

                                try {

                                        conn = helper.getConnection();

                                        conn.setAutoCommit(false);

                                        st = conn.prepareStatement(sql);

                                        if (values != null) {

                                                Object value;

                                                for (int i = 0; i < values.length; i++) {

                                                        value = values[i];

                                                        if (value instanceof Geometry) {

                                                                byte[] bytes;

                                                                try {

                                                                        bytes =

                                                                                        (byte[]) ((Geometry) value).invokeOperation(

                                                                                                        ToWKB.CODE, null);

                                                                } catch (BaseException e) {

                                                                        throw new InvalidParameterException();

                                                                }

                                                                st.setBytes(i + 1, bytes);

                                                        }

                                                        st.setObject(i + 1, value);

                                                }

                                        }

                                        if (fetchSize > 0) {

                                                st.setFetchSize(fetchSize);

                                        }

                                        rs = st.executeQuery();

                                        if (fetchSize > 0) {

                                                rs.setFetchSize(fetchSize);

                                        }

                                        return rs;

                                } catch (SQLException e) {

                                        try {

                                                rs.close();

                                        } catch (Exception e1) {

                                        }

                                        try {

                                                st.close();

                                        } catch (Exception e1) {

                                        }

                                        /*

                                        try {

                                                conn.close();

                                        } catch (Exception e1) {

                                        }

                                        */

                                        throw new JDBCExecutePreparedSQLException(sql,values,e);

                                }

                        }

                });

        }

        protected void closeResulset(final ResultSet rs) throws JDBCException,

        ResourceExecuteException {

                getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                Statement st = rs.getStatement();

                                Connection con = st.getConnection();

                                try {

                                        rs.close();

                                } finally {

                                        // TODO revisar esto

                                        try{ st.close();  } catch (Exception ex){ };

                                        // try{ con.close(); } catch (Exception ex){ };

                                }

                                return null;

                        }

                });

        }

        public String compoundSelect(FeatureType type, String filter, String order,

                        long limit, long offset) throws DataException {

                StringBuilder sql = new StringBuilder();

                JDBCStoreParameters params = getJDBCParameters();

                if (directSQLMode) {

                        sql.append(params.getSQL());

                        sql.append(' ');

                } else {

                    String sFields = (String)getParameters().getDynValue("fields");

            if (sFields == null){

                FeatureAttributeDescriptor[] fields = type

                .getAttributeDescriptors();

                // Select

                sql.append("Select ");

                for (int i = 0; i < fields.length - 1; i++) {

                    sql.append(helper.getSqlFieldName(fields[i]));

                    sql.append(", ");

                }

                sql.append(helper.getSqlFieldName(fields[fields.length - 1]));

                sql.append(' ');

                FeatureAttributeDescriptor[] pkFields = getStoreServices()

                .getProviderFeatureType(type.getId()).getPrimaryKey();

                if (pkFields != null && pkFields.length > 0) {

                    // checks for pk fields are in select

                    boolean toAdd;

                    for (int i = 0; i < pkFields.length; i++) {

                        toAdd = true;

                        for (int j = 0; j < fields.length; j++) {

                            if (pkFields[i].getName().equals(fields[j].getName())) {

                                toAdd = false;

                                break;

                            }

                            if (toAdd) {

                                sql.append(", ");

                                sql.append(helper.getSqlFieldName(pkFields[i]));

                            }

                        }

                    }

                    sql.append(' ');

                }

            }else{

                sql.append("Select ");

                sql.append(sFields);

                sql.append(' ');

            }

                        // table

                        sql.append("from ");

                        sql.append(params.tableID());

                        sql.append(' ');

                        // Where

                        appendWhere(sql, filter);

                        // Order

                        /*

                        if ((params.getBaseOrder() != null && params.getBaseOrder()

                                        .length() != 0)

                                        || (order != null && order.length() != 0)) {

                                sql.append("order by ");

                                if (order != null && order.length() != 0) {

                                        // order

                                        sql.append(order);

                                } else {

                                        // initial order

                                        sql.append(params.getBaseOrder());

                                }

                                sql.append(' ');

                        }

                        */

                }

                // limit offset

                /*

                if (limit > 0 || offset > 0) {

                        sql.append(helper.compoundLimitAndOffset(limit,offset));

                }^

                */

                logger.info("Executing SQL: " + sql.toString());

                return sql.toString();

        }

        private Object coerce(FeatureAttributeDescriptor att, Object v) throws CoercionException {

                if (!att.getObjectClass().isInstance(v)) {

                    if (v instanceof TIMESTAMP && att.getDataType().getCoercion() instanceof CoerceToDateTime) {

                        TIMESTAMP ora_ts = (TIMESTAMP) v;

                        try {

                            return ora_ts.timestampValue(); 

                        } catch (Exception ex) {

                            throw new CoercionException(ex);

                        }

                    } else {

                    return getDataTypesManager().coerce(att.getType(), v);

                    }

                } else {

                        return v;

                }

        }

        private static DataTypesManager dtManager = null;

        protected DataTypesManager getDataTypesManager() {

                if( dtManager==null ) {

                        dtManager = ToolsLocator.getDataTypesManager();

                }

                return dtManager;

        }

        @Override

        protected FeatureProvider internalGetFeatureProviderByReference(

                        FeatureReferenceProviderServices reference,

                        FeatureType featureType)

                        throws DataException {

                StringBuilder filter = new StringBuilder();

                FeatureAttributeDescriptor[] pk =

                                getFeatureStore().getFeatureType(featureType.getId())

                                                .getPrimaryKey();

                List<Object> values = new ArrayList<Object>();

                int i;

                Object id_obj = null;

                // assumes PK with one component (Long) !!

                id_obj = OracleUtils.getId(reference, featureType);

                for (i = 0; i < pk.length - 1; i++) {

                        values.add(

                                        helper.dalValueToJDBC(pk[i],

                                        id_obj));

                        filter.append(helper.getSqlFieldName(pk[i]));

                        filter.append(" = ? AND ");

                }

                values.add(helper.dalValueToJDBC(pk[i],

                                id_obj));

                filter.append(helper.getSqlFieldName(pk[i]));

                filter.append(" = ? ");

                String sql = compoundSelect(featureType, filter.toString(), null, 1, 0);

                FeatureProvider data;

                int rsId = createResultSet(sql, values.toArray(), 1);

                try {

                        if (!resulsetNext(rsId)) {

                                throw new RuntimeException("Reference Not found");

                        }

                        data = createFeatureProvider(featureType);

                        loadFeatureProvider(data, rsId);

                } finally {

                        closeResulset(rsId);

                }

                return data;

        }

        protected void executeRemovePreparedStatement(Connection conn, String sql,

            List<FeatureAttributeDescriptor> attributes, Iterator<FeatureReferenceProviderServices> featureReferences) throws DataException {

            PreparedStatement st;

            try {

                st = conn.prepareStatement(sql);

            } catch (SQLException e) {

                throw new JDBCPreparingSQLException(sql, e);

            }

            try {

                List<Object> values = new ArrayList<Object>();

                FeatureReferenceProviderServices featureRef;

                //              FeatureType featureType;

                while (featureReferences.hasNext()) {

                    st.clearParameters();

                    featureRef = featureReferences.next();

                    values.clear();                   

                    Iterator<FeatureAttributeDescriptor> iter = attributes.iterator();

                    FeatureAttributeDescriptor attr;

                    while (iter.hasNext()) {

                        attr =  iter.next();

                        if (attr.isPrimaryKey()){

                            values.add(helper.dalValueToJDBC(attr, featureRef.getFeature().get(attr.getIndex())));

                        }                       

                    }

                    for (int i = 0; i < values.size(); i++) {

                        st.setObject(i + 1, values.get(i));

                    }

                    try {

                        int nAffected =st.executeUpdate();

                        if (nAffected == 0) {

                            throw new JDBCUpdateWithoutChangesException(sql, values);

                        }

                        if (nAffected > 1){

                            logger.warn("Remove statement affectst to {} rows: {}",

                                nAffected, sql);

                        }

                    } catch (SQLException e) {

                        throw new JDBCExecutePreparedSQLException(sql, values, e);

                    }

                }

            } catch (SQLException e) {

                throw new JDBCSQLException(e);

            } finally {

                try {st.close();} catch (SQLException e) {  };

            }

        }

}