InstantSQL Data Conversion

Data Conversion

InstantSQL data transfers between the application and the data source generally require two translations in each direction.

When transferring data from a data source for columns of a result set or output parameter values to the COBOL program, the driver that supports the data source connection converts the data from the data source format to C format data. InstantSQL then converts the C format data to RM/COBOL format data.

When transferring data from the COBOL program to a data source from input parameters, InstantSQL converts the data from RM/COBOL format data to C format data. Then the driver that supports the data source connection converts from C format data to the data source format.

For purposes of InstantSQL, COBOL numeric edited data items are considered to be numeric in the following discussions.

Because of the limitations of SQL numeric data types, COBOL 30-digit precision is generally not supported. For example, a BIGINT data item supports 19 digits for signed items and 20 digits for unsigned items.

When transferring a NULL value from the data source, the COBOL data item is set to all binary zero characters (ALL X"00"), regardless of the data type of the column or the COBOL data item. The associated length indicator (specified in the SQL GET DATA or SQL BIND COLUMN statement) is set to the value sql-Null-Data. The length indicator value sql-Null-Data unambiguously indicates a NULL in the data source, but a test for ALL X"00" in the COBOL data item can also be used to check for NULL valued data in those cases where this is known not to be valid result.

When transferring a COBOL value that should be treated as NULL to the data source, the associated length indicator (specified in the SQL BIND PARAMETER statement) should be set to sql-Null-Data, in which case the COBOL data item value is ignored and a NULL is sent to the data source. The associated length indicator is the only means of sending a NULL to the data source. There is no value of a COBOL data item that will cause a NULL to be sent to the data source. (Except some data sources treat a zero length string as equivalent to NULL in a VARCHAR data item as noted below.)

CHAR, DECIMAL, LONGVARCHAR, NUMERIC, and VARCHAR Data Conversions

When transferring data from the data source to the COBOL program, the character string received from the driver is stored in the COBOL data item as follows:

If the COBOL data item is nonnumeric and right justified, left space padding or truncation is provided as necessary.

If the COBOL data item is nonnumeric and left justified, right space padding or truncation is provided as necessary.

If the COBOL data item is numeric, the character string is assumed to be a numeric string (optional leading sign, digits, and an optional embedded decimal point), which is converted to an algebraic value and stored in the numeric data item. For numeric edited data items, the algebraic value is edited into the data item as it is stored. Truncation will occur if the algebraic value is too large for the receiving data item.

When truncation occurs, InstantSQL sets sqe-DataTruncation to true unless a more serious error also occurs. Data truncation errors will occur even if the truncated characters are all spaces.

The driver supporting the data source is responsible for converting DECIMAL and NUMERIC data to character strings.

The length indicator value returned for result columns and output parameters indicates the length of the character string returned, without regard to any truncation or space padding. A length indicator value of zero indicates a zero length string. A length indicator value of sql-Null-Data indicates a NULL in the data source.

When transferring data from the COBOL program to the data source, a numeric data item is first converted to a numeric string. The resulting string, or, for nonnumeric items, the original string, is stripped of trailing spaces. The driver then transfers this string to the data source. The driver is responsible for padding a string value that is shorter than the fixed length of a CHAR data item with spaces. If trailing spaces are significant in a variable length VARCHAR or LONGVARCHAR data item, the length indicator may be set to a value that includes the space characters and the driver will pad the string with spaces to the length specified. Alternatively, a X"00" character may be inserted in the COBOL string after the last significant space and the length indicator set to sql-NTS, indicating a null terminated string. A length indicator value of sql-NTS is assumed when OMITTED is specified for the length indicator COBOL argument in the SQL BIND PARAMETER statement. If the supplied data length, as determined by this discussion, is too large for the data source item, InstantSQL sets sqe-DataTruncation to true unless a more serious error also occurs. A length indicator value of sql-Null-Data indicates that the COBOL data item value is to be ignored and a NULL value sent to the data source.

