readtext

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, unitext, 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, unitext, or image data. Use the textptr function to determine this value. text, unitext, 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, unitext, 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, the SAP ASE 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 readpast using chars

Usage

• The textptr function returns a 16-byte binary string (text pointer) to the text, unitext, or image column in the specified row or to the text, unitext, or image column in the last row returned by the query, if more than one row is returned. Declare a local variable to hold the text pointer, then use the variable with readtext.

• The value in the global variable @@textsize, which is the limit on the number of bytes of data to be returned, supersedes the size specified for readtext if it is less than that size. Use set textsize to change the value of @@textsize.

• When using bytes as the offset and size, the SAP ASE server may find partial characters at the beginning or end of the text data to be returned. If it does, and character set conversion is on, the server replaces each partial character with a question mark (?) before returning the text to the client.

• The SAP ASE server must determine the number of bytes to send to the client in response to a readtext command. When the offset and size are in bytes, determining the number of bytes in the returned text is simple. When the offset and size are in characters, the server must calculate the number of bytes being returned to the client. As a result, performance may be slower when using characters as the offset and size. The using characters option is useful only when the SAP ASE server is using a multibyte character set: it ensures that readtext does not return partial characters.

• You cannot use readtext on text, unitext, or image columns in views.

• If you attempt to use readtext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message instructs you to run dbcc fix_text on the table.

See also text, image, and unitext Datatypes in Reference Manual: Building Blocks.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

readtext requires select permission on the table.