Invalid locator de-referenced

I am trying to copy data from one SQL server to another. The data contains big image or texts. After SQLGetData, we used the SQLPrepare, SQLBindParameter, SQLParam and SQLPutData, and SQL_LEN_DATA_AT_EXEC to insert the data.

Our application is unicode based, but the data type in the SQL server is text, so we bind the paramter using SQL_C_WCHAR as C type and SQL_LONGVARCHAR as sql type. But we got the error:

SQLParamData: RETCODE= -1; State=37000, Native Error=7143

Microsoft claims it has this problem when using the MDAC2.7, see http://support.microsoft.com/kb/325765, but with a different error message.

The SQL server ODBC driver we are using is 2000.85.1117.00, but I got "Invalid locator de-referenced", Not sure if it has been resolved completely or not?

Any possible workaround?

Thank

Strong

It should have been fixed in MDAC 2.7 SP1. Can you try MDAC 2.7 SP1 or after?

From some source, it mentioned two workarounds that you might want to try as well.

1. Instead of calling SQL_LEN_DATA_AT_EXEC(409602), if we call SQL_LEN_DATA_AT_EXEC(204801), then it works. Please note that according to ODBC Spec, SQL_LEN_DATA_AT_EXEC expects client to bind number of BYTES, so this is not actually an workaround.

2. Instead of binding SQL_C_WCHAR, if we bind it as SQL_C_CHAR, then we are able to insert more than 400k. This workaround is not an option for the customer as they are using Access Linked table to Sql server, and access always bind it as SQL_C_WCHAR. Customer does not have control over how access binds it.

|||

Thanks for the reply. Actually I am using the MDAC 2.8 and SQL driver version 2000.85.1117.0 which should include the fix for the SQL_LEN_DATA_AT_EXEC problem. I am wondering if there are any new upper limits in the driver for SQLPutData, SQLParamData and SQL_LEN_DATA_AT_EXEC?

What are the differences between SQL_LEN_DATA_AT_EXEC(length) and SQL_DATA_AT_EXEC? the 'length' in SQL_LEN_DATA_AT_EXEC means the TOTAL bytes will be sent? or just the byte number sent in chunks when calling SQLPutData? What does the SQL_LEN_DATA_AT_EXEC(0) means?

Thanks

Strong

|||

SQL_LEN_DATA_AT_EXEC(length), where length is the total length of the text, ntext, or image parameter data in bytes. So, you can call SQLPutData one or more times as long as the total number of bytes to send is equal to length.

The difference between SQL_DATA_AT_EXEC and SQL_LEN_DATA_AT_EXEC(length) is that if a data source needs to know how many bytes of long data will be sent so that it can preallocate space, then SQL_LEN_DATA_AT_EXEC(length) must be used, otherwise SQL_DATA_AT_EXEC can be used. To determine if a data source requires this value, the application can call SQLGetInfo with the SQL_NEED_LONG_DATA_LEN option.

|||

Hi! Junfeng,

I figured out how I got the error "Invalid locator de-referenced", I rather think it is the issue (or limitation) from SQL Driver. The version of SQL driver I am using is 2000.85.1117.00 and the SQL Server is 2000 - 8.00.2039.

I was trying to insert 2 more than 400,000 bytes (200,000 wchar_t) data into 2 text columns using SQLParamData and SQLPutData, the code likes following but exactly, but quite sure it is correct:

*******************

Table: create table test (col1 text, col2 text)

sql: insert into test values (?, ?)

wchar_t * param1 (more than 200,000 wide chars)

wchar_t* param2 (more than 200,000 wide chars)

The ODBC API calls include:

SQLPrepare

strlen1 = SQL_LEN_DATA_AT_EXEC(param1widecharlength * sizeof(wchar_t)) -- convert to byte length

strlen2 = SQL_LEN_DATA_AT_EXEC(param2widecharlength * sizeof(wchar_t))

SQLBindParameter(stmt, 1, SQ_PARAM_INPUT, SQL_C_WCHAR, SQL_LONGVARCHAR, 214748367, 0, param1, 0, &strlen1); -- OK

SQLBindParameter(stmt, 2, SQ_PARAM_INPUT, SQL_C_WCHAR, SQL_LONGVARCHAR, 214748367, 0, param2, 0, &strlen2); -- OK

....

SQLExecute -- returns SQL_NEED_DATA, OK

SQLPOINTER retPtr1;

SQLPOINTER retPtr2;

SQLParamData(stmt, &retPtr1) -- returns SQL_NEED_DATA (OK) but the retPtr1 points to param2 (is it any probem here?)

while loop to SQLPutData

RETCODE rc = SQLPutData(stmt, (SQLPOINTER)((wchar_t*)retPtr1+ sent), 2000 * sizeof(wchar_t));

SQLParamData(stmt, &retPtr2) -- get error "Invalid locator de-referenced"

-

Supposed that the first 2 SQLParamData calls return SQL_NEED_DATA with returned retPtr1 and retPtr2 pointing to param1 and param2,

then the 3rd call of SQLParamData returns SQL_SUCCESS

-

If I did not used the SQLParamData and SQLPutData, it seems work fine.

Strong