Trying to bind a string column for table valued parameter using ODBC

Like said in the title i am trying to bind a string column for a table valued parameter using ODBC.

So far i created a table like that:

CREATE TABLE [dbo].[TVPItem]
(
    ItemNo      INT IDENTITY(1,1) NOT NULL,
    ProdCode    VARCHAR(100),
    Qty         INT
)

And a TVP like that:

CREATE TYPE [dbo].[TVPParam]
AS TABLE
(
    ProdCode    VARCHAR(100),
    Qty         INT
)

And a procedure that takes TVP to insert into table:

CREATE PROCEDURE [dbo].[TVPOrderEntry]
(
    @Items      dbo.TVPParam READONLY
)
AS
BEGIN
    INSERT INTO dbo.TVPItem (ProdCode, Qty)
    SELECT ProdCode, Qty
    FROM @Items;
END;

And a VS2022 C++ project containing

#include <stdio.h>
#include <stdlib.h>
#include <tchar.h>
#include <windows.h>
#include "sql.h"
#include "sqlext.h"
#include "msodbcsql.h"

#include <array>
#include <string>
#include <vector>

// cardinality of order item related array variables
#define ITEM_ARRAY_SIZE 4

// struct to pass order entry data
typedef struct OrdEntry_struct
{
    SQLUINTEGER ItemCount;
    std::array<std::string, ITEM_ARRAY_SIZE> ProdCode;
    std::array<SQLINTEGER, ITEM_ARRAY_SIZE> Qty;
} OrdEntryData;

SQLHANDLE henv, hdbc, hstmt;
TCHAR szConnStrIn[256] = _T("DSN=SampleDSN;UID=test;PWD=test");

static void connect()
{
    SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);

    // This is an ODBC v3 application
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

    SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

    // Run in ANSI/implicit transaction mode
    SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);

    SQLDriverConnect(hdbc, NULL, (SQLTCHAR*)szConnStrIn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
}

static void OrdEntry_TVP(OrdEntryData& order)
{
    // Initialize TVP row count
    // Variable for TVP row count
    SQLLEN cbTVP = order.ItemCount;

    // Bind parameters for call to TVPOrderEntry
    // 1 - Items TVP
    SQLBindParameter(
        hstmt,
        1,// ParameterNumber
        SQL_PARAM_INPUT,// InputOutputType
        SQL_C_DEFAULT,// ValueType
        SQL_SS_TABLE,// Parametertype
        ITEM_ARRAY_SIZE,// ColumnSize - for a TVP this the row array size
        0,// DecimalDigits - DecimalDigits: For a table-valued parameter this must always be 0.
        NULL,// ParameterValuePtr - for a TVP this is the type name of the TVP
        // (not needed with stored proc)
        NULL,// BufferLength - for a TVP this is the length of the type name or SQL_NTS
        // (not needed with stored proc)
        &cbTVP);// StrLen_or_IndPtr - for a TVP this is the number of rows available

    // Bind columns for the TVP
    // First set focus on param
    SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)1, SQL_IS_INTEGER);

    std::vector<char*> strings;
    strings.reserve(order.ItemCount);

    std::vector<SQLLEN> stringsLength;
    stringsLength.reserve(order.ItemCount);

    for (int i = 0; i < order.ItemCount; i++)
    {
        strings.push_back(const_cast<char*>(order.ProdCode[i].c_str()));
        stringsLength.push_back(order.ProdCode[i].size());
    }

    // Col 1 - ProdCode
    SQLBindParameter(
        hstmt, 1, SQL_PARAM_INPUT,
        SQL_C_CHAR, SQL_VARCHAR,
        0, 0, strings[0],
        sizeof(char*), &stringsLength[0]);

    // Col 2 - Qty
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.Qty.data(), sizeof(SQLINTEGER), NULL);

    // Reset param focus
    SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER);

    // Call one procedure which inserts both the order and items
    SQLExecDirect(hstmt, (SQLTCHAR*)_T("{call TVPOrderEntry(?)}"), SQL_NTS);

    // Flush results & reset hstmt
    SQLMoreResults(hstmt);

    SQLFreeStmt(hstmt, SQL_RESET_PARAMS);

    // Commit the transaction
    SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
}

int main()
{
    connect();
    SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

    OrdEntryData order;

    order.ProdCode[0] = "1000";
    order.Qty[0] = 100;
    order.ProdCode[1] = "2000";
    order.Qty[1] = 200;
    order.ProdCode[2] = "3000";
    order.Qty[2] = 300;
    order.ProdCode[3] = "4000";
    order.Qty[3] = 400;
    order.ItemCount = 4;

    OrdEntry_TVP(order);
}

I set up User DSN named SampleDSN which points to my local test DB. That's what table contains after the code execution completes:

I am facing a problem - the int column (Qty) binds alright, but varchar column (ProdCode) binds correctly first row only. I want the column ProdCode to be 1000, 2000, 3000, 4000 respectively as in code provided. What am I doing wrong?

P.S. using SQL Server 2022, ODBC Driver 17 for SQL Server

The problem was that the std::vector<char*> strings is not actually an array of strings but an array of pointers. What I ended up doing is determine max string length, add a padding null character to all strings until max string length so all string are the same length, then concatenate it to a big vector of char with it's size = strings count * max string length. By doing so we ensure that all string are stored contiguously.

When calling SQLBindParameter I pass a pointer to concatenated data as rgbValue param, max string length as cbValueMax, and a pointer to data of vector of actual strings length (without padding chars) as pcbValue.

Sample code:

std::vector<char> strings;
std::vector<SQLLEN> actualStringsLength;

long long maxStringLength = 0;

std::for_each(
    order.ProdCode.begin(),
    order.ProdCode.end(),
    [&maxStringLength, &actualStringsLength](std::string& s) -> void
    {
        actualStringsLength.push_back(s.size());
        if ((s.size()) > maxStringLength)
            maxStringLength = s.size();
    });

strings.reserve(maxStringLength * order.ProdCode.size());

std::for_each(
    order.ProdCode.begin(),
    order.ProdCode.end(),
    [&strings, &maxStringLength](std::string& s) -> void {
        for (int i = 0; i < maxStringLength; i++)
        {
            if (i < s.size())
                strings.push_back(s.at(i));
            else strings.push_back('\0');
        }
    });

// Col 1 - ProdCode
SQLRETURN r = SQLBindParameter(
    hstmt, 1, SQL_PARAM_INPUT,
    SQL_C_CHAR, SQL_VARCHAR,
    0, 0, strings.data(),
    maxStringLength, actualStringsLength.data());