#651 MySQL tinyint column shown as BIT

2.6.6
closed-invalid
Core (462)
5
2008-08-06
2008-08-05
hallojens
No

Hi,

just discovered some strange behaviour with MySQL. Tinyint fields are shown as BIT in SQuirreL, the values are translated to true/false.

I was able to reproduce the problem by creating a simple table with the MySQL commandline interface such as:

create table test_tiny_int (value tinyint(1) unsigned);

insert into test_tiny_int (value) values (1);

mysql> select * from test_tiny_int;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

When connecting to the DB via SQuirreL the result is:

SELECT * FROM test_tiny_int;

value
true

And the column description shows:

COLUMN_NAME TYPE_NAME IS_NULLABLE DECIMAL_DIGITS COLUMN_SIZE COLUMN_DEF REMARKS DATA_TYPE BUFFER_LENGTH NUM_PREC_RADIX NULLABLE SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
value BIT YES <null> <null> <null> -7 65535 10 1 0 0 <null> 1

The MySQL Version is 5.0.37, SQuirreL is 2.6.6 and the JDBC driver is mysql-connector-java-5.1.6-bin.jar

best regards
Jens

Discussion

  • Rob Manning

    Rob Manning - 2008-08-05

    Logged In: YES
    user_id=1287991
    Originator: NO

    The type code (-7) is the JDBC type for "BIT". If it is to be treated like a TINYINT, the driver needs to tell SQuirreL that the type is -6.
    So, in the alias properties dialog, choose the "Driver properties" tab and scroll to the property "tinyInt1isBit". Check the "specify" checkbox and choose "false" for the value. Then reconnect and TINYINTs will appear to be TINYINTs instead of BIT(1) (BOOLEAN). Let us know whether or not that solves the problem for you.

    Rob

     
  • Rob Manning

    Rob Manning - 2008-08-05
    • labels: --> Core
    • assigned_to: nobody --> manningr
    • status: open --> pending-invalid
     
  • hallojens

    hallojens - 2008-08-06

    Logged In: YES
    user_id=1869389
    Originator: YES

    your solution works perfectly, thanks a lot. I have totally overseen the possibility of configuring the driver in such a fine grained way.
    Sorry for bothering you with that.

    Jens

     
  • hallojens

    hallojens - 2008-08-06
    • status: pending-invalid --> closed-invalid
     
  • Rob Manning

    Rob Manning - 2008-08-06

    Logged In: YES
    user_id=1287991
    Originator: NO

    It's no problem. It's confusing that they (MySQL Driver Folks) chose to "translate" the column type by default, forcing you to configure the driver to undo the translation. Here is the bug report (that they deemed "not a bug") where I found the driver property documented:

    http://bugs.mysql.com/bug.php?id=12972

    Driver properties can be quite useful at times - I'm glad you know how to get to them in SQuirreL now :)

    Rob

     

Log in to post a comment.