Chapter 11 - Using BLOBs

  • Article

This chapter discusses the basic principles involved when working with BLOBs (binary large objects) in Microsoft® SQL Server™ 2000. Applying lessons learned from the design and implementation of the Microsoft TerraServer database, this chapter discusses issues to consider when designing tables with BLOB data. Using the Northwind database in SQL Server 2000, this chapter shows how to create a table with text, ntext, or image columns, and how to insert and modify BLOB data on the server and on the client.

In this chapter and in SQL Server Books Online, BLOB is a generic term that refers to text, ntext, and image data. In the 1999 SQL ANSI standard (SQL-99), BLOB refers to large binary-valued image data. CLOB refers to large text data, and NCLOB refers to large ntext data.

The code examples in this chapter are also available on the SQL Server 2000 Resource Kit CD-ROM in the file \Docs\ChapterCode\Ch11Code.txt. For more information, see Chapter 39, "Tools, Samples, eBooks, and More."

This chapter contains the following sections:

  • Designing BLOBs

    BLOB Storage in SQL Server. Discusses ways that SQL Server can store BLOB data, and discusses the text in row option and the text pointer functionality. 

    Learning from the Microsoft TerraServer Design and Implementation. Discusses the options database designers have when creating tables with text, ntext, or image columns.

    BLOBs in Special Operations. Identifies information needed when BLOBs are part of operations such as backing up and restoring a database. 

  • Implementing BLOBs 

    BLOBs on the Server. Discusses ways to use Transact-SQL to manipulate BLOB data on the server when data is stored outside the data row (out-row text, out-row BLOB) and when it is stored inside the row (in-row text, in-row BLOB).

    BLOBs on the Client. Discusses ways to work with BLOB data on the client, and how to use the application programming interfaces (API) ADO, OLE DB, and ODBC.

  • Working with BLOBs 

    Summarizes considerations when working with BLOBs in SQL Server. 

Designing BLOBs

Cc917636.spacer(en-us,TechNet.10).gif Cc917636.spacer(en-us,TechNet.10).gif

This topic covers issues to consider before programming the database application with text, ntext, and image data:

  • How BLOB data is stored in SQL Server 

  • How to learn from applications that have used BLOBS successfully in SQL Server 

  • Information about using BLOBs when they are part of operations such as backing up a database 

BLOB Storage in SQL Server

BLOBs are very large variable binary or character data, typically documents (.txt, .doc) and pictures (.jpeg, .gif, .bmp), which can be stored in a database. In SQL Server, BLOBs can be text, ntext, or image data type:

text 

Variable-length non-Unicode data, stored in the code page of the server, with a maximum length of 231 - 1 (2,147,483,647) characters.

ntext 

Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text.

image 

Variable-length binary data from 0 through 231 - 1 (2,147,483,647) bytes.

SQL Server handles these data types the same: SQL Server stores image data in the same way it stores text or ntext data, under the same conditions. It is common for text, ntext, and image data to be called generally as text data.

Each text, ntext, and image column in a table can contain up to 2 gigabytes (GB) of BLOB data. The BLOB data is stored in a collection of 8-kilobyte (KB) data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure.

SQL Server 2000 introduced an option in which small-sized BLOBs can be stored in the text, ntext, and image columns in the same data row as other columns of a table. This option, called text in row because the BLOB data is stored in the data row, must be enabled explicitly. Until the release of SQL Server 2000, all text data was stored outside the data row (out row). Only 16-byte pointers to the root of the BLOB B-tree structure were stored in the text, ntext, or image columns of a table (BLOB root pointer).

Out-Row BLOBs

Unless the text in row option is enabled for a table, and therefore belongs in a SQL Server 2000 database, you do not need to know whether SQL Server 7.0 or SQL Server 2000 is running to determine how the BLOB data is stored; both versions of SQL Server store BLOBs outside the row. Only BLOB root pointers are stored in the text, ntext, or image columns in the table. The BLOB root pointers point to the root of the B-tree structure that actually stores the BLOB data. When the size of the BLOB data is less than or equal to 64 bytes, the root structure holds the data. When the BLOB size is longer than 64 bytes, the root structure holds the links that map the path to the data, which are stored in the leaf nodes of the B-tree.

Cc917636.outrwtext(en-us,TechNet.10).gif

In-Row BLOBs

The text in row option allows small-sized text, ntext, and image data to be stored inside a table row. When the text in row option is enabled, SQL Server stores the BLOB data in the data row if the size is less than or equal to the in-row limit, and if there is enough space in the row to hold the BLOB data. The text in row limit applies to each BLOB column in the row. Thus, if the in-row limit is 256 bytes, each BLOB column in the row can store up to 256 bytes of data as long as the total bytes for the row does not exceed 8,060 bytes.

When the text, ntext, or image data is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or modify the data. Reading and writing the in-row text, ntext, or image data becomes as fast as reading or writing varchar, nvarchar, or varbinary data.

When the BLOB string is too long for the data row and is stored in a separate BLOB page, with the text in row option enabled, SQL Server continues to access the data faster. With in-row BLOB, the root structure is stored in the data row when the BLOB data itself is too large. Thus, the path by which SQL Server accesses the BLOB data is shorter. This is different from regular out-row BLOBs where the data row never holds the BLOB data, and where the root structure that holds pointers to the BLOB data is in a separate page.

Cc917636.inrwtext(en-us,TechNet.10).gif

The text, ntext, or image strings stored in the data row are stored in the same way as other variable-length data is stored. SQL Server uses only the number of bytes needed to store the BLOB string even if the specified text in row limit is longer.

If a text, ntext, or image string is longer than the text in row option limit or the available space in the row, the BLOB root structure is stored in the row. The root structure holds pointers to the text fragments stored in separate data pages. SQL Server uses only enough space to hold the root structure; however, the row must have enough space to hold the root structure, and the space needed to store the root structure should be shorter than the text in row option limit. If the row data, including the BLOB root structure, is larger than 8,060 bytes, the insert action will fail.

If a table has several text, ntext, or image columns, and you attempt to insert multiple text, ntext, or image strings, SQL Server assigns space to the strings one at a time, based on the order of the column ID.

For example, TableS contains six columns; four are BLOB columns. The text in row option is enabled and the text in row limit is 1,000 bytes. If you insert into TableS a row that includes a total of 5,060 bytes for the non-BLOB columns and 900 bytes for each of the four BLOB columns, SQL Server:

  1. Inserts the 5,060 bytes to the non-BLOB column. 

  2. Reads the column IDs of the BLOB columns and identifies the order. 

  3. Inserts 900 bytes to the first three BLOB columns, based on the column IDs. 

  4. Places a root structure in the fourth BLOB column and stores the 900-byte BLOB in a separate data page. 

The sp_tableoption stored procedure is used to manage the text in row option on tables that have text, ntext, or image columns. Use sp_tableoption to set text in row on or off, and to set the option limits. The text in row option limit defaults to 256 bytes, but can be set from 24 bytes through 7,000 bytes.

For more information about sp_tableoption, see SQL Server Books Online.

These examples show ways to use sp_tableoption to change text in row option settings on the Employees table of the Northwind database.

Code Example 11.1 

--Turns option ON and sets in-row limit to default, 256 bytes.
sp_tableoption 'Employees', 'text in row', 'on' 

--Sets in-row limit to 5,000 bytes.
sp_tableoption 'Employees', 'text in row', '5000' 

