I'm writing small C++ console application (VS 2013 / Windows7) that uses SQL Server native client sqlncli.h. I'm have trouble with NULL fields in program - I'm not see right values in out.
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sqlncli.h>
#include <cstdio>
#include <string>
#include <iostream>
using namespace std;
int main()
{
SQLHENV environHandle;
SQLHDBC connectHandle;
SQLHSTMT statement;
SQLWCHAR* connectString = (SQLWCHAR*)TEXT("Driver={SQL Server};Server=XX.XX.XX.XX;Database=DBNAME;Trusted_Connection=yes;");
SQLRETURN status;
status = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &environHandle);
cout << "SQLAllocHandle returned " << status << "\r\n";
if (status == SQL_SUCCESS_WITH_INFO || status == SQL_SUCCESS)
{
status = SQLSetEnvAttr(environHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, SQL_IS_INTEGER);
cout << "SQLSetEnvAttr returned " << status << "\r\n";
if (status == SQL_SUCCESS_WITH_INFO || status == SQL_SUCCESS)
{
status = SQLAllocHandle(SQL_HANDLE_DBC, environHandle, &connectHandle);
cout << "SQLAllocHandle SQL_HANDLE_DBC returned " << status << "\r\n";
if (status == SQL_SUCCESS_WITH_INFO || status == SQL_SUCCESS)
{
status = SQLDriverConnect(connectHandle, NULL, connectString, SQL_NTS, NULL, 256, NULL, SQL_DRIVER_NOPROMPT);
cout << "SQLDriverConnect returned " << status << "\r\n";
if (status == SQL_SUCCESS_WITH_INFO || status == SQL_SUCCESS)
{
status = SQLAllocHandle(SQL_HANDLE_STMT, connectHandle, &statement);
cout << "SQLAllocHandle SQL_HANDLE_STMT returned " << status << "\r\n";
if (status == SQL_SUCCESS_WITH_INFO || status == SQL_SUCCESS)
{
status = SQLExecDirect(statement, (SQLWCHAR*)TEXT("SELECT * FROM [DBNAME].[dbo].[Table1]"), SQL_NTS);
cout << "SQLExecDirect returned " << status << "\r\n";
if (status == SQL_SUCCESS_WITH_INFO || status == SQL_SUCCESS)
{
int rec_id;
int id;
char date[64];
char ps[201];
while (SQLFetch(statement) == SQL_SUCCESS)
{
SQLGetData(statement, 1, SQL_C_ULONG, &rec_id, 0, NULL);
SQLGetData(statement, 2, SQL_C_ULONG, &id, 0, NULL);
SQLGetData(statement, 3, SQL_C_CHAR, date, 64, NULL);
SQLGetData(statement, 4, SQL_C_CHAR, ps, 201, NULL);
cout << rec_id << " " << id << " " << date << " " << ps << endl;
}
}
}
cout << "Connected;";
cin.get();
}
}
}
}
return 0;
}
Table1 structure :
CREATE TABLE [dbo].[Table1](
[rec_id] [int] IDENTITY(1,1) NOT NULL,
[id] [int] NOT NULL,
[date] [datetime] NOT NULL,
[ps] [varchar](200) NULL
) ON [PRIMARY]
In console out I see first 3 fields correct, but fourth field is like ╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠╠ How to catch NULL fields of type VARCHAR, NVARCHAR, FLOAT?
The last argument for the SQLGetData
function is
StrLen_or_IndPtr
[Output] Pointer to the buffer in which to return the length or indicator value. If this is a null pointer, no length or indicator value is returned. This returns an error when the data being fetched is NULL. SQLGetData can return the following values in the length/indicator buffer:
The length of the data available to return
SQL_NO_TOTAL
SQL_NULL_DATA
You are currently passing NULL
for this, but if you provide a pointer to a valid SQLLEN
variable then you can check for the SQL_NULL_DATA
return.
Also, if you initialise your buffer (e.g. char ps[201] = {0};
) then you won't get the garbage print-out if no data is returned for a field.