Problem with SQLBindParameter on IN/OUT parameter

Question

I have the following parameter being bound for calling a SQL procedure:

TCHAR str[41];
SQLINTEGER cb;
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT_OUTPUT, SQL_C_TCHAR, SQL_C_TCHAR, 40, 0, str, 0, &cb);

When I loop through str after calling the query I can see the result is there, but it's not quite right:

std::stringstream ss;
ss<<"{";
for(int i=0;i<41;++i)
{
    ss<<(int)str[i];
    if(i<40)ss<<',';
}
ss<<"}"<<endl<<"{";
for(int i=0;i<41;++i)
{
    if(str[i])
        ss<<"'"<<str[i]<<"'";
    else
        ss<<"null";
    if(i<40)ss<<',';
}
ss<<"}"<<endl;
cout<<ss.str();

{53,55,56,49,53,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,0} {'5','7','8','1','5',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',null}

For whatever reason, str is being padded with spaces. I know the SQL procedure is actually returning "57815" from testing it in isolation. Any ideas what's going on and how to resolve it? The cb value is set to 40 after the SQL call is executed.

anon · Accepted Answer · 2010-02-09 11:18:14Z

1

If the SQL database field is of type CHAR, it will be space padded (and if it isn't a CHAR, what type is it?). This is expected behaviour. If it isn't are you sure that SQL_C_TCHAR is the type you really want - it isn't listed on this MSDN page.

answered Feb 9, 2010 at 11:18

anon

Sign up to request clarification or add additional context in comments.

3 Comments

Mr. Boy Over a year ago

If it's space-padded how am I supposed to know the correct value... the DB row could in come cases contain trailing spaces for instance. In my usage it is safe to assume trailing spaces can be discarded but there must be general solution?

anon Over a year ago

@John You still haven't said what the type of the SQL field is in the database. If the field is of type CHAR then basically it shouldn't be - it should be a VARCHAR.

Mr. Boy Over a year ago

Aha. I see what you mean. VARCHAR does indeed fix it. Thanks!

BigBoss · Accepted Answer · 2012-05-30 10:42:38Z

0

You should use VARCHAR instead of CHAR, use SQL_VARCHAR as SQL type of your argument

answered May 30, 2012 at 10:42

BigBoss

6,9243 gold badges26 silver badges39 bronze badges

Collectives™ on Stack Overflow

Problem with SQLBindParameter on IN/OUT parameter

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related