Note When the resulting string from the COBOL program is all spaces, InstantSQL normally strips all the trailing spaces, thus giving an empty string. Some databases, for example, Oracle, do not support an empty string, often by treating empty strings as equivalent to NULL, which has different semantics than expected for an empty string. InstantSQL provides various solutions to this problem. The application program may specify the length indicator with a value of 1 or greater, in which case InstantSQL will preserve sufficient spaces to satisfy the length indicator. Also, the configuration values OracleVarCharMinSize (for connections to Oracle databases) and VarCharMinSize (for connections to databases other than Oracle) may be set to 1, which also causes InstantSQL to preserve at least one space. By default, OracleVarCharMinSize is 1 since all known Oracle databases treat a zero length string as NULL. (See the topic InstantSQL Configuration for more information on configuring InstantSQL.)

Even though DECIMAL and NUMERIC data types are grouped with character string data types, these conversions preserve the algebraic value when such data items are converted to/from numeric COBOL data items capable of holding the range of values in the data source.

BINARY, LONGVARBINARY, and VARBINARY Data Conversions

BINARY data types in SQL do not generally represent numbers; instead, they are just strings of binary data values, such as bit map images. Thus, it is usually incorrect for a COBOL numeric argument to be used with these data types. Similarly, a right justified nonnumeric argument would usually be incorrect.

When transferring data from the data source to the COBOL program, the character string received from the driver is stored in the COBOL data item as follows:

If the COBOL data item is nonnumeric and right justified, left X"00" padding or truncation is provided as necessary.

If the COBOL data item is nonnumeric and left justified, right X"00" padding or truncation is provided as necessary.

When truncation occurs, InstantSQL sets sqe-DataTruncation to true unless a more serious error also occurs. Data truncation errors will occur even if the truncated characters are all X"00".

The length indicator value returned for result columns and output parameters indicates the length in bytes of the binary data returned, without regard to any truncation or X"00" padding. A length indicator value of zero indicates a zero length data item. A length indicator value of sql-Null-Data indicates a NULL in the data source.

When transferring data from the COBOL program to the data source, numeric data items are first converted to a numeric string. The driver then transfers the resulting string, or, for nonnumeric items, the original string, to the data source. The length of the transfer will be controlled by the length indicator value if a length indicator is associated with the item. No trailing bytes are stripped during the transfer. The ODBC driver is responsible for padding fixed length BINARY data with trailing binary zeroes. If the transferred data is too long for the data source item, InstantSQL sets sqe-DataTruncation to true unless a more serious error also occurs. A length indicator value of sql-Null-Data indicates that the COBOL data item value is to be ignored and a NULL value sent to the data source.

BIT Data Conversions

A BIT data item generally represents true or false with the values 1 or 0, respectively.

When transferring data from the data source to the COBOL program, the value from the data source is stored as an unsigned integer in the range 0 through 255 into the COBOL data item. If the COBOL data item is nonnumeric, the numeric value from the data source is converted to a character string. If the data source has properly represented a bit value, the only values returned will be 1 or 0 ("1" or "0" for nonnumeric COBOL data items). A length indicator value of sql-Null-Data indicates a NULL in the data source; otherwise, the length indicator will be 1 for BIT data.

When transferring data from the COBOL program to the data source, if the COBOL data item has a value of 0, a 0 is sent to the data source; otherwise, a 1 is sent to the data source. If the COBOL data item is nonnumeric, its value must be a string of decimal digits, which is converted to a numeric value. A length indicator value of sql-Null-Data indicates that the COBOL data item value is to be ignored and a NULL value sent to the data source.

TINYINT, SMALLINT, INTEGER, and BIGINT Data Conversions

