SQLBindCol() for the char column

[OneEyeMan]

Hi, ALL,
I have a problem.

I'm trying to connect to MS SQL Server and perform a query. The table I am trying to access has a "char(1)" column.

This is the code I'm using:

SQLWCHAR dataFontItalic[2];
SQLLEN cbDataFontItalic = SQL_NTS;
ret = SQLBindCol( stmt_tableProp, 6, SQL_C_BIT, &dataFontItalic, 2, &cbDataFontItalic );

When I call SQLFetch() it produces a following error from SQLGetDiagRec():

Invalid character value for cast specification.

Changing the type of dataFontItalic to SQLCHAR does not help. Passing '1' to SQLBindCol() also does not help.

Could someone please help?

Thank you.

[VictorN]

Are you sure you are passing the correct ColumnNumber into SQLFetch?
From MSDN:

ColumnNumber
[Input] Number of the result set column to bind. Columns are numbered in increasing column order starting at 0, where column 0 is the bookmark column. If bookmarks are not used — that is, the SQL_ATTR_USE_BOOKMARKS statement attribute is set to SQL_UB_OFF — then column numbers start at 1.

[OneEyeMan]

Hi, VictorN,
Yes, I'm sure.

Here is the the table structure:

CREATE TABLE "foo"(abt_tnam varchar(129) NOT NULL,
                            abt_tid integer,
                            abt_ownr varchar(129) NOT NULL, 
                            abd_fhgt smallint, 
                            abd_fwgt smallint, 
                            abd_fitl char(1), 
                            abd_funl char(1), 
                            abd_fchr smallint, 
                            abd_fptc smallint, 
                            abd_ffce varchar(18), 
                            abh_fhgt smallint, 
                            abh_fwgt smallint, 
                            abh_fitl char(1), 
                            abh_funl char(1), 
                            abh_fchr smallint, 
                            abh_fptc smallint, 
                            abh_ffce varchar(18), 
                            abl_fhgt smallint, 
                            abl_fwgt smallint, 
                            abl_fitl char(1), 
                            abl_funl char(1), 
                            abl_fchr smallint, 
                            abl_fptc smallint, 
                            abl_ffce varchar(18), 
                            abt_cmnt varchar(254) 
                            PRIMARY KEY( abt_tnam, abt_ownr ));

And I didn't set SQL_ATTR_USE_BOOKMARKS to anything - just used the default value.

Thank you.

[GCDEF]

Hi, VictorN,
Yes, I'm sure.

Here is the the table structure:

CREATE TABLE "foo"(abt_tnam varchar(129) NOT NULL,
                            abt_tid integer,
                            abt_ownr varchar(129) NOT NULL, 
                            abd_fhgt smallint, 
                            abd_fwgt smallint, 
                            abd_fitl char(1), 
                            abd_funl char(1), 
                            abd_fchr smallint, 
                            abd_fptc smallint, 
                            abd_ffce varchar(18), 
                            abh_fhgt smallint, 
                            abh_fwgt smallint, 
                            abh_fitl char(1), 
                            abh_funl char(1), 
                            abh_fchr smallint, 
                            abh_fptc smallint, 
                            abh_ffce varchar(18), 
                            abl_fhgt smallint, 
                            abl_fwgt smallint, 
                            abl_fitl char(1), 
                            abl_funl char(1), 
                            abl_fchr smallint, 
                            abl_fptc smallint, 
                            abl_ffce varchar(18), 
                            abt_cmnt varchar(254) 
                            PRIMARY KEY( abt_tnam, abt_ownr ));

And I didn't set SQL_ATTR_USE_BOOKMARKS to anything - just used the default value.

Thank you.

Try SQL_C_CHAR

[OneEyeMan]

Hi,

Try SQL_C_CHAR

Yes, that did it.
So what is the difference? I thought that since it is char(1) in the DB I should bind it to SQL_C_BIT, which is "unsigned char".

I mean how do you differentiate between different types?

Thank you.

[Arjay]

I mean how do you differentiate between different types?

Try searching for "SQL_C_CHAR odbc to native types".

[OneEyeMan]

Hi,

Try searching for "SQL_C_CHAR odbc to native types".

This is the first link that comes up.

In there SQL_C_CHAR is mapped to unsigned char * and SQL_C_BIT is mapped to unsigned char.

So I thought that since the type of the field is char[1] and not char[2+] it should be SQL_C_BIT. And then there are SQL_C_STINYINT[j] and SQL_C_UTINYINT[j], which are mapped to signed char and unsigned char respectively.

Now does this mean that for SQL_C_BIT I should declare my variable as:

SQLWCHAR dataFontItalic;

?

Thank you.

[Arjay]

According to the table in the link, the variable should be declared as SQLCHAR, not SQLWCHAR.

I never can remember the types to use so I find it helpful to type in a type, right click on it and choose "go to declaration" to find out how it's defined and whether I'm using the correct constant. Well, this works in the later versions of Visual Studio anyway.

[OneEyeMan]

Hi,
So are you saying that the binding should be based on the target variable type and not the field type?
I thought that the DB type should give the binding rule...

Thank you.

[2kaud]

In there SQL_C_CHAR is mapped to unsigned char * and SQL_C_BIT is mapped to unsigned char.

Note that in c/c++,

char ch;
char chp[1];

ch and chp are not the same types. chp is of type char* (pointer to char) even though it only has allocated storage for 1 char.

[OneEyeMan]

Hi,

Note that in c/c++,

char ch;
char chp[1];

ch and chp are not the same types. chp is of type char* (pointer to char) even though it only has allocated storage for 1 char.

Like I said - the binding is not based on the database field type but on the storage data type.

Thank you.

[Arjay]

Like I said - the binding is not based on the database field type but on the storage data type.

Right, that's why I suggested looking at the type header declarations to help you figure out which one to use. It's been 22 years since I've manually mapped ODBC columns, but I remember that it requires some trial and error and looking at the header declarations is helpful.

[GCDEF]

Hi,


Like I said - the binding is not based on the database field type but on the storage data type.

Thank you.

No. He's saying that a char array doesn't become a char just because you declare it with length 1. It's still a char array and should be treated that way.