SqlGetData function returns SQL_NO_TOTAL (-4) for "StrLen_or_IndPtr " argument in SNAC ODBC
Here is a blog entry about retrieving xml data size from sql server by using ODBC and Sql Native client
-Create following stored procedure in sql server 2005
-Create a system DSN with name testxml and use SQL Native Client as provider
-Open Visual c++ and paste following code and put a break point sqlgetdata function. You will see that data length comes as "-4"
-If you enable odbc trace, you will see following entry:
connectodbc 16c8-16cc ENTER SQLGetData
HSTMT 003B2470
UWORD 1
SWORD -8 <SQL_C_WCHAR>
PTR 0x0012FE38
SQLLEN 0
SQLLEN * 0x0012FE2C
connectodbc 16c8-16cc EXIT SQLGetData with return code 1 (SQL_SUCCESS_WITH_INFO)
HSTMT 003B2470
UWORD 1
SWORD -8 <SQL_C_WCHAR>
PTR 0x0012FE38
SQLLEN 0
SQLLEN * 0x0012FE2C (-4)
DIAG [01004] [Microsoft][SQL Native Client]String data, right truncation (0)
Here is my stored proc:
CREATE proc [dbo].[PRSelectXml]
as
select convert(xml, 'abc') as xmlData
here is my c++ code;
#include "stdafx.h"
int _tmain(int argc, _TCHAR* argv[])
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;
SQLPOINTER rgbValue;
int i = 5;
rgbValue = &i;
//SQLCHAR * OutConnStr = (SQLCHAR * )malloc(255);
//SQLSMALLINT * OutConnStrLen = (SQLSMALLINT *)malloc(255);
// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
// Set the ODBC version environment attribute
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
// Allocate connection handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Set login timeout to 5 seconds
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)(rgbValue), 0);
// Connect to data source
retcode = SQLConnect(hdbc, (SQLWCHAR*) L"testxml", SQL_NTS, (SQLWCHAR*) NULL, 0, NULL, 0);
// Allocate statement handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
// Process data
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode=SQLExecDirect(hstmt,(SQLWCHAR*) L"exec PRSelectXml",SQL_NTS);
UCHAR v;
SDWORD size = 0;
while (SQLFetch(hstmt) ==SQL_SUCCESS)
{
//Put a break point here
retcode = SQLGetData(hstmt, (SQLUSMALLINT) 1, SQL_C_WCHAR, &v, 0, &size);
}
if(retcode == SQL_ERROR)
{
printf("Error on execution");
return 9;
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
return 0;
}
When server sends different data types to SNAC, the data formats might not be the same. For instance, for varchar(max), server will first send the total length of a row data. But for xml, server won't send the total length of a row data rather a special value indicating the length is unknown will be sent out. Thus, for xml (maybe large UDT as well) type, SNAC won't be able to know the actual length before reading the data and reading a special value indicating the end of xml data.
This is by-design. Here is sample code to workaround this issue.
SQLLEN cbData = 0; // Please use SQLLEN instead of SDWORD. This is because SQLLEN is defined as long for x86 and INT64 for x64. If you define it as SDWORD, you will have AV for x64 machines.
rc = SQLGetData(hstmt, 1, SQL_C_WCHAR, NULL, 0, &cbData);
CheckAndHandle(rc);
if (cbData != SQL_NO_TOTAL)
{
WCHAR *rgchBuff = new WCHAR[cbData / sizeof(WCHAR) + 1];
rc = SQLGetData(hstmt, 1, SQL_C_WCHAR, (SQLPOINTER)rgchBuff, cbData / sizeof(WCHAR) + 1, &cbData);
CheckAndHandle(rc);
HandleData(rgchBuff, cbData);
}
else
{
WCHAR rgchBuff[2048];
SQLLEN cbActualSize = 0;
do
{
cbData = 0;
rc = SQLGetData(hstmt, 1, SQL_C_WCHAR, (SQLPOINTER)rgchBuff, sizeof(rgchBuff), &cbData);
if (rc == SQL_ERROR)
{
Handle(rc);
Break;
}
If (cbData == SQL_NO_DATA || cbData > sizeof(rgchBuff))
{
cbActualSize = sizeof(rgchBuff) - sizesof(WCHAR); // excluding the null terminator.
}
else
{
cbActualSize = cbData; // cbData is for the length of available data only thus we shouldn't exclude the null terminator.
}
HandleData(rgchBuff, cbActualSize);
} while (rc != SQL_SUCCESS && rc != SQL_NO_DATA);
}
Feel free to contact with me if you have any questions.
*Kagan Arca