The following rules apply to conversions involving numeric literals. Following are terms used in this topic:
Term
Meaning
Store assignment
Refers to sending data into a table column in a database when calling SQLExecute and SQLExecDirect. During store assignment, "target" refers to a database column and "source" refers to data in application buffers.
Retrieval assignment
Refers to retrieving data from the database into application buffers when calling SQLFetch, SQLGetData, and SQLFetchScroll. During retrieval assignment, "target" refers to the application buffers and "source" refers to the database column.
CS
Value in the character source.
NT
Value in the numeric target.
NS
Value in the numeric source.
CT
Value in the character target.
Precision of an exact numeric literal
Number of digits that the literal contains.
Scale of an exact numeric literal
Number of digits to the right of the expressed or implied decimal point.
Precision of an approximate numeric literal
Precision of the literal’s mantissa.
Rules for converting from a character source (CS) to a numeric target (NT)
1 Replace CS with the value obtained by removing any leading or trailing spaces in CS. If CS is not a valid numeric-literal, SQLSTATE 22018 (Invalid character value for cast specification) is returned.
2 Replace CS with the value obtained by removing leading zeroes before the decimal point, trailing zeroes after the decimal point, or both.
3 Convert CS to NT. If the conversion results in a loss of significant digits, SQLSTATE 22003 (Numeric value out of range) is returned. If the conversion results in the loss of nonsignificant digits, SQLSTATE 01S07 (Fractional truncation) is returned.
Rules for converting from a numeric source (NS) to a character target (CT)
1 Let LT be the length in characters of CT.
For retrieval assignment, LT is equal to the length of the buffer in characters minus the number of bytes in the null-termination character for this character set.
2 Take one of the following actions depending on the type of NS.
▪If NS is an exact numeric type, then let YP equal the shortest character string that conforms to the definition of exact-numeric-literal such that the scale of YP is the same as the scale of NS, and the interpreted value of YP is the absolute value of NS.
▪If NS is an approximate numeric type, then let YP be a character string as follows:
Case:
a If NS is equal to 0, then YP is the string "0".
b Let YSN be the shortest character string that conforms to the definition of exact-numeric-literal and whose interpreted value is the absolute value of NS. If the length of YSN is less than the (precision + 1) of the data type of NS, then let YP equal YSN.
c Otherwise, YP is the shortest character string that conforms to the definition of approximate-numeric-literal whose interpreted value is the absolute value of NS and whose mantissa consists of a single digit that is not 0, followed by a period and an unsigned-integer.
3 If NS is less than 0, then let Y be the result of:
'-' || YP
where || is the string concatenation operator.
Otherwise, let Y equal YP. 4.
4 Let LY be the length in characters of Y.
5 Take one of the following action depending on the value of LY.
▪If LY equals LT, then CT is set to Y.
▪If LY is less than LT, then CT is set to Y extended on the right by appropriate number of spaces.
▪Otherwise (LY > LT), copy the first LT characters of Y into CT.
Case:
– If this is a store assignment, return the error SQLSTATE 22001 (String data, right-truncated).
– If this is retrieval assignment, return the warning SQLSTATE 01004 (String data, right-truncated). When the copy results in the loss of fractional digits (other than trailing zeros), depending on the driver definition, one of the following actions occurs:
a The driver truncates the string in Y to an appropriate scale (which can be zero also) and writes the result into CT.
b The driver rounds the string in Y to an appropriate scale (which can be zero also) and writes the result into CT.
c The driver neither truncates nor rounds, but just copies the first LT characters of Y into CT.