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.
