How to bind an Array Parameter (Stored Procedure) via Native ODBC

Question

I've been looking for a way to pass an array of integers to a stored procedure via the ODBC API in C / C++. While there is VARCHAR(XX) as array type of characters, there is no array type for integers, such as VARINT(XX) or something like that.

Many answers (e.g. How to send a big array to a stored procedure ) suggest using Table-Valued Parameters. However they all use C# / ADO.NET, Java or PHP code with types which are simply not available in the C ODBC API.

My stored procedure:

-- "Array" Parameter type, used in the stored procedure later on
CREATE TYPE dbo.CharList
AS TABLE (Id INT)
GO

CREATE PROCEDURE [dbo].[Update_Character_Nation]
    @OldNation_Id   INT,
    @NewNation_Id   INT,
    @Excluded       dbo.CharList READONLY, -- array / table parameter
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Characters
    SET Nation_Id = @NewNation_Id
    WHERE Nation_Id = @OldNation_Id AND Id NOT IN (SELECT Id FROM @ExcludedChars) -- parameter used to exclude result sets here
END

So I've tried several things:

1. Passing the array directly

void UpdateCharacterNationTestArray()
{
    unsigned int old_nation_id = 2;
    unsigned int new_nation_id = 4;

    unsigned int excluded_character_ids[] = {24, 36};
    SQLLEN arr_size = ARRAYSIZE(excluded_character_ids);
    SQLINTEGER cb = SQL_NTS;

    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &old_nation_id, 0, NULL);
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &new_nation_id, 0, NULL);
    SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, arr_size, 0, excluded_character_ids, 0, &cb); // Binding array directly, like one would do with a VARCHAR type

    SQLRETURN res = SQLExecDirect(hstmt, (SQLCHAR*)"{call dbo.Update_Character_Nation(?,?,?)}", SQL_NTS);
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO)
    {
        printf("Error during query execution: %hd\n", res);
        ProcessLogs(SQL_HANDLE_STMT, hstmt);
    }
}

This fails with the diagnostic record:

ERROR; native: 206; state: 22018; msg: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: int is incompatible with CharList

2. Passing the array as Table-Valued Parameter (or at least trying to do so)

void UpdateCharacterNationTestTVP()
{
    unsigned int old_nation_id = 2;
    unsigned int new_nation_id = 4;

    unsigned int excluded_character_ids[] = {24, 36};
    SQLLEN arr_size = ARRAYSIZE(excluded_character_ids);
    SQLINTEGER cb = SQL_NTS;
    SQLCHAR* tvp_name = (SQLCHAR*)"dbo.CharList";

    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &old_nation_id, 0, NULL);
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &new_nation_id, 0, NULL);
    SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, tvp_name, 0, &cb); // Binding the type here. All parameters after SQL_PARAM_INPUT are taken from MSDN documentation about TVP

    // Super scary binding stuff tvp requires you to do
    SQLINTEGER cb_rows[] = {SQL_NTS, SQL_NTS};
    SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)3, SQL_IS_INTEGER);  // focusing the third parmeter (the TVP one) for the call(s) below
    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, arr_size /*or 0*/, 0, excluded_character_ids, sizeof(UINT), cb_rows); // binding of the actual array in a column styled fashion here
    SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER);  // resetting the focus

    SQLRETURN res = SQLExecDirect(hstmt, (SQLCHAR*)"{call dbo.Update_Character_Nation(?,?,?)}", SQL_NTS);
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO)
    {
        printf("Error during query execution: %hd\n", res);
        ProcessLogs(SQL_HANDLE_STMT, hstmt);
    }
}

This code (copied off this MSDN article more or less: Use Table-Valued Parameters (ODBC) ) errors out with:

ERROR; native: 0; state: 07002; msg: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

3. ?

I'm ultimately looking for a way to call a stored procedure with an array of unsigned integers. If there is a another way of doing that, please advise. If possible, I'd like to stay away from string (VARCHAR) parsing though.

Environment

For visibility I have not included the ODBC initialization code however these are the environment specs:

Visual Studio 2013
SQL Server 2014
ODBC Version: SQL_OV_ODBC3
Driver Connection: TCP/IPv4
hstmt = allocated SQL statement handle
C++ (the code however is not making use of C++ language features, hence 100% C compatible)

Any ideas on the approaches above or a completely new suggestion on how to bind an array as stored procedure parameter is appreciated.

It's been ages since I touched ODBC, but: 1) cb = SQL_NTS seems wrong as they use it for the actual number of rows 2) passing parameter name is not necessary as they are bound by position 3) passing SQL_NTS to cb_rows seems wrong too but they are likely ignored for ordinal types. — A.K.
Thanks for the short thoughts about this. You are right about your first and second point. The SQLBIndParameter call which binds the TVP column needs the cb value to be set to the actual amount of available rows. Then leaving the TVP name out in this binding call works as well :) OT: I think a noticeable amount of people still use native ODBC because of the lack of alternatives for native Win32 programming. — Vinz

Mathew Paxinos Mathew Paxinos · Accepted Answer · 2017-11-02T21:54:28

Your best option may be to parse your array into a delimited list and pass it through to the procedure.

There is an excellent answer for how to do that, with references to other sites, etc on the below question:

T-SQL stored procedure that accepts multiple Id values

How to bind an Array Parameter (Stored Procedure) via Native ODBC

2 Answers