MySQL Bugs: #20169: prepared statements: MYSQL_TYPE

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

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)

Thank you for a problem report. Please, attach the test program. See also related bug #16376.

test_types.c

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

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.

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

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)

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).

Bug#22869 "Error not raised if binding doesn't work" was marked as a duplicate of this bug.

Bug#22869 "Error not raised if binding doesn't work" was marked as a duplicate of this bug.

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.