Start a new topic

Queries executed on wrong database on Informix

[This topic is migrated from our old forums. The original author name has been removed]

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

[This reply is migrated from our old forums.]

Re: Queries executed on wrong database on Informix
Ivan, Just to inform you that this is now fixed in the 9.0.1 version. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Queries executed on wrong database on Informix
Most (possibly all) of our databases use unbuffered logging. Regards, Ivan
[This reply is migrated from our old forums.]

Re: Queries executed on wrong database on Informix
Thanks Ivan, A related question is what database logging mode you are using? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Queries executed on wrong database on Informix
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
[This reply is migrated from our old forums.]

Re: Queries executed on wrong database on Informix
Ivan, What version of the Informix JDBC driver are you using? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Queries executed on wrong database on Informix
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.
[This reply is migrated from our old forums. The original author name has been removed]

Re: Queries executed on wrong database on Informix
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Queries executed on wrong database on Informix
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
[This reply is migrated from our old forums.]

Re: Queries executed on wrong database on Informix
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
[This reply is migrated from our old forums.]

Re: Queries executed on wrong database on Informix
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Queries executed on wrong database on Informix
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.