shaji_am
asked on
ODBC and NULL values
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
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
Hello,
What kind of C++ you use, I mean VC++ or C++Builder?
if you use VC++ you can try CRecordset::IsFieldNull function.
Regards,
What kind of C++ you use, I mean VC++ or C++Builder?
if you use VC++ you can try CRecordset::IsFieldNull function.
Regards,
svik,
that is not an answer. it is a question.
Please post comments, like everybody else. Thanks!
-- Dan
that is not an answer. it is a question.
Please post comments, like everybody else. Thanks!
-- Dan
OK,
Sorry, I am new in your site.
Regards,
Sorry, I am new in your site.
Regards,
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
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
ASKER
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
}
Spaces should be OK, right?
If not, just check the first character for a space.
if (m_rsData->NAME[0] == ' ')
{
//Do Something here
}
>>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
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
ASKER
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.
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
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
hi shaji_am,
Do you have any additional questions? Do any comments need clarification?
-- Dan
Do you have any additional questions? Do any comments need clarification?
-- Dan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What kind of C++ you use, I mean VC++ or C++Builder?
if you use VC++ you can try CRecordset::IsFieldNull function.
Regards,