Queries executed on wrong database on Informix

On Informix, when editing data via grid dbvis performs some queries on the default database instead of the selected database. To recreate: 1. On an Informix instance create 2 databases, e.g. "foo" and "bar". 2. In each database create a table with some data, e.g.: CREATE TABLE trial_table(id INTEGER PRIMARY KEY, str VARCHAR(50)); INSERT INTO trial_table VALUES(1, 'one'); 3. In the database connection, select *"foo"* as the default database, e.g.: jdbc:informix-sqli://:/foo:informixserver= 4. Connect to the server, and in the tree navigate to the "trial_table" in *"bar"* database 5. Open the "Data" tab 6. Edit the data, e.g. change "one" to "two" and hit Save Expected: Row is updated in bar.trial_table Actual: Row is updated in foo.trial_table I don't have to tell you that this is a horrible, horrible bug. I would greatly appreciate prompt action... Best regards, Ivan Product: DbVisualizer Pro 9.0 Build: #1900 (2012/11/29 11:43) Java VM: Java HotSpot(TM) Client VM Java Version: 1.7.0_09 Java Vendor: Oracle Corporation OS Name: Windows XP OS Arch: x86 OS Version: 5.1

We found an additional symptom. If you select the data from the table in sql commander: SELECT * FROM trial_table; And edit the data in the resulting grid, this will update the table in the correct (selected) database. However, if you select the data using: SELECT * FROM informix.trial_table; If you try to edit the resulting grid, dbvis will update the table in the *DEFAULT* database.

Hi Ivan, I agree that this is a very serious bug. Can you please test a work-around: 1) Open Tool Properties, 2) Expand the Informix node under the Databases tab, 3) Select the SQL Statements node, 4) Select INSERT INTO TABLE in the list and add "$$catalog$$:" right after "INSERT INTO", i.e. make it: INSERT INTO $$catalog$$:$$schema$$$$schemaseparator$$$$table$$ ($$columns$$) VALUES ($$column-values$$) 5) Click Apply and rerun the test. Best Regards, Hans

Hi Ivan, I just realized that the same qualifier must be added also for UPDATE WHERE and DELETE WHERE, and probably should be added for all the SELECT statements as well. We will look into a fix like that (i.e. using the catalog qualifier by default for all), but I appreciate if you can confirm that it solves the problem. Best Regards, Hans

I can verify that adding "$$catalog$$:" solves the problem. I will update my statements manually for now. Glad we caught it before I did some damage to the production database. Regards, Ivan

Out of curiosity, when did this break? I don't remember having problems with previous versions. Also most other databases have the same query templates. Are other databases affected? I could not reproduce this on MySQL. Regards, Ivan

Another note... Your solution seems to fix the issue when working on a grid accessed via the tree. However, if I make an select query in SQL commander, and try to update a row in the resulting grid then i get an error. This is my modified query for UPDATE WHERE (I added the $$catalog$$ as suggested): UPDATE $$catalog$$:$$schema$$$$schemaseparator$$$$table$$ SET $$column-values$$ WHERE $$where-columns$$ The query dbvis tries to execute is similar to: UPDATE :: SET = WHERE The database is repeated twice and this causes the error. If I remove "$$catalog$$:" from the update query template, then this problem disappears, and updating works as expected. TLDR: your solution fixes the problem when accessing the table grid via tree, but breaks when accessing via query in sql commander.

I'm on 3.70.JC5. We have also reproduced the bug using 3.70JC1, and possibly some others. I'll check with my colleagues. Regards, Ivan

Most (possibly all) of our databases use unbuffered logging. Regards, Ivan