Informix - set isolation to dirty read

[bambamns]

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][+][-]

1. 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][+][-]

1. set isolation to dirty read;
2.  
3. 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]

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]

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]

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

[bambamns]

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]

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.

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