Informix - set isolation to dirty read
lazarus / 16-Databases

----------------------------------------------------------------------
bambamns · 2024-09-06 06:06

Hi,

I'm using ODBC for connection to Informix database.
Inside database are data of one ERP and I'm just collecting some
information from it  - just plain select.
Very often, tables in database are locked during writing data (ERP is
writing them), so only way to access those table is to use isolation
level with :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i set
isolation to dirty read;
When I use this isolation level together with select in some Informix
admin tools, it works perfect every time, but I can't use it with
Lazarus ODBC Connection + SQL Query.
When I run something like this :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i set
isolation to dirty read; select * from something;
With isolation level I get error message : EDatabaseError: Operation
cannot be performed on an inactive dataset
Without isolation level, select is working, but only when target
tables are not locked.

Is there any way in Lazarus to use isolation level for Informix over
ODBC connection ?

----------------------------------------------------------------------
bambamns · 2024-11-19 14:41

Hi,

If anyone run into this problem and have a headache as I had, ASAIK
only solution is to set in ODBC driver isolation level to : Read
Uncommitted.

For more information about this you can read
https://www.ibm.com/docs/en/informix-servers/12.10?topic=levels-using-
dirty-read-isolation-level.

----------------------------------------------------------------------
MarkMLl · 2024-11-19 14:52

Thanks for that: I think Informix has been rather "under this
project's radar".

I've had to fiddle with isolation in the context of PostgreSQL and had
to conclude that it's not very well standardised across the various
interface libraries in the FCL.

MarkMLl

----------------------------------------------------------------------
Thaddy · 2024-11-19 18:55

I once had a discussion about IBM Informix with my ceo. I wanted it
gone. I won.
That was just 15 years ago.

----------------------------------------------------------------------
bambamns · 2024-11-21 12:14

Well,

Informix is not my "weapon of choice", but current ERP my company
still use is made with Infromix database and I had to write a lot of
sql's for reporting in Excel, over some exe and I made it with
Lazarus.

Anyway if anyone run on this problem, this is the way how to force it
to work any time without worry if table is locket or not.

----------------------------------------------------------------------
MarkMLl · 2024-11-21 12:39

Quote from: bambamns on November 21, 2024, 01:14:14 pmInformix is not
my "weapon of choice", but current ERP my company still use is made
with Infromix database and I had to write a lot of sql's for reporting
in Excel, over some exe and I made it with Lazarus.

Don't worry, Thaddy's turning into a grouch before his time ;-)

I've still got a few systems using ODBC (to PostgeSQL) and in the past
have had to investigate some fairly baroque hacks to get at server
facilities (e.g. asynchronous notifications). However even using
backend-specific libraries one can still have problems: there's quite
simply too much variation between servers to channel a common API
(plus of course stuff useful for debugging which hasn't made it into
the API).

MarkMLl