# TypeManager.java

    /*
    ** Module   : TypeManager.java
    ** Abstract : Provides converter objects for marshaling between native/BDT to SQL values.
    **
    ** Copyright (c) 2019-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 OM  20191101 Created initial version. Basic BDT support in both direction.
    **     CA  20200622 Small performance improvement - use IdentityHashMap if the key is java.lang.Class.
    ** 002 CA  20201214 Fixed setByteArrayParameter when the value is null - the type is VARBINARY.
    **     CA  20210305 The object and handle fields have a Long representation at the SQL table field.
    **     OM  20220813 Attempt to patch prepareUrl(). Probably needs to be dialect aware.
    **     OM  20220927 Made prepareUrl() dialect-aware (syntax + specific ports).
    **     OM  20221117 Some properties (datetime-tz) may take a different number of positional parameters when
    **                  used in insert/update or where predicate.
    ** 003 CA  20240331 Use logical constants for TRUE, FALSE, UNKNOWN, within internal FWD runtime, so logical
    **                  type checks must include sub-classes, too.
    ** 004 CA  20241107 Added character constant support.
    ** 005 ICP 20250123 Added support for integer and int64 constants.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 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 Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.orm.types;

    import com.goldencode.p2j.oo.lang.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.util.*;
    import java.io.*;
    import java.math.*;
    import java.sql.*;
    import java.sql.Date;
    import java.time.*;
    import java.util.*;
    import java.util.concurrent.*;
    import java.util.function.*;
    import java.util.regex.*;

    /**
     * This manager provides objects that handle two-way conversion between SQL values to BDT.
     */
    public class TypeManager
    {
       /**
        * The registry with all known converters. Since they implement the both the {@code Converter}
        * and {@code ParameterSetter}, they will act in both direction, converting pieces of data
        * coming from SQL to BDT values and vice-versa.
        */
       private static final Map<Class<? extends BaseDataType>, DataHandler> typeHelpers = new IdentityHashMap<>();

       /**
        * The registry with all known parameter type handlers. The {@code ParameterSetter}
        * implementations allow the converted value to be set as parameter to a provided
        * {@code PreparedStatement}.
        */
       private static final Map<Class<?>, ParameterSetter> paramSetters = new IdentityHashMap<>();

       /** Dialect-specific functions to create a {@link Blob} instance. */
       private static final Map<String, BiFunction<Connection, byte[], Blob>> blobCreators = new ConcurrentHashMap<>();

       /** Dialect-specific functions to create a {@link Clob} instance. */
       private static final Map<String, BiFunction<Connection, String, Clob>> clobCreators = new ConcurrentHashMap<>();

       static
       {
          Int64Type int64Type = new Int64Type();
          DateType dateType = new DateType(int64Type);

          // FWD data types
          typeHelpers.put(blob.class, new BlobType());
          typeHelpers.put(character.class, new CharacterType());
          typeHelpers.put(clob.class, new ClobType());
          typeHelpers.put(comhandle.class, new ComhandleType());
          typeHelpers.put(datetimetz.class, new DatetimetzType(dateType, int64Type));
          typeHelpers.put(datetime.class, new DatetimeType(dateType, int64Type));
          typeHelpers.put(date.class, dateType);
          typeHelpers.put(decimal.class, new DecimalType());
          typeHelpers.put(handle.class, new HandleType(int64Type));
          typeHelpers.put(int64.class, int64Type);
          typeHelpers.put(integer.class, new IntegerType(int64Type));
          typeHelpers.put(logical.class, new LogicalType());
          typeHelpers.put(object.class, new ObjectType(int64Type));
          typeHelpers.put(raw.class, new RawType());
          typeHelpers.put(recid.class, new RecidType(int64Type));
          typeHelpers.put(rowid.class, new RowidType(int64Type));
          // BDT constants
          typeHelpers.put(logical.logicalConstant.class, new LogicalType());
          typeHelpers.put(character.characterConstant.class, new CharacterType());
          typeHelpers.put(integer.integerConstant.class, new IntegerType(int64Type));
          typeHelpers.put(int64.int64Constant.class, int64Type);
          // dynamic FWD classes
          typeHelpers.put(Text.class, new CharacterType());
          typeHelpers.put(NumberType.class, new NumericType());

          // setters for BDT parameters in queries
          paramSetters.put(blob.class, TypeManager::setFwdBlobParameter);
          paramSetters.put(character.class, TypeManager::setFwdCharacterParameter);
          paramSetters.put(clob.class, TypeManager::setFwdClobParameter);
          paramSetters.put(comhandle.class, TypeManager::setFwdComhandleParameter);
          paramSetters.put(datetimetz.class, TypeManager::setFwdDatetimetzParameter);
          paramSetters.put(datetime.class, TypeManager::setFwdDatetimeParameter);
          paramSetters.put(date.class, TypeManager::setFwdDateParameter);
          paramSetters.put(decimal.class, TypeManager::setFwdDecimalParameter);
          paramSetters.put(handle.class, TypeManager::setFwdHandleParameter);
          paramSetters.put(int64.class, TypeManager::setFwdInt64Parameter);
          paramSetters.put(integer.class, TypeManager::setFwdIntegerParameter);
          paramSetters.put(logical.class, TypeManager::setFwdLogicalParameter);
          paramSetters.put(object.class, TypeManager::setFwdObjectParameter);
          paramSetters.put(raw.class, TypeManager::setFwdRawParameter);
          paramSetters.put(recid.class, TypeManager::setFwdRecidParameter);
          paramSetters.put(rowid.class, TypeManager::setFwdRowidParameter);
          // BDT constants
          paramSetters.put(logical.logicalConstant.class, TypeManager::setFwdLogicalParameter);
          paramSetters.put(character.characterConstant.class, TypeManager::setFwdCharacterParameter);
          paramSetters.put(integer.integerConstant.class, TypeManager::setFwdIntegerParameter);
          paramSetters.put(int64.int64Constant.class, TypeManager::setFwdInt64Parameter);

          // add Java native data types:
          paramSetters.put(Blob.class, TypeManager::setBlobParameter);
          paramSetters.put(Clob.class, TypeManager::setClobParameter);
          paramSetters.put(Integer.class, TypeManager::setIntegerParameter);
          paramSetters.put(Long.class, TypeManager::setLongParameter);
          paramSetters.put(String.class, TypeManager::setStringParameter);
          paramSetters.put(Boolean.class, TypeManager::setBooleanParameter);
          paramSetters.put(Double.class, TypeManager::setDoubleParameter);
          paramSetters.put(Float.class, TypeManager::setFloatParameter);

          // and other support types:
          paramSetters.put(byte[].class, TypeManager::setByteArrayParameter);
          paramSetters.put(BigDecimal.class, TypeManager::setBigDecimalParameter);
          paramSetters.put(java.util.Date.class, TypeManager::setUtilDateParameter);
          paramSetters.put(java.sql.Timestamp.class, TypeManager::setTimestampParameter);
          paramSetters.put(java.sql.Date.class, TypeManager::setSqlDateParameter);
          paramSetters.put(java.time.OffsetDateTime.class, TypeManager::setOffsetDateTimeParameter);
       }

       /**
        * Gets a parameter handler for a specific type. The returned {@link ParameterSetter} expects a
        * {@link BaseDataType} of {@code type} type to be passed as the 3rd argument to its
        * {@code setParameter} when the method is invoked.
        *
        * @param   type
        *          The type of the argument that needs to be set in a {@code PreparedStatement}.
        *
        * @return  An object which can set a parameter of a specified type in a query.
        */
       public static ParameterSetter getTypeHandler(Class<?> type)
       {
          return paramSetters.get(type);
       }

       /**
        * Gets a converter for a specific target {@code BaseDataType}.
        *
        * @param   type
        *          The class to which the conversion needs to be performed.
        *
        * @return  A {@code Converter} that will handle conversion to the requested type.
        */
       public static DataHandler getDataHandler(Class<? extends BaseDataType> type)
       {
          return typeHelpers.get(type);
       }

       /**
        * Add a dialect-specific function to create a {@link Blob} instance.
        *
        * @param    url
        *           The JDBC URL for the database connection.
        * @param    blobCreator
        *           The dialect-specific function to create a {@link Blob} instance.
        */
       public static void addBlobCreator(String url, BiFunction<Connection, byte[], Blob> blobCreator)
       {
          blobCreators.putIfAbsent(prepareUrl(url), blobCreator);
       }

       /**
        * Add a dialect-specific function to create a {@link Clob} instance.
        *
        * @param    url
        *           The JDBC URL for the database connection.
        * @param    clobCreator
        *           The dialect-specific function to create a {@link Clob} instance.
        */
       public static void addClobCreator(String url, BiFunction<Connection, String, Clob> clobCreator)
       {
          clobCreators.putIfAbsent(prepareUrl(url), clobCreator);
       }

       /**
        * Sets a value assumed of type {@code Integer} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Integer}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setIntegerParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.INTEGER);
          }
          else
          {
             stmt.setInt(index, (Integer) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Long} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Long}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setLongParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.BIGINT);
          }
          else
          {
             stmt.setLong(index, (Long) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code String} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code String}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setStringParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.VARCHAR);
          }
          else
          {
             stmt.setString(index, (String) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Boolean} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Boolean}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setBooleanParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.BOOLEAN);
          }
          else
          {
             stmt.setBoolean(index, (Boolean) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Double} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Double}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setDoubleParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.DOUBLE);
          }
          else
          {
             stmt.setDouble(index, (Double) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Float} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Float}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setFloatParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.FLOAT);
          }
          else
          {
             stmt.setFloat(index, (Float) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Blob} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Blob}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setBlobParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.BLOB);
          }
          else
          {
             Connection conn = stmt.getConnection();
             String url = prepareUrl(conn.getMetaData().getURL());
             BiFunction<Connection, byte[], Blob> blobCreator = blobCreators.get(url);
             Blob blob = blobCreator.apply(conn, (byte[]) val);
             stmt.setBlob(index, blob);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Clob} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Clob}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setClobParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.CLOB);
          }
          else
          {
             Connection conn = stmt.getConnection();
             String url = prepareUrl(conn.getMetaData().getURL());
             BiFunction<Connection, String, Clob> clobCreator = clobCreators.get(url);
             Clob blob = clobCreator.apply(conn, (String) val);
             stmt.setClob(index, blob);
          }

          return 1;
       }

       /**
        * Sets a value assumed of type {@code java.util.Date} to a parameter in a
        * {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code java.util.Date}.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setDateParameter(PreparedStatement stmt, int index, Object val)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.DATE);
          }
          else
          {
             stmt.setDate(index, new java.sql.Date(((java.util.Date) val).getTime()));
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code BigDecimal} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code BigDecimal}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setBigDecimalParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.DECIMAL);
          }
          else
          {
             stmt.setBigDecimal(index, (BigDecimal) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code byte} array to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code byte} array.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setByteArrayParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.VARBINARY);
          }
          else
          {
             stmt.setBytes(index, (byte[]) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code java.util.Date} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code java.util.Date}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setUtilDateParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.DATE);
          }
          else
          {
             stmt.setDate(index, new java.sql.Date(((java.util.Date) val).getTime()));
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code Timestamp} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code Timestamp}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setTimestampParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.TIMESTAMP);
          }
          else
          {
             stmt.setTimestamp(index, (Timestamp) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code java.sql.Date} to a parameter in a {@code PreparedStatement}.
        *
        * @param   stmt
        *          The {@code PreparedStatement} to receive the parameter.
        * @param   index
        *          The position of the parameter in the statement.
        * @param   val
        *          The value to be set, assumed to be of type {@code java.sql.Date}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered.
        */
       static int setSqlDateParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.DATE);
          }
          else
          {
             stmt.setDate(index, (java.sql.Date) val);
          }
          return 1;
       }

       /**
        * Sets a value assumed of type {@code OffsetDateTime} to a parameter in a {@code PreparedStatement}.
        * <p>
        * <strong>Note:</strong><br>
        * This implementation of {@code DataHandler} is the only one that fills in two parameters in a
        * prepared statement.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameters.
        * @param   val
        *          The value of the parameter. Must be a {@code OffsetDateTime}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate.
        *
        * @return  2 if the parameter is used to update / insert a new record (the number of position used by a
        *          datetimetz value) or 1 if the parameter is used in where predicate (all supported dialects
        *          handle time-zoned instants natively, without the need of offset).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       static int setOffsetDateTimeParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val == null)
          {
             stmt.setNull(index, Types.TIMESTAMP_WITH_TIMEZONE);
             if (forUpdate)
             {
                stmt.setNull(index + 1, Types.INTEGER);
             }
          }
          else
          {
             stmt.setObject(index, val, Types.TIMESTAMP_WITH_TIMEZONE);
             if (forUpdate)
             {
                stmt.setObject(index + 1,
                               ((OffsetDateTime) val).getOffset().getTotalSeconds() / 60,
                               Types.INTEGER);
             }
          }
          return forUpdate ? 2 : 1;
       }

       /**
        * Extracts the data from a {@code raw} object and use it to initialize a parameter of type
        * raw in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code raw}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdRawParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             raw asBDT = (raw) val;
             if (!asBDT.isUnknown())
             {
                stmt.setBytes(index, asBDT.getByteArray());
                return 1;
             }
          }

          stmt.setNull(index, Types.VARBINARY);
          return 1;
       }

       /**
        * Extracts the data from an {@code object} object and use it to initialize a parameter of type
        * object in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code object}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdObjectParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             object<? extends _BaseObject_> asBDT = (object<? extends _BaseObject_>) val;
             if (!asBDT.isUnknown())
             {
                stmt.setLong(index, object.resourceId(asBDT));
                return 1;
             }
          }

          stmt.setNull(index, Types.BIGINT);
          return 1;
       }

       /**
        * Extracts the data from a {@code logical} object and use it to initialize a parameter of type
        * logical in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code logical}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdLogicalParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             logical asBDT = (logical) val;
             if (!asBDT.isUnknown())
             {
                stmt.setBoolean(index, asBDT.toJavaType());
                return 1;
             }
          }

          stmt.setNull(index, Types.BOOLEAN);
          return 1;
       }

       /**
        * Extracts the data from an {@code integer} object and use it to initialize a parameter of type
        * integer in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code integer}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdIntegerParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             integer asBDT = (integer) val;
             if (!asBDT.isUnknown())
             {
                stmt.setInt(index, asBDT.intValue());
                return 1;
             }
          }

          stmt.setNull(index, Types.INTEGER);
          return 1;
       }

       /**
        * Extracts the data from an {@code int64} object and use it to initialize a parameter of type
        * int64 in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code int64}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdInt64Parameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             int64 asBDT = (int64) val;
             if (!asBDT.isUnknown())
             {
                stmt.setLong(index, asBDT.longValue());
                return 1;
             }
          }

          stmt.setNull(index, Types.BIGINT);
          return 1;
       }

       /**
        * Extracts the data from a {@code handle} object and use it to initialize a parameter of type
        * handle in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code handle}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdHandleParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             handle asBDT = (handle) val;
             if (!asBDT.isUnknown())
             {
                stmt.setLong(index, asBDT.getResourceId());
                return 1;
             }
          }

          stmt.setNull(index, Types.BIGINT);
          return 1;
       }

       /**
        * Extracts the data from a {@code decimal} object and use it to initialize a parameter of type
        * decimal in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code decimal}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdDecimalParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             decimal asBDT = (decimal) val;
             if (!asBDT.isUnknown())
             {
                stmt.setBigDecimal(index, asBDT.toBigDecimal());
                return 1;
             }
          }

          stmt.setNull(index, Types.DECIMAL);
          return 1;
       }

       /**
        * Extracts the data from a {@code date} object and use it to initialize a parameter of type
        * date in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code date}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdDateParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             date asBDT = (date) val;
             if (!asBDT.isUnknown())
             {
                stmt.setDate(index, new Date(asBDT.dateValue(null).getTime()));
                return 1;
             }
          }

          stmt.setNull(index, Types.DATE);
          return 1;
       }

       /**
        * Extracts the data from a {@code datetime} object and use it to initialize a parameter of
        * type datetime in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code datetime}.
        * @param   forUpd
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdDatetimeParameter(PreparedStatement stmt, int index, Object val, boolean forUpd)
       throws SQLException
       {
          if (val != null)
          {
             datetime asBDT = (datetime) val;
             if (!asBDT.isUnknown())
             {
                stmt.setTimestamp(index, new Timestamp(asBDT.dateValue().getTime()));
                return 1;
             }
          }

          stmt.setNull(index, Types.TIMESTAMP);
          return 1;
       }

       /**
        * Extracts the data from a {@code datetimetz} object and use it to initialize a parameter of
        * type datetimetz in a SQL prepared query.
        * <p>
        * <strong>Note:</strong><br>
        * This implementation of {@code DataHandler} is the only one that fills in two parameters in a
        * prepared statement.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameters.
        * @param   val
        *          The value of the parameter. Must be a {@code datetimetz}.
        * @param   forUpd
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate.
        *
        * @return  2 (the number of position used by a datetimetz value) if case of an insert/update and 1
        *          otherwise (all supported SQL databases handle correctly the time-zoned instants natively).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdDatetimetzParameter(PreparedStatement stmt, int index, Object val, boolean forUpd)
       throws SQLException
       {
          if (val != null)
          {
             datetimetz asBDT = (datetimetz) val;
             if (!asBDT.isUnknown())
             {
                stmt.setObject(index, asBDT.offsetDateTimeValue(), Types.TIMESTAMP_WITH_TIMEZONE);
                if (forUpd)
                {
                   stmt.setObject(index + 1, asBDT.getTimeZoneOffset().intValue(), Types.INTEGER);
                }
                return forUpd ? 2 : 1;
             }
          }

          stmt.setNull(index, Types.TIMESTAMP_WITH_TIMEZONE);
          if (forUpd)
          {
             stmt.setNull(index + 1, Types.INTEGER);
          }
          return forUpd ? 2 : 1;
       }

       /**
        * Extracts the data from a {@code comhandle} object and use it to initialize a parameter of
        * type comhandle in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code comhandle}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdComhandleParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             comhandle asBDT = (comhandle) val;
             if (!asBDT.isUnknown())
             {
                stmt.setString(index, asBDT.toStringMessage());
                return 1;
             }
          }

          stmt.setNull(index, Types.VARCHAR);
          return 1;
       }

       /**
        * Extracts the data from a {@code clob} object and use it to initialize a parameter of type
        * clob in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code clob}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdClobParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             clob asBDT = (clob) val;
             if (!asBDT.isUnknown())
             {
                stmt.setClob(index, new StringReader(asBDT.getValue()));
                return 1;
             }
          }

          stmt.setNull(index, Types.CLOB);
          return 1;
       }

       /**
        * Extracts the data from a {@link character} object and use it to initialize a parameter of
        * type char in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code character}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdCharacterParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             character asBDT = (character) val;
             if (!asBDT.isUnknown())
             {
                stmt.setString(index, asBDT.toJavaType());
                return 1;
             }
          }

          stmt.setNull(index, Types.VARCHAR);
          return 1;
       }

       /**
        * Extracts the data from a {@code blob} object and use it to initialize a parameter of type
        * blob in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code blob}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdBlobParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             blob asBDT = (blob) val;
             if (!asBDT.isUnknown())
             {
                stmt.setBlob(index, new ByteArrayInputStream(asBDT.getByteArray()));
                return 1;
             }
          }

          stmt.setNull(index, Types.BLOB);
          return 1;
       }

       /**
        * Extracts the data from a {@code recid} object and use it to initialize a parameter of type
        * recid in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code recid}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdRecidParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             recid asBDT = (recid) val;
             if (!asBDT.isUnknown())
             {
                stmt.setInt(index, asBDT.intValue());
                return 1;
             }
          }

          stmt.setNull(index, Types.INTEGER);
          return 1;
       }

       /**
        * Extracts the data from a {@code rowid} object and use it to initialize a parameter of type
        * rowid in a SQL prepared query.
        *
        * @param   stmt
        *          The prepared statement whose parameter is to be set.
        * @param   index
        *          The index/position of the parameter.
        * @param   val
        *          The value of the parameter. Must be a {@code rowid}.
        * @param   forUpdate
        *          {@code true} if the parameter in the statement is used to update a field and {@code false} if
        *          it is used as part of the where predicate. Ignored here.
        *
        * @return  1 (the number of position used by this value).
        *
        * @throws  SQLException
        *          when an error is encountered while setting the value.
        */
       private static int setFwdRowidParameter(PreparedStatement stmt, int index, Object val, boolean forUpdate)
       throws SQLException
       {
          if (val != null)
          {
             rowid asBDT = (rowid) val;
             if (!asBDT.isUnknown())
             {
                stmt.setLong(index, asBDT.getValue());
                return 1;
             }
          }

          stmt.setNull(index, Types.BIGINT);
          return 1;
       }

       /**
        * Strip the JDBC URL's options.
        * <p>
        * Note: This proved not to be a reliable method to identify a database. The URL may optionally contain the
        *       host, port, user, and password. While a connection to same database can be done using various
        *       user/password combinations, the optional port may cause connection to a different database.
        *
        * @param   url
        *          The JDBC URL.
        *
        * @return  The URL without any options.
        */
       private static String prepareUrl(String url)
       {
          // the H2 url does not feature a port number and has the optional parameters separated by ;
          if (url.startsWith("jdbc:h2:"))
          {
             // TODO: the path for the database may not be normalized
             int idx = url.indexOf(';');
             return (idx >= 0) ? url.substring(0, idx) : url;
          }

          // the other dialects use ? to separate the optional parameters and may contain a port number. If not,
          // use the default dialect-specific, in a normalized manner
          int idx = url.indexOf('?');
          String noOptionsUrl = (idx >= 0) ? url.substring(0, idx) : url;

          Matcher matcher = Pattern.compile("(\\w*):(\\w*):(//)?(\\w*)(:\\d*)?(/)?(\\w*)").matcher(noOptionsUrl);
          if (matcher.find())
          {
             String port = matcher.group(5);
             if (port == null)
             {
                // compute the dialect-specific default port
                port = url.startsWith("jdbc:mariadb:") || url.startsWith("jdbc:mysql:") ? ":3306" :
                       url.startsWith("jdbc:postgresql:") ? ":5432" :
                       ":1433" /*mssql*/;
             }
             return matcher.group(1) + ":" +           // "jdbc"
                    matcher.group(2) + ":" +           // dialect
                    matcher.group(4) + port +  ":" +   // host:port
                    matcher.group(7);                  // database name
          }

          return noOptionsUrl;
       }
    }