Bug #20169 prepared statements: MYSQL_TYPE_BIT not supported
Submitted: 31 May 2006 15:32 Modified: 12 Feb 2007 20:43
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.23-BK, 5.0.20 OS:Linux (Linux)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[31 May 2006 15:32] Axel Schwenke
Description:
In http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html the manual mentions the type MYSQL_TYPE_BIT that is associated to the C type unsigned long long. However this parameter type is not implemented in any MySQL version up to current 5.2.

How to repeat:
Compile the attached test program 'test_types.c', run it and and see the error message:

$./test_types 
MYSQL_TYPE_BIT = 16
param bind failed
Using unsupported buffer type: 16  (parameter: 14)
[31 May 2006 16:15] Valeriy Kravchuk
Thank you for a problem report. Please, attach the test program. See also related bug #16376.
[1 Jun 2006 23:10] Axel Schwenke
test_types.c

Attachment: test_types.c (text/plain), 6.79 KiB.

[1 Jun 2006 23:13] Axel Schwenke
Hi, 
don't know why my I had to upload again, but now the file seems to be there.
Seems this is a duplicate anyway.
[1 Jun 2006 23:22] Axel Schwenke
<grr>

No, it's not a duplicate, but bug #16376 was bogus. The type MYSQL_TYPE_BIT exists, but it is not implemented for mysql_stmt_bind_param(). This can easily be checked with the implementation in libmysql.c:3005 ff.

So we should just revert the change in the manual in reaction to bug #16376.
[14 Jun 2006 14:07] Valeriy Kravchuk
Verified just as described with 5.0.23-BK (and your test program):

openxs@suse:~/dbs/5.0> ./20169
MYSQL_TYPE_BIT = 16
param bind failed
Using unsupported buffer type: 16  (parameter: 14)
[14 Jun 2006 19:30] Konstantin Osipov
The author of the bug reporter is making a classical mistake that is induced by our reuse of field type typecodes for specification of input data types.
There is a clear differentiation between these two uses of the typecodes.

On input: the typecode should describe the input data type. E.g. if you supply long long, the typecode should be MYSQL_TYPE_LONGLONG. There is no such host language type in C as BIT - therefore MYSQL_TYPE_BIT should not be used.

On output the typecode describes the RDBMS type of the data - e.g. for BIT() column, it is MYSQL_TYPE_BIT. 

In other words, you can get BIT typecode from the server in the metadata, but there is no way you can use it to submit data currently. The bug can be converted to a feature request, but then in addition to implementing it, we'll have to document that when one specifies MYSQL_TYPE_BIT as the typecoed for input data, MySQL assumes that the real type of the input buffer is long long, which is counter intuitive in my opinion. Instead, long term, we should separate input typecodes from output typecodes (we can map the new typecodes to the old values for backward compatibility).
[19 Oct 2006 8:20] Konstantin Osipov
Bug#22869 "Error not raised if binding doesn't work" was marked as a duplicate of this bug.
[19 Oct 2006 8:20] Konstantin Osipov
Bug#22869 "Error not raised if binding doesn't work" was marked as a duplicate of this bug.
[12 Feb 2007 20:44] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've revised the section on prepared statement data types.
The type code table is now two tables: One for the codes that
are allowable for input values and one for the codes that are
allowable for output values. MYSQL_TYPE_BIT is not one of
the allowable input values, so to submit a BIT value, you can
pass it using an integer variable and the corresponding
type code, and MySQL will perform type conversion to BIT
on the server side.