SQLBindCol() for the char column
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 13 of 13

Thread: SQLBindCol() for the char column

  1. #1
    Join Date
    Aug 2002
    Posts
    640

    SQLBindCol() for the char column

    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:

    Code:
    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.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    18,940

    Re: SQLBindCol() for the char column

    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.
    Victor Nijegorodov

  3. #3
    Join Date
    Aug 2002
    Posts
    640

    Re: SQLBindCol() for the char column

    Hi, VictorN,
    Yes, I'm sure.

    Here is the the table structure:

    Code:
    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.

  4. #4
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,499

    Re: SQLBindCol() for the char column

    Quote Originally Posted by OneEyeMan View Post
    Hi, VictorN,
    Yes, I'm sure.

    Here is the the table structure:

    Code:
    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

  5. #5
    Join Date
    Aug 2002
    Posts
    640

    Re: SQLBindCol() for the char column

    Hi,
    Quote Originally Posted by GCDEF View Post
    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.

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,555

    Re: SQLBindCol() for the char column

    Quote Originally Posted by OneEyeMan View Post
    I mean how do you differentiate between different types?
    Try searching for "SQL_C_CHAR odbc to native types".

  7. #7
    Join Date
    Aug 2002
    Posts
    640

    Re: SQLBindCol() for the char column

    Hi,
    Quote Originally Posted by Arjay View Post
    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:

    Code:
    SQLWCHAR dataFontItalic;
    ?

    Thank you.

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,555

    Re: SQLBindCol() for the char column

    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.

  9. #9
    Join Date
    Aug 2002
    Posts
    640

    Re: SQLBindCol() for the char column

    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.

  10. #10
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    5,637

    Re: SQLBindCol() for the char column

    In there SQL_C_CHAR is mapped to unsigned char * and SQL_C_BIT is mapped to unsigned char.
    Note that in c/c++,

    Code:
    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.
    All advice is offered in good faith only. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/

    C, C++ Compiler: Microsoft VS2017.2

  11. #11
    Join Date
    Aug 2002
    Posts
    640

    Re: SQLBindCol() for the char column

    Hi,
    Quote Originally Posted by 2kaud View Post
    Note that in c/c++,

    Code:
    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.

  12. #12
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,555

    Re: SQLBindCol() for the char column

    Quote Originally Posted by OneEyeMan View Post
    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.

  13. #13
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,499

    Re: SQLBindCol() for the char column

    Quote Originally Posted by OneEyeMan View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This a Codeguru.com survey!


On-Demand Webinars (sponsored)