A BLOB is compatible with, and comparable to, all other BLOBs -- that is, all BLOBs are mutually assignable and mutually comparable. BLOBs may not be directly compared with, or directly assigned to, any other <data type> class, though implicit type conversions can sometimes occur in expressions, SELECTs, INSERTs, DELETEs and UPDATEs. Explicit BLOB conversions can be forced with the CAST operator.

CAST

In SQL, CAST is a scalar operator that converts a given scalar value to a given scalar <data type>. The required syntax for the CAST operator is:

CAST (<cast operand> AS <cast target>)
   <cast operand> ::= scalar_expression
   <cast target> ::= <Domain name> | <data type>

The CAST operator converts values of a source <data type> into values of a target <data type>, where each <data type> is an SQL pre-defined <data type> (data conversions between UDTs are done with a user-defined cast). The source <data type>, or <cast operand>, can be any expression that evaluates to a single value. The target <data type>, or <cast target>, is either an SQL predefined <data type> specification or the name of a Domain whose defined <data type> is the SQL predefined <data type> that you want to convert the value of "scalar_expression" into. (If you use CAST (... AS <Domain name>), your current <AuthorizationID> must have the USAGE Privilege on that Domain.)

It isn't, of course, possible to convert the values of every <data type> into the values of every other <data type>. For BLOBs, the rules are:

  • CAST (NULL AS <data type>) and CAST (blob_source_is_a_null_value AS <data type>) both result in a CAST of NULL.
  • You can CAST a BLOB source to a BLOB target. You can also CAST a BLOB source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

When you CAST a BLOB to a BLOB target, if the octet length of the source value is less than or equals the maximum octet length of the target, the result of the CAST is the source BLOB value. If the octet length of the source value is greater than the maximum octet length of the target, the result of the CAST is as much of the source BLOB value as will fit into the target -- in this case, and your DBMS will return the SQLSTATE warning 01004 "warning-string data, right truncation".

When you CAST a BLOB to a UDT or a <reference type> target, your DBMS invokes the user defined cast routine, with the source value as the routine's argument. The CAST result is the value returned by the user defined cast.

If you want to restrict your code to Core SQL, don't use <Domain name> as a CAST target: CAST only to a <data type> and don't use CAST to convert any BLOB value to another <data type>.

Assignment

In SQL, when a BLOB is assigned to a BLOB target, the assignment is done one octet at a time, from left to right -- that is, the source value's most significant octet is assigned to the target's most significant octet, then the source's next octet is assigned to the target's next octet, and so on.

When a BLOB is taken from SQL-data to be assigned to a BLOB target, the size of the target is first set either to the size of the source or to its own maximum length, whichever is less. If the source is longer than the target, the source is truncated to fit the target. In this case, your DBMS will return the SQLSTATE warning 01004 "warning-string data, right truncation".

[Obscure Rule] Since only SQL accepts null values, if your source is NULL. then your target's value is not changed. Instead, your DBMS will set its indicator parameter to -1, to indicate that an assignment of the null value was attempted. If your target doesn't have an indicator parameter, the assignment will fail: your DBMS will return the SQLSTATE error 22002 "data exception-null value, no indicator parameter". If your source is a non-null value that fits into your target, your DBMS will set the target's indicator parameter (if any) to zero. If your source is longer than your target, your DBMS will set your target's indicator parameter to the length of the source; that is, if your source is 12 octets long and your target can accept only 10 octets, your DBMS will set the target's indicator parameter to 12, to indicate that 2 octets were lost on assignment. If the source's length is too big to be assigned to the indicator, the assignment will fail: your DBMS will return the SQLSTATE error 22022 "data exception-indicator overflow". We'll talk more about indicator parameters in our chapters on SQL binding styles.

When a BLOB is assigned to a SQL-data BLOB target, the size of the target is first set either to the size of the source or to its own maximum length, whichever is less. If the source is larger than the target, but the extra octets are all 0-octets, the source's significant octet value is assigned to the target. If the source is larger than the target and the extra octets are not all 0-octets, the assignment will fail: your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation".

