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 Adaptive Server, and reduces the amount of memory otherwise needed by the client to process the LOB.
Adaptive Server 15.7 lets client applications send and receive locators as host variables and parameter markers.
When you create an LOB locator, Adaptive Server 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. Adaptive Server 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.
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.
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.
