Managing ntext, text, and image Data

The Microsoft® SQL Server™ ntext, text, and image data types are capable of holding extremely large amounts of data (up to 2 GB) in a single value. A single data value is typically larger than can be retrieved by an application in one step; some values may be larger than the virtual memory available on the client. Therefore, special steps are usually needed to retrieve these values.

If an ntext, text, and image data value is no longer than a Unicode, character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes respectively), the value can be referenced in SELECT, UPDATE, and INSERT statements much the same way as the smaller data types. For example, an ntext column with a short value can be referenced in a SELECT statement select list the same way an nvarchar column is referenced. There are some restrictions that must be observed, such as not being able to directly reference an ntext, text, or image column in a WHERE clause. These columns can be included in a WHERE clause as parameters of a function that returns another data type (such as ISNULL, SUBSTRING or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.

Handling Larger Data Values

When the ntext, text, and image data values get larger, however, they must be handled on a block by block basis. Both Transact-SQL and the database APIs contain functions that allow applications to work with ntext, text, and image data block by block.

The database APIs follow a common pattern in the ways they handle long ntext, text, and image columns:

• To read a long column, the application simply includes the ntext, text, or image column in a select list, and then binds the column to a program variable large enough to hold a reasonable block or chunk 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.
• 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 ntext, text, 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.

Using ntext, text, and image Data with Database APIs

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

• ADO

  ADO can map ntext, text, 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. For more information, see Using Long Data Types.

• OLE DB

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

• ODBC

  ODBC has a feature called data-at-execution to deal with the ODBC data types for long data: SQL_WLONGVARCHAR (ntext), SQL_LONGVARCHAR (text), 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, SQLPutData is called to send long data one block at a time. For more information, see Managing text and image Columns.

• DB-Library

  DB-Library applications also bind ntext, text, and image columns to program variables. The DB-Library function dbtxtptr is used to get a pointer to the location of the long column occurrence in the database. dbreadtext is used to read the long data one block at a time. Functions such as dbwritetext, dbupdatetext, and dbmoretext are used to write the long data one block at a time. For more information, see Text and Image Functions.