To enable LOB locator support, establish a connection to Adaptive Server with the EnableLOBLocator connection property set to 1. When EnableLOBLocator is set to 0, the default value, the ODBC Driver cannot retrieve a locator for a LOB column. When enabling LOB Locators, the connection should be set to autocommit off.
You must also include the sybasesqltypes.h file in your program. The sybasesqltypes.h file is located in the include directory, under the ODBC installation directory.
The ODBC datatype mapping for the Adaptive Server locator datatypes are:
ASE Datatype |
ODBC SQL Type |
ODBC C Type |
|---|---|---|
text_locator |
SQL_TEXT_LOCATOR |
SQL_C_TEXT_LOCATOR |
image_locator |
SQL_IMAGE_LOCATOR |
SQL_C_ IMAGE_LOCATOR |
unitext_locator |
SQL_UNITEXT_LOCATOR |
SQL_C_ UNITEXT_LOCATOR |
The supported conversions for the Adaptive Server locator datatypes are:
SQL_C_TEXT_ LOCATOR |
SQL_C_IMAGE_ LOCATOR |
SQL_C_UNITEXT_LOCATOR |
|
|---|---|---|---|
SQL_TEXT_LOCATOR |
X |
||
SQL_IMAGE_LOCATOR |
X |
||
SQL_UNITEXT_LOCATOR |
X |
||
SQL_LONGVARCHAR |
|||
SQL_WLONGVARCHAR |
|||
SQL_LONGVARBINARY |
|||
LEGEND: x = supported conversion. |
|||
SQLBindCol – TargetType can be any of the ODBC C locator datatypes.
SQLBindParameter – ValueType can be any of the ODBC C locator datatypes. ParameterType can be any of the ODBC SQL locator datatypes.
SQLGetData – TargetType can be any of the ODBC C locator datatype.
SQLColAttribute – the SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE descriptors can return any of the ODBC SQL locator datatype.
SQLDescribeCol – the datatype pointer can be any of the ODBC SQL locator datatypes.
See Microsoft ODBC API Reference.
In Adaptive Server Enterprise ODBC Driver 15.7, when Adaptive Server returns a LOB locator, you can use SQLGetData and SQLBindCol to retrieve the underlying prefetched LOB data by binding the column to SQL_C_CHAR or SQL_C_WCHAR for text locators, or to SQL_C_BINARY for image locators.
Set the SQL_ATTR_LOBLOCATOR attribute to enable or disable locators in a connection. If EnableLOBLocator has been specified in the connection string, SQL_ATTR_LOBLOCATOR is initialized with the value of EnableLOBLocator, otherwise, it is set to SQL_LOBLOCATOR_OFF, the default value. To enable locators, set the attribute to SQL_LOBLOCATOR_ON. Use SQLSetConnectAttr to set the attribute’s value and SQLGetConnectAttr to retrieve its value.
Use SQLSetStatementAttr to set SQL_ATTR_LOBLOCATOR_FETCHSIZE to specify the size—in bytes for binary data and in characters for character data—of the LOB data to retrieve. The default value, 0, indicates that prefetched data is not requested, while a value of -1 retrieves the entire LOB data.
If the underlying LOB data size of the column being
retrieved exceeds the prefetched data size that you have set, native
error 3202 is raised when an ODBC client attempts to directly retrieve
the data. When this happens, the client can retrieve the complete
data by calling SQLGetData to obtain the underlying
locator and perform all of the operations available on locators.
Example 1 Retrieves an image locator using SQLGetData when the prefetched data represents the complete LOB value:
//Set Autocommit off SQLRETURN sr; sr = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0); //Enable LOB Locator for this exchange sr = SQLSetConnectAttr(dbc, SQL_ATTR_LOBLOCATOR, (SQLPOINTER)SQL_LOCATOR_ON, 0); // Set size of prefetched LOB data sr = SQLSetStatementAttr(stmt, SQL_ATTR_LOBLOCATOR_FETCHSIZE, (SQLPOINTER)32768, 0); //Get a locator from the server SQLLEN lLOBLen = 0; Byte cBin[COL_SIZE]; SQLLEN lBin = sizeof(CBin); unsigned char cLOC[SQL_LOCATOR_SIZE]; SQLLEN lLOC = sizeof(cLOC); int id = 4; SQLLEN l1 = sizeof(int); SQLLEN idLen = sizeof(int); sr = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, idLen, 0, &id, idLen, &idLen); printError(sr, SQL_HANDLE_STMT, stmt); //Execute the select statement to return a locator sr = SQLExecDirect(stmt, selectCOL_SQL, SQL_NTS); printError(sr, SQL_HANDLE_STMT, stmt); sr = SQLFetch(stmt); printError(sr, SQL_HANDLE_STMT, stmt); //Retrieve the binary data (Complete Data is returned) sr = SQLGetData(stmt, 1, SQL_C_BINARY, cBin, lBin, &lBin); printError(sr, SQL_HANDLE_STMT, stmt); //Cleanup sr = SQLFreeStmt(stmt, SQL_UNBIND); sr = SQLFreeStmt(stmt, SQL_RESET_PARAMS); sr = SQLFreeStmt(stmt, SQL_CLOSE); SQLEndTran(SQL_HANDLE_DBC, dbc,SQL_COMMIT); //Disable LOB Locator for the future sr = SQLSetConnectAttr(dbc, SQL_ATTR_LOBLOCATOR, (SQLPOINTER)SQL_LOCATOR_OFF, 0);
Example 2 Retrieves an image locator using SQLGetData when prefetched data represents a truncated LOB value:
//Set Autocommit off
SQLRETURN sr;
sr = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
//Enable LOB Locator for this exchange
sr = SQLSetConnectAttr(dbc, SQL_ATTR_LOBLOCATOR,
(SQLPOINTER)SQL_LOCATOR_ON, 0);
//Set size of prefetched LOB data
sr = SQLSetStatementAttr(stmt,
SQL_ATTR_LOBLOCATOR_FETCHSIZE, (SQLPOINTER)32768, 0);
//Get a locator from the server
SQLLEN lLOBLen = 0;
Byte cBin[COL_SIZE];
SQLLEN lBin = sizeof(CBin);
unsigned char cLOC[SQL_LOCATOR_SIZE];
SQLLEN lLOC = sizeof(cLOC);
int id = 4;
SQLLEN l1 = sizeof(int);
SQLLEN idLen = sizeof(int);
sr = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, idLen,
0, &id, idLen, &idLen);
printError(sr, SQL_HANDLE_STMT, stmt);
//Execute the select statement to return a locator
sr = SQLExecDirect(stmt, selectCOL_SQL, SQL_NTS);
printError(sr, SQL_HANDLE_STMT, stmt);
sr = SQLFetch(stmt);
printError(sr, SQL_HANDLE_STMT, stmt);
// Retrieve the binary data(Truncated data is returned)
sr = SQLGetData(stmt, 1, SQL_C_BINARY, cBin, lBin, &lBin);
if(sr == SQL_SUCCESS_WITH_INFO)
{
SQLTCHAR errormsg[ERR_MSG_LEN];
SQLTCHAR sqlstate[SQL_SQLSTATE_SIZE+1];
SQLINTEGER nativeerror = 0;
SQLSMALLINT errormsglen = 0;
retcode = SQLGetDiagRec(handleType, handle, 1, sqlstate, &nativeerror,
errormsg, ERR_MSG_LEN, &errormsglen);
printf("SqlState: %s Error Message: %s\n", sqlstate, errormsg);
//Handle truncation of LOB data; if data was truncated call SQLGetData to
// retrieve the locator.
/* Warning returns truncated LOB data */
if (NativeError == 32028) //Error code may change
{
BYTE ImageLocator[SQL_LOCATOR_SIZE];
sr = SQLGetData(stmt, 1, SQL_C_IMAGE_LOCATOR, &ImageLocator,
sizeof(ImageLocator), &Len);
printError(sr, SQL_HANDLE_STMT, stmt);
/*
Perform locator specific calls using image Locator on a separate
statement handle if needed
*/
}
}
//Cleanup
sr = SQLFreeStmt(stmt, SQL_UNBIND);
sr = SQLFreeStmt(stmt, SQL_RESET_PARAMS);
sr = SQLFreeStmt(stmt, SQL_CLOSE);
SQLEndTran(SQL_HANDLE_DBC, dbc,SQL_COMMIT);
//Disable LOB Locator for the future
sr = SQLSetConnectAttr(dbc, SQL_ATTR_LOBLOCATOR, (SQLPOINTER)SQL_LOCATOR_OFF,
0);
The ODBC API does not directly support LOB locators. An ODBC client application must use Transact-SQL functions to operate on the locators and manipulate LOB values. Adaptive Server Enterprise ODBC Driver introduces several stored procedures to facilitate the use of the required Transact-SQL functions.
This section discusses how various operations can be performed on a LOB locator. The input and output values of the parameters can be of any type that Adaptive Server can implicitly convert to the stored procedure definitions.
For details about the Transact-SQL commands and functions listed here, see "Transact-SQL Functions" in the Adaptive Server Enterprise Reference Manual: Building Blocks.
Use sp_drv_create_text_locator to create a text_locator and optionally initialize it with a value. sp_drv_create_text_locator accesses the Transact-SQL function create_locator.
sp_drv_create_text_locator [init_value]
init_value – a varchar or text value used to initialize the new locator.
None.
A column of type text_locator. The LOB that the locator references has init_value when supplied.
Use sp_drv_create_unitext_locator to create a unitext_locator and optionally initialize it with value. sp_drv_create_unitext_locator accesses the Transact-SQL function create_locator.
sp_drv_create_unitext_locator [init_value]
init_value – a univarchar or unitext used to initialize the new locator.
None.
A column of type unitext_locator. The LOB that the locator references has init_value when supplied.
Use sp_drv_create_image_locator to create an image_locator and optionally initialize it with value. sp_drv_create_image_locator accesses the Transact-SQL function create_locator.
sp_drv_create_image_locator [init_value]
init_value – a varbinary or image used to initialize the new locator.
None.
A column of type image_locator. The LOB that the locator references has init_value when supplied.
Use sp_drv_locator_to_text, which accesses the Transact-SQL function return_lob.
sp_drv_locator_to_text locator
locator – text_locator to retrieve value of.
None.
A column containing the text value referenced by locator.
Use sp_drv_locator_to_unitext, which accesses the Transact-SQL function return_lob.
sp_drv_locator_to_unitext locator
locator – unitext_locator to retrieve value of.
None.
A column containing the unitext value referenced by locator.
Use sp_drv_locator_to_image, which accesses the Transact-SQL function return_lob.
sp_drv_locator_to_image locator
locator – image_locator to retrieve value of.
None.
A column containing the image value referenced by locator.
Use sp_drv_text_substring, which accesses the Transact-SQL function substring.
sp_drv_text_substring locator, start_position, length
locator – a text_locator that references the data to manipulate.
start_position – an integer specifying the position of the first character to read and retrieve.
length – an integer specifying the number of characters to read.
None.
A column of type text containing the substring retrieved.
Use sp_drv_unitext_substring, which accesses the Transact-SQL function substring.
sp_drv_unitext_substring locator, start_position, length
locator – a unitext_locator that references the data to manipulate.
start_position – an integer specifying the position of the first character to read and retrieve.
length – an integer specifying the number of characters to read.
None.
A column of type unitext containing the substring retrieved.
Use sp_drv_image_substring, which accesses the Transact-SQL function substring.
sp_drv_image_substring locator, start_position, length
locator – an image_locator that references the data to manipulate.
start_position – an integer specifying the position of the first byte to read and retrieve.
length – an integer specifying the number of bytes to read.
None.
A column of type image containing the substring retrieved.
Use sp_drv_text_setdata, which accesses the Transact-SQL function setadata.
sp_drv_text_setdata locator, offset, new_data, data_length
locator – a text_locator that references the text column to insert into.
offset – an integer specifying the position from which to start writing the new content.
new_data – varchar or text data to insert.
data_length – an integer containing the number of characters written.
None.
Use sp_drv_unitext_setdata, which accesses the Transact-SQL function setadata.
sp_drv_unitext_setdata locator, offset, new_data, data_length
locator –a unitext_locator that references the unitext column to insert into.
offset – an integer specifying the position from which to start writing the new content.
new_data – univarchar or unitext data to insert.
data_length – an integer containing the number of characters written.
None.
Use sp_drv_image_setdata, which accesses the Transact-SQL function setadata.
sp_drv_image_setdata locator, offset, new_data, datalength
locator – an image_locator that references the image column to insert in.
offset – an integer specifying the position from which to start writing the new content.
new_data – varbinary or image data to insert.
data_length – an integer containing the number of bytes written.
None.
Use sp_drv_text_locator_setdata, which accesses the Transact-SQL function setadata.
sp_drv_text_locator_setdata locator, offset, new_data_locator, data_length
locator – a text_locator that references the text column to insert into.
offset – an integer specifying the position from which to start writing the new content.
new_data_locator – a text_locator that references the text data to insert.
data_length – an integer containing the number of characters written.
None.
Use sp_drv_unitext_locator_setdata, which accesses the Transact-SQL function setadata.
sp_drv_unitext_locator_setdata locator, offset, new_data_locator, data_length
locator –a unitext_locator that references the unitext column to insert into.
offset – an integer specifying the position from which to start writing the new content.
new_data_locator – a unitext_locator that references the unitext data to insert.
data_length – an integer containing the number of characters written.
None.
Use sp_drv_image_locator_setdata, which accesses the Transact-SQL function setadata.
sp_drv_image_locator_setdata locator, offset, new_data_locator, datalength
locator – an image_locator that references the image column to insert in.
offset – an integer specifying the position from which to start writing the new content.
new_data_locator – an image_locator that references the image data to insert.
data_length – an integer containing the number of bytes written.
None.
Use truncate lob to truncate the LOB data referenced by a LOB locator. See the Adaptive Server Enterprise Reference Manual: Commands.
Use sp_drv_text_locator_charlength to find the character length of a LOB column referenced by a text locator. sp_drv_text_locator_charlength accesses the Transact-SQL function char_length.
sp_drv_text_locator_charlength locator, data_length
locator – a text_locator that references the text column to manipulate.
data_length – an integer specifying the character length of the text column referenced by locator.
None.
Use sp_drv_text_locator_bytelength to find the byte length of a LOB column referenced by a text locator. sp_drv_text_locator_bytelength accesses the Transact-SQL function data_length.
sp_drv_image_locator_bytelength locator, data_length
locator – a text_locator that references the text column to manipulate.
data_length – an integer specifying the byte length of the text column referenced by locator.
None.
Use sp_drv_unitext_locator_charlength to find the character length of a LOB column referenced by a unitext locator. sp_drv_unitext_locator_charlength accesses the Transact-SQL function char_length.
sp_drv_unitext_locator_charlength locator, data_length
locator – a unitext_locator that references the unitext column to manipulate.
data_length – an integer specifying the character length of the unitext column referenced by locator.
None.
Use sp_drv_unitext_locator_bytelength to find the byte length of a LOB column referenced by a unitext locator. sp_drv_unitext_locator_bytelength accesses the Transact-SQL function data_length.
sp_drv_image_locator_bytelength locator, data_length
locator – a unitext_locator that references the unitext column to manipulate.
data_length – an integer specifying the byte length of the unitext column referenced by locator.
None.
Use sp_drv_image_locator_bytelength to find the byte length of a LOB column referenced by an image locator. sp_drv_image_locator_bytelength accesses the Transact-SQL function data_length.
sp_drv_image_locator_bytelength locator, data_length
locator – an image_locator that references the image column to manipulate.
data_length – an integer specifying the byte length of the image column referenced by locator.
None.
Use sp_drv_varchar_charindex, which accesses the Transact-SQL function charindex.
sp_drv_varchar_charindex search_string, locator, start, position
search_string – the literal, of type varchar, to search for.
locator – a text_locator that references the text column to search from.
start – an integer specifying the position from which to begin searching. The first position is 1.
position – an integer specifying the starting position of search_string in the LOB column referenced by locator.
None.
Use sp_drv_textlocator_charindex, which accesses the Transact-SQL function charindex.
sp_drv_textlocator_charindex search_locator, locator, start, position
search_locator – a text_locator that points to the literal to search for.
locator – a text_locator that references the text column to search from.
start – an integer specifying the position from which to begin searching. The first position is 1.
position – an integer specifying the starting position of the literal in the LOB column referenced by locator.
None.
Use sp_drv_univarchar_charindex, which accesses the Transact-SQL function charindex.
sp_drv_univarchar_charindex search_string, locator, start, position
search_string – the literal, of type univarchar, to search for.
locator – a unitext_locator that references the unitext column to search from.
start – an integer specifying the position from which to begin searching. The first position is 1.
position – an integer specifying the starting position of search_string in the LOB column referenced by locator.
None.
Use sp_drv_unitext_locator_charindex, which accesses the Transact-SQL function charindex.
sp_drv_charindex_unitextloc_in_locator search_locator, locator, start, position
search_locator – a unitext_locator that points to the literal to search for.
locator – a unitext_locator that references the text column to search from.
start – an integer specifying the position from which to begin searching. The first position is 1.
position – an integer specifying the starting position of the literal in the LOB column referenced by locator.
None.
Use sp_drv_varbinary_charindex, which accesses the Transact-SQL function charindex.
sp_drv_varbinary_charindex byte_sequence, locator, start, position
byte_sequence – the byte sequence, of type varbinary, to search for.
locator – an image_locator that references the image column to search from.
start – an integer specifying the position from which to begin searching. The first position is 1.
position – an integer specifying the starting position of search_string in the LOB column referenced by locator.
None.
Use sp_drv_image_locator_charindex, which accesses the Transact-SQL function charindex.
sp_drv_image_locator_charindex sequence_locator, locator, start, start_position
sequence_locator – an image_locator that points to the byte sequence to search for.
locator – an image_locator that references the image column to search from.
start – an integer specifying the position from which to begin searching. The first position is 1.
start_position – an integer specifying the starting position of the byte sequence in the LOB column referenced by locator.
None.
Use sp_drv_text_locator_valid, which accesses locator_valid.
sp_drv_text_locator_valid locator
locator – the text_locator to validate.
A bit representing one of these values:
0 – false, locator is invalid.
1 – true, locator is valid.
None.
Use sp_drv_unitext_locator_valid, which accesses locator_valid.
sp_drv_unitext_locator_valid locator
locator – the unitext_locator to validate.
A bit representing one of these values:
0 – false, locator is invalid.
1 – true, locator is valid.
None.
Use sp_drv_image_locator_valid, which accesses locator_valid.
sp_drv_image_locator_valid locator
locator – the image_locator to validate.
A bit representing one of these values:
0 – false, locator is invalid.
1 – true, locator is valid.
None.
Use deallocate locator. See the Adaptive Server Enterprise Reference Manual: Commands.
Example 1 Allocates handles and establishes a connection:
// Assumes that DSN has been named "sampledsn" and // UseLobLocator has been set to 1. SQLHENV environmentHandle = SQL_NULL_HANDLE; SQLHDBC connectionHande = SQL_NULL_HANDLE; SQLHSTMT statementHandle = SQL_NULL_HANDLE; SQLRETURN ret; SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &environmentHandle); SQLSetEnvAttr(environmentHandle, SQL_ATTR_ODBC_VERSION, SQL_ATTR_OV_ODBC3); SQLAllocHandle(SQL_HANDLE_DBC, environmentHandle, &connectionHandle); Ret = SQLConnect(connectionHandle, "sampledsn", SQL_NTS, "sa", SQL_NTS, "Sybase",SQL_NTS);
Example 2 Selects a column and retrieves a locator:
// Selects and retrieves a locator for bk_desc, where
// bk_desc is a column of type text defined in a table
// named books. bk_desc contains the text "A book".
SQLPrepare(statementHandle, "SELECT bk_desc FROM books
WHERE bk_id =1", SQL_NTS);
SQLExecute(statementHandle);
BYTE TextLocator[SQL_LOCATOR_SIZE];
SQLLEN Len = 0;
ret = SQLGetData(statementHandle, SQL_C_TEXT_LOCATOR,
TextLocator, sizeof(TextLocator),&Len);
If(Len == sizeof(TextLocator))
{
Cout << Locator was created with expected size <<
Len;
}
Example 3 Determines data length:
SQLLEN LocatorLen = sizeof(TextLocator);
ret = SQLBindParameter(statementHandle, 1,
SQL_PARAM_INPUT, SQL_C_TEXT_LOCATOR,
SQL_TEXT_LOCATOR, SQL_LOCATOR_SIZE, 0, TextLocator,
sizeof(TextLocator), &LocatorLen);
SQLLEN CharLenSize = 0;
SQLINTEGER CharLen = 0;
ret = SQLBindParameter(statementHandle, 2,
SQL_PARAM_OUTPUT, SQL_C_LONG,SQL_INTEGER,0 , 0,
&CharLen, sizeof(CharLen), &CharLenSize);
SQLExecDirect(statementHandle,
"{CALL sp_drv_text_locator_charlength( ?,?) }" , SQL_NTS);
cout<< "Character Length of Data " << charLen;
Example 4 Appends text to a LOB column:
SQLINTEGER retVal = 0;
SQLLEN Col1Len = sizeof(retVal);
SQLCHAR appendText[10]=”abcdefghi on C++”;
SQLBindParameter(statementHandle, 14,
SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &retVal, 0, Col1Len);
SQLBindParameter(statementHandle, 21, SQL_PARAM_INPUT,
SQL_C_TEXT_LOCATOR, SQL_TEXT_LOCATOR,
SQL_LOCATOR_SIZE, 0, &TextLocator,
sizeof(TextLocator), SQL_NULL_HANDLE);
SQLBindParameter(statementHandle, 32, SQL_PARAM_INPUT,
SQL_C_SLONG, SQL_INTEGER, 0, 0, &charLen, 0, SQL_NULL_HANDLE);
SQLBindParameter(statementHandle, 43, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 10, 0, append_text,
sizeof(append_text), SQL_NULL_HANDLE);
SQLExecDirect(statementHandle,
"{? = CALL sp_drv_setdata_text (?, ?, ?,?) }" , SQL_NTS);
SQLFreeStmt(statementHandle, SQL_CLOSE);
Example 5 Retrieves LOB data from a LOB locator.
SQLCHAR description[512];
SQLLEN descriptionLength = 512;
SQLBindParameter(statementHandle, 1, SQL_PARAM_INPUT,
SQL_C_TEXT_LOCATOR, SQL_TEXT_LOCATOR,
SQL_LOCATOR_SIZE, 0, TextLocator,
sizeof(TextLocator), SQL_NULL_HANDLE);
SQLExecDirect(statementHandle, "{CALL sp_drv_locator_to_text(?)}", SQL_NTS);
SQLFetch(statementHandle);
SQLGetData(statementHandle, 1,SQL_C_CHAR, description,
descriptionLength, &descriptionLength)
Cout << "LOB data referenced by locator:" << description
<< endl;
Cout << "Expected LOB data:A book on C++" << endl;
Example 6 Transfers data from a client application to a LOB locator.
description = "A lot of data that will be used for a lot
of inserts, updates and deletes"; descriptionLength = SQL_NTS;
SQLBindParameter(statementHandle, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 512, 0, description,
sizeof(description), &descriptionLength);
SQLExecDirect(statementHandle,
"{CALL sp_drv_create_text_locator(?)}", SQL_NTS);
SQLFetch(statementHandle);
SQLGetData(statementHandle, SQL_C_TEXT_LOCATOR,
TextLocator, sizeof(TextLocator),&Len);
