C++ assign variable value from SQL Server SELECT

Question

I've got a script that does a bunch of SQL inserts but I've been trying to add a section that assigns the returned value of a select query into a variable.

The code I've been using to do the inserts is:

if (SQL_SUCCESS != SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"BULK INSERT mytable FROM 'C:/dir/myfile.csv' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');", SQL_NTS)) {
        show_error(SQL_HANDLE_STMT, sqlstatementhandle);

That all works fine, but I can't figure out how to use the output of a query. The output will be a single int value, which I'd like to assign to an int variable.

Apologies if this is something that's blindingly obvious.

EDIT

Based on the answer below, the following has now worked for me. Thanks!

SQLRETURN    retcode;
SQLHSTMT     hstmt; // I use my own stmnthndl(sqlstatementhandle) below, this line left in for demonstration

retcode = SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"SELECT count(*) FROM mytable;",SQL_NTS);
SQLINTEGER   sCustID;
SQLLEN cbCustID;

if (retcode == SQL_SUCCESS) {
    while (TRUE) {
        retcode = SQLFetch(sqlstatementhandle);
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

            // get the first column
            SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &sCustID, 0, &cbCustID);

            //You can now print it
            cout << "CustID:" << sCustID;
        }
        else {
            break;
        }
    }
}

agold · Accepted Answer · 2015-12-03 16:55:14Z

You can use SQLGetData to get the data for a single column in the resultset. Taken from this example, first you execute the SQL query with SQLExecDirect:

SQLRETURN    retcode;
SQLHSTMT     hstmt;

retcode = SQLExecDirect(hstmt,
   (SQLCHAR*)"SELECT CUSTID, NAME, PHONE FROM CUSTOMERS ORDER BY 2, 1, 3",
   SQL_NTS);

Then you can get the data, I show a reduced version of the same example:

SQLINTEGER   sCustID, cbCustID;

if (retcode == SQL_SUCCESS) {
   while (TRUE) {
      retcode = SQLFetch(hstmt);
      if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
         show_error();
      }
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){

         // get the first column
         SQLGetData(hstmt, 1, SQL_C_ULONG, &sCustID, 0, &cbCustID);

         //You can now print it
         fprintf(out, "CustID: %-5d", sCustID);
      } else {
         break;
      }
   }
}

The syntax of SQLGetData is:

SQLRETURN SQLGetData(
      SQLHSTMT       StatementHandle,
      SQLUSMALLINT   Col_or_Param_Num,
      SQLSMALLINT    TargetType,
      SQLPOINTER     TargetValuePtr,
      SQLLEN         BufferLength,
      SQLLEN *       StrLen_or_IndPtr);

With:

StatementHandle: the handle to the executed SQL query.
Col_or_Param_Num: the column number, which starts at 1.
TargetType: the data type, you can probably use SQL_INTEGER, see SQL data types and C data types.
TargetValuePtr: the pointer to the output data.
BufferLength: the length, but is not used for fixed length data such as integers.
StrLen_or_IndPtr: an optional output that can return the length of the data or an error code.

Note: like you commented you might need to cast the SQL query to SQLCHAR * since SQLCHAR is an unsigned char, see the data types.

Thanks so much for this, had to make one change - I don't know if it's to do with how the rest of my code is laid out? I had to add (SQLCHAR*) in front of the query

Collectives™ on Stack Overflow

C++ assign variable value from SQL Server SELECT

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