Problems with passing Binary as a table-valued parameter

Question

I'm using table-valued parameters to pass binary values.

I used SQLBindParameter and passed the values as SQL_C_BINARY, SQL_BINARY, BinarySize, etc. as the arguments, but when I run SQLExecute, I get an error or the result is different from what I expected.

The code I used for the first time is below.

SQLBindParameter(StmtHandle
, static_cast<SQLUSMALLINT>(tvpIndex + 1)
, SQL_PARAM_INPUT
, SQL_C_BINARY
, SQL_BINARY
, 40
, 0
, (SQLPOINTER)tvpParam->GetValuePtr()
, 40
, NULL);

**)tvpParam->GetValuePtr() is the first address in the 40-byte array.
ex) The array looks like this, std::array<BINARY(40), 10> test;
tvpParam->GetValuePtr() == &test[0]

There are two typical cases of error messages.

First

In Binary(40), if the 40-byte value contains a zero in the middle, the subsequent values are terminated.

Secondly

Binary(40) is passed with 40 bytes full, the error message “String or Binary will be truncated” is displayed.

The other datatypes are working fine, CHAR and VARCHAR are also working fine.

Based on this result, I wonder if the value is recognized as a string during the passing process, even though I specified Binary as SQLBindParameter.

---

The code I used for the second time is below.
SQLBindParameter(_StmtHandle
, static_cast<SQLUSMALLINT>(tvpIndex + 1)
, SQL_PARAM_INPUT
, SQL_C_BINARY
, SQL_BINARY
, 40
, 0
, (SQLPOINTER)tvpParam->GetValuePtr()
, 40
, &_TVP_Parameter_Length_List[tvpIndex]);

**) _TVP_Parameter_Length_List[tvpIndex] is also 40.

I put NULL as the last argument and it seemed to recognize it as a string like the first question. However, when I passed the address value of SQLLEN with 40 as the last argument, it returned the value of SQL_NEED_DATA is returned as the result of SQLExecute. Why is SQL_NEED_DATA returned when the BINARY size is not large...?

When I use the same code above for passing a single parameter, it works fine.
However, when I try to pass a Binary with TVP, I run into a problem.

Answer 1

Your guess is right, the problem you met on the first call is caused by passing NULL to the last argument.

There is a note in the article of SQLBindParameter.

Application developers are strongly discouraged from specifying a null pointer for StrLen_or_IndPtr when the data type of the parameter is SQL_C_BINARY. To make sure that a driver does not unexpectedly truncate SQL_C_BINARY data, StrLen_or_IndPtr should contain a pointer to a valid length value.

It's hard to say why did you get an unexpect returned value on second call. The context of your code is too less to get useful information. Looks like the code is the second stage of sample code which in the comment you post, and you were using column-wise parameters binding. If it is, the last argument should be a pointer to the array of SQLLEN.