We help IT Professionals succeed at work.

ODBC and NULL values

shaji_am
shaji_am asked
on
Medium Priority
718 Views
Last Modified: 2008-02-26
Hi ,

I am doing a c++ project and my database is SQL server.

Some  columns  of a  table  contains null values ,spaces and other values. I am using ODBC to get the table values.
My intention is to write these values to a text file. But I failed to read the null and spaces from the table. Because of these 'null's, I cannot properly write to the text file. In my c++ program how can I check whether a variable(bind column) contains null or space.

Thanks
Comment
Watch Question

Commented:
Hello,

What kind of C++ you use,  I mean VC++ or C++Builder?
if you use  VC++ you can try CRecordset::IsFieldNull function.

Regards,

Commented:
Hello,

What kind of C++ you use,  I mean VC++ or C++Builder?
if you use  VC++ you can try CRecordset::IsFieldNull function.

Regards,
CERTIFIED EXPERT
Author of the Year 2009

Commented:
svik,
that is not an answer.  it is a question.

Please post comments, like everybody else.  Thanks!

-- Dan

Commented:
OK,

Sorry, I am new in your site.

Regards,
CERTIFIED EXPERT

Commented:
svik, read the policy etc on the welcome page,

anyway, i think that you will find a NULL is just a zero length string if bindcol points at a string. make sure before each fetch you set the first char in the string that may receive the NULL to '\0',

reject the current answer to re-open the question to other experts

Author

Commented:

I am using VC++. But I am using ODBC .
Since most of your data types will be CString, you can use the IsEmpty() method to check for NULLs.
Spaces should be OK, right?
If not, just check the first character for a space.

if (m_rsData->NAME[0] == ' ')
   {
   //Do Something here
   }
CERTIFIED EXPERT
Author of the Year 2009

Commented:
>>I am using ODBC to get the table values.

Are you using CRecordset and CDatabase?  Or are you using the low-level API functions such as SQLAllocHandle, SQLBindParam, and so forth?

Also, will you try to describe the problem more clearly and with more detail?  As it is, your question is impossible to answer.

-- Dan

Author

Commented:

Hi,

I am using ODBC functions like SQLAllocEnv(),SQLConnect,
SQLBindCol() etc.. to get values from the table.
Some of the bind varables contain null values which I need to distinguish.

Thanks.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
In your SqlBindCol function, the final argument is  StrLen_or_IndPtr.  You can set it to point to an SQLLEN datatype.

After the SQLFetch, you can examine that value to see if you got NULL data.

Example:

SQLCHAR    szCustName[100];
SQLINTEGER cbCustName;

SQLINTEGER nCustID;
SQLINTEGER cbCustID;

rc= SQLBindCol( hStmt, 1, SQL_C_CHAR, szCustName, 100, &cbCustName);
rc= SQLBindCol( hStmt, 2, SQL_C_ULONG, &nCustID, SQL_NTS, &cbCustID);

...
rc= SQLFetch( hStmt );

if ( cbCustName == 0 ) {
     // clm #1 had a 0-len string
}
if ( cbCustName == SQL_NULL_DATA ) {
     // clm #1 was NULL in the database
}

if ( cbCustID == SQL_NULL_DATA ) {
     // clm #2 was NULL in the database
}

=-=-=-=-=-=-=-=-=-=-
There is a simpler technquie that I use.  Before doing the SQLFetch, preset the buffers with some sort of default data.  Then in the SQLBindCol, just pass NULL as the final parameter (that is, ignore.

rc= SQLBindCol( hStmt, 1, SQL_C_CHAR, szCustName, 100, NULL);

szCustName[0]= '\0';
nCustID = -1;

rc= SQLFetch( hStmt );

Then you will have an empty string ("") for szCustName when column #1 is empty or NULL and you will have nCustID= -1 when column #2 is NULL.

Of course, that technique assumes that -1 will never be received as a "valid value"  You could choose some other arbitrary value such as:  

nCustID = 12345679999;

if that is less likely to occur in the real data.

-- Dan
CERTIFIED EXPERT
Author of the Year 2009

Commented:
hi shaji_am,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
CERTIFIED EXPERT
Author of the Year 2009
Commented:
hi shaji_am,
Do you have any additional questions?  Do any comments need clarification?

-- Dan

Explore More ContentExplore courses, solutions, and other research materials related to this topic.