Data type conversions

Data type conversions during resolving

Note: The features described in this chapter are available in JBuilder Professional and Enterprise.

During the resolving phase when you save changes made to the data back to its source, the data is converted from Variant data types to JDBC types as follows:

  1. From the user-specified data type to the default Variant data type
  2. From Variant data types to JDBC types, if necessary

Conversion from the user-specified data type to the default Variant data type

If you set a column's dataType property, thereby over-riding the default JDBC-to-Variant mapping, automatic data type coercion is done, for example, in the case of persistent columns. The exception to the automatic type coercion is when converting to and from a String to any type other than String (and other conversions involving such differing data types). A VariantException is thrown in these cases.

To customize the coercion, or to prevent the VariantException from being thrown (in cases as described in the previous paragraph), wire the CoerceFromListener.coerceFromColumn() event.

When you write an event handler for CoerceFromListener, you typically need to write one for CoerceToListener as well. If your DataSet is editable and uses the default UpdateMode option, update queries will be generated that look for rows on the server that match the original data of the rows that were edited. Without coercion to convert field values back to what they looked like before the CoerceToListener modified them, these update queries will likely fail. Note that this will happen even if you don't edit values in the column with the CoerceToListener because update queries, by default, compare all searchable columns in rows on the server.

See also: Data type conversion during providing, CoerceToListener.coerceToColumn() event

Mapping of Variant data types to JDBC data types

When resolving changes made to the local copy of data back to its Database source, Variant data types are translated into corresponding JDBC data types according to the table below. The JDBC data types listed are defined in java.sql.Types. The Variant data types are defined in com.borland.dx.dataset.Variant.

Any other value specified for a Variant data type generates a DataSetException of UnrecognizedDataType.

Variant data type translation

Variant data type Conversion process to JDBC type
Variant.BIGDECIMAL The value is bound using the java.sql.PreparedStatement object's setBigDecimal() method.
Variant.BINARY The value is bound using the java.sql.PreparedStatement object's setBinaryStream() method.
Variant.BOOLEAN The value is bound using the java.sql.PreparedStatement object's setBoolean() method.
Variant.DATE The value is bound using the java.sql.PreparedStatement object's setDate() method.
Variant.DOUBLE If the column's sqlType is java.sql.Types.REAL or java.sql.Types.FLOAT, the value is bound using the java.sql.PreparedStatement object's setFloat() method. Otherwise, the value is bound using the java.sql.PreparedStatement object's setDouble() method.
Variant.FLOAT If the column's sqlType is java.sql.Types.REAL or java.sql.Types.FLOAT, the value is bound using the java.sql.PreparedStatement object's setFloat() method.

Otherwise, the value will be bound using the java.sql.PreparedStatement object's setDouble() method.

Variant.INT The value is bound using the java.sql.PreparedStatement object's setInt() method.
Variant.LONG The value is bound using the java.sql.PreparedStatement object's setLong() method.
Variant.OBJECT The value is bound using the java.sql.PreparedStatement object's setObject() method.
Variant.STRING If the Column's sqlType is java.sql.Types.LONGVARCHAR, the value is bound as java.sql.Types.LONGVARCHAR.

If the column's sqlType is java.sql.Types.VARCHAR, the value is bound as java.sql.Types.VARCHAR.

If the column has precision (in other words, precision does not equal -1), the string is right-padded with spaces up to length specified by precision and is bound as java.sql.Types.CHAR. For example, if the value is "cat" and the column's precision is 5, the value will be bound as "cat ".

Otherwise, the value is bound using the java.sql.PreparedStatement object's setString() method.

Variant.TIMESTAMP The value is bound using the java.sql.PreparedStatement object's setTimestamp() method.
Variant.TIME The value is bound using the java.sql.PreparedStatement object's setTime() method.

Data type coercions

In addition to the inital Variant to JDBC data type conversion, a second data type translation phase occurs where the JDBC-typed data is saved back to the data source.

If a column's dataType is not the same data type as that of the data source, automatic data type coercion occurs. The exception to the automatic type coercion is when converting to and from a String to any type other than String (and other conversions involving such differing data types). A VariantException is thrown in these cases.

To customize the coercion, or to prevent the VariantException from being thrown in cases as noted in the previous paragraph, wire the CoerceFromListener.coerceFromColumn() event.

See also: Data type conversions during data providing, CoerceToListener.coerceToColumn() event

Data type conversions during data providing

Note: The features described in this chapter are available in JBuilder Professional and Enterprise.

During the providing phase, data is converted from JDBC data types to Variant data types. This involves the following:

  1. Mapping of JDBC to Variant data types
  2. Data type coercions occur, if necessary

Mapping of JDBC data types to Variant data types

When you obtain data from the data source, the JBDC data types are translated into corresponding Variant data types. The JDBC data types are listed below and are defined in java.sql.Types. The Variant data types are defined in com.borland.dx.dataset.Variant.

Precision, scale and type have default values if they are not explicitly set. Precision and scale default to -1. Type defaults to Variant.STRING. The sqlType is based on the value returned from the JDBC driver.

For information on data mapping of SQL server physical types to JBDC data types, check your driver documentation.

JDBC data type translation

JDBC type Maps to Variant data type
java.sql.Types.BIGINT Variant.LONG
java.sql.Types.BINARY Variant.BINARY with precision
java.sql.Types.BIT Variant.BOOLEAN
java.sql.Types.CHAR Variant.STRING with precision
java.sql.Types.DATE Variant.DATE
java.sql.Types.DECIMAL Variant.BIGDECIMAL with scale and precision
java.sql.Types.DOUBLE Variant.DOUBLE
java.sql.Types.FLOAT Variant.DOUBLE
java.sql.Types.INTEGER Variant.INT
java.sql.Types.LONGVARBINARY Variant.BINARY without precision
java.sql.Types.LONGVARCHAR Variant.STRING without precision (BLOB columns typically have an open-ended length)
java.sql.Types.NUMERIC Variant.BIGDECIMAL with scale and precision
java.sql.Types.OTHER Variant.OBJECT
java.sql.Types.REAL Variant.DOUBLE
java.sql.Types.SMALLINT Variant.SHORT
java.sql.Types.TIME Variant.TIME
java.sql.Types.TIMESTAMP Variant.TIMESTAMP
java.sql.Types.TINYINT Variant.BYTE
java.sql.Types.VARBINARY Variant.BINARY without precision
java.sql.Types.VARCHAR Variant.STRING with precision

Data type coercions

In addition to the initial JDBC to Variant data type conversion, a second data type translation phase occurs where the Variant-typed data is stored in Column components.

If you set a column's dataType property, thereby over-riding the default JDBC-to-Variant mapping, automatic data type coercion is done, for example, in the case of persistent columns. The exception to the automatic type coercion is when converting to and from a String to any type other than String(and other conversions involving such differing data types). A VariantException is thrown in these cases.

To customize the coercion, or to prevent the VariantException from being thrown, wire the CoerceToListener.coerceToColumn() event.

When you write an event handler for CoerceToListener, you typically need to write one for CoerceFromListener as well. If your DataSet is editable and uses the default UpdateMode option, update queries will be generated that look for rows on the server that match the original data of the rows that were edited. Without coercion to convert field values back to what they looked like before the CoerceToListener modified them, these update queries will likely fail. Note that this will happen even if you don't edit values in the column with the CoerceToListener because update queries, by default, compare all searchable columns in rows on the server.

See also: Data type conversion during resolving, CoerceFromListener.coerceFromColumn() event