How to Bind Parameters via Odbc C#

How to bind parameters via ODBC C#?

Odbc cannot use named parameters. This means that the command string uses placeholders for every parameter and this placeholder is a single question mark, not the parameter name.

OdbcCommand.Parameters

Then you need to add the parameters in the collection in the same order in which they appear in the command string

OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM [user] WHERE id = ?";
cmd.Parameters.Add("@id", OdbcType.Int).Value = 4;
OdbcDataReader reader = cmd.ExecuteReader();

You have also another problem, the USER word is a reserved keyword per MS Access Database and if you want to use that as field name or table name then it is required to enclose every reference with square brackets. I strongly suggest, if it is possible, to change that table name because you will be hit by this problem very often.

Why is parametrization via ODBC C# not binding?

Your SQL should be

dbCommand.CommandText = "SELECT col2 FROM TableName WHERE ID = @ID";

since your parameter is named as "@ID"

ODBC Command Parameters Not Binding

so i searched for parameterName and i found this article https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter.parametername(v=vs.110).aspx

they had a different way of calling a stored proc and it appears to work...

using (var conn = new OdbcConnection(sConn))
{
    const string cmdIns = "{ call spSound_Insert(?,?,?,?) }";
    using (var sqlCmdIns = new OdbcCommand(cmdIns, conn))
    {
        sqlCmdIns.CommandType = CommandType.StoredProcedure;
        sqlCmdIns.Parameters.Add("", OdbcType.NVarChar, 40).Value = "123";
        sqlCmdIns.Parameters.Add("", OdbcType.VarBinary, -1).Value = new Byte[128];
        sqlCmdIns.Parameters.Add("", OdbcType.Bit).Value = true;
        sqlCmdIns.Parameters.Add("", OdbcType.NVarChar, 128).Value = "test note";

        conn.Open();
    }
}

Can't get parameters to work with OdbcConnection in C#

The problem with your query like i said before is the single quote. If you pass in the value without a parameter you need to use those single quotes because this defines a string in a statement.

With the parameters the framework handels all this stuff for you. It also checks for sql injection and removes dissalowed chars. Especially for string and datetime values this is really helpful.

ODBC Comman add multiple parameters with one value

Not possible if using ODBC. In ODBC, named parameters are used only in calls to stored procedures and cannot be used in other SQL statements. https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/binding-parameters-by-name-named-parameters?view=sql-server-ver15.

However, it is possible if using .Net Oracle driver, not ODBC.

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

Digging deeper, I found out that the first ODBC API call failing is actually the binding call for the TVP column / parameter:

SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, tvp_name, 0, &cb);

This call returns SQL_ERROR and checking the diagnostic record issued by this gave me the following error:

HY004 [Microsoft][ODBC SQL Server Driver]Invalid SQL data type

This specific issue was asked here already but sadly remained unsolved. Ultimately using an outdated ODBC driver was the cause of this whole problem. See my answer on another question for more details on how to fix that: https://stackoverflow.com/a/47113255/2334932

Then two points brought up by @A.K. in his comment finally solved the issue:

1. Parameter Length

The last value passed to SQLBindParameter, the parameter length or cb here, needed to have actual amount of available rows rather than SQL_NTS, as it is used as input parameter here.

2. Passing the parameter name is not necessary as they are bound by position

I think it will work with or without, but specifying the name of the TVP here is actually not necessary and can be left out.

So changing the third SQLBindParameter call to this fixed the rest of the issue(s):

cb = arr_size;
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, NULL, 0, &cb);

Full working code

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 = arr_size; // Needs to have the actual amount of available rows

    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, NULL, 0, &cb); // Does not need the name of the 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);
    }
}

---

Related Topics