--Turns option OFF.
sp_tableoption 'Employees', 'text in row', 'off' 

--Results in error; parameter is below the allowed range.
sp_tableoption 'Employees', 'text in row', '20' 

--Results in error; parameter is above allowable range.
sp_tableoption 'Employees', 'text in row', '7003'

Considerations When Enabling or Changing text in row Option Values 

When you enable the text in row option in a table or change the text in row option settings of a table, consider the following:

  • When the text in row option is enabled for the first time, SQL Server does not convert the existing text, ntext, or image data to in-row text immediately; it makes the conversion when the text is updated. Any text, ntext, or image data inserted after the text in row option has been enabled is inserted as an in-row text. 

  • When the text in row option limit is increased, SQL Server does not convert the existing text, ntext, or image data to adhere to the new limit immediately; it makes the conversion when the data is updated. Any text, ntext, or image data inserted after the limit is increased adheres to the new limit. 

  • Turning off the text in row option can be a long-running, logged operation. SQL Server locks the table and converts all in-row text, ntext, and image data to regular text, ntext, and image strings. The length of time the procedure must run, and the amount of data modified depends on how much text, ntext, and image data must be converted from in-row to out-row text. 

  • Reducing the text in row option limit can be a long-running, logged operation. SQL Server locks the table and changes all in-row text, ntext, and image columns, which have current in-row data longer than the new limit, to adhere to the new limit. 

Out-Row and In-Row BLOB Functionality Differences

When BLOB data is stored outside the data row, the data stored in the BLOB column depends on whether or not text in row is enabled. If text in row is enabled, and the BLOB data is too large for the row, the root structure is stored in the row. The root structure contains pointers to the text fragments stored in separate data pages. If text in row is not enabled, BLOB data is never stored in the row, and the BLOB root pointer is stored in the row. The BLOB root pointer points to the root structure that contains pointers to the text fragments.

The text pointers are 16-byte handles with which text fragments are accessed. Whether or not the text in row option is on, you obtain the text pointer value by using the TEXTPTR function. For more information, see SQL Server Books Online.

The text pointers for in-row text differ from text pointers for out-row text. The following table summarizes the functionality differences. For more information, see "Managing ntext, text, and image Data" in SQL Server Books Online.

Functionality

Out-Row Text

In-Row Text

Lifetime

Valid while the row exists.
Actions that invalidate text pointers:
· Deletion of a row

Valid only within a transaction.
Actions that invalidate in-row text pointers:
· Termination of a session, even if a transaction is still active
· Deletion of a row (DELETE)
· Schema changes to a table through DDL statements (ALTER TABLE, CREATE [CLUSTERED] INDEX, DROP [CLUSTERED] INDEX, DROP TABLE, TRUNCATE TABLE)
· sp_indexoption
· sp_tableoption with text in row option
It is recommended that text pointers are invalidated explicitly after they are needed through the sp_invalidate_textptr stored procedure.

Locking

Data row is not locked.
The only way to lock the data row is to raise the isolation level to Repeatable Read or higher.

Data row is locked in Shared (S) mode while text pointer is valid, when the transaction is Read Committed isolation level or higher. When the transaction ends, the text pointer becomes invalid and the lock is released.
No locks are needed and none are placed on the data row if the isolation level of the transaction is Read Uncommitted or the database is in read-only mode.

Number Limit

Allows an unlimited number of text pointers.

Allows 1,024 text pointers for each database in a transaction. If a transaction is operating on more than one database, each database in that transaction can have a maximum of 1,024 transactions.

Null Values

You cannot obtain a text pointer when you insert NULL text. To obtain a text pointer, you must use the UPDATE statement to set the text to NULL. At that time, SQL Server allocates an 84-byte root structure for the NULL value.

You can obtain a text pointer on NULL text.
When you update a text value to NULL, SQL Server does not allocate any root space.

SQL Server 2000 introduced the sp_invalidate_textptr stored procedure as a tool for explicitly invalidating in-row text pointers. If the optional text pointer value is specified, the stored procedure invalidates that text pointer; otherwise, sp_invalidate_textptr invalidates all text pointers in the transaction. This stored procedure can be used only when the text in row option is enabled.

Due to the limited number of text pointers allowed and their use of system resources, text pointers should be invalidated explicitly when they are not needed. Invalidating text pointers through sp_invalidate_textptr is an efficient and quick process that will not affect performance.

For more information about sp_invalidate_textptr, see SQL Server Books Online.

The following code samples illustrate the interaction among transaction levels, locking, and text pointers.

Code Example 11.2 

--Use the example table, Tbl, and turn text in row ON.
DROP TABLE Tbl
GO

CREATE TABLE Tbl (EmployeeID int, LastName nvarchar(40), Notes ntext)

EXEC sp_tableoption 'Tbl', 'text in row', 'on'

INSERT INTO Tbl VALUES(1, 'Davolio', 'This field holds information on a specific employee.')
INSERT INTO Tbl VALUES(2, 'Fuller', 'This field holds information on a specific employee.')

SELECT * FROM Tbl

--Here is the result set.
1 Davolio This field holds information on a specific employee.
2 Fuller This field holds information on a specific employee.

--Although the transaction isolation level is READ UNCOMMITTED, this code will succeed because READTEXT is only reading the data. 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr VARBINARY(16)
SELECT @ptr=TEXTPTR(Notes)
FROM Tbl
WHERE EmployeeID=2
READTEXT Tbl.Notes @ptr 17 26
COMMIT TRAN
GO

--Because the transaction isolation level is READ UNCOMMITTED and WRITETEXT is updating an existing ntext column, this code will fail. 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr VARBINARY(16)
SELECT @ptr=TEXTPTR(Notes)
FROM Tbl
WHERE EmployeeID=2
WRITETEXT Tbl.Notes @ptr 'This is the changed text information on the employee.'
COMMIT TRAN
GO

--This is the error message:
You cannot update a blob with a read-only text pointer. The statement has been terminated.

--When the transaction isolation level is raised to READ COMMITTED, the same transaction will succeed.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRAN
DECLARE @ptr VARBINARY(16)
SELECT @ptr=TEXTPTR(Notes)
FROM Tbl
WHERE EmployeeID=2
WRITETEXT Tbl.Notes @ptr 'This is the changed text information on the employee.'
COMMIT TRAN
GO

SELECT Notes
FROM Tbl
WHERE EmployeeID=2
--The result shows the changed text.

The following code confirms that each database in a transaction is limited to 1,024 valid text pointers.

Code Example 11.3 

DROP TABLE Tbl
GO
CREATE TABLE Tbl (EmployeeID int, LastName nvarchar(40), Notes ntext)
EXEC sp_tableoption 'Tbl', 'text in row', 'on'

BEGIN TRAN
DECLARE @ptrval VARBINARY(16)
DECLARE @ivar INTEGER
SET @ivar = 1
WHILE @ivar <= 1025 BEGIN
INSERT Tbl VALUES ( 1,'', 'mnopqrstuvwxyz')
SET @ivar = @ivar + 1
END
SELECT COUNT(*) FROM Tbl
SET @ivar = 1
WHILE @ivar <= 1024 BEGIN
SELECT @ptrval = TEXTPTR(Notes) FROM Tbl WHERE EmployeeID=@ivar
SET @ivar = @ivar + 1
END
PRINT 'You will get an error here. You can have up to 1,024 text pointers per database per transaction.'
SELECT @ptrval = TEXTPTR(Notes) FROM Tbl WHERE EmployeeID = 1025
DELETE Tbl WHERE EmployeeID = 1
PRINT 'Now, you can read the data. The DELETE explicitly invalidated one text pointer, so there are only 1,024 text pointers.'
SELECT @ptrval = TEXTPTR(Notes) FROM Tbl WHERE EmployeeID = 1025
READTEXT Tbl.Notes @ptrval 0 1
COMMIT
GO