When transferring data from the data source to the COBOL program, the value from the data source is stored into the COBOL data item. If the COBOL data item is nonnumeric, the numeric value from the data source is converted to a character string. Result columns are treated as unsigned if the SQLColAttributes function indicates they are unsigned. Output parameters are treated as unsigned if their SQL data type is unsigned for all data source supported data types that have the same SQL data type (a data source may support multiple occurrences of the same SQL data type); otherwise, the parameter is considered to be signed. For InstantSQL conversions from SQL data to COBOL data, whether a data type is signed or unsigned only matters for TINYINT, SMALLINT, INTEGER and BIGINT data types. A length indicator value of sql-Null-Data indicates a NULL in the data source; otherwise, the length indicator will be 1 for TINYINT, 2 for SMALLINT, 4 for INTEGER or 8 for BIGINT data.

When transferring data from the COBOL program to the data source, the numeric value of the COBOL data item is sent to the data source. If the COBOL data item is nonnumeric, its value must be a string of decimal digits, which is converted to a numeric value. A data truncation error will occur if the numeric value from the COBOL data item is outside the range for the data source data item. Input parameters are treated as unsigned if their SQL data type is unsigned for all data source supported data types that have the same SQL data type (a data source may support multiple occurrences of the same SQL data type); otherwise, the parameter is considered to be signed. For InstantSQL conversions from COBOL data to SQL data, whether a data type is signed or unsigned only matters for TINYINT, SMALLINT and INTEGER data types (this intentionally excludes BIGINT). A length indicator value of sql-Null-Data indicates that the COBOL data item value is to be ignored and a NULL value sent to the data source.

Note Data truncation is not necessarily detected in all cases. For example, the Access 97 driver does not detect truncation when values larger than 255 are transferred to a column with the data type TINYINT.

Note RM/COBOL supports numeric data with up to 30 digits of precision. In general, a precision greater than 19 (20 for unsigned data) is not supported by databases, and that is only when the BIGINT data type is supported. Thus, InstantSQL generally does not support this capability of RM/COBOL.

DATE Data Conversions

The COBOL data item must be:

nonnumeric with a length of 8 or 7 characters; or

numeric with a scale of zero and a precision of 8 or 7 digits.

When the length or precision is 8, the COBOL data item will receive or must contain a date in the form YYYYMMDD, where YYYY is a four-digit year, MM is a two-digit month of the year, and DD is a two-digit day of the month. When the length or precision is 7, the COBOL data item will receive or must contain a date in the form YYYYddd, where YYYY is a four-digit year and ddd is a three-digit day of the year.

When transferring data from the data source to the COBOL program, the value from the data source is stored into the COBOL data item. If the COBOL data item is nonnumeric, the numeric value from the data source is converted to a character string. A length indicator value of sql-Null-Data indicates a NULL in the data source; otherwise, the length indicator will be 6 for DATE data.

TIME Data Conversions

The COBOL data item must be:

nonnumeric with a length of 2, 4, 6 or 8 characters; or

numeric with a scale of zero and a precision of 2, 4, 6 or 8 digits.

The COBOL data item will receive or must contain a time in the form HHmmSScc, where HH is a two-digit hour of the day, mm is a two-digit minute of the hour, SS is a two-digit second of the minute and cc is a two-digit centi-second of the second. When the length or precision is 2, only the hours field is present, when 4, only the HHmm fields are present, and so forth.

When transferring data from the data source to the COBOL program, the time value from the data source is stored into the COBOL data item according to the format described for a COBOL time data item. Since SQL time values do not contain centi-seconds, if the COBOL data item includes the cc field, it will always be set to 00. If the COBOL time data item does not include some or all of the time fields, these values are truncated without error. A length indicator value of sql-Null-Data indicates a NULL in the data source; otherwise, the length indicator will be 6 for TIME data.

