ODBC SQLBindParameter HY104 – Howto bind an empty string to an SQL_VARCHAR

ODBC: SQLBindParameter:HY104:1:0:[Microsoft][ODBC SQL Server Driver]Invalid precision value

I hit a problem a while back where I could not bind an empty string to a parameter in ODBC, at the time it was not a big deal because it was more appropriate to set the column to null when this was the case.

However I recently had to revisit the code and now had a case where I really did want to bind an empty string rather than a null to parameter.

Part of the problem is because I was wrapping up SQLBindParameter in a more convenient function, thus hiding some of the detail.

void bindString
    (struct _query *Q, int i, void *data, size_t len, SQLLEN *ind)
{
  if (data) {
    Q->ret = SQLBindParameter(Q->stmt, i, SQL_PARAM_INPUT, 
                              SQL_C_CHAR, SQL_VARCHAR,
                              len, 0,
                              data, (SQLINTEGER) len, 0);
  } else {
    *ind = SQL_NULL_DATA;
    Q->ret = SQLBindParameter(Q->stmt, i, SQL_PARAM_INPUT, 
                              SQL_C_CHAR, SQL_VARCHAR,
                              1, 0, 0, 0, ind);
  }
  if (!SQL_SUCCEEDED(Q->ret)) {
    DUMP_DIAGNOSTICS("SQLBindParameter", Q->stmt, SQL_HANDLE_STMT);
  }
 }

As part of that I was passing the same length for both the ColumnSize (parameter length) and the C string length. Passing length 0 for the C string length is totally correct, however 0 is not a legal value for a ColumnSize, hence the error.

ColumnSize represents the size of the parameter in the query. It only needs to be as big as the value of the parameter, except in the empty string case. So to allow the empty (zero length) C string to be bound, we need to simply bind it to a parameter of at lease size 1. The modified version of my bindString() function that supports empty strings is:

void bindString
    (struct _query *Q, int i, void *data, size_t len, SQLLEN *ind)
{
  if (data) {
    Q->ret = SQLBindParameter(Q->stmt, i, SQL_PARAM_INPUT, 
                              SQL_C_CHAR, SQL_VARCHAR,
                              (SQLINTEGER) (len ? len : 1), 0,
                               data, (SQLINTEGER) len, 0);
  } else {
    *ind = SQL_NULL_DATA;
    Q->ret = SQLBindParameter(Q->stmt, i, SQL_PARAM_INPUT, 
                              SQL_C_CHAR, SQL_VARCHAR,
                              1, 0, 0, 0, ind);
  }
  if (!SQL_SUCCEEDED(Q->ret)) {
    DUMP_DIAGNOSTICS("SQLBindParameter", Q->stmt, SQL_HANDLE_STMT);
  }
 }

					
Advertisements

About austinfrance

Technical Developer @ RedSky IT / Explorer Software
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

One Response to ODBC SQLBindParameter HY104 – Howto bind an empty string to an SQL_VARCHAR

  1. Paul says:

    Thanks! just the answer I was looking for!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s