ODBC ANSI_WARNINGS warning message is breaking stored procedures

Question

I think ANSI warnings are somehow breaking my stored procedure calling, and I'm trying to figure out how to fix it.

DB Server:

MS SQL Server 2008 Client:
Ubuntu Bionic
unixODBC
Microsoft ODBC Driver 17 for SQL Server for Linux
nanodbc

I have a stored procedure that I am calling from a Linux machine. Somewhere in the stored procedure, there is a statement that causes the following warning:

Warning: Null value is eliminated by an aggregate or other SET operation.

This is because the author of the function did something like a count on a nullable column. Here's the stuff about that.

Here's a minimal example of a stored procedure that breaks:

USE [my_database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [test_nanodbc]
AS
BEGIN
    SET NOCOUNT ON;

        select count(my_nullable_column) from Table_with_nullable_columns

        raiserror('fake error!',16,1)
END

Here's the code that I'm running on the linux box:

try {
        nanodbc::connection connection(get_connection_string(),5);
        nanodbc::statement statement(connection);
        statement.timeout(5);
        prepare(statement,"{? = CALL [test_nanodbc]()};");
        int returnVal;
        statement.bind(0,&returnVal,nanodbc::statement::PARAM_RETURN);
        nanodbc::result r = statement.execute();

        print("returned" << returnVal);
    }
    catch(const std::exception& e) {
        print("Could not call procedure: " << e.what());
    }

If the select statement in the stored procedure is commented out, the error will be raised as I'd expect. However, if the select statement is there and the warning is present, the error will NOT be raised and the code has no idea that the c++ failed.

If I run the stored procedure in SQL Server management studio, I get the desired and expected output:

Warning: Null value is eliminated by an aggregate or other SET operation. Msg 50000, Level 16, State 1, Procedure test_nanodbc, Line 30 [Batch Start Line 2] fake error!

I don't really know odbc; I've just been using nanodbc, so I haven't really investigated much how it's doing stuff under the hood and am just delving into that. So maybe it's a nanodbc bug or something they haven't done or maybe it's a MS SQL server driver for linux bug. I tend to think a MS driver bug because of the output I got after turning debug messages on? It calls the function SQLExecute and it returns SQL_ERROR in the one case, but SQL_SUCCESS in the other.

Here's the output of nanodbc with debug messages on when the select statement causing the warning is commented out:

/path/to/nanodbc/nanodbc.cpp:759 SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:771 SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)NANODBC_ODBC_VERSION, SQL_IS_UINTEGER)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:791 SQLAllocHandle(SQL_HANDLE_DBC, env, &conn)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:791 SQLAllocHandle(SQL_HANDLE_DBC, env, &conn)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1021 SQLSetConnectAttr(dbc_, SQL_LOGIN_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1041 SQLDriverConnect(dbc_, 0, (NANODBC_SQLCHAR*)connection_string.c_str(), SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)
<-- rc: SQL_SUCCESS_WITH_INFO | 
<-- rc: SQL_SUCCESS_WITH_INFO | 
/path/to/nanodbc/nanodbc.cpp:1368 SQLAllocHandle(SQL_HANDLE_STMT, conn.native_dbc_handle(), &stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1457 SQLSetStmtAttr(stmt_, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1395 SQLCancel(stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1749 SQLFreeStmt(stmt_, SQL_RESET_PARAMS)
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1368 SQLAllocHandle(SQL_HANDLE_STMT, conn.native_dbc_handle(), &stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1439 SQLPrepare(stmt_, (NANODBC_SQLCHAR*)query.c_str(), (SQLINTEGER)query.size())
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1457 SQLSetStmtAttr(stmt_, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1838 SQLDescribeParam(stmt_, param_index + 1, &param.type_, &param.size_, &param.scale_, &nullable)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1895 SQLBindParameter(stmt_, param.index_ + 1, param.iotype_, sql_ctype<T>::value, param.type_, param_size, param.scale_, (SQLPOINTER)buffer.values_, value_size, bind_len_or_null_[param.index_].data())
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1658 SQLFreeStmt(stmt_, SQL_CLOSE)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1676 SQLSetStmtAttr(stmt_, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)(std::intptr_t)batch_operations, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1457 SQLSetStmtAttr(stmt_, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1682 SQLExecute(stmt_)
<-- rc: SQL_ERROR | 
/path/to/nanodbc/nanodbc.cpp:369 SQLGetDiagRec(handle_type, handle, (SQLSMALLINT)i, sql_state, &native_error, 0, 0, &total_bytes)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:387 SQLGetDiagRec(handle_type, handle, (SQLSMALLINT)i, sql_state, &native_error, sql_message.data(), (SQLSMALLINT)sql_message.size(), &total_bytes)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:369 SQLGetDiagRec(handle_type, handle, (SQLSMALLINT)i, sql_state, &native_error, 0, 0, &total_bytes)
<-- rc: SQL_NO_DATA | 
/path/to/nanodbc/nanodbc.cpp:1358 SQLCancel(stmt_)
/path/to/nanodbc/nanodbc.cpp:1749 SQLFreeStmt(stmt_, SQL_RESET_PARAMS)
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1057 SQLDisconnect(dbc_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
1671142017.201946700[ERROR] /test_node: Could not call procedure: /path/to/nanodbc/nanodbc.cpp:1684: 0000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]fake error!

Now here's the output with the statement that causes the null warning:

/path/to/nanodbc/nanodbc.cpp:759 SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:771 SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)NANODBC_ODBC_VERSION, SQL_IS_UINTEGER)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:791 SQLAllocHandle(SQL_HANDLE_DBC, env, &conn)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:791 SQLAllocHandle(SQL_HANDLE_DBC, env, &conn)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1021 SQLSetConnectAttr(dbc_, SQL_LOGIN_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1041 SQLDriverConnect(dbc_, 0, (NANODBC_SQLCHAR*)connection_string.c_str(), SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)
<-- rc: SQL_SUCCESS_WITH_INFO | 
<-- rc: SQL_SUCCESS_WITH_INFO | 
/path/to/nanodbc/nanodbc.cpp:1368 SQLAllocHandle(SQL_HANDLE_STMT, conn.native_dbc_handle(), &stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1457 SQLSetStmtAttr(stmt_, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1395 SQLCancel(stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1749 SQLFreeStmt(stmt_, SQL_RESET_PARAMS)
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1368 SQLAllocHandle(SQL_HANDLE_STMT, conn.native_dbc_handle(), &stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1439 SQLPrepare(stmt_, (NANODBC_SQLCHAR*)query.c_str(), (SQLINTEGER)query.size())
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1457 SQLSetStmtAttr(stmt_, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1838 SQLDescribeParam(stmt_, param_index + 1, &param.type_, &param.size_, &param.scale_, &nullable)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1895 SQLBindParameter(stmt_, param.index_ + 1, param.iotype_, sql_ctype<T>::value, param.type_, param_size, param.scale_, (SQLPOINTER)buffer.values_, value_size, bind_len_or_null_[param.index_].data())
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1658 SQLFreeStmt(stmt_, SQL_CLOSE)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1676 SQLSetStmtAttr(stmt_, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)(std::intptr_t)batch_operations, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1457 SQLSetStmtAttr(stmt_, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(std::intptr_t)timeout, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1682 SQLExecute(stmt_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:2265 SQLSetStmtAttr(stmt_.native_statement_handle(), SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)(std::intptr_t)rowset_size_, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:2275 SQLSetStmtAttr(stmt_.native_statement_handle(), SQL_ATTR_ROWS_FETCHED_PTR, &row_count_, 0)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1743 SQLNumResultCols(stmt_, &cols)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:2735 SQLDescribeCol(stmt_.native_statement_handle(), i + 1, (NANODBC_SQLCHAR*)column_name, sizeof(column_name) / sizeof(NANODBC_SQLCHAR), &len, &sqltype, &sqlsize, &scale, &nullable)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:2882 SQLBindCol(stmt_.native_statement_handle(), i + 1, col.ctype_, col.pdata_, col.clen_, col.cbdata_)
<-- rc: SQL_SUCCESS | 
1671142773.036405035[ INFO] /test_node: returned -6
/path/to/nanodbc/nanodbc.cpp:1358 SQLCancel(stmt_)
/path/to/nanodbc/nanodbc.cpp:1749 SQLFreeStmt(stmt_, SQL_RESET_PARAMS)
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:1057 SQLDisconnect(dbc_)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS | 
/path/to/nanodbc/nanodbc.cpp:747 SQLFreeHandle(handle_type, handle)
<-- rc: SQL_SUCCESS |

Also curious: the return value for the procedure whenever there is an offending statment (that is, an ansi warning) is the return value that the procedure returned the last time it did not have a warning issued. So for example, if I change the proc to not raise an error nor do a bad select and run it, my c++ will run fine and will show returned 0. Then, if I add the offending select to the proc, whether I raise an exception or not, the return value shown by my c++ will be 0. However, if I change the proc to raise an error without an offending statement and return a value of -6, my c++ will catch the exception. Then, if I change the proc to have an offending select, my c++ will not throw an exception, but the return value will be -6, regardless of whether or not I raise an exception.

I'm not familiar with nanoodbc, but for a lot of SQL libraries you have to consume any result set(s) before exceptions/errors will trigger. Do you need to iterate over the `r` results to confirm there are no result sets pending? — AlwaysLearning, Dec 16 '22 at 01:52

ODBC ANSI_WARNINGS warning message is breaking stored procedures

0 Answers0