Learning from the TerraServer Design and Implementation

BLOBs are data types that can be very large and can place heavy demands on disk, memory, and network resources. When designing tables containing text, ntext, and image data types, the database designer needs to be aware of the choices available for BLOB data storage and the implications of each choice.

This topic discusses when to use, and how to design the use of, BLOBs in SQL Server applications. These guidelines are based on techniques developed by the SQL Server team in creating Microsoft TerraServer, a very large database application that provides free public access to maps and aerial photographs of the United States over the Internet. TerraServer contains more than 250 million .jpeg and .gif files stored in BLOB fields, which demonstrates the ability of SQL Server 7.0 and SQL Server 2000 to scale to very large databases. For more information about TerraServer, see https://www.terraserver.microsoft.com/.

Based on the SQL Server TerraServer implementation, these choices must be considered when designing tables with BLOBs:

  • Use a BLOB data type, or a varchar or varbinary data type. 

  • Store BLOBs either in a database or in a file system. 

  • Store each large object in one field, or divide the object into several BLOB fields. 

BLOB Data Type vs. varchar or varbinary Data Type

Binary large objects do not need to be stored as text, ntext, or image data; they can be stored in tables as varchar or varbinary data types. The choice of data type depends on the predominant size of the BLOBs to be stored. If the data will never exceed 8 KB, use varbinary or varchar data types. If the size of these large objects will exceed 8 KB, use text, ntext, or image data types.

Database or File System

Web applications often have graphics associated with tabular data. For example, real estate Web sites typically include photographs of homes for sale. On company intranet sites, client databases can contain image files of client products. For such applications, a common design question involves whether the images should be stored in the database or in a file system. In most cases, the best choice is to store the images in the database together with the other data.

Storing the images in a database is the better choice if the application in which the images will be used count on the benefits of a database system. The benefits of storing the images in the database include:

  • Scalability. Although file systems are designed to handle a large number of objects of varying sizes, file systems usually are not optimized for a huge number (tens of millions) of small files. Database systems are optimized for such cases. 

    Availability. SQL Server has availability features that extend beyond those provided by the file system.

    • SQL Server replication is a set of solutions that allow you to copy, distribute, and potentially modify data in a distributed environment. 

    • Log shipping provides a way of keeping a stand-by copy of a database in case the primary system fails. 

Storing images in a file system would be a better choice if:

  • The application in which the images will be used requires streaming performance, such as real-time video playback. 

  • BLOBs require frequentaccess by applications, such as Microsoft PhotoDraw® or Adobe Photoshop, which only know how to access files.

  • You want to use some specific feature in the NTFS file system such as Remote Storage.

As with most guidelines though, these points can assist in decision making only after a thorough research of the specific use, environment, and purpose of the application.

Storing BLOBs

A database may follow good entity modeling and have a logical design, but if the application and users cannot access and manipulate the BLOB data stored in it efficiently, the database is not effective. BLOBs are large and can put a great deal of stress on the underlying memory, disk, and network hardware. More than any other data type, BLOBs require database designers to understand how the application will access and use the BLOBs in practice. Spend time designing how the BLOB objects will be represented in the database. Analyze how the BLOB data is going to be used, and by whom. How you lay out the large objects within the database affects both the response time and throughput of the application. This topic uses the TerraServer implementation to illustrate the choices available for storing BLOBS, and their impact on performance.

Users around the world access TerraServer using a Web browser. In the TerraServer Web site users choose if a single TerraServer Web page contains a 400 x 200 pixel image, a 600 x 400 pixel image, or an 800 x 600 pixel image. Buttons on the Web page give users the ability to move an image to the north, south, east, or west, and zoom in or zoom out on an image. Depending on their Internet connection, users could choose a large or small image size. (The TerraServer application assumed that users might be accessing the Internet using a slow-speed modem, and might have a small computer monitor.)

A set of Web servers running Microsoft Internet Information Services (IIS) host the TerraServer application logic and generate HTML Web pages that are sent back to the Web browsers of users. The Web servers execute SQL queries to retrieve the meta data and image data stored in database tables. As of the last quarter of 2000, TerraServer contained 157,000 images produced by the United States Geological Survey (USGS). The average size of a black and white image was 45 MB, and the average single-color infrared image is 151 MB. The average image was 6,200 by 7,700 pixels.

Given this scenario, the choices for BLOB handling became evident.

  • Store each image file as a single BLOB. This choice may be an obvious one, but it is not practical. The average black and white image is 45 MB, which could take hours to download over the Internet to a Web browser. 

  • Process the BLOB using the middle-tier system. For TerraServer, the middle-tier arrangement means the Web servers extract the appropriate number of pixels from a 45-MB BLOB, compress the extracted pixels in a format supported by the browser, and then send the compressed format to the user. From a database system perspective, this is the simplest design. From the perspective of TerraServer, it could have been a major mistake. TerraServer averages 46,000 visitors per day accessing 1 million Web pages containing images. At times, 3,000 or more simultaneous users are connected to TerraServer. Such a design would have taxed the disk, memory, and network components of the SQL Server database and Web servers.

  • Divide and store image files in tiles. Terra Server implemented this choice because the design strategy supports the TerraServer application optimally. TerraServer is designed so an image starts to appear on the Web browser immediately, regardless of the connection speed to the Internet. In the current design, each image tile is stored in the TerraServer database as a single 200 pixel by 200 pixel BLOB compressed to 9.5 KB in either .jpeg or .gif format. The smallest TerraServer image page contains two 200 x 200 pixel image tiles. The TerraServer Web page is a 25-KB HTML document containing references (IMG SRC tags) to the BLOBs in the database; therefore, it takes only a few seconds on a slow modem to transfer 34.5 KB of data.

For an example of how a console application can load image data in the image data type field of a database, run the Bii utility. Bii detects when an image field is the destination in the database and the input is a file name that can be located in the file system. The Bii.exe (Bii) utility is available on the SQL Server 2000 Resource Kit CD-ROM in the folder \ToolsAndSamples\Bii.

BLOBs in Special Operations

