SQLBindParameter & empty strings

SQLBindParameter & empty strings

Post by SXNh » Sat, 19 Aug 2006 14:49:01


Hi,

I was using MySQL before migrating to SQL Server 2005 Express. I didn't have
any problems with SQLBindParameter when binding an SQL_CHAR param whose value
was an empty string, but SQL Server doesn't seem to like it.

At first I had set the ColumnSize to 0 -- which is probably what caused the
HY104 Invalide precision value error. After changing the ColumnSize (say my
column was a varchar(30) and i set columnsize to 30), I didn't get the error
anymore, but I found that the length of the 'data' written into the database
was 30 instead of 0!

How can I get around this problem of an empty string?

Thanks,
Isa
 
 
 

SQLBindParameter & empty strings

Post by Arni » Sun, 20 Aug 2006 00:17:35


I hope this helps. My 'normal' function to bind a string checks
for a string length of zero (empty string). If that's the case,
it calls NullString() as shown below. It may just be that your
Length/Indicator parameter has to be set to SQL_NULL_DATA.

void VParameter::NullString( void )
{

SQLRETURN rc;

m_LenInd = SQL_NULL_DATA;

rc = SQLBindParameter( m_hStmt, m_paramNumber,
SQL_PARAM_INPUT,

SQL_C_CHAR, SQL_CHAR, 1, 0, 0, 0, (SQLLEN *)
&m_LenInd );

CHECK_STMT( m_hStmt, rc );

}

HTH,
- Arnie

 
 
 

SQLBindParameter & empty strings

Post by SXNh » Tue, 22 Aug 2006 07:52:01

Thanks Arnie, worked like magic ;)

Regards,
Isa
 
 
 

SQLBindParameter & empty strings

Post by SXNh » Tue, 22 Aug 2006 09:01:02

Hi Arnie,

Sorry to trouble you again, but I was wondering, is there any way that I can
insert an empty string using SQLBindParameter instead of inserting a NULL?

Thanks in advance,
Isabella
 
 
 

SQLBindParameter & empty strings

Post by Arni » Wed, 23 Aug 2006 21:15:48


I don't know; I've never tried. To my way of thinking, an empty
VARCHAR is NULL.

I provide functions to allow a caller to ask if any returned
column is NULL. But, for a VARCHAR, if the caller just blindly
reads the column, I return an empty string.

Sorry I can't help you more.

- Arnie
 
 
 

SQLBindParameter & empty strings

Post by Chris Kush » Wed, 23 Aug 2006 23:30:36

Arnie pointed out how to set a column to NULL, this is quite different (to a
RDBMS) than setting an empty string.
You will find many cases where DBA's or developers have specified columns to
be 'NOT NULL'.

The framework i use queries the parameter information (SQLDescribeParam)
after SQLPrepare() is called.
For a parameter that corresponds to a varchar(50) column the framework then
uses the equivalent of the following when binding the columns:

char buf[...];
long len = strlen(buff);

SQLBindParameter(
hnd,
ord,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_VARCHAR,
50, // returned by SQLDescribeParam
0,
buff,
51, // add one for null term in buff
&len
);

So in the above, the main difference is that len would = 0 (instead of
SQL_NULL_DATA as Arnie used).


cmk