1

I have a query that declares a table variable. It seems nanodbc cannot fetch data although the query works fine when directly executed on SQL server. Does it mean complicated queries are not supported by nanodbc?

Environment

  • nanodbc version: 2.14
  • DBMS name/version: MS SQL Server 2017
  • ODBC connection string:
  • OS and Compiler: Windows, Visual C++ 2019
  • CMake settings:

Actual behavior

sql queries containing table variables do not work.

Expected behavior

sql queries containing table variables should work.

Minimal Working Example

void show(nanodbc::result& results)
{
    const short columns = results.columns();
    long rows_displayed = 0;

    cout << "\nDisplaying " << results.affected_rows() << " rows "
         << "(" << results.rowset_size() << " fetched at a time):" << endl;

    // show the column names
    cout << "row\t";
    for(short i = 0; i < columns; ++i)
        cout << results.column_name(i) << "\t";
    cout << endl;

    // show the column data for each row
    while(results.next())
    {
        cout << rows_displayed++ << "\t";
        for(short col = 0; col < columns; ++col)
            cout << "(" << results.get<string>(col, "null") << ")\t";
        cout << endl;
    }
}

nanodbc::connection db_conn_ = nanodbc::connection(db_conn_string_);
execute(db_conn_, NANODBC_TEXT("DROP TABLE IF EXISTS research.dbo.nanodbc_test"));
execute(db_conn_, NANODBC_TEXT("CREATE TABLE research.dbo.nanodbc_test(Name varchar(20), Age int)"));
execute(db_conn_, NANODBC_TEXT("INSERT INTO research.dbo.nanodbc_test(Name, Age) VALUES('Bar', 20)"));
execute(db_conn_, NANODBC_TEXT("INSERT INTO research.dbo.nanodbc_test(Name, Age) VALUES('Foo', 30)"));
nanodbc::result result_working = execute( db_conn_, NANODBC_TEXT("select * from research.dbo.nanodbc_test"));
show(result_working);
//The following query does not return valid result, result_not_working contains 0 observations.
nanodbc::result result_not_working = execute(
    db_conn_,
    NANODBC_TEXT(
        "declare @names table(n varchar(20) not null primary key);"
        "   insert into @names select * from string_split('Bar,Foo', ',');"
        "   select[Name], [Age]"
        "   from research.dbo.nanodbc_test where Age in(20, 30)"
        "   and [Name] in(select n from @names)"
    )
);
show(result_not_working);

The question above is solved by adding 'set nocount on' at the beginning of the query suggested by David and Dan mentioned below.

What I am trying to solve is actually a bit more complicated. I want to run a parameterized query.

string query = NANODBC_TEXT(
        "set nocount on"
        "   declare @names table(n varchar(20) not null primary key);"
        "   insert into @names select * from string_split(?, ',');"
        "   select[Name], [Age]"
        "   from research.dbo.nanodbc_test where Age in(20, 30)"
        "   and [Name] in(select n from @names)"
    );

nanodbc::statement statement = nanodbc::statement(db_conn__);
prepare(statement, query);

string names = "Bar,Foo";
//The error happens when I try to bind a string parameter.
statement.bind(0, names.c_str());

Can someone help on this? Thanks.

Lee Gao
  • 11
  • 3
  • 2
    Try adding SET NOCOUNT ON as the first statement in that batch. – David Browne - Microsoft Aug 17 '20 at 18:02
  • 1
    Adding to David's comment, the insert into the table variable generates row count (DONE_IN_PROC) messages that need to be consumed. Some API's do that transparently but other's do not. `SET NOCOUNT ON;` will suppress those messages. – Dan Guzman Aug 17 '20 at 18:13
  • @DavidBrowne-Microsoft, thank you! It works! – Lee Gao Aug 18 '20 at 14:47
  • @DanGuzman, thanks for the explanations! – Lee Gao Aug 18 '20 at 14:48
  • @DavidBrowne-Microsoft, hey David, do you know how to get the parameterized query binding (please see the updated description above) to work by any chance? Thanks! – Lee Gao Aug 18 '20 at 22:04
  • @DanGuzman, hey Dan, do you know how to get the parameterized query binding (please see the updated description above) to work by any chance? Thanks! – Lee Gao Aug 18 '20 at 22:05
  • @LeeGao, sorry, I know nothing about nanodbc. – Dan Guzman Aug 18 '20 at 22:21
  • @DanGuzman, no worries. How do you connect to SQL Server in C++. I am novice to C++. I find nanodbc straight-forward to use but I am a bit upset that I could not figure out how to use it to handle queries like this. – Lee Gao Aug 19 '20 at 04:25
  • My personal preference is ADO.NET managed code nowadays (SqlClient in C#) because it's very easy to use. One can use ADO.NET in managed C++ but, if you also have needs for native, you'll incur an interop performance penalty (presumably you're using C++ for low-level stuff and performance). My experience with native C++ is limited to the direct ODBC API calls rather than a library like [this example](https://learn.microsoft.com/en-us/sql/connect/odbc/cpp-code-example-app-connect-access-sql-db). – Dan Guzman Aug 19 '20 at 12:04

0 Answers0