When you store BLOBs in a database, certain operations may be affected.

  • Backup and restore operations 

    When designing your database backup strategy, consider that SQL Server 2000 can perform text and image operations with considerably less transaction log usage, depending on the recovery model selected for the database. Selecting a recovery model involves tradeoffs in performance and risks of transaction loss. For information about guidelines for selecting a recovery model, see "Selecting a Recovery Model" in SQL Server Books Online. 

    For more information, see "Backup and Restore Operations" and "Using Recovery Models" in SQL Server Books Online. 

  • Copy and logged operations 

    If your database application sends or receives large amounts of BLOB data, consider changing the packet size of the chunks of data that transfer requests and results between clients and servers. To change or set the packet size, use the network packet size option in Configuration Options. A packet size larger than the 4,096-byte default may improve efficiency by lowering the network reads and writes. A packet size of 8,080 bytes is best for performance in SQL Server 2000 and SQL Server 7.0. 

  • Full-text index and search operations 

    You can index and search certain types of data stored in BLOB columns. When a database designer decides that a table will contain a BLOB column and the column will participate in a full-text index, the designer must create, in the same table, a separate character-based data column that will hold the file extension of the file in the corresponding BLOB field. During the full-text indexing operation, the full-text service looks at the extensions listed in the character-based column, applies the corresponding filter to interpret the binary data, and extracts the textual information needed for indexing and querying. 

    When a field in a BLOB column contains documents with one of the following file extensions, the full-text search service uses a filter to interpret the binary data and extract the textual information.

    • .doc 

    • .txt 

    • .xls 

    • .ppt 

    • .htm 

    The extracted text is indexed and becomes available for querying.

    In addition, after it has been full-text indexed, text data stored in BLOB columns can be queried using the CONTAINS or FREETEXT predicates. For example, this query searches the Description column in the Categories table for the phrase "bean curd". Description is an ntext column.

Code Example 11.4 

USE Northwind
GO
SELECT Description
FROM Categories
WHERE CONTAINS(Description, ' "bean curd" ' )

For more information, see "Full-text Search" and "Full-text Indexes" in SQL Server Books Online.

Implementing BLOBs

Cc917636.spacer(en-us,TechNet.10).gif Cc917636.spacer(en-us,TechNet.10).gif

BLOB data types are similar to other data types. SQL statements can insert, modify, and retrieve them from tables, and SQL functions can compute the length of BLOB strings.

However, BLOB data types are opaque; they cannot be compared. Because BLOBs can be very large, and moving them around can be resource intensive, applications may not want to cache their values.

On the server, when programming tables with BLOB data, you can use text, ntext, and image data types in SELECT, INSERT, UPDATE, and DELETE statements, but you cannot use them with most aggregate functions as well as clauses and expressions that perform comparisons (GROUP BY, HAVING, COMPUTE, and ORDER BY).

In addition, you can use the text pointer, which is a programming element defined to provide chunked manipulation of BLOB data. As discussed in previous sections in this chapter, there are two kinds of text pointers, the out-row text pointer and the in-row text pointer.

This table summarizes the Transact-SQL statements, functions, and stored procedures to use in programming BLOBs on the server. These Transact-SQL elements are used for text, ntext, and image data types, unless otherwise specified. In addition, many of these Transact-SQL elements are not specific for BLOBs, but also are used for other data types.

Transact-SQL

Description

@@TEXTSIZE

Returns the current value of the TEXTSIZE option in the SET TEXTSIZE statement
When used for ntext data, the value returned is @@TEXTSIZE/2 characters.

CREATE TABLE

Creates tables that can contain text, ntext, or image columns

DATALENGTH

Returns the number of bytes used to represent the expression

DELETE

Removes rows from tables

INSERT

Adds new rows to tables

PATINDEX

Used only for text data types (not image or ntext)
Returns the starting position of the first occurrence of a pattern in a specified expression on valid text data types

READTEXT

Reads text, ntext, or image values from text, ntext, or image columns, starting from a specified offset and reading the specified number of bytes

SELECT

Retrieves all or specified row data from tables

SET TEXTSIZE

Specifies the size of text, ntext, and image data returned with a SELECT statement
When used for ntext data, the value is size/2 characters.

sp_invalidate_textptr

Invalidates text pointers

sp_tableoption

Sets the text in row option on or off, and specifies option limits

SUBSTRING

Retrieves a block of data starting at a specific offset from the start of a column

TEXTPTR

Returns the value, in varbinary format, of the text pointer that corresponds to a text, ntext, or image column

TEXTVALID

Checks if a specified text pointer is valid

UPDATE

Changes existing data in tables

UPDATETEXT

Changes a portion of existing text, ntext, or image field

WRITETEXT

Replaces data in existing text, ntext, or image field

BLOBs on the Server

This topic discusses the process of creating tables with BLOB columns, inserting and modifying BLOB data, retrieving this data, and getting information about the BLOB data and columns using Transact-SQL. For more information about specific Transact-SQL statements and functions, see SQL Server Books Online.

  1. Creating tables with BLOB columns 

    A table with BLOB columns is created using the CREATE TABLE statement (the same way as tables without BLOB columns).

    Code Example 11.5 

    CREATE TABLE Tbl (EmployeeID int, LastName nvarchar(40), Notes ntext)

SQL Server can store the BLOB columns of the table on filegroups separate from the filegroup that stores the rest of the table by using the TEXTIMAGE\_ON keyword. By default, SQL Server stores the BLOB columns in the same filegroup as the table. 

**Code Example 11.6** 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">DROP TABLE Tbl

GO CREATE TABLE Tbl (EmployeeID int, LastName nvarchar(40), Notes ntext) TEXTIMAGE_ON [DEFAULT] 

Designing database schema carefully becomes even more important when your tables contain BLOB columns. You cannot use ALTER COLUMN in the ALTER TABLE statement if the column you are changing is **text**, **ntext**, or **image**. (A **timestamp** column also cannot be altered.)

**Code Example 11.7** 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">--This statement will generate an error.

ALTER TABLE Tbl ALTER COLUMN Notes text

--This is the error. Cannot alter column 'Notes' because it is 'ntext'. 

All BLOB data that you insert into the table will not be stored in the data row. Instead, an out-row BLOB root pointer that points to the root structure of a data tree is stored in the row. To store BLOB data in row, execute the **sp\_tableoption** stored procedure with the **text in row** option. 

**Code Example 11.8** 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">--This command turns on text in row with the default in-row limit of 256 bytes.

USE Northwind GO EXEC sp_tableoption 'Employees', 'text in row', 'on' 

