[Microsoft][SQL Server Native Client 10.0]String data, right truncation
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: [Microsoft][SQL Server Native Client 10.0]String data, right truncation

  1. #1
    Join Date
    Apr 2009
    Posts
    21

    [Microsoft][SQL Server Native Client 10.0]String data, right truncation

    Hi all,
    I wrote my own SQLDatabase class base on TinyODBC class, and i have problem with my SQLPreparedStatement::SetString method.

    In my app, i construct new instance of SQLPreparedStatement class and call SetString method to bind a string to statement. When i execute that statement i got an error, it says "[Microsoft][SQL Server Native Client 10.0]String data, right truncation".

    Code:
            ODBC::SQLConnection conn(Configuration::Database->ConnectionString());
    	conn.OpenEx();
    
    	ODBC::SQLResult* pResult = conn.Query(_T("SELECT ISNULL(MAX(ProductCategoryID), 0) + 1 FROM Production.ProductCategory"));
    	if (pResult != NULL)
    	{
    		int nNewProductCategoryID = pResult->Field(1).AsInt32();
    		ODBC::SQLFreeResult(&pResult);
    
    		ODBC::SQLPreparedStatement stmt(conn, _T("INSERT INTO Production.ProductCategory(ProductCategoryID, Name) VALUES (?, ?)"));
    		stmt.SetInt32(0, nNewProductCategoryID);
    		stmt.SetString(1, (LPCTSTR)sProductCategoryName);
    		if(stmt.Execute())
    		{
    			nProductCategoryID = nNewProductCategoryID;
    			return true;
    		}
    	}
    I dont know why i get that error because in other same statement (that use from TinyODBC class), statement execute success with no error.

    SQLDatabase classODBCDatabase.rar
    TinyODBC classTinyODBC.rar

    Thank in advance

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,466

    Re: [Microsoft][SQL Server Native Client 10.0]String data, right truncation

    Which line causes this error?
    What is the value you passes in your SetString method?
    What kind of exception do you catch? Where and how?
    Victor Nijegorodov

  3. #3
    Join Date
    Apr 2009
    Posts
    21

    Re: [Microsoft][SQL Server Native Client 10.0]String data, right truncation

    Quote Originally Posted by VictorN View Post
    Which line causes this error?
    What is the value you passes in your SetString method?
    What kind of exception do you catch? Where and how?
    Hi VictorN,
    1.Which line causes this error (What kind of exception do you catch? Where and how?)

    Code:
    #ifdef _UNICODE
    	typedef std::wstring	tstring;
    	typedef wchar_t			tchar;
    #else
    	typedef std::string		tstring;
    	typedef char			tchar;
    #endif
    
    ......
    RETCODE nRetv;
    SQLExecute(m_hstmt));
    if (!SQL_SUCCEEDED(nRetv))
    {
    	tstring sErrorText, sErrorCode;
    	if (__get_error(SQL_HANDLE_STMT, stmt.m_hstmt, sErrorText, sErrorCode))
    	{
    		SQLException* pException = new SQLException(sErrorText.c_str(), sErrorCode.c_str());
    		throw pException;
    	}
    	else
    	{
    		SQLException* pException = new SQLException(_T("Unknow error"));
    		throw pException;
    	}
    }
    nRetv = -1 means SQL_ERROR and i use __get_error (that calls SQLGetDiagRec) to get error info. It says "[Microsoft][SQL Server Native Client 10.0]String data, right truncation'

    the definition of __get_error function and tstring like this
    Code:
    bool __get_error(SQLSMALLINT nHandleType, SQLHANDLE handle, tstring & sErrorText, tstring & sErrrorCode)
    {       
            TCHAR szErrorCode[256];
    	TCHAR szErrorText[256];
    	SQLINTEGER nNativeError;
    	SQLSMALLINT nTotalBytes;
    	RETCODE nRetv;
    		// Ask for info
    	nRetv = SQLGetDiagRec(
    		nHandleType,
    		handle,
    		1,
    		(SQLTCHAR *)&szErrorCode,
    		&nNativeError,
    		(SQLTCHAR *)&szErrorText,
    		sizeof(szErrorText),
    		&nTotalBytes);
    	if (SQL_SUCCEEDED(nRetv))
    	{
    		sErrorText = szErrorText;
    		sErrrorCode = szErrorCode;
    		return true;
    	}
    	return false;
    }
    2. What is the value you passes in your SetString method
    Code:
    ODBC::SQLPreparedStatement stmt(conn, _T("INSERT INTO Production.ProductCategory(ProductCategoryID, Name) VALUES (?, ?)"));
    stmt.SetInt32(0, nNewProductCategoryID);
    stmt.SetString(1, (LPCTSTR)sProductCategoryName);
    where sProductCategoryName is Cstring type and its value is _T("Just for test")
    The definition of SetString method like this.
    Code:
    void SetString(int nParamIndex, const tstring& val)
    {					
    	SQLINTEGER nActualSize = SQL_NTS;
    	SQLBindParameter(m_hstmt,
    	nParamIndex + 1,                          //nParamIndex is base from 0 (zero)
    	SQL_PARAM_INPUT,
    	SQL_C_TCHAR,
    	SQL_WCHAR,
    	(SQLUINTEGER)val.size(),
    	0,
    	(SQLPOINTER *)val.c_str(),
    	(SQLINTEGER)(val.size() + 1),
    	&nActualSize);
    }

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,466

    Re: [Microsoft][SQL Server Native Client 10.0]String data, right truncation

    Not sure it could be applied to your case but check it out: http://social.msdn.microsoft.com/For...-359496d517e9/
    Victor Nijegorodov

  5. #5
    Join Date
    Apr 1999
    Posts
    27,433

    Re: [Microsoft][SQL Server Native Client 10.0]String data, right truncation

    Quote Originally Posted by pcuong1983 View Post
    the definition of __get_error function and tstring like this
    Don't use sizeof() as you're doing now to determine the number of characters.
    Code:
    bool __get_error(SQLSMALLINT nHandleType, SQLHANDLE handle, tstring & sErrorText, tstring & sErrrorCode)
    {       
            TCHAR szErrorCode[256];
            //...
    	nRetv = SQLGetDiagRec(
    		nHandleType,
    		handle,
    		1,
    		(SQLTCHAR *)&szErrorCode,
    		&nNativeError,
    		(SQLTCHAR *)&szErrorText,
    		sizeof(szErrorText),
    		&nTotalBytes);
    For Unicode, characters are 2 bytes in size. So sizeof(szErrorText) is 512, not 256.

    The correct usage is
    Code:
    sizeof(szErrorText)  / sizeof(TCHAR) 
    This works regardless whether TCHAR is a char or wchar_t.

    Regards,

    Paul McKenzie
    Last edited by Paul McKenzie; April 5th, 2013 at 05:08 AM.

  6. #6
    Join Date
    Apr 2009
    Posts
    21

    Re: [Microsoft][SQL Server Native Client 10.0]String data, right truncation

    Quote Originally Posted by Paul McKenzie View Post
    Don't use sizeof() as you're doing now to determine the number of characters.
    Code:
    bool __get_error(SQLSMALLINT nHandleType, SQLHANDLE handle, tstring & sErrorText, tstring & sErrrorCode)
    {       
            TCHAR szErrorCode[256];
            //...
    	nRetv = SQLGetDiagRec(
    		nHandleType,
    		handle,
    		1,
    		(SQLTCHAR *)&szErrorCode,
    		&nNativeError,
    		(SQLTCHAR *)&szErrorText,
    		sizeof(szErrorText),
    		&nTotalBytes);
    For Unicode, characters are 2 bytes in size. So sizeof(szErrorText) is 512, not 256.

    The correct usage is
    Code:
    sizeof(szErrorText)  / sizeof(TCHAR) 
    This works regardless whether TCHAR is a char or wchar_t.

    Regards,

    Paul McKenzie
    Thank Paul McKenzie,
    I have corrected my __get_error method and My problem is SQLBindParameter function with String (input type)


    Quote Originally Posted by VictorN View Post
    Not sure it could be applied to your case but check it out: http://social.msdn.microsoft.com/For...-359496d517e9/
    Thank VictorN, I followed your link and i sure all args of SQLBindParameter function are correct type.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center