RE: SQL query receives garbage if record locked (Delphi)

Again the BDE problem. You have so many layers now, that you cannot tell where the problem is. {Not Informix because it works from Dbaccess {Even from a remote Unix server ?}} Two/Tree Years ago we had thousands of problems with: 1 Delphi 3 Client -> BDE -> MS ODBC - > ODBC driver -> ISTAR 2 Delphi 3 Client -> BDE -> MS ODBC - > ODBC driver {Openlink} 3 Delphi 3 Client -> BDE -> BDE Informix driver -> driver -> ISTAR. We found that 99% of the time, the BDE was the culprit. Those days we had not much control of the BDE, and the only other solution at that stage was to replace the BDE with ODBCExpress. Now we had full control of the SQL's that was send to the Informix via the ODBC layer. Delphi 3 Client -> we control our queries -> ODBCExpress -> MS ODBC Layer -> ODBC driver -> Informix. Coding took longer, but just about 0% fault finding. The database did what it was supposed to do. {In the past we spend 60% time faultfinding and trying to improve performance} Now your fun begins. Options: 1 Start by changing the ODBC driver. Get the latest MS layer. Wow, it might work now, but I just about guarantee something else is broken now. Replace the BDE. If ODBC is still giving problems, look at other middle ware e.g. TUXEDO. 2 Ask Delhi News 3 Get yourself a new job Good luck Hannes -----Original Message----- From: Dinko Srkoc [SMTP:dsrkoc@helix.nospam.hr] Sent: Monday, April 10, 2000 12:01 PM To: informix-list@iiug.org Subject: SQL query receives garbage if record locked (Delphi) General info: D4, BDE 5.01/5.1.1, ODBC intersolv 3.11/informix 3.31, Informix 7.31.UC2 on Solaris 2.5.1 (SCO and NT ....) Description of situation: I have two Delphi clients which are connected to a database. -> 1st client: starts a transaction and updates some data in a table but don't commit nor rollback. Page/row is now locked. -> 2nd client: executes a query: "select * from table" Expected behavior: 2nd client should receive "Key violation ... SAM error: Record is locked" or similar exception. Actual behavior: 2nd client receives garbage and no exception is raised The same happens for both the tiDirtyRead and tiReadCommitted TransIsolation levels. I should also mention that I tried the same scenario with a console application (dbaccess) and it worked as expected (exception raised). If Delphi/dbaccess mix tried then if Delphi client makes a lock everything is fine, but if I make a lock from dbaccess then Delphi client receives garbage again. If I change query to "select * from table order by 1" than it's OK, an exception is raised, but only if updated row isn't indexed. Question: Why the 2nd client behaves as it does and how to provoke the lock exception? Any help would be greatly appreciated. Dinko Srkoc helix d.o.o.