You can enable the **text in row** option at any time, but changing from out-row to in-row BLOB storage or from in-row to out-row BLOB storage can be long-running operations when the table already has **text** data, which has to be converted. For more information, see "Considerations When Enabling or Changing text in row Option Values" in this chapter. 

  1. Modifying BLOB values 

    Transact-SQL provides several ways with which you can modify BLOB fields in a table.

    • Insert the actual data using the INSERT statement. 

      Code Example 11.9 

      DROP TABLE Tbl
      GO
      CREATE TABLE Tbl (EmployeeID int, LastName nvarchar(40), Notes ntext)
      INSERT Tbl VALUES(1, 'Davolio', 'This field holds information on a specific
      employee.')
      INSERT Tbl VALUES(2, 'Fuller', 'This field holds information on a specific
      employee.') 

      If the table does not have in-row text and you insert a null value into a text, ntext, or image column, SQL Server saves space by not creating a text pointer and not allocating an 8-KB text page for the NULL value.

    • Modify a text column using the UPDATE statement. 

      Code Example 11.10 

      --This example updates the Notes for Employee 2.
      UPDATE Tbl
      SET Notes = 'This (just updated) field holds information on a specific employee.'
      WHERE EmployeeID=2 

      When you use an UPDATE statement to modify a text, ntext, or image column, the UPDATE initializes the column. This means a valid text pointer is assigned to the column, and at least one data page is allocated for the column.

      UPDATE actions are logged operations. Thus, when text, ntext, or image data is written to the database using the UPDATE statement, the operation can fill the transaction log with the large amount of data that typically comprise BLOB data types. To avoid this, use the WRITETEXT or UPDATETEXT statements to replace large blocks of text, ntext, or image data. By default, these statements are not logged. 

      You do not obtain text pointers automatically when the BLOB data you insert in a table with text in row enabled is too large. You must use the TEXTPTR function to obtain the text pointer. 

    • Change the whole block of text data using the WRITETEXT statement. 

      The WRITETEXT statement rewrites, or replaces, the entire data value for the text, ntext, or image field. A WRITETEXT operation is not logged if the database recovery model is simple or bulk-logged. For WRITETEXT to work properly, the column must already contain a valid text pointer. 

      Code Example 11.11 

      BEGIN TRAN
      DECLARE @ptrval varbinary(16)
      SELECT @ptrval = TEXTPTR(Notes)
      FROM Tbl
      WHERE EmployeeID = 1
      WRITETEXT Tbl.Notes @ptrval 'The original text in this field is delimited by
      brackets [This field holds information on a specific employee.]. '
      COMMIT 

    • Change part of the data using the UPDATETEXT statement. 

      To change a portion of a text, ntext, or image * *field rather than the entire field, use the UPDATETEXT statement. 

      This example uses UPDATETEXT to update an employee's academic degree in the Employees table. 

      Code Example 11.12 

      USE Northwind
      GO
      BEGIN TRAN
      DECLARE @ptrval varbinary(16)
      SELECT @ptrval = TEXTPTR(Notes)
      FROM Employees
      WHERE EmployeeID = 9
      UPDATETEXT Employees.Notes @ptrval 11 20 'Master''s degree in Medieval
      Literature'
      COMMIT 

  2. Retrieve BLOB values 

    The SELECT statement, on its own and using functions, is the main way in which BLOB values or information about BLOB values is retrieved. Use the SELECT statement for the following tasks:

    • To reference a BLOB column.

      For example, this query retrieves Photo and Notes, which are image and text columns, from the Employees table of the Northwind database 

      Code Example 11.13 

      SELECT Photo, Notes
      FROM Employees 

      You cannot use ntext, text or image columns in the select list of subqueries. 

    • To retrieve the binary value of image files stored in image columns, or the text value of data stored in text or ntext columns. 

      Code Example 11.14 

      SELECT Photo, Notes
      FROM Employees
      WHERE EmployeeID='1' 

      The default limit of the length of the text data returned by a SELECT statement is 4,000 bytes. The default length of text or ntext columns included in the select list is set by the smallest of the actual size of the text, the default TEXTSIZE session setting, or the hard-coded application limit.

      The current TEXTSIZE setting is reported by the @@TEXTSIZE function. The full amount of data is returned if the length is less than TEXTSIZE. (The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set @@TEXTSIZE to its maximum of 2 GB, 2,147,483,647 bytes.) TEXTSIZE is set at execute time and not at parse time. 

      For example, to determine the current TEXTSIZE setting, execute this query. 

      Code Example 11.15 

      SELECT @@TEXTSIZE
      --Returns 64512 

      To specify or change the length of the returned text for the session use the SET TEXTSIZE statement, which specifies the size of text and ntext data returned with a SELECT statement. The maximum setting for SET TEXTSIZE is 2 GB, as specified in bytes. Setting the TEXTSIZE to 0 resets it to the default of 4 KB, 4,096 bytes. 

      Code Example 11.16 

      SET TEXTSIZE 0 

      SELECT @@TEXTSIZE --Shows that the TEXTSIZE has changed to 4,096. 

      To determine the number of bytes used to represent a BLOB data, use the DATALENGTH function, which is useful for data types that store variable-length data. (When the value of the data field is NULL, the DATALENGTH is NULL.) 

    • To retrieve blocks of text, ntext, or image data. 

      Use the SUBSTRING function with the SELECT statement to retrieve blocks of BLOB data. The READTEXT statement also can be used to retrieve blocks of data. You use this statement after obtaining the text pointer value from a BLOB field by using the TEXTPTR function with the SELECT statement. 

      To retrieve blocks of BLOB data up to 8 KB, use SUBSTRING; to retrieve larger blocks of BLOB data, use READTEXT.

      Use the SUBSTRING function to retrieve parts of text, ntext, or image data starting from a specific offset from the start of the column. When used with BLOB data, offsets, which are the start and length of the data, must be specified in bytes. The returned string when the expression is a BLOB is as follows:

      Given expression

      Return type

      Text 

      Varchar 

      Ntext 

      Nvarchar 

      Image 

      Varbinary 

      Use the READTEXT statement to read blocks of ntext, text, or image data. READTEXT reads the specified number of bytes in a BLOB column starting from a given point or offset. Execute the TEXTPTR function to obtain the text pointer value to be used in the text_ptr argument of the READTEXT statement. The text pointer value also can be used in WRITETEXT and UPDATETEXT statements. 

      Code Example 11.17 

      SELECT TEXTPTR(Notes)
      FROM Employees 

      Because the text_ptr argument and the TEXTPTR function work with 16-byte binary strings, declare a local variable to hold the text pointer and use that variable with READTEXT. 

      Code Example 11.18 

      DECLARE @ptr varbinary(16)
      SELECT @ptr=TEXTPTR(Notes)
      FROM Employees
      WHERE LastName = 'Callahan'
      READTEXT Employees.Notes @ptr 23 44 

      When the value of the size argument in READTEXT is more than the value of the @@TEXTSIZE function, SQL Server uses the value of the @@TEXTSIZE function. 

      For tables with in-row text, TEXTPTR returns a handle for the text to be processed. You can obtain a valid text pointer even if the text value is null.

      If the table does not have in-row text, and if a text, ntext, or image column has not been initialized by an UPDATETEXT statement, TEXTPTR returns a null pointer.

      You cannot use UPDATETEXT, WRITETEXT, or READTEXT without a valid text pointer. To check whether or not a text pointer exists and is valid, use the TEXTVALID function. This function returns 1 if the pointer is valid and 0 if the pointer is invalid. The identifier for the text column must include the table name. 

      Code Example 11.19 

      --This example reports whether or not a valid text pointer exists for each value in
      the Notes column of the Employees table. 

      SELECT EmployeeID, 'Valid (if 1) Text data'
      = TEXTVALID ('Employees.Notes', TEXTPTR(Notes))
      FROM Employees
      ORDER BY EmployeeID
      GO 

      For BLOBs that are text data type, use the PATINDEX function to retrieve offset information on particular patterns of bytes. PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression. If SQL Server does not find the pattern, the function returns zeros. Values returned by PATINDEX can be used in a SUBSTRING function or a READTEXT statement.

      This SELECT statement uses the SUBSTRING and PATINDEX functions to retrieve any part of a text value that is between a start tag and an end tag: 

      Code Example 11.20 

      USE Northwind
      GO
      CREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT)
      GO
      INSERT INTO TextParts
      VALUES( 1,
      'Sample string START TAG What I want END TAG Trailing text.')
      GO
      SELECT SUBSTRING( ColB,
      /* Calculate start as start of tag + tag length. */
      (PATINDEX('%START TAG%', ColB) + 10),
      /* Calculate SUBSTRING length as end - start. */
      (
      PATINDEX('%END TAG%', ColB) -
      ( PATINDEX('%START TAG%', ColB) + 10 )
      )
      )
      FROM TextParts
      GO 

      Here is the result set:
      ------------------------
      What I want 

      (1 row(s) affected) 

      Tables cannot be joined directly on ntext, text, or image columns; however, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING. For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2. In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE clause. 

      WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info) 

      The text in row option affects the way you read and access data in row. You still can use the SELECT statement to read entire BLOB strings, and use the SUBSTRING function to read parts of the string. 

    • With in-row text pointers, you can use READTEXT, UPDATETEXT, or WRITETEXT statements to read or modify parts of the BLOB values stored in row. 

      Code Example 11.21 

      DROP TABLE Tbl
      GO
      CREATE TABLE Tbl (EmployeeID int, LastName nvarchar(40), Notes ntext)
      GO
      INSERT INTO Tbl VALUES(1, 'Davolio', 'This field holds information on a specific
      employee.')
      INSERT INTO Tbl VALUES(2, 'Fuller', 'This field holds information on a specific
      employee.')
      DECLARE @ptrval varbinary(16)
      SELECT @ptrval = TEXTPTR(Notes)
      FROM Tbl
      WHERE EmployeeID = 1
      READTEXT Tbl.Notes @ptrval 34 17 

    • All INSERT and UPDATE statements referencing the table must specify complete strings and cannot modify only parts of the BLOB strings. 

BLOBs and Cursors

The existence of BLOB columns in tables accessed by cursors can affect cursor functionality and performance. If your cursors access tables with text, ntext, or image data, consider these points.

  • On tables with text in row enabled, you cannot have a cursor over in-row text pointers, but you can have a cursor over in-row text. 

BLOBs on the Client

The database APIs follow a common pattern in handling BLOBs:

Reading BLOB columns 

To read a long column, the application includes the text, ntext, or image column in a select list, and then binds the column to a program variable large enough to hold a reasonable block of the data. The application then executes the statement and uses an API function or method to retrieve the data into the bound variable one block at a time.

Writing BLOB columns 

To write a long column, the application executes an INSERT or UPDATE statement with a parameter marker (?) in the place of the value to be placed in the text, ntext, or image column. The parameter marker (or parameter, in the case of ADO) is bound to a program variable large enough to hold the blocks of data. The application goes into a loop where it first moves the next set of data into the bound variable, and then calls an API function or method to write that block of data. This is repeated until the entire data value has been sent.

This is a summary of the ways the database APIs handle text, ntext, and image data:

  • ADO

    ADO can map text, ntext, or image columns or parameters to a Field or Parameter object. Use the GetChunk method to retrieve the data one block at a time and the AppendChunk method to write data one block at a time.

    GetChunk and AppendChunk are sequential in nature. For example, when GetChunk is first called, the method starts reading the value of the BLOB column and keeps a pointer to the next byte that will be read. Subsequent calls keep reading from this stream. Because the API manages the process, reading and writing to any other field resets the internal pointer, consequently resetting calls to GetChunk for a specific column as well. 

  • OLE DB

    OLE DB uses the ISequentialStream interface to support text, ntext, and image data types. The ISequentialStream::Read method reads the long data one block at a time, and ISequentialStream::Write writes the long data to the database one block at a time.

  • ODBC

    ODBC has a data-at-execution feature that handles the ODBC data types for long data: SQL_LONGVARCHAR (text), SQL_WLONGVARCHAR (ntext), and SQL_LONGVARBINARY (image). These data types are bound to a program variable. SQLGetData is then called to retrieve the long data one block at a time, and SQLPutData is called to send long data one block at a time. The ODBC SQLPutData function is faster and uses less dynamic memory than the Transact-SQL WRITETEXT statement. These functions can insert up to 2 GB of text, ntext, or image data. 

    The text in row option does not affect the operation of the OLE DB Provider for SQL Server or the SQL Server ODBC driver, other than to speed access to the text, ntext, and image data. 

ADO: Storing and Retrieving Files Using the Stream Object

One way of handling BLOBs from client-side applications is by using the ADO stream object. ADO is included as part of the MDAC (Microsoft Data Access Components) stack, which is included with SQL Server 2000. The following samples illustrate how to store files in SQL Server BLOB columns and how to store a BLOB column to a file. Unless otherwise specified, all samples use the Northwind database, and following are the variables declared:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim st As ADODB.Stream

The ADOBlob.bas (ADOBlob) sample is available on the SQL Server 2000 Resource Kit CD-ROM in the folder \ToolsAndSamples\BLOB\ADO.

Saving a BLOB Column to a File 

This sample saves the contents of the Notes field of Employee 9 in the Employees table to a file.

  1. Establish a connection to SQL Server. 

    'Create a new connection object

Set cn = New ADODB.Connection

'Specify connection information cn.ConnectionString = "Provider=SQLOLEDB; Data Source=(local); Integrated Security=SSPI; Initial Catalog=Northwind"

'Establish the connection cn.Open

  1. Open an updatable recordset containing the row to be updated. 

    'Execute the command and retrieve the returned recordset

Set rs = New ADODB.Recordset rs.Open "select Notes from employees where EmployeeID=9", cn, adOpenForwardOnly, adLockReadOnly

  1. Ensure files with the same name do not exist. 

    'Clear any existing file of the same name

If Dir("c:\Emp9.txt") <> "" Then Kill "c:\Emp9.txt" End If

  1. Set up the stream object specifying type (text or binary). 

    'Initialize the stream object used to persist to a file

Set st = New ADODB.stream st.Type = adTypeText st.Open

  1. Load the file to be stored, and update the recordset with the contents of the stream. 

    'Write the value of the field to the stream

st.WriteText rs.fields("Notes").Value

'Save the content of the stream to a file st.SaveToFile("c:\Emp9.txt")

'Close the stream st.close

  1. Close the connection and free resources. 

    'Close recordset and connection

rs.Close cn.Close

'Free resources Set rs = Nothing Set st = Nothing Set cn = Nothing

Saving a File in a BLOB Column 

This sample updates the Notes field of Employee 9 in the Employees table with the contents of a file. This example uses the same text file as the previous example. To make the update operation more obvious, edit the text file and change the content.

  1. Establish the connection, as in the previous sample. Retrieve the desired column and row. 

    'Execute the command and retrieve the returned recordset.

Set rs = New ADODB.Recordset rs.Open = "select Notes from employees where EmployeeID = 9", cn, adOpenKeyset, adLockOptimistic

  1. Set up the stream object. The object has to be created and its type defined (text or binary); it has to be opened, and the value of the file written to the stream. 

    'Initialize the stream object used to load the file.

Set st = New ADODB.stream st.Type = adTypeText st.Open st.LoadFromFile ("c:\Emp9.txt")

'Write the value of the field to the stream. rs.Fields("Notes").Value = st.ReadText

  1. Save the contents of the stream to the field. 

    rs.Update
  1. Close the connection and free resources, as in the previous sample. 
OLE DB: Reading BLOBs from a Database and Saving BLOBs in a File System

The following sample excerpts show how to use the SQL Server OLE DB Provider (SQLOLEDB) to connect to a SQL Server database, execute a SELECT statement retrieving a photo from EmployeeID 9 in the Employees table, and save the photo in the file "Employee9Photo.jpg".

The OLEDBLOBS.cpp (OLEDBLOBS) sample is available on the SQL Server 2000 Resource Kit CD-ROM in the folder \ToolsAndSamples\BLOB\OLEDB.

  1. Call CoInitialize to initialize the OLE and OLE DB libraries. 

    //Initialize OLE environment.

CoInitialize(NULL);

  1. Create an instance of the SQL OLE DB provider. An IDBInitialize interface is obtained and will be used to set connection information and connect to a server. 

    //Create an instance of the SQL Server OLEDB Provider.

CoCreateInstance(CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER,IID_IDBInitialize, (void**)&pIDBInitialize); 

Properties defined as part of a property set with information to connect to the data source are: Servername, database, userid, and password. 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">//Initialize the property values needed to establish the connection.

for(i = 0; i < nProps; i++) VariantInit(&InitProperties[i].vValue);

//Server name. InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE; InitProperties[0].vValue.vt = VT_BSTR; InitProperties[0].vValue.bstrVal= SysAllocString(DATA_SOURCE); InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED; InitProperties[0].colid = DB_NULLID;

//Database. InitProperties[1].dwPropertyID = DBPROP_INIT_CATALOG; InitProperties[1].vValue.vt = VT_BSTR; InitProperties[1].vValue.bstrVal= SysAllocString(DATABASE); InitProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED; InitProperties[1].colid = DB_NULLID;

//Username (login). InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID; InitProperties[2].vValue.vt = VT_BSTR; InitProperties[2].vValue.bstrVal= SysAllocString(USERNAME); InitProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED; InitProperties[2].colid = DB_NULLID;

//Password. InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD; InitProperties[3].vValue.vt = VT_BSTR; InitProperties[3].vValue.bstrVal= SysAllocString(PASSWORD); InitProperties[3].dwOptions = DBPROPOPTIONS_REQUIRED; InitProperties[3].colid = DB_NULLID;

/* Construct the DBPROPSET structure(rgInitPropSet). The DBPROPSET structure is used to pass an array of DBPROP structures (InitProperties) to the SetProperties method. */ rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT; rgInitPropSet[0].cProperties = nProps; rgInitPropSet[0].rgProperties = InitProperties;

  1. Set the properties, obtain an IDBProperties interface, and then call the SetProperties method.

    //Set initialization properties.

hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties); hr = pIDBProperties->SetProperties(1, rgInitPropSet);

  1. After the connection properties are set, call the Initialize() method on IDBInitialize to establish the connection: 

    //Establish the connection to the data source.

pIDBInitialize->Initialize();

  1. After there is a connection to the server, create a session by obtaining an IDBCreateSession and calling CreateSession() on it. With this last call, retrieve a reference to an IDBCreateCommand interface that will allow creation of new commands that can be sent to the server. 

    if (FAILED(pIDBInitialize->QueryInterface(IID_IDBCreateSession,(void**)

&pIDBCreateSession))) … // The next session created receives the SQL Server connection of // the data source object. if (FAILED(pIDBCreateSession->CreateSession(NULL,IID_IDBCreateCommand, (IUnknown**) &pIDBCreateCommand)))

  1. On the IDBCreateCommand interface, call the CreateCommand() method to create a new command and obtain a reference to an ICommandText interface. This is the interface to use for sending SQL batches to the server to be executed. 

    if (FAILED(pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**)

&pICommandText))) 

