SQL Null return "stops" return in c++ using odbc

Question

My table looks a bit like this

Table cards
|  CardId  |  cityName| eventName|  Colour|
|----------+----------+----------+--------|
|     29   |   "test" |   null   | "blue" |
|     2    |   null   |  "test   |  null  |

Let's say now that I do

 "select * from card where CardId = 29"

My return vector will contain: "test" and that's it. So it will essentially contain all the data BEFORE eventName. Same thing would apply to the following

 "select * from card where CardId = 2" 

The C++ code connecting to the db:

void SqlConnection::sqlExecuteSelect(string *select) {

SQLCHAR DBName[20] = "PandemicMain";
SQLCHAR SQLStmt[4000] = { 0 };
SQLRETURN rc = SQL_SUCCESS;
ODBC_Class Example;
if (Example.ConHandle != NULL)

{

    rc = SQLConnect(Example.ConHandle, DBName, SQL_NTS, (SQLCHAR *) "concordia", SQL_NTS, (SQLCHAR *) "University4", SQL_NTS);

    // Allocate An SQL Statement Handle 

    rc = SQLAllocHandle(SQL_HANDLE_STMT, Example.ConHandle, &Example.StmtHandle);

    rc = SQLExecDirect(Example.StmtHandle, SQLStmt, SQL_NTS);

    if (rc == SQL_SUCCESS)

    {

        // Define A SELECT SQL Statement  
        char* finalSelect = new char[select->length() + 1];
        std::strcpy(finalSelect, select->c_str());

        strcpy((char *)SQLStmt, finalSelect);

        // Prepare And Execute The SQL Statement  

            rc = SQLExecDirect(Example.StmtHandle, SQLStmt, SQL_NTS);

        // Display The Results Of The SQL Query  
        if (!rc == SQL_SUCCESS) {
            cout << "*************************** failed ***************" << endl;
        }
        if (rc == SQL_SUCCESS)

        {

            Example.GetResultset();
            Connection.colData = Example.colData;



            // At this point you would want to do something  

            // with the resultset, such as display it.  

        }

    }

    // Free The SQL Statement Handle  

    if (Example.StmtHandle != NULL)

        SQLFreeHandle(SQL_HANDLE_STMT, Example.StmtHandle);

    // Disconnect From The Northwind Sample Database  
    rc = SQLDisconnect(Example.ConHandle);

}
}

Odbc.h

#pragma once

////////////////////////////////////////
#include <windows.h>
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
#include <string>
#include <vector>
#include <iostream>
using namespace std;



// Define The ODBC_Class Class
class ODBC_Class
{
  struct ColDescription
  {
    SQLSMALLINT colNumber;
    SQLCHAR colName[80];
    SQLSMALLINT nameLen;
    SQLSMALLINT dataType;
    SQLULEN colSize;
    SQLSMALLINT decimalDigits;
    SQLSMALLINT nullable;
  };
// Attributes
public:
    SQLHANDLE EnvHandle;
    SQLHANDLE ConHandle;
    SQLHANDLE StmtHandle;
    SQLRETURN rc;
    vector<ColDescription> cols;
    vector< vector<string> > colData;
    // Operations
public:
    ODBC_Class(); // Constructor
    ~ODBC_Class(); // Destructor
    SQLRETURN GetResultset();
    void DescribeColumns();
private:
    _inline SQLRETURN Describe(ColDescription& c);
    SQLRETURN GetColData(int colnum, string& str);
};

Odbc.cpp

 #include "ODBC.h"

//***************************CODE FROM 
http://www.dreamincode.net/forums/topic/127959-odbc-c-example/ 
*************************************/
// Define The ODBC_Class Class


// Define The Class Constructor
ODBC_Class::ODBC_Class()
{
// Initialize The Return Code Variable
rc = SQL_SUCCESS;
// Allocate An Environment Handle
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle);
// Set The ODBC Application Version To 3.x
if (rc == SQL_SUCCESS)
    rc = SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION,
    (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
// Allocate A Connection Handle
if (rc == SQL_SUCCESS)
    rc = SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &ConHandle);
}

// Define The Class Destructor
ODBC_Class::~ODBC_Class()
{
// Free The Connection Handle
if (ConHandle != NULL)
    SQLFreeHandle(SQL_HANDLE_DBC, ConHandle);
// Free The Environment Handle
if (EnvHandle != NULL)
    SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle);
}

// Get the data for one column and return the info in the form
// of a std::string.  The ODBC driver will make all necessary
// data conversions from whatever type the data is in the database
// to SQL_CHAR.  You could make this function more comples by 
// getting the return type as it appears in the database then constructing
// a VARIANT object to hold the data.
SQLRETURN ODBC_Class::GetColData(int colnum, string& str)
{
  SQLCHAR buf[255] = { 0 };
  if ((rc = SQLGetData(StmtHandle, colnum, SQL_CHAR, buf, sizeof(buf), NULL)) == SQL_SUCCESS)
      str = reinterpret_cast<char*>(buf);
 return rc;
}

//
// Define The ShowResults() Member Function
SQLRETURN ODBC_Class::GetResultset()
{
   // Get all column description
   DescribeColumns();
   // erase anything that's in the colData vector
   colData.clear();
   // fetch a row from the resultset
   while (SQLFetch(StmtHandle) == SQL_SUCCESS)
   {
       // vector of strings to hold the column data
       vector<string> col;
       string data;
       // column counter
       int i = 1;
       // get the data for each column and add it to 
       // the col vector
       while (GetColData(i, data) == SQL_SUCCESS)
       {
           col.push_back(data);
           ++i; // increment the column number
       }
       // add column data to the colData vector
       colData.push_back(col);
    }
   return SQL_SUCCESS;
  }

 // Get the description for one column in the resultset.
 // This was made a seprate function to simplify the coding
 SQLRETURN  ODBC_Class::Describe(ColDescription& c)
{
    return SQLDescribeCol(StmtHandle, c.colNumber,
         c.colName, sizeof(c.colName), &c.nameLen,
         &c.dataType, &c.colSize, &c.decimalDigits, &c.nullable);
}

// Get the description for all the columns in the resultset.
void ODBC_Class::DescribeColumns()
{
    ColDescription c;
    c.colNumber = 1;
     cols.clear();
    while (Describe(c) == SQL_SUCCESS)
   {
      cols.push_back(c);
       ++c.colNumber;
   }

 }

After using SQLGetDiagRec

I get the following message:

SqlState: 22002

So I do know this means "Indicator variable required but not supplied"

Anyway to deal with this?

Answer 1

I had exactly the same error for a while. Basically the ODBC is trying to tell you that the value you are retrieving is NULL. It will do this with the last Param in SQLGetData(), you can find more information here: https://learn.microsoft.com/en-gb/sql/odbc/reference/syntax/sqlgetdata-function

The line:

  if ((rc = SQLGetData(StmtHandle, colnum, SQL_CHAR, buf, sizeof(buf), NULL)) == SQL_SUCCESS)
  str = reinterpret_cast<char*>(buf);

You need to change the NULL value you are passing in to something it can populate. That is the buffer that the ODBC will try and fill if it is a NULL value. It will always return the length of the column you return in this field (but will not fall over if you pass a NULL) unless it is returning a NULL in which case it needs it. Pass an SQLLEN in by ref and it should return as an SQL_NULL_DATA.