[Obscure Rule] There are two ways to assign a null value to an SQL-data target. Within SQL, you can use the <keyword> NULL in an INSERT or an UPDATE statement to indicate that the target should be set to NULL. that is, if your source is NULL. your DBMS will set your target to NULL. Outside of SQL, if your source has an indicator parameter that is set to -1, your DBMS will set your target to NULL.(regardless of the value of the source). (An indicator parameter with a value less than -1 will cause an error: your DBMS will return the SQLSTATE error 22010 "data exception-invalid indicator parameter value".)

Comparison

SQL provides only two scalar comparison operators -- = and <> -- to perform operations on BLOBs. These will be familiar; there are equivalent operators in other computer languages. If any of the comparands are NULL. the result of the operation is UNKNOWN. For example:

   X'A3D0' = X'A3D0'

returns TRUE.

   X'A3D0' <> {result is NULL}

returns UNKNOWN.

When a BLOB is compared to another BLOB. the comparison is done one octet at a time, from left to right -- that is, the first comparand's most significant octet is compared to the second comparand's most significant octet, then the next two octets are compared, and so on. Two BLOBs, blob_argument_1 and blob_argument_2, are equal if (a) they have the same length and (b) each octet within blob_argument_1 compares as equal to the corresponding octet in blob_argument_2.

If you want to restrict your code to Core SQL, don't use BLOBs in comparisons.

Other Operations

With SQL, you have several other operations that you can perform on BLOBs.

Concatenation

The required syntax for a BLOB concatenation is:

BLOB concatenation ::=
BLOB operand_1 || <<fixed>>BLOB<</fixed>> operand_2

The concatenation operator operates on two operands, both of which must evaluate to a BLOB. It joins the binary strings together in the order given and returns a BLOB with a length equal to the sum of the lengths of its operands. If either of the operands is NULL. the result of the operation is also NULL. Here are two examples of BLOB concatenations:

   X'0000' || X'0011'     -- returns 00000011
   blob_column || X'0011' -- returns blob_column's value followed by 0011

[Obscure Rule] If the sum of the lengths of a BLOB concatenation's operands is not greater than the maximum allowed length for a BLOB. the concatenation result is a BLOB with a length equal to the sum of the lengths of the operands. If the sum of the operands' lengths is greater than the maximum allowed, but the extra octets are all 0-octets, the concatenation result is a BLOB with a length equal to the maximum allowed length. If the sum of the operands' lengths is greater than the maximum allowed and the extra octets are not all 0-octets, the concatenation will fail: your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation".

If you want to restrict your code to Core SQL, don't use the concatenation operator with BLOBs.

Scalar operations

SQL provides five scalar operations that return a BLOB. the <case expression>, the <cast specification>, the <BLOB substring function>, the <BLOB overlay function> and the <BLOB trim function>. It also provides four scalar functions that operate on BLOBs, returning a number: the <BLOB position expression>, the <bit length expression>, the <char length expression> and the <octet length expression>. All but the first two are described below. We'll discuss the <case expression> in Chapter 29 "Simple Search Conditions"; for now, just remember that CASE can evaluate to a binary string and can therefore be used anywhere in an SQL statement that a binary string could be used.

<BLOB substring function>

The required syntax for a <BLOB substring function> is:

<BLOB substring function> ::=
SUBSTRING (blob_argument
   FROM start_argument [ FOR length_argument ])

SUBSTRING operates on three arguments: the first must evaluate to a BLOB. the other two must evaluate to exact numeric integers. It extracts a substring from "blob_argument" and returns a BLOB with a maximum length that equals the maximum length of the BLOB argument. If any of the arguments are NULL, SUBSTRING returns NULL.

The "start_argument" is a number that marks the first octet you want to extract from "blob_argument". If SUBSTRING includes the (optional) FOR clause, "length_argument" is the total number of octets you want to extract. If you omit the FOR clause, SUBSTRING will begin at "start_argument" and extract all the rest of the octets from "blob_argument". Here are some examples of SUBSTRING:

<BLOB substring function> ::=
   SUBSTRING(X'1049FE2996D54AB7' FROM 5)        -- returns 96D54AB7
   SUBSTRING(X'1049FE2996D54AB7' FROM 5 FOR 3)  -- returns 96D54A
   SUBSTRING(blob_column FROM 1 FOR 4)          -- returns the first four
                                                   octets of the value in 
                                                   BLOB_COLUMN