For example, to retrieve the record from the **Photo** column of **EmployeeID** 10 in the **Employees** table, set the text of the command this way: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">if (FAILED(pICommandText-&gt;SetCommandText(DBGUID_DBSQL,OLESTR("select Photo from

employees where EmployeeId=9")))) 

The command can be executed to obtain **IRowset** to bind, fetch rows, and read the columns of each row.

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">if (FAILED(pICommandText-&gt;Execute(NULL, IID_IRowset, NULL, &amp;cRowsAffected,

(IUnknown**) &pIRowset)))

  1. To retrieve the BLOB column (Photo) in chunks, bind ISequentialStream to the only column in the rowset. To do this, create an accessor specifying the bindings required. 

    //Retrieve data from a rowset.

//Set up the DBOBJECT structure to bind as IsequentialStream. dbobject.dwFlags = STGM_READ; dbobject.iid = IID_ISequentialStream;

//Create the DBBINDING, requesting a storage-object pointer. dbbinding.iOrdinal = 1; //First and single column dbbinding.obValue = 0; dbbinding.obStatus = sizeof(IUnknown*); dbbinding.obLength = 0; dbbinding.pTypeInfo = NULL; dbbinding.pObject = &dbobject; dbbinding.pBindExt = NULL; dbbinding.dwPart = DBPART_VALUE | DBPART_STATUS; dbbinding.dwMemOwner = DBMEMOWNER_CLIENTOWNED; dbbinding.eParamIO = DBPARAMIO_NOTPARAM; dbbinding.cbMaxLen = 0; dbbinding.dwFlags = 0; dbbinding.wType = DBTYPE_IUNKNOWN; dbbinding.bPrecision = 0; dbbinding.bScale = 0;

