How to write to a varchar(max) column using ODBC
- by andyjohnson
Summary: I'm trying to write a text string to a column of type varchar(max) using ODBC and SQL Server 2005. It fails if the length of the string is greater than 8000. Help!
I have some C++ code that uses ODBC (SQL Native Client) to write a text string to a table. If I change the column from, say, varchar(100) to varchar(max) and try to write a string with length greater than 8000, the write fails with the following error
[Microsoft][ODBC SQL Server
Driver]String data, right truncation
So, can anyone advise me on if this can be done, and how?
Some example (not production) code that shows what I'm trying to do:
SQLHENV hEnv = NULL;
SQLRETURN iError = SQLAllocEnv(&hEnv);
HDBC hDbc = NULL;
SQLAllocConnect(hEnv, &hDbc);
const char* pszConnStr = "Driver={SQL Server};Server=127.0.0.1;Database=MyTestDB";
UCHAR szConnectOut[SQL_MAX_MESSAGE_LENGTH];
SWORD iConnectOutLen = 0;
iError = SQLDriverConnect(hDbc, NULL, (unsigned char*)pszConnStr,
SQL_NTS, szConnectOut,
(SQL_MAX_MESSAGE_LENGTH-1), &iConnectOutLen,
SQL_DRIVER_COMPLETE);
HSTMT hStmt = NULL;
iError = SQLAllocStmt(hDbc, &hStmt);
const char* pszSQL = "INSERT INTO MyTestTable (LongStr) VALUES (?)";
iError = SQLPrepare(hStmt, (SQLCHAR*)pszSQL, SQL_NTS);
char* pszBigString = AllocBigString(8001);
iError = SQLSetParam(hStmt, 1, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)pszBigString, NULL);
iError = SQLExecute(hStmt); // Returns SQL_ERROR if pszBigString len > 8000
The table MyTestTable contains a single colum defined as varchar(max). The function AllocBigString (not shown) creates a string of arbitrary length.
I understand that previous versions of SQL Server had an 8000 character limit to varchars, but not why is this happening in SQL 2005?
Thanks,
Andy
