Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1841
  • Last Modified:

Retriving data type SQL_WLONGVARCHAR from SQL server (ntext or text type).

Hello,

I'm new in here so forget me if I'm doing anything wrong... (Such as wrong points estimation)
I have recently downloaded a c++ class for handling ODBC connection.
(I have took the code from http://www.codeproject.com/database/caaodbcclass.asp)

It seems like this class doesn't taking into acount cases when ntext or text data is needed to be retriveed from SQL server.

For that matter, the function which handle the data reiteration looks like this:

BOOL CODBCRecordset::GetFieldValue(int nField, CHAR *szData)
{
      SQLRETURN ret;
      SQLINTEGER cbValue;
      int nLength = GetFieldLength(nField) + 1;
      
      ret = SQLGetData(m_hStmt, (SQLUSMALLINT)nField + 1, SQL_C_CHAR, szData, nLength, &cbValue) == SQL_SUCCESS;
      return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}

I have no problem getting any data type except for text and ntext sql server data types.

I would appresiate it if someone could help me on that, and send me a code sample (or adjusting the above function) so it would work as needed.

Any other information regarding this issue (Important notes) will be welcomed!

Thanks,

Eran.


0
seomaster1
Asked:
seomaster1
  • 10
  • 6
1 Solution
 
itsmeandnobodyelseCommented:
The data type for ntext or nvarchar data is SQL_C_WCHAR. Or you could use SQL_C_TCHAR if you are using types LPCTSTR, LPTSTR, TCHAR as well.

So, you should have (or implement) a function like that:

BOOL CODBCRecordset::GetFieldValue(int nField, WCHAR *szData)
{
     SQLRETURN ret;
     SQLINTEGER cbValue;
     int nLength = (GetFieldLength(nField) + 1) * 2;
     
     ret = SQLGetData(m_hStmt, (SQLUSMALLINT)nField + 1, SQL_W_CHAR, szData, nLength, &cbValue) == SQL_SUCCESS;
     return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}

Regards, Alex



Note, you have to provide a buffer with an appropriate size. The size passed
0
 
itsmeandnobodyelseCommented:
Note, you have to provide a buffer with an appropriate size. I don't know if GetFieldLength(nField) returns the length in bytes or shorts.
0
 
seomaster1Author Commented:
Thanks for you respond.

I have adjust the function and it looks like this:
#define MAX_FIELD_VALUE_LEN 5000
BOOL CODBCRecordset::GetFieldValue(int nField, std::string *szData)
{
      SQLRETURN ret;
      SQLINTEGER cbValue;
      CHAR tmpStr[MAX_FIELD_VALUE_LEN];      //Assignning maxmimum size of content
      int nLength = GetFieldLength(nField) + 1;

      ret = SQLGetData(m_hStmt, (SQLUSMALLINT)nField + 1, SQL_C_WCHAR, tmpStr, nLength, &cbValue) == SQL_SUCCESS;
      
      (*szData) = tmpStr;
      return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}



LONG CODBCRecordset::GetFieldLength(int nField)
{
      SQLSMALLINT fSqlType, ibScale, fNullable;
      SQLUINTEGER cbColDef;
      
      SQLDescribeCol(m_hStmt, nField + 1, NULL, 0, 0, &fSqlType, &cbColDef, &ibScale, &fNullable);

      return cbColDef;      
}



From the main code I have user the GetFieldValue fuction like this:
std::string str = "";

memset(&str, 0, sizeof(1000000000));
recObj.GetFieldValue(0, &str);


The result is:
* I'm getting into str only the first character (While there are hundreds).
* The nLength variable in the function got the value: 1073741824.
* I have used the memset in order toprovide a buffer with an appropiate size. Is that how it is done?

Thanks,

Eran.


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
itsmeandnobodyelseCommented:
>> I'm getting into str only the first character (While there are hundreds).

I guess, you *see* only the first character as the second is a binary zero character.

std::string is a typedef of std::basic_string<char> what is wrong here even if std::string would take a right-hand wide char string (what it doesn't).

There are three ways out:

1. If you never have UNICODE strings but only ASCII or single-byte strings, i would recommend to change NTEXT type to TEXT, respectively NVARCHAR to VARCHAR in the database. The N-Type stores one character into two bytes what is necessary only for UNICODE strings.

2. You could convert WCHAR string to CHAR *before* you assign it to the str variable. That is valid if you only need one-byte characters to return.


BOOL CODBCRecordset::GetFieldValue(int nField, std::string& strData)
{
     SQLRETURN ret;
     SQLINTEGER cbValue;
     WCHAR wszTmpStr[MAX_FIELD_VALUE_LEN];     //Assignning maxmimum size of content
     int nLength = GetFieldLength(nField) + 1;

     ret = SQLGetData(m_hStmt, (SQLUSMALLINT)nField + 1, SQL_C_WCHAR,
                                wszTmpStr, 2* nLength, &cbValue) == SQL_SUCCESS;
     char szTmpStr[MAX_FIELD_VALUE_LEN*2];
     if (wcstombs(szTmpStr, wszTmpStr, sizeof(szTmpStr)) > 0)
     {
         strData = szTmpStr;
         return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
     }
     cout << "string contains wide characters: " << endl;
     return FALSE;
}


3. Change the interface to wide strings

BOOL CODBCRecordset::GetFieldValue(int nField, std::wstring& wstrData)
{
     SQLRETURN ret;
     SQLINTEGER cbValue;
     WCHAR tmpStr[MAX_FIELD_VALUE_LEN];     //Assignning maxmimum size of content
     int nLength = GetFieldLength(nField) + 1;

     ret = SQLGetData(m_hStmt, (SQLUSMALLINT)nField + 1, SQL_C_WCHAR, tmpStr, nLength*2, &cbValue) == SQL_SUCCESS;
     
     wstrData = tmpStr;
     return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}


Regards, Alex



   
0
 
itsmeandnobodyelseCommented:
>> The nLength variable in the function got the value: 1073741824.

The length parameter is cbValue and not nLength. I could be set to SQL_NTSL what means that the string returned is null terminated.

#define SQL_NTSL                  (-3L)

Regards, Alex

0
 
seomaster1Author Commented:
Hi,
Thanks Alex. You really helped me solved this issue.
Continuing this issue, I'm trying to retrieve longtex datatype from mysql (With the above function) but with no success...

I'm connecting to the DB through MyODBC 3.51 and mysql 5.0.1.


Thanks again.

Eran.
0
 
itsmeandnobodyelseCommented:
>> I'm trying to retrieve longtex datatype from mysql

Do you know by sure that *longtext* datatype of MySQL is to store UNICODE or wide text strings??? I would have assumed that longtext is that what other systems call CLOB (character long object) a text type that exceeds VARCHAR type (VARCHAR has different size depending on DBMS, e. g. 256 bytes on MSACCESS and 4096 bytes on ORACLE). If i have right, you have to use SQL_LONGVARCHAR type for SQL data type and SQL_C_CHAR for C data type. And forget all wide or TCHAR strings when dealing with that data type.

BOOL CODBCRecordset::GetFieldValue(int nField, std::string& strData)
{
     SQLRETURN ret;
     SQLINTEGER cbValue;
     char tmpStr[2000000];     //Assigning maxmimum size of text content
     int nLength = 2000000;

     ret = SQLGetData(m_hStmt, (SQLUSMALLINT)nField + 1, SQL_C_CHAR, tmpStr, nLength, &cbValue);
     
     strData = tmpStr;
     return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}

Note, you may using a smaller buffer and a loop to get data in portions. If more data is available, SQLGetData returns SQL_SUCCESS_WITH_INFO + SQLSTATE 01004 (Data truncated). When it returns the last part of the data, SQLGetData returns SQL_SUCCESS


Regards, Alex
0
 
seomaster1Author Commented:
Hi Alex,

Thanks very much! You are realy helping me on these matters.
Another issue:

I'm trying to retrieve data (FROM Mysql using ODBC 3.51) with a query that contains the CONCAT() function. Using SQLExecDirect returns Error.

Only when I remove the CONCAT(), the query succeed.

The function I use:
-------------------------starts----------------------

BOOL CODBCRecordset::Open(SQLTCHAR *szSqlStr)
{
      SQLRETURN ret;

      ret = SQLExecDirect(CODBCRecordset::m_hStmt, szSqlStr, SQL_NTS);

      if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
      {
            SQLFetch(m_hStmt);
            return TRUE;
      }

      return FALSE;
}

-------------------------ends----------------------

I guess it has something to do with the last parameter of SQLExecDirect (The SQL_NTS), but I'm not sure and didn't find any information about it.

Thanks a lot, Alex.

Eran.
0
 
itsmeandnobodyelseCommented:
>> Only when I remove the CONCAT(), the query succeed.

Because of that it has nothing to do with SQLExecDirect.

Note, when using SQLExecDirect, SQL parsing is made by the DBMS engine (i. e. MYSql server) *and not* by ODBC. When using functions you have to consider two things:

1. The name and calling syntax of functions is different between DBMS. So, CONCAT function may be named  'strconcat' for example in MySQL. You have to check the list of functions specific to the DBMS. Because of that i rarely used functions in my queries or had to use an DBMS specific translator to build SQL statements.

2. Most functions only work on specific column types. If one of the columns is LONGVARCHAR concatination won't work on all DBMS i know.

In MSDN you could find a table comparing ORACLE and SQLServer functions by searching for "SQL SELECT SUBSTR CONCAT". For MySQL i found the following link:

         http://dev.mysql.com/doc/mysql/en/Functions.html

You may post the invalid SQL statement and/or evaluate the error return.

Regards, Alex

   


0
 
seomaster1Author Commented:


The sql query I ran, does work for mysql by using phpMyAdmin for intance.
It only doesn't work through the code I sent you, so I guess it doesn't have something to do with the MySQL query syntax.



Thanks,

Eran.


0
 
itsmeandnobodyelseCommented:
>> It only doesn't work through the code I sent you

The SQL parser of the ODBC engine must not be identical with that in phpMyAdmin. If you have a MS EXCEL installation, you could use MSQuery.exe (that comes with Excel) to check the SQL statement. MSQuery is an ODBC GUI that can access any ODBC datasource.

>> and/or evaluate the error return

If a query fails normally an exception is thrown. You should see the exception error when running in Debug mode.

Here is a function that evaluates the error return:

//////////////////////////////////////////////////////////////////////////
Bool OdbcEnvironment::checkSqlReturn(RETCODE ret, String* pInfo, Bool bGetInfo, Bool bUpdate)
{
   if (ret == SQL_SUCCESS || (ret == SQL_SUCCESS_WITH_INFO && !bGetInfo))
      return True;

   if (pInfo == NULL)
      return False;

   SQLHSTMT& hStmt = bUpdate? m_hStmtUpd : m_hStmt;
   UChar szSqlState[10];
   UChar szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
   Long  nativeError;
   Short nLenErrorMsg;

   String&  info = *pInfo;
   String   newLine;

   if (ret == SQL_SUCCESS_WITH_INFO)
      info = "SQL_SUCCESS_WITH_INFO " + info;
   else
      info = "SQL_ERROR " + info;

   for (;;)
   {
       if (SQLError(m_hEnv, m_hDbc, hStmt, szSqlState, &nativeError,
          szErrorMsg, SQL_MAX_MESSAGE_LENGTH-1, &nLenErrorMsg) != SQL_SUCCESS)
          break;


       info << newLine << " | " << String((CharPtr)szSqlState) <<
                        " : " << String(nativeError) << " | " << String((CharPtr)szErrorMsg);
       if (newLine.isEmpty())
          newLine = "\n\t";
   }
   return False;
}

Tell me if you need help to adopt it.

Regards, Alex
0
 
seomaster1Author Commented:
Hi Alex,

1. I don't have the MSQuery.exe file even though I do have MS EXCEL (2003).
2. When I run the query, I'm getting -1 as a result and no exception is thrown (Not in this case. There were some cases that an exception has been thrown).
3. The method you sent me suppose to return to pInfo the sql error? Can you give me more details regarding the use of this function? When do I use it and what kind of parameters should I send to it?


Thanks,

Eran.
0
 
itsmeandnobodyelseCommented:
Try that:

#ifndef SQL_MAX_MESSAGE_LENGTH
#define SQL_MAX_MESSAGE_LENGTH 1024
#endif

#include <string>
#include <iostream>
#include <sstream>
using namespace std;

//////////////////////////////////////////////////////////////////////////
bool checkSqlReturn(RETCODE ret, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt,
                    string& info, bool bIgnoreSuccessWithInfo)
{
   // do nothing if SQL_SUCCESS or SQL_SUCCESS_WITH_INFO but ignored
   if (ret == SQL_SUCCESS || (ret == SQL_SUCCESS_WITH_INFO && bIgnoreSuccessWithInfo))
      return true;

   unsigned char szSqlState[10];
   unsigned char szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
   long  nativeError;
   short nLenErrorMsg;

   string   newLine;

   if (ret == SQL_SUCCESS_WITH_INFO)
      info = "SQL_SUCCESS_WITH_INFO " + info;
   else
      info = "SQL_ERROR " + info;

   ostringstream ostrInfo;
   ostrInfo << info;

   for (;;)
   {
       if (SQLError(henv, hdbc, hstmt, szSqlState, &nativeError,
          szErrorMsg, SQL_MAX_MESSAGE_LENGTH-1, &nLenErrorMsg) != SQL_SUCCESS)
          break;


       ostrInfo << newLine << " | " << string((char*)szSqlState) <<
                        " : " << nativeError << " | " << string((char*)szErrorMsg);
       if (newLine.empty())
          newLine = "\n\t";
   }
   info = ostrInfo.str();
   return false;
}

You have to pass the return code of the last SQL call, the handles of environment, connect and statement, and a string variable where you get the error message(s) text. The bool variable 'bIgnoreSuccessWithInfo' controls whether you want info about a SQL_SUCCESS_WITH_INFO return code or not.

A return value of 'true' says there was nor error, a return of 'false' means there was an error or warning.

   ...
   ret = SQLExecDirect(m_hStmt, szSqlStr, SQL_NTS);

   string info;
   if (!checkSqlReturn(ret, AfxGetHENV(), m_pDatabase->m_hdbc, m_hStmt, info, false))
   {
         cout << info;
   }

   ...

Regards, Alex

Regards, Alex


0
 
seomaster1Author Commented:

The AfxGetHENV method located at the stdafx.h library, correct?
As I understand, this include won't work in Unix which is not so good for me. This application supposed to be running on both windows and Unix OS.

Anyway, I have tried to include it just for tests, and got error that if I include it I must not include windows.h since it is already included. So I have removed the windows.h include and got the following errors:

c:\program files\microsoft visual studio\vc98\include\sqltypes.h(114) : error C2146: syntax error : missing ';' before identifier 'SQLHWND'
c:\program files\microsoft visual studio\vc98\include\sqltypes.h(114) : fatal error C1004: unexpected end of file found


Thanks,

Eran.
0
 
itsmeandnobodyelseCommented:
>> As I understand, this include won't work in Unix which is not so good for me.

No, that isn't true. I saw from your implementation above, that you used MFC and CRecordset derived class CODBCRecordset. That MFC class has a parent class CDatabase and a member variable m_pDatabase that points to that parent. The SQLHENV handle is stored somewhere in AFX helper as only one instance is needed regardless how many connections.

If not using MFC you have to store (unique) SQLHENV returned by SQLAllocEnv(..) and SQLHDBC returned by SQLAllocConnect(..)  - you have one SQLHENV handle for any open connection - somewhere as members in your own classes as you'll need them for any native SQL call.

Regards, Alex
0
 
itsmeandnobodyelseCommented:
>> error C2146: syntax error : missing ';' before identifier 'SQLHWND'

I suppose you have to include stdafx.h as very first include when using MFC. The header "stdafx.h" includes "afx.h" as well as "windows.h". The latter you need before including "sql.h" respectively "sqltypes.h".

That is because of that sequence in "sqltypes.h"

   #if defined(WIN32) || defined(OS2)
        typedef HWND                    SQLHWND;
   #elif defined (UNIX)
        typedef Widget                    SQLHWND;
   #else
     /* placehold for future O/S GUI window handle definition */
       typedef SQLPOINTER              SQLHWND;
   #endif

That fails to compile if neither HWND is defined *before* including "sqltypes.h" nor HWND has been typedef'd by you above sql includes.

Regards, Alex


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now