if (FAILED(hr = pIRowset->QueryInterface(IID_IAccessor, (void**) &pIAccessor))) … if (FAILED(hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1, &dbbinding, 0, &haccessor, &ulbindstatus)))

  1. Memory must be allocated for the data being retrieved from each row. Because you are binding as IsequentialStream, only enough memory to receive the interface pointer and a status field needs to be allocated. 

    //Allocate memory for the returned pointer and the status

//field. The first sizeof(IUnknown*) bytes are for the pointer //to the object; the next sizeof(ULONG) bytes are for the status. pData=new BYTE[sizeof(IUnknown*)+sizeof(ULONG)];

  1. Call GetNextRows on IRowset to return the next row; and then call GetData for each returned row. 

    if (FAILED(pIRowset->GetNextRows(NULL, 0, 1, &cRows, &pRows)))

… if (SUCCEEDED(pIRowset->GetData(*pRows, haccessor, pData))) 

To separate the pieces of information of the returned buffer: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">dwStatus = (ULONG)((BYTE*)pData)[dbbinding.obStatus];

pISequentialStream = ((ISequentialStream*) pData);

  1. Use the Win32 API CreateFile to open the destination file. 

    lstrcpy(FileName,L"Employee9Photo.jpg");

HANDLE hfile=CreateFile(FileName,GENERIC_READ | GENERIC_WRITE, FILE_SHARE_READ, NULL,CREATE_ALWAYS,FILE_ATTRIBUTE_NORMAL,NULL);

  1. Use the returned ISequentialStream interface to read chunks of CHUNK_SIZE bytes and save them in the file. 

    do

