MySQL Forums
Forum List  »  Connector/ODBC

SQLBindCol, binding BIT column to SQL_C_CHAR doesn't work
Posted by: Zhang Garriot
Date: August 03, 2018 07:46PM

I'm working on a project that creates an universal data query layer for ODBC drivers.
It's requested that we should treat BIT values as string.

I use SQLBindCol to bind the bit column to SQL_C_CHAR and SQLFetchScroll to get the data. But binding BIT column to SQL_C_CHAR doesn't work well with MySQL ODBC.
When binding BIT columns to SQL_C_CHAR, it returns a 10 bytes value with number 1 or 0 in the first byte for each row.

My case:
MySQL Server:
MySQL 8.0.12 on Windows.
MySQL ODBC Driver 8.0 64bit.

DB Table:
CREATE TABLE `bit_test` (
`pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_bit` bit(1) NOT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `pk_UNIQUE` (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Data:
# pk, col_bit
1, 1
2, 0
3, 1
4, 0
5, 1

Pseudo code:
...
SQLCHAR buffer[5][10] = { 0 };
SQLBindCol(HSTMT, 1, SQL_C_CHAR, buffer, 5, NULL);
sqlReturn = SQLFetchScroll(HSTMT, SQL_FETCH_NEXT, 0);
...

In debugging mode, after SQLFetchScroll is executed, I can see buffer is filled with 10 bytes values and the first byte of each value is either 1 or 0.

I'm expecting that the first byte of each value should be either '1' or '0' so that the values can be treated as strings.

My code runs well with other database and their ODBC drivers, like PostgreSQL.
In PostgreSQL, when binding BIT column to SQL_C_CHAR, I can get "1" or "0" as the values. And it returns short values when BIT column is bound to SQL_C_BIT.

Is MySQL ODBC Driver designed to work in this way that SQLBindCol must bind the columns to the correct C types which are returned by SQLDecribeCol?

Options: ReplyQuote


Subject
Written By
Posted
SQLBindCol, binding BIT column to SQL_C_CHAR doesn't work
August 03, 2018 07:46PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.