Managing BLOB Data
Depending on whether the BLOB data is stored in the data row or outside of the data row, the way you manage it will be different. When BLOB strings are stored in the data row, reading and writing the data strings can be as fast as reading and writing character and binary strings. SQL Server does not have to access separate pages of data to read or write the BLOB string. Unless the 'text in row' option is specified, BLOB strings are stored outside the data row with only the text pointers to these strings in the data row. These text pointers point to the pages that contain the string fragments that contain the actual BLOB data in multiple pages, as discussed earlier in this lesson.
Using the Large Object System Functions
Whenever you access data stored in any of the large object data types, you will use one of the following system functions. These system functions are used exclusively for the large object data types.
TEXTPTR()—Returns a binary value that represents a pointer to the TEXT, NTEXT, or IMAGE object. The pointer remains valid until the row is deleted.
TEXTVALID()—Checks whether a specified text pointer is valid.
Text pointers are used in the READTEXT, WRITETEXT, and UPDATETEXT statements to reference the BLOB strings that are not stored in the row itself. In addition, you can also use several other functions to access data stored in a BLOB. These are
PATHINDEX()—Returns the starting position of the first occurrence of the specified pattern, or zeros if the pattern is not found. This can be used on all character and text data types including TEXT and NTEXT.
DATALENGTH()—Returns the number of bytes used to store the data in the BLOB.
SUBSTRING()—Returns part of a character, binary, text, or image expression.
Using the DATALENGTH() and SUBSTRING() functions is the same with BLOBs as when they are used with smaller text objects. The following example shows you how to use both functions with a BLOB data column:
Select pub_id,
Datalength(pr_info) as 'DataLen',
Substring(pr_info,30,15) as 'short_pr_Info'
From pub_info
The output will display the actual number of bytes in each pr_info column and 15 characters from the data for each row.
pub_id DataLen short_pr_Info ------ ----------- --------------- 0736 65071 New Moon Books, 0877 675 Binnet & Hardle 1389 1476 Algodata Infosy 1622 18518 Five Lakes Publ 1756 131 Ramona Publishe 9901 114 GGG&G, publishe 9952 135 Scootney Books, 9999 544 Lucerne Publish (8 row(s) affected)
Using the TEXTPTR() Function
The TEXTPTR() function is used to retrieve the pointer value for a BLOB from the data row in the database table. A valid text pointer can be obtained even if the text value in the column is NULL. If the table is not storing the BLOBs in the data row and the BLOB column has not been initialized, the TEXTPTR() function will return a NULL pointer. To retrieve a text pointer, you must declare a variable to hold the pointer value to be used later in the SQL script. As shown here, the syntax for the TEXTPTR() function is very simple:
TEXTPTR (<column name>)
The following example shows how to use this function to retrieve the TEXT BLOB 'logo' from the Pubs database:
Use pubs Declare @ptrval varbinary(16) Select @ptrval = textptr(logo) From pub_info Where pub_id = '0736'
The only output you would get from this script is the message telling that the command completed successfully. The TEXTPTR() function was used to locate the IMAGE column logo associated with the publishers ID of '0736'. The text pointer is stored in the local variable @ptrval, which can then be used later in the SQL script.
Caution
In SQL Server 2000, if you use an 'in row' text pointer, it must be used inside a transaction. If you try to use it outside a transaction, the following message will be displayed:
Server: Msg 7101, Level 16, State 1, Line 2 You cannot use a text pointer for a table with option 'text in row'set to ON.
To display a text pointer, you simply specify the function in the SELECT statement as shown:
Use pubs Select textptr(logo) as 'Text Pointer' From pub_info Where pub_id = '0736'
When you execute this statement, the following output will be displayed:
Text Pointer ---------------------------------- 0xFFFF6E00000000005C00000001000000 (1 row(s) affected)
As you can see, displaying the text pointer is not really useful, but you will see how the text pointer is used to access the BLOB data stored in the table.
Using the TEXTVALID() Function
Before you actually use the value stored in the text pointer for a BLOB, you must make sure that it is a valid pointer. Otherwise, any statement in which you use the pointer variable will fail. The TEXTVALID() function will check the specified text pointer variable to verify that it is a valid pointer. The syntax of this function is
TEXTVALID ('Table.column', text_ptr)The function will return an integer value of 1 if the pointer is valid and a 0 if it is not valid. Using this information, you can set a local variable with the returned value and then use it to conditionally perform one or more SQL statements as shown in the following example:
Use pubs
Declare @val int
Declare @ptrval Varbinary(16)
Select @ptrval = Textptr(pr_info) from pub_info
Set @val = Textvalid('pub_info.pr_info', @ptrval)
If @val = 0
Begin
Raiserror ('This pointer is invalid', 1, 15)
End
if @val <> 0
Begin
Readtext pub_info.pr_info @ptrval 0 15
End
If the pointer value returned were invalid, this SQL script would display the following output:
Msg 50000, Level 1, State 50000 This pointer is invalid
Otherwise, the text contained in the column would be displayed as shown here:
pr_info ---------------------- This is sample (1 row(s) affected)
Caution
You cannot use a UPDATETEXT, WRITETEXT, or READTEXT statement without a valid text pointer.
Working with the PATINDEX() Function
The PATINDEX() function enables you to search a large text string for the first occurrence of a specified pattern. Here is the syntax for this function, along with the description of the two arguments:
Patindex ('%pattern%', expression)pattern This is a literal string, which can contain wildcard characters. However, if you use the '%' character, it must precede and follow the pattern unless searching for the first or last characters.
expression This is usually a column that is searched for the specified pattern.
The PATINDEX() function can be very useful when used with any text data type. It can be used in a WHERE clause as shown in the following example:
Use Pubs
Select pub_id,
title
From titles
Where Patindex('%wonderful%', notes) > 0
In this example, only titles with the word wonderful will be returned in the result set and displayed as shown.
pub_id ------ --------------------------------------------------------------- 0877 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (1 row(s) affected)
Using the READTEXT, WRITETEXT, and UPDATETEXT Statements
When working with BLOB data that is stored outside the data row, you must use the SQL statements that are designed specifically for this purpose. These statements provide you with the standard functionality you are familiar with as it relates to reading, writing, and updating the information in the BLOB. Table 21.2 lists each of the three functions and a description of how they are used.
Both the READTEXT and UPDATETEXT statements enable you to work with chunks or blocks of a text column at a time. The WRITETEXT statement does not allow you to work with chunks, only with the entire column. In addition, the WRITETEXT and UPDATETEXT statements will not log the operation by default, although you can specify that you want logging to be active.
Using the WRITETEXT Statement
The WRITETEXT statement completely overwrites an existing text or image column by providing the column name, the text pointer for the column of a specific row, and the actual data to be written. The syntax of this statement is
WRITETEXT {table.column text_ptr}
{data}
Remember that for the WRITETEXT statement to work properly, the column must already contain a valid text pointer. If the column does not contain a valid text pointer, you must initialize it by using a variation on one of the following:
Explicitly insert a non-NULL value in the text column when you use an INSERT statement. The WRITETEXT statement will overwrite whatever you put in that column anyway.
Define a default on the column with a non-NULL value.
Explicitly update the row after inserting it, and then set the column to NULL.
The following example shows how to use the WRITETEXT statement along with the TEXTPTR() function to ensure that the text pointer is valid:
Use Pubs
Declare @ptrval varbinary(16)
Select @ptrval = Textptr(pr_info)
From pub_info
Where pub_id = '999'
If @ptrval Is Not Null
Writetext pub_info.pr_info @ptrval 'Test Data'
You can see that we check for a valid text pointer value by using the 'Is Not Null' condition. If the text pointer were valid, the following statement would overwrite the text data of the row where the pub_ID is 999. Because you can reference only one text pointer at a time, you would normally use cursor processing when working with BLOBs.
Using the READTEXT Statement
The READTEXT statement is very similar to the WRITETEXT statement, except that it enables you to specify a starting position and the length of data to be read. The basic syntax for this statement is
Readtext [table_name.column_name
Text_ptr offset size [Holdlock]
With the READTEXT statement, you can work with either the entire contents of the data or with any specified portion of the data. The following example shows how you can retrieve any part of the contents; in fact, you can see how to retrieve the entire contents even if you do not know the length of the data. The DATALENGTH() function provides you with the actual length of the data in the column and you can use it in the READTEXT statement.
Use pubs
Declare @ptrval varbinary(16)
Declare @dataLen int
Select @ptrval = TEXTPTR(pr_info),
@dataLen = datalength(pr_info)
From pub_info
Where pub_id = '9901'
Readtext pub_info.pr_info @ptrval 0 25
Readtext pub_info.pr_info @ptrval 0 @datalen
The output displayed by this example is
pr_info ---------------------------------- This is sample text data (1 row(s) affected) pr_info --------------------------------------------------------- This is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G is located in München, Germany. (1 row(s) affected)
Using the UPDATETEXT Statement
The UPDATETEXT statement enables you to work with the text in pieces to insert, overwrite, or append data to the BLOB. You can also copy data from another text column and append it or overwrite the column with it. The syntax of this statement is a bit more complex than the other two statements we have looked at. Table 21.3 describes the arguments of the UDPATETEXT statement.
UPDATETEXT { table_name.dest_column_name dest_text_ptr }
{ NULL | insert_offset }
{ NULL | delete_length }
[ inserted_data
| { table_name.src_column_name src_text_ptr } ]
The following example shows how to use the UDPATETEXT statement to modify a single character in the pr_info column. In addition, it displays the section of text before and after the update to show the change.
Use pubs
Declare @ptrval binary(16)
Select @ptrval = TEXTPTR(pr_info)
From pub_info pr, publishers p
Where p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
Readtext pub_info.pr_info @ptrval 80 10
Updatetext pub_info.pr_info @ptrval 88 1 'b'
Readtext pub_info.pr_info @ptrval 80 10
Results:
pr_info ------------ . New Moon (1 row(s) affected) pr_info ------------ . New Mobn (1 row(s) affected)