ODBC: How to bind a empty string?

Question

I have a code that binds a string to an ODBC statement that looks like this

std::string v = "...";
SQLBindParameter(stmt, c, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
                          v.size(), 0, &v[0], v.size(), 0)

It works for any regular string, but not for empty string. I get an error message instead:

Error: [Microsoft][ODBC SQL Server Driver]Invalid precision value

I changed the function call to this

 SQLBindParameter(stmt, c, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
                              std::max<SQLUINTEGER>(v.size(), 1), 0, &v[0], v.size(), 0)

And now empty string can be inserted, yet it is converted into a space instead of an empty string.

I don't want to insert a NULL value because many SQL statements become counter-intuitive when NULL is encountered. Is any way to bind an empty string at all?

(The database engine is SQL Server 2005 with ODBC3)

Aren't empty strings and strings with one space treated the same in CHAR fields in most DBs including SQLServer (e.g. see http://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space). So isn't your modified call actually working correctly in the same way as someone else reported solving this problem at http://austinfrance.wordpress.com/2012/02/27/odbc-sqlbindparameter-hy104-howto-bind-an-empty-string-to-an-sql_varchar/ — mc110, Jul 03 '14 at 08:23
@mc110: I did chose his route, but I thought it was only a hack. Apparent SQL standard specifies that trailing spaces be ignored. It is so unfortunate. You should turn that into an answer and I will accept it. — Siyuan Ren, Jul 03 '14 at 09:13

score 3 · Accepted Answer · edited May 23 '17 at 11:44

Empty strings and strings with one space are treated the same in CHAR fields in most DBs, including SQLServer (e.g. see SQL Server 2008 Empty String vs. Space and a MicroSoft explanation that this is part of the SQL-92 spec here: http://support.microsoft.com/kb/316626).

So your modified call actually works correctly.

This is the same way someone else reported solving this problem at http://austinfrance.wordpress.com/2012/02/27/odbc-sqlbindparameter-hy104-howto-bind-an-empty-string-to-an-sql_varchar/

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.

ODBC: How to bind a empty string?

1 Answers1

Linked