When transferring data from the COBOL program to the data source, the time value of the COBOL data item is validated and sent to the data source. If the validation fails, an error occurs and the InstantSQL statement is terminated without sending the bad value to the data source. The validation is that HH must be 00-23, mm must be 00-59 and SS must be 00-61. (The range of seconds allows as many as two leap seconds to maintain synchronization of sidereal time.) Since SQL time values do not contain centi-seconds, if the COBOL data item specifies centi-seconds, they will be truncated without error. If the COBOL time data item does not include some or all of the time fields, zeroes are supplied to create the data source time value. To indicate a NULL time, the length indicator value must be set to sql-Null-Data.

TIMESTAMP Data Conversions

The COBOL data item must be:

nonnumeric with a length of 8, 10, 12, 14 or 16 characters; or

numeric with a scale of zero and a precision of 7, 9, 11, 13 or 15 digits.

The COBOL data item will receive or must contain a timestamp. If the length or precision is even, the timestamp form is YYYYMMDDHHmmSScc, where YYYY is a four-digit year, MM is a two-digit month of the year, and DD is a two-digit day of the month. If the length or precision is odd, the timestamp form is YYYYdddHHmmSScc, where YYYY is a four-digit year and ddd is a three-digit day of the year. In either form, HH is a two-digit hour of the day, mm is a two-digit minute of the hour, SS is a two-digit second of the minute and cc is a two-digit centi-second of the second. When the length or precision does not include space for the complete time portion of the timestamp, time fields are truncated from the right without error. A minimal timestamp COBOL data item contains only a date. A length indicator value of sql-Null-Data indicates a NULL in the data source; otherwise, the length indicator will be 16 for TIMESTAMP data.

When transferring data from the data source to the COBOL program, the timestamp value from the data source is stored into the COBOL data item according to the format described for a COBOL timestamp data item. If the COBOL timestamp data item does not include some or all of the time fields, these values are truncated without error.

When transferring data from the COBOL program to the data source, the timestamp value of the COBOL data item is validated and sent to the data source. If the validation fails, an error occurs and the InstantSQL statement is terminated without sending the bad value to the data source. The validation is that the date must be valid in the Gregorian calendar, HH must be 00-23, mm must be 00-59 and SS must be 00-61. (The range of seconds allows as many as two leap seconds to maintain synchronization of sidereal time.) If the COBOL timestamp data item does not include some or all of the time fields, zeroes are supplied to create the data source timestamp value. To indicate a NULL timestamp, the length indicator value must be set to sql-Null-Data; that is, a date value of zero would cause a conversion error rather than a NULL valued timestamp.

DOUBLE PRECISION, FLOAT, and REAL Data Conversions

These data types represent floating-point numbers, or, as they are called in databases, approximate numeric values.

When transferring data from the data source to the COBOL program, if the COBOL data item is numeric, the value is stored into the numeric data item. If the COBOL data item is nonnumeric, the value is converted to a string representation of a floating-point number and the resulting string is copied to the COBOL nonnumeric data item. A length indicator value of sql-Null-Data indicates a NULL in the data source; otherwise, the length indicator will be 8 for DOUBLE PRECISION or FLOAT data or 4 for REAL data.

When transferring data from the COBOL program to the data source, if the COBOL data item is numeric, its value is converted to a floating-point number and this number is sent to the data source. If the COBOL data item is nonnumeric, it is assumed to contain a string representation of a floating-point number, which is converted to the floating-point value and this value is then sent to the data source. A length indicator value of sql-Null-Data indicates that the COBOL data item value is to be ignored and a NULL value sent to the data source.

A string representation of a floating-point number is as follows:

[whitespace] [sign] [digits] [.digits] [ {d | D | e | E}[sign]digits]

A whitespace may consist of space and tab characters, which are ignored; sign is either plus (+) or minus (–); and digits are one or more decimal digits. If no digits appear before the radix character, at least one must appear after the radix character. The decimal digits can be followed by an exponent, which consists of an introductory letter (d, D, e, or E) and an optionally signed integer. If neither an exponent part nor a radix character appears, a radix character is assumed to follow the last digit in the string. The first character that does not fit this form stops the scan.