If first query fails, stop executing?

Hi! I've had this problem since I started using dbvis and it's horrible (for me). :) To reproduce (i use informix, not sure about other dbs): - connect to informix, execute any query, leave tab open - do nothing for a longer while, so that connection timeouts (this happens when I leave it overnight) - the next day, the first query to be executed in this tab will cause an error: [Error Code: -79716, SQL State: IX000] System or internal error java.io.IOException Now this is not that bad, but if I execute multiple queries (ctrl+enter), than the first query fails with above error, and then connection is reestablished and other queries are executed successfully (effectively the first one is skipped because of the error). One of my work patterns is to test things using SQL scripts like: BEGIN; UPDATE this; DELETE that; INSERT other; ROLLBACK; Now, when the first query fails (BEGIN), this causes other queries not to be executed in a transaction, and therefore not rolled back at the end. Which is pretty bad. Would it be possible to detect that connection is not active and not execute anything in this case? Or some other clever solution. Thx. Ivan Product: DbVisualizer Pro 9.0.7 Build: #1935 (2013/06/04 10:02) Java VM: Java HotSpot(TM) Client VM Java Version: 1.7.0_11 Java Vendor: Oracle Corporation OS Name: Windows XP OS Arch: x86 OS Version: 5.1

Example: 09:40:13 [BEGIN - 0 row(s), 0.000 secs] [Error Code: -79716, SQL State: IX000] System or internal error java.io.IOException ... Physical database connection acquired for: Backoffice Development (informix) 09:40:14 [INSERT - 1 row(s), 0.750 secs] Command processed 09:40:14 [INSERT - 1 row(s), 0.156 secs] Command processed 09:40:14 [INSERT - 1 row(s), 0.125 secs] Command processed 09:40:14 [INSERT - 1 row(s), 0.047 secs] Command processed 09:40:14 [INSERT - 1 row(s), 0.171 secs] Command processed 09:40:15 [INSERT - 25 row(s), 0.281 secs] Command processed 09:40:15 [INSERT - 0 row(s), 0.000 secs] 1) [Error Code: -268, SQL State: 23000] Unique constraint (warehouse:informix.u108_51) violated. 2) [Error Code: -100, SQL State: IX000] ISAM error: duplicate value for a record with unique key. 09:40:15 [INSERT - 0 row(s), 0.000 secs] 1) [Error Code: -268, SQL State: 23000] Unique constraint (warehouse:informix.u108_51) violated. 2) [Error Code: -100, SQL State: IX000] ISAM error: duplicate value for a record with unique key. 09:40:15 [INSERT - 0 row(s), 0.000 secs] 1) [Error Code: -268, SQL State: 23000] Unique constraint (warehouse:informix.u108_51) violated. 2) [Error Code: -100, SQL State: IX000] ISAM error: duplicate value for a record with unique key. 09:40:15 [INSERT - 0 row(s), 0.000 secs] 1) [Error Code: -268, SQL State: 23000] Unique constraint (warehouse:informix.u108_51) violated. 2) [Error Code: -100, SQL State: IX000] ISAM error: duplicate value for a record with unique key. 09:40:15 [ROLLBACK - 0 row(s), 0.000 secs] [Error Code: -255, SQL State: IX000] Not in transaction. ... 12 statement(s) executed, 30 row(s) affected, exec/fetch time: 1.530/0.000 sec [6 successful, 0 warnings, 6 errors]

Here's a full reproduce scenario: 1. Open dbvis, open connection to informix, execute these commands using ctrl+enter: ----- select * from systables; select * from systables; select * from systables; ----- All queries execute OK. Log: ----- INFO: 14:32:46 [SELECT - 460 row(s), 0.000 secs] Result set fetched INFO: 14:32:47 [SELECT - 460 row(s), 0.000 secs] Result set fetched INFO: 14:32:47 [SELECT - 460 row(s), 0.000 secs] Result set fetched SUMMARY: ... 3 statement(s) executed, 1380 row(s) affected, exec/fetch time: 0.000/0.109 sec [3 successful, 0 warnings, 0 errors] ----- Leave dbvis open. 2. On the machine where informix is installed, log into shell and run the following command to find the session ID corresponding to the connection made by dbvis. ----- $ onstat -g ses IBM Informix Dynamic Server Version 12.10.FC1WE -- On-Line -- Up 105 days 04:44:42 -- 1112076 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 106848 informix - 0 - 0 12288 12288 off 106847 tcimerma WS-IT05 5608 ws-it05 1 81920 69784 off 106846 ihabunek - -1 ws-it05. 1 86016 66816 off 106733 tcimerma WS-IT05 5608 ws-it05 1 385024 227128 off ----- In this case, I'm logged in as ihabunek so my session id is 106846. 3. Kill the sesion ----- $ onmode -z 106846 ----- You can check "onstat -g sess" to see if it's killed. 4. Go back to dbvis, and execute the same queries again, in the same sql commander tab by pressing Ctrl+Enter. The first query fails. Second and third are executed. Log: ----- ERROR: 14:36:54 [SELECT - 0 row(s), 0.000 secs] [Error Code: -79716, SQL State: IX000] System or internal error java.io.IOException INFO: ... Physical database connection acquired for: Backoffice Development (ihabunek) INFO: 14:36:54 [SELECT - 460 row(s), 0.000 secs] Result set fetched INFO: 14:36:55 [SELECT - 460 row(s), 0.000 secs] Result set fetched SUMMARY: ... 3 statement(s) executed, 920 row(s) affected, exec/fetch time: 0.000/0.078 sec [2 successful, 0 warnings, 1 errors] ----- QED. :)

Hi Ivan, This happens because when the database (or network) drops a connection, the client is not aware of it until an attempt is made to run a statement on that connection. We are looking into changes to the connection handling for a future version to handle this in a better way, but until then I can only suggest that you add a dummy statement first in your script, e.g. select 1 from sys dual; I hope this helps, Hans