BLOB(1K)
Now that we've described SQL's BLOB <data type>, let's look at some example SQL statements that put it to use.
These SQL statements make a Table with one binary string Column, insert a row, then search for any binary string equal to the bit string 01000100:
CREATE TABLE Binary_Examples ( occurrence_binary BLOB(2K));
INSERT INTO Binary_Examples ( occurrence_binary) VALUES (X'4D');
SELECT occurrence_binary FROM Binary_Examples
WHERE occurrence_binary = X'44';
BLOB
Operations
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 as follows.break
CAST (<cast operand> AS <cast target>) <cast operand> ::= scalar_expression
<cast target> ::= <Domain name> | <data type>
Page 86
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 your target <data type>. 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 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, 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, (a) don't use <Domain name> as a CAST target;
CAST only to a <data type> and (b) don't use CAST to convert any BLOB value to another <data type>.
Assignment
In SQL, when a BLOB is assigned to a binary string 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 binary string 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."
Here are some examples of the result you'll get when you move a BLOB from your database to a host language program (assume your host language variable has a variable size of 2 octets):break
source X'5550' yields a target value of 5550 source X'55' yields a target value of 55
source X'555500' yields a target value of 5555 and SQLSTATE 01004 source X'555555' yields a target value of 5555 and SQLSTATE 01004
Page 87
[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 Chapters 39, 40, and 52 on SQL binding styles.
When a binary string is assigned to an 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."
Here are some examples of the result you'll get when you assign a binary string to your SQL database (assume your target is defined as BLOB(2)):
source X'5550' yields a target value of 5550 source X'55' yields a target value of 55 source X'555500' yields a target of 5555
source X'555555' yields no change to target; assignment fails with SQLSTATE 22001
[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.continue
Page 88
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 as follows.
BLOB concatenation ::=
BLOB operand_1 || BLOB operand_2
The concatenation operator operates on two operands, each 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've already talked about casting binary strings). 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.break
Page 89
<BLOB substring function>
The required syntax for a <BLOB substring function> is as follows.
<BLOB substring functional> ::=
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:
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 fo ur octets of the value in BLOB_COLUMN
If length_argument is negative, your DBMS will return SQLSTATE error 22011
"data exception-substring error." If start_argument is greater than the length of blob_argument, or if (start_argument + length_argument) is less than one, SUBSTRING returns a zero-length binary string. If start_argument is negative, or if (start_argument + length_argument) is greater than the length of
blob_argument, that's okay — the DBMS just ignores any octets before the start of blob_argument or after the end of blob_argument.
[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 Chapter 4 "Bit Strings" and Chapter 7
"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 as follows.
<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 replace. Thus, start_argument and
length_argument identifycontinue
Page 90
the portion of blob_argument_1 that you want to replace, while blob_argument_2 is what you want to replace with. If you omit the FOR clause, then length_argument defaults to the length of blob_argument_2. Here are some examples of OVERLAY:
OVERLAY(X'ABCDE' PLACING X'01' FROM 2) -- returns AO1DE
OVERLAY(X'DEF' PLACING X'01234' FROM 2 FOR 3) -- returns D01234
OVERLAY(X'DEFABCD' PLACING X'01234' FROM 2 FOR 3) -- returns D01234BCD
[Obscure Rule] OVERLAY can also operate on a character string. We've ignored this option for now — look for it in Chapter 7 "Character Strings."
<BLOB trim function>
The required syntax for a <BLOB trim function> is as follows.
<BLOB trim function> ::=
TRIM ( [ [ { LEADING | TRAILING | BOTH } ] [ blob_argument_1 ] FROM ]
blob_argument_2)
TRIM operates on two arguments, each of which must evaluate to BLOBs. It strips all leading, all trailing, or both leading and 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 also equivalent; they both strip away all leading zero-octets:break
TRIM(LEADING FROM blob_column)
TRIM(LEADING X'00' FROM blob_column)
Page 91
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 as follows.
<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 argument is 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') -- return 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 Chapter 4 "Bit Strings" and Chapter 7 "Character Strings."
<bit length expression>
The required syntax for a <bit length expression> is as follows.
<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>.break
Page 92
[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 Chapter 4 "Bit Strings" and Chapter 7
"Character Strings."
<char length expression>
The required syntax for a <char length expression> is as follows.
<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, rounded up.) 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 an <octet length expression> is as follows.
<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, rounded up.) 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 two set functions that operate on binary strings: 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."
SQL provides two set functions that operate on binary strings: 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."