LOB Locators in Transact-SQL Statements

Large object (LOB) locators let you reference an LOB in Transact-SQL statements rather than referencing the LOB itself.

Because the size of a text, unitext, or image LOB can be many megabytes, using an LOB locator in Transact-SQL statements reduces network traffic between the client and SAP ASE, and reduces the amount of memory otherwise needed by the client to process the LOB.

SAP ASE lets client applications send and receive locators as host variables and parameter markers.

When you create an LOB locator, SAP ASE caches the LOB value in memory and generates an LOB locator to reference it.

A LOB locator remains valid for the duration of the transaction in which it was created. SAP ASE invalidates the locator when the transaction commits or is rolled back.

LOB locators use three different datatypes:

text_locator – for text LOBs.
unitext_locator – for unitext LOBs.
image_locator – for image LOBs.

You can declare local variables for the locator datatypes. For example:

declare @v1 text_locator

Because LOBs and locators are stored only in memory, you cannot use locator datatypes as column datatypes for user tables or views, or in constraints or defaults.

You can create a LOB locator explicitly or implicitly.

In general, when used in a Transact-SQL statement, locators are implicitly converted to the LOB they reference. That is, when a locator is passed to a Transact-SQL function, the function operates on the LOB that is referenced by the locator.

Any changes you make to the LOB referenced by the locator are not reflected in the source LOB in the database—unless you explicitly save them. Similarly, any changes you make to the LOB stored in the database are not reflected in the LOB referenced by the locator.

Note: Locators are best used in Transact-SQL statements that return only a few rows, or in cursor statements. This allows locators and associated LOBs to be processed and released in a manner that conserves memory. You may need to increase available memory if several LOBs are created in a single transaction.

Implicitly Create a Locator
If you assign one locator to another, a new locator value is assigned to the new variable. Each locator has a unique locator value.
Explicitly Create a Locator
Use the create_locator function to explicitly create a locator.
Convert the Locator Value to the LOB Value
After using a locator in a Transact-SQL statement, you can convert (dereference) the locator to the corresponding LOB.
Locator Scope
In general, a locator is valid for the duration of a transaction. Use the deallocate locator function to override the default scoping, and deallocate the locator within the transaction.

Parent topic: Datatypes