[700.1465] Bug: Informix and boolean values

DBVisualizer doesn't interpret boolean columns correctly. I believe this bug is present in earlier versions as well (6.5.x), but I'm doing testing on 700.1465. I'm using: * DBVisualizer 700.1465 (early release) * Informix 11.50 ux5 * IBM's Informix JDBC 3.50.JC2 * Windows XP SP3 * JRE 1.6.0_17 To reproduce, create the following two tables: CREATE TABLE person ( person_id SERIAL NOT NULL, PRIMARY KEY (person_id) ); CREATE TABLE contact ( contact_id SERIAL NOT NULL, person_id INTEGER, bool1 BOOLEAN, bool2 BOOLEAN, PRIMARY KEY (contact_id), FOREIGN KEY (person_id) REFERENCES person (person_id) ); Now, select the newly created 'contact' table in the tree on the left, and open the Object View. There are 2 problems: 1. Object View->DDL lists columns bool1 and bool2 as BLOB instead of BOOLEAN. The full DDL from DBVis is: CREATE TABLE contact ( contact_id SERIAL NOT NULL, person_id INTEGER, bool1 BLOB, bool2 BLOB, PRIMARY KEY (contact_id), FOREIGN KEY (person_id) REFERENCES person (person_id) ) 2. Object View->Indexes shows for every index (in this case primary and foreign key) that bool1 and bool2 are a part of the index. A screenshot can be found here: [http://i.imgur.com/Ya8XX.png]. Edited by: Ivan Habunek on Dec 11, 2009 11:16 AM (just layout)

Hi Ivan, Thanks for reporting this. The BLOB/BOOLEAN mixup is caused by insufficient information being reported in the informix.syscolumns system table, but I found a work-around that should be safe, so this will be fixed in the next maintenance release as well as in the next EA. The problem with the BOOLEAN columns showing up in all indexes is caused by a bug in the JDBC driver. I tested with the latest version (3.50 JC5) but it is not corrected (you may still want to upgrade, though, since it may fix other things). There is unfortunately no easy way to work around this bug. I'll keep looking for a solution, but the best would be to get IBM to fix the JDBC driver. Best Regards, Hans

Hi Hans, Thanks for the swift response. I did a little digging and found something curious concerning the index columns problem. I was under the belief that DbVisualizer uses SQL code from /resources/profiles/informix.xml when fetching index columns. In informix.xml, there is a query called informix.getIndexColumns. I have copied it here for reference: [http://snipt.org/qgng]. When I execute this query manually on the contact table in my example, it returns the correct result - only one column called contact_id. It did not return bool1 and bool2 alongside as DbVisualizer does. This was strange so I enabled query logging on my database and recorded the query that DbVisualizer executes when fectching index columns. This is the query I recorded: [http://snipt.org/qgnh]. This query returns the wrong results which are displayed in DbVisualizer in the Index tab. Is this something from the JDBC driver itself? I cannot find this query in the DbVisualizer installation. Why does DbVisualizer not use a query which returns correct results and which is included in it's Informix xml file? Best regards, Ivan

Hi Ivan, > In informix.xml, there is a query called informix.getIndexColumns. I have copied it here for reference: [http://snipt.org/qgng]. When I execute this query manually on the contact table in my example, it returns the correct result - only one column called contact_id. It did not return bool1 and bool2 alongside as DbVisualizer does. That query is used for the Columns tab you see when you select an Index node. The way it looks now, it doesn't contain much information about the columns but it may be possible to add enough to use the same query for the Indexes tab for a Table node. > This was strange so I enabled query logging on my database and recorded the query that DbVisualizer executes when fectching index columns. This is the query I recorded: [http://snipt.org/qgnh]. This query returns the wrong results which are displayed in DbVisualizer in the Index tab. Is this something from the JDBC driver itself? I cannot find this query in the DbVisualizer installation. Yes, that's what the JDBC driver uses, and clearly does not give the correct result. > Why does DbVisualizer not use a query which returns correct results and which is included in it's Informix xml file? See above; I'll see if it can be modified to include more complete info about each column without sacrificing performance, Best Regards, Hans

Or you could just filter the JDBC query results and discard any rows which have ordinal_position = 0 (first index column starts at 1). But that would be a horrible hack. :) BTW, i just noticed that the same thing happens with LVARCHAR columns. They also appear in index composition where they shouldn't. The DDL is fine this time. Thanks for your assistance. Greatly appreciated. Can't wait to get v7 final. Regards, Ivan

Hi Ivan, Filtering the result as you suggest is a possible solution as well. We have this in our bug report system and will look at it, but I can't promise it will be part of 7.0 final. The DDL problem you also reported will however be fixed, in the next EA and 6.5 maintenance release. Best Regards, Hans