If "start_argument" is larger than the length of "blob_argument", or if the length of the required substring is less than one, SUBSTRING returns a zero- length binary string. If the length of the required substring is less than "start_argument", SUBSTRING will fail: your DBMS will return the SQLSTATE error 22011 "data exception-substring error".

[Obscure Rule] SUBSTRING can also operate on a bit string and a character string. We've ignored these options for now -- look for them in our chapters on bit strings and character strings.

If you want to restrict your code to Core SQL, don't use SUBSTRING with BLOBs.

<BLOB overlay function>

The required syntax for a <BLOB overlay function> is:

<BLOB overlay function> ::=
OVERLAY (blob_argument_1 PLACING blob_argument_2
   FROM start_argument [ FOR length_argument ])

OVERLAY operates on four arguments: the first two must evaluate to BLOBs, the other two must evaluate to exact numeric integers. It extracts a substring from "blob_argument_1", replacing it with "blob_argument_2", and returns the resulting BLOB. If any of the arguments are NULL. OVERLAY returns NULL.

The "start_argument" is a number that marks the first octet you want to replace in "blob_argument_1". If OVERLAY includes the (optional) FOR clause, "length_argument" is the total number of octets you want to extract from "blob_argument_1". If you omit the FOR clause, OVERLAY will begin at "start_argument" and extract the number of octets in "blob_argument_2". Here are some examples of OVERLAY:

   OVERLAY(X'1049FE2996D54AB7' PLACING X'1010' FROM 5)
    -- returns 1049FE2910104AB7

   OVERLAY(X'1049FE2996D54AB7' PLACING X'1010' FROM 5 FOR 1)
    -- returns 1049FE291010D54AB7

[Obscure Rule] OVERLAY can also operate on a character string. We've ignored this option for now -- look for it in our chapter on character strings.

<BLOB trim function>

The required syntax for a <BLOB trim function> is:

<BLOB trim function> ::=
TRIM ( [ [ { LEADING | TRAILING | BOTH } ]
      [ blob_argument_1 ] FROM ]
      blob_argument_2)

TRIM operates on two arguments, both of which must evaluate to BLOBs. It strips all leading, all trailing or all leading and all trailing trim octets from "blob_argument_2" and returns the resulting BLOB. If any of the arguments are NULL. TRIM returns NULL.

The trim specification is either LEADING (i.e.: trim all leading trim octets), TRAILING (i.e.: trim all trailing trim octets) or BOTH (i.e.: trim all leading and all trailing trim octets). If this clause is omitted, TRIM defaults to BOTH. For example, these two TRIM functions are equivalent: they both strip away all leading and all trailing zero-octets:

   TRIM(X'00' FROM blob_column)
   TRIM(BOTH X'00' FROM blob_column)

"blob_argument_1" defines the trim octet: the octet that should be stripped away by the TRIM function. If "blob_argument_1" is omitted, TRIM strips zero-octets away. For example, these two TRIM functions are equivalent: they both strip away all trailing zero-octets:

TRIM(TRAILING FROM blob_column)
TRIM(TRAILING X'00' FROM blob_column)

These two TRIM functions are equivalent: they both strip away all leading zero-octets:

TRIM(LEADING FROM blob_column)
TRIM(LEADING X'00' FROM blob_column)

These two TRIM functions are equivalent: they both strip away all leading and all trailing zero-octets:

TRIM(blob_column)
TRIM(BOTH X'00' FROM blob_column)

If the length of "blob_argument_1" is not one octet, TRIM will fail: your DBMS will return the SQLSTATE error 22027 "data exception-trim error".

[Obscure Rule] TRIM can also operate on a character string. We've ignored this option for now -- look for it in Chapter 7 "Character Strings".

<BLOB position expression>

The required syntax for a <BLOB position expression> is:

<BLOB position expression> ::=
POSITION (blob_argument_1 IN blob_argument_2)

POSITION operates on two arguments, both of which must evaluate to a BLOB. It determines the first octet position (if any) at which "blob_argument_1" is found in "blob_argument_2" and returns this as an exact numeric integer. If either of the arguments are NULL. POSITION returns NULL. If "blob_argument_1" is a zero-length binary string, POSITION returns one. If "blob_argument_1" is not found in "blob_argument_2", POSITION returns zero. Here is an example:

   POSITION(X'3D' IN X'AF923DA7')
   -- returns 5