{ //Read each chunk of CHUNK_SIZE bytes. if (SUCCEEDED(hr =pISequentialStream->Read(Picture,CHUNK_SIZE, &cbRead))) { WriteFile(hfile, Picture, cbRead, &BytesWritten,NULL); } } while (SUCCEEDED(hr) && cbRead >= CHUNK_SIZE);

  1. Close the file, disconnect from the server, and then release all interfaces. 

    //Close the file.

CloseHandle(hfile);

pISequentialStream->Release(); pIAccessor->ReleaseAccessor(haccessor, NULL); pIAccessor->Release(); pIRowset->Release(); pIDBInitialize->Release();

  1. Uninitialize the OLE libraries. 

    //Uninitialize OLE libraries.

CoUninitialize();

ODBC: Handling BLOBs in a Database

The samples in this section save the photo field for each record of the Employees table into a separate file named "EmpNo" + EmployeeId and update the photo field of Employee 9 with the contents of a file named "Employee9Photo.jpg".

  1. Saving BLOBs to FileAllocate an environment handle and specify an ODBC version 3.0 application using SQLAllocHandle and SQLSetEnvAttr. 

    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

  1. Allocate the connection handle and connect using the specified data source, username, and password using SQLAllocHandle and SQLConnect. 

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

retcode = SQLConnect(hdbc1, szDSN, (SWORD)strlen((const char *)szDSN),szUID, (SWORD)strlen((const char *)szUID),szAuthStr, (SWORD)strlen((const char *)szAuthStr));

  1. Allocate a statement handle and execute it with the SELECT statement using SQLAllocHandle and SQLExecDirect. 

    SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

SQLExecDirect(hstmt1, (SQLTCHAR*)"SELECT EmployeeID, Photo FROM Employees", SQL_NTS); 

Columns in ODBC may be retrieved by binding program variables to columns or by using **SQLGetData** to read from a column into a variable or buffer. Binding requires reading a whole column in a single call, and **SQLGetData** allows reading a column in multiple sequential chunks.

This section of the code retrieves the **EmployeeId** and **Photo** columns. **EmployeeId** will be used to make part of the output file name and **Photo** is the actual binary chunk. **EmployeeId** will be read as a bound variable into a character array (to be concatenated as part of the file name) and **Photo** will be read using **SQLGetData** in chunks of 8,000 bytes. 

  1. Bind the first column before starting to fetch the result set. 

    SQLBindCol(hstmt1, 1, SQL_C_CHAR, (SQLPOINTER) &sEmpID, CHUNK_SIZE, &pIndicators[0]);

  1. Fetch each row of the resulting rowset. 

    while (SQLFetch(hstmt1) == SQL_SUCCESS)

  1. Call SQLGetData for the first time with a buffer size of 0 to find out the total size, in bytes, of the column. 

    retcode=SQLGetData(hstmt1, 2, SQL_C_BINARY, Picture, 0, &pIndicators[1]);

  1. Open the destination file using the Win32® CreateFile API. 

    lstrcpy(FileName,"EmpNo");

lstrcat(FileName, (LPCTSTR) sEmpID); HANDLE hfile=CreateFile(FileName,GENERIC_READ | GENERIC_WRITE, FILE_SHARE_READ, NULL,CREATE_ALWAYS,FILE_ATTRIBUTE_NORMAL,NULL);

  1. Call SQLGetData in a loop, and then write to the opened file. 

    while (SQLGetData(hstmt1, 2, SQL_C_BINARY, Picture, CHUNK_SIZE,

&pIndicators[1])!=SQL_NO_DATA) { if (pIndicators[1]>CHUNK_SIZE) WriteFile(hfile, Picture, CHUNK_SIZE, &BytesWritten,NULL); else WriteFile(hfile, Picture, pIndicators[1], &BytesWritten,NULL); }

  1. Close the file using CloseHandle, disconnect from SQL Server, and then release the statements, connection, and environment handles. 

    CloseHandle(hfile);

} else { SQLGetDiagRec(SQL_HANDLE_STMT, hstmt1,1, State, &NativeError, Message, BuffLen, &TextLen); printf("%s",Message); } } SQLFreeHandle(SQL_HANDLE_STMT, hstmt1); SQLDisconnect(hdbc1); SQLFreeHandle(SQL_HANDLE_DBC, hdbc1); SQLFreeHandle(SQL_HANDLE_ENV, henv);

The ODBCSaveToFile.cpp (ODBCSaveToFile) sample is available on the SQL Server 2000 Resource Kit CD-ROM in the folder \ToolsAndSamples\BLOB\ODBCSave.

Loading an Image File to a BLOB Column 

Allocation of an environment handle and connection is the same as in the previous sample.

  1. When a statement handle is allocated, open the source file and retrieve its byte length. 

    HANDLE hfile=CreateFile(FileName,GENERIC_READ, FILE_SHARE_READ,

NULL,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL,NULL); FileSize=GetFileSize(hfile,NULL);

  1. Bind a parameter to the statement indicating that the data for the parameter will be supplied at execution. 

    cbTextSize=SQL_LEN_DATA_AT_EXEC(FileSize);

retcode = SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, FileSize, 0,(VOID *)NULL, 0, &cbTextSize);

  1. Execute an UPDATE statement with a parameter marker (?) for the Photo value. 

    SQLExecDirect(hstmt1, (SQLTCHAR*)"UPDATE Employees set Photo = ? where EmployeeId=9",

SQL_NTS);

  1. Call SQLParamData to return information about whether or not a parameter needs data, and to fill a passed-in parameter indicating data for which parameter data is being requested. All subsequent SQLPutData calls will be mapped to the current bound parameter. To move to the next parameter (if that were the case) or to indicate the end of the data for the parameter, SQLParamData is called again. 

    retcode = SQLParamData(hstmt1, &pParmID);

  1. Iterate reading from the file and calling SQLPutData. 

    while (BytesWritten < FileSize)

{ if (FileSize-BytesWritten>CHUNK_SIZE) lChunkSize=CHUNK_SIZE; else lChunkSize=FileSize-BytesWritten; ReadFile(hfile,Picture,lChunkSize, &BytesRead, NULL); SQLPutData(hstmt1, Picture, lChunkSize); BytesWritten+=BytesRead; }

  1. Complete the statement execution calling SQLParamData. The row is updated. 

    SQLParamData(hstmt1, &pParmID);
  1. Closing the source file and freeing ODBC resources are the same as in the previous sample. 

The ODBCLOBS.cpp (ODBCLOBS) sample is available on the SQL Server 2000 Resource Kit CD-ROM in the folder \ToolsAndSamples\BLOB\ODBCLoad.

For more information, see "Managing text and image Columns" in SQL Server Books Online.

Working with BLOBs in SQL Server

Cc917636.spacer(en-us,TechNet.10).gif Cc917636.spacer(en-us,TechNet.10).gif

In summary, the BLOB data types—text, ntext, and image data—are similar to other data types in SQL Server. They can be inserted, modified, and retrieved from tables. BLOBs, however, are unique in that they can be very large.

Consequently, when working with BLOBs, you might address similar issues as when working with other kinds of data. You also must be aware of and understand the effects and demands of BLOB data on systems, applications, and performance. Knowing the features and tools in SQL Server 2000 will help you implement and manipulate BLOB data in your applications efficiently.

Considerations when planning BLOB storage include:

  • In SQL Server, text, ntext, and image data are stored the same way. BLOB data is stored in separate data pages from other table data. Small-sized BLOBs can be stored in the data row in SQL Server 2000, if the text in row option is enabled explicitly. Text pointers for BLOB data stored in the table row are different from text pointers for BLOBs stored outside the table. 

  • BLOBs do not need to be stored as text, ntext, and image data types, they do not need to be stored in one piece, and they do not need to be stored in a database. Binary large objects can be stored as varchar or varbinary data types. BLOB data can be stored in file systems, and BLOB data can be chunked or tiled. 

  • The presence of BLOB data may require different procedures, options, or settings for operations such as backing up a database, copying, and full-text search.

When programming BLOBs:

  • You can use the same Transact-SQL statements—such as SELECT, INSERT, UPDATE, DELETE—used for other kinds of data. At the same time, some Transact-SQL elements work only with BLOB data and text pointers. 

  • When BLOB columns are referenced in SELECT statements, SQL Server converts fast-forward cursors to either dynamic or keyset-driven cursors, depending on conditions. 

  • On the client, a simple way of storing and retrieving BLOBs is by using the ADO stream object. You also can use SQL OLE DB and ODBC to read and save BLOBs. 

Cc917636.spacer(en-us,TechNet.10).gif