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?

Actually, I don't know anything about ODBC - just was curious. My 1st guess was that you get something like a char** and some pointer arithmetic could solve your issue but: Googling a little bit provided easysoft: UsingSelectWithParameterArrays and Dr.Dobbs: C Database Programming with ODBC. It looks like, access to results of "select" requests is not quite trivial. May be, you should edit your question and show your C++ code. — Scheff's Cat
– Scheff's Cat, Commented Apr 5, 2017 at 6:19
Mhm good Idea. Although I'm very new to C++ ( especially the sql side of it) my Sql connection code is taken from a website. I'll add it in; good idea — Pepe
– Pepe, Commented Apr 5, 2017 at 6:21
The code does not show how you bind the columns to the buffer and how you retrieve data: Using SqlBindCol(), or using SqlGetData(). What about SqlFetch()? What lib do you use / what is class ODBC_Class? — erg
– erg, Commented Apr 5, 2017 at 6:31
Ah My apologies. I thought I should've only put in the connection to the sql. Added it in now. Thank you for pointing it out — Pepe
– Pepe, Commented Apr 5, 2017 at 12:38

Nat · Accepted Answer · 2017-04-19 08:38:12Z

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.

answered Apr 19, 2017 at 8:38

Nat

1641 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

atlaste Over a year ago

Actually you need the length field for other stuff as well, such as char(N) values (note that they are padded!) and even varchar values. Also, I also prefer using SQL_WCHAR, which can be converted to an std::u16string quite easily; SQLGetData will fail in modern ODBC SQL Server drivers if you don't. One last detail, if you use SQL_WCHAR, you usually end up dividing the length by 2 (UTF-16 does that...) -- and if you don't check for SQL_NULL_DATA, -1/2 will give 0, so you will get an empty string instead of a NULL.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related