[NON-PORTABLE] The precision of POSITION's result is non-standard because the SQL Standard requires implementors to define the result's precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of POSITION an INTEGER <data type>.

[Obscure Rule] POSITION can also operate on a bit string and a character string. We've ignored these options for now -- look for them in our chapters on bit strings and character strings.

<bit length expression>

The required syntax for a <bit length expression> is:

<bit length expression> ::=
BIT_LENGTH (blob_argument)

BIT_LENGTH operates on an argument that evaluates to a BLOB. It determines the length of the argument, in bits, and returns this as an exact numeric integer, e.g.: BIT_LENGTH(X'4AD9') returns 16. If the argument is NULL. BIT_LENGTH returns NULL.

[NON-PORTABLE] The precision of BIT_LENGTH's result is non-standard because the SQL Standard requires implementors to define the result's precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of BIT_LENGTH an INTEGER <data type>.

[Obscure Rule] BIT_LENGTH can also operate on a bit string and a character string. We've ignored these options for now -- look for them in our chapters on bit strings and character strings.

<char length expression>

The required syntax for a <char length expression> is:

<char length expression> ::=
{CHAR_LENGTH | CHARACTER_LENGTH} (blob_argument)

CHAR_LENGTH (or CHARACTER_LENGTH) operates on an argument that evaluates to a BLOB. It determines the length of the argument, in octets, and returns this as an exact numeric integer, e.g.: CHAR_LENGTH(X'4AD9') returns 2. (The octet length of a string is the bit length divided by 8, ignoring any remainder.) If the argument is NULL. CHAR_LENGTH returns NULL.

[NON-PORTABLE] The precision of CHAR_LENGTH's result is non-standard because the SQL Standard requires implementors to define the result's precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of CHAR_LENGTH an INTEGER <data type>.

[Obscure Rule] CHAR_LENGTH can also operate on a bit string and a character string. We've ignored these options for now -- look for them in Chapter 4 "Bit Strings" and Chapter 7 "Character Strings".

<octet length expression>

The required syntax for a <octet length expression> is:

<octet length expression> ::=
OCTET_LENGTH (blob_argument)

OCTET_LENGTH operates on an argument that evaluates to a BLOB. It determines the length of the argument, in octets, and returns this as an exact numeric integer, e.g.: OCTET_LENGTH(X'4AD9') returns 2. (The octet length of a string is the bit length divided by 8, ignoring any remainder.) If the argument is NULL, OCTET_LENGTH returns NULL.

[NON-PORTABLE] The precision of OCTET_LENGTH's result is non-standard because the SQL Standard requires implementors to define the result's precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of OCTET_LENGTH an INTEGER <data type>.

[Obscure Rule] OCTET_LENGTH can also operate on a bit string and a character string. We've ignored these options for now -- look for them in Chapter 4 "Bit Strings" and Chapter 7 "Character Strings".

Set functions

SQL provides three set functions that operate on binary strings: COUNT(*), COUNT, and GROUPING. Since none of these operate exclusively with binary string arguments, we won't discuss them here; look for them in Chapter 33 "Searching with Groups".

Predicates

In addition to the comparison operators, SQL provides four other predicates that operate on BLOBs: the <like predicate>, the <null predicate>, the <exists predicate> and the <quantified predicate>. Each will return a boolean value: either TRUE. FALSE or UNKNOWN. Only the first predicate operates strictly on string values; we'll discuss it here. Look for the rest in our chapter on search conditions.

<like predicate>

The required syntax for a <like predicate> is:

<like predicate> ::=
blob_argument [ NOT ] <<fixed>>LIKE<</fixed>> pattern [ ESCAPE escape_octet ]

LIKE is a predicate that operates on three operands that evaluate to BLOBs: it searches for values that contain a given pattern. NOT LIKE is the converse and lets you search for values that don't contain a given pattern. The "blob_argument" is the binary string you're searching within, the "pattern" is the pattern you're searching for and the optional "escape_octet" is an octet that tells your DBMS to treat a metacharacter in the pattern as itself (rather than as a metacharacter). If "blob_argument" contains the pattern, LIKE returns TRUE and NOT LIKE returns FALSE. If "blob_argument" does not contain the pattern, LIKE returns FALSE and NOT LIKE returns TRUE. If any of the operands are NULL. LIKE and NOT LIKE return UNKNOWN.

