readtext

Description

Reads text and image values, starting from a specified offset and reading a specified number of bytes or characters.

Syntax

readtext [[database.]owner.]table_name.column_name
	text_pointer offset size 
	[holdlock | noholdlock] [readpast]
	[using {bytes | chars | characters}]
	[at isolation {
		[ read uncommitted | 0 ] | 
		[ read committed | 1 ] |
		[ repeatable read | 2 ]| 
		[ serializable | 3 ] } ]

Parameters

table_name.column_name

is the name of the text or image column. You must include the table name. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

text_pointer

is a varbinary(16) value that stores the pointer to the text or image data. Use the textptr function to determine this value (see Example 1). text and image data is not stored in the same set of linked pages as other table columns. It is stored in a separate set of linked pages. A pointer to the actual location is stored with the data; textptr returns this pointer.

offset

specifies the number of bytes or characters to skip before starting to read text or image data.

size

specifies the number of bytes or characters of data to read.

holdlock

causes the text value to be locked for reads until the end of the transaction. Other users can read the value, but they cannot modify it.

noholdlock

prevents the server from holding any locks acquired during the execution of this statement, regardless of the transaction isolation level currently in effect. You cannot specify both a holdlock and a noholdlock option in a query.

readpast

specifies that readtext should silently skip rows with exclusive locks, without waiting and without generating a message.

using

specifies whether readtext interprets the offset and size parameters as a number of bytes (bytes) or as a number of textptr characters (chars or characters are synonymous). This option has no effect when used with a single-byte character set or with image values (readtext reads image values byte by byte). If the using option is not given, readtext interprets the size and offset arguments as bytes.

at isolation

specifies the isolation level (0, 1, or 3) of the query. If you omit this clause, the query uses the isolation level of the session in which it executes (isolation level 1 by default). If you specify holdlock in a query that also specifies at isolation read uncommitted, Adaptive Server issues a warning and ignores the at isolation clause. For the other isolation levels, holdlock takes precedence over the at isolation clause.

read uncommitted

specifies isolation level 0 for the query. You can specify 0 instead of read uncommitted with the at isolation clause.

read committed

specifies isolation level 1 for the query. You can specify “1” instead of read committed with the at isolation clause.

repeatable read

specifies isolation level 2 for the query. You can specify “2” instead of serializable with the at isolation clause.

serializable

specifies isolation level 3 for the query. You can specify “3” instead of serializable with the at isolation clause.

Examples

Example 1

Selects the second through the sixth character of the copy column:

declare @val varbinary(16) 
select @val = textptr(copy) from blurbs
where au_id = "648-92-1872" 
readtext blurbs.copy @val 1 5 using chars

Example 2

declare @val varbinary(16) 
select @val = textptr(copy) from blurbs readpast
where au_id = "648-92-1872" 
readtext blurbs.copy @val 1 5 readpast using chars

Usage


Using the readpast option

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

readtext requires select permission on the table. readtext permission is transferred when select permission is transferred.

See also

Commands set, writetext

System procedures text and image datatypes