The pattern you specify in "pattern" may contain any combination of regular octets and metaoctets. Any single octet in "pattern" that is not a metaoctet or the "escape_octet" represents itself in the pattern. For example, this predicate:

   blob_column <<fixed>>LIKE<</fixed>> X'A3'

is TRUE for the octet represented by 'A3'.

Special significance is attached to metaoctets in a pattern. The metaoctets are: _ and %. That is, an underline octet has the same bit pattern as an underline character in the SQL_TEXT Character set and a percent octet has the same bit pattern as a percent sign in the SQL_TEXT Character set. (In practice, the bit pattern for _ will be X'5F' and the bit pattern for % will be X'25'. These values correspond to the encodings used in all the ISO character sets.)

If the predicate doesn't include an ESCAPE clause, they are interpreted as follows:

  • _ An underline octet means "any single octet". For example, this predicate: 
   blob_column <<fixed>>LIKE<</fixed>> X'A_C'

is TRUE for X'A C', X'AAC', X'ABC', X'A6C' and so on.

  • % A percent sign means "any string of zero or more octets". For example, this predicate: 
   blob_column <<fixed>>LIKE<</fixed>> X'A%C'

is TRUE for X'AC', X'A C', X'ABC', X'A6C', X'A66666C' and so on.

If you want to search for an octet that would normally be interpreted as a metaoctet, you must use the optional ESCAPE clause. To do so:

  • Pick an octet that you won't need in the pattern and designate it as your escape octet.
  • In the pattern, use your escape octet followed immediately by the metaoctet, to designate the metaoctet as an octet you want to search for. For example: 
   ... LIKE X'B%'

(without an ESCAPE clause) means "like the hexit B followed by anything at all", while:

   ... LIKE X'B?%' ESCAPE X'?'

means "like the hexit B followed by a percent octet" (since % is preceded by the escape character it has no special significance in this pattern). Your escape character can also be followed by itself in the pattern, if you want to search for the escape character. For example:

   ... LIKE X'B??' ESCAPE X'?'

means "like the hexit B followed by a question mark octet" (since ? is preceded by the escape character it has no special significance in this pattern). Your best choice for an escape character is an SQL special character which isn't a [NOT] LIKE metacharacter. We suggest the question mark.

The "escape_octet" must be exactly one octet long. If it isn't, [NOT] LIKE will fail: your DBMS will return the SQLSTATE error 2200D "data exception-invalid escape octet". If "escape_octet" is _ or % and that metaoctet is used once only in your pattern, or if "escape_octet" is used without being followed by a metaoctet (or by itself) in your pattern, [NOT] LIKE will fail: your DBMS will return the SQLSTATE error 22025 "data exception-invalid escape sequence". For example, these two predicates will both result in SQLSTATE 22025:

   LIKE X'B%B' ESCAPE X'%'
   LIKE X'B?B' ESCAPE X'?'

For the purposes of [NOT] LIKE, a substring of "blob_argument" is a sequence of zero or more contiguous octets, where each octet belongs to exactly one such substring. A substring specifier of "pattern" is either (a) _: an arbitrary octet specifier, (b) %: an arbitrary string specifier, (c) "escape_octet" followed by _ or % or "escape_octet" or (d) any other single octet. If "blob_argument" and "pattern" both have a length of zero, LIKE returns TRUE. LIKE also returns TRUE if "pattern" is found in "blob_argument". That is, LIKE returns TRUE only if the number of substrings in "blob_argument" equals the number of substring specifiers in "pattern" and all of these conditions are also met:

  • If the pattern's n-th substring specifier is _, then the argument's n-th substring must be any single octet.
  • If the pattern's n-th substring specifier is %, then the argument's n-th substring must be any sequence of zero or more octets.
  • If the pattern's n-th substring specifier is any other octet, then the argument's n-th substring must have the same length and bit pattern as that substring specifier.

[Obscure Rule] [NOT] LIKE can also operate on character strings. We've ignored this option for now -- look for it in Chapter 7 "Character Strings"

If you want to restrict your code to Core SQL, don't use the [NOT] LIKE predicate with BLOBs.

Comments

Comments loading...