Set Isolation

Hi all, IDS 9.4, RH9 we access some views in our database using Crystal Report in order to do some reporting. The problem is, that we have to access the database using the isolation level "dirty read". When accessing normal tables (not views), we could create a select-trigger on these tables. From these triggers, a stored procedure was called that sets the isolation level to dirty read. But there is no way to create a select-trigger on a view. Crystal-Reports only allows us to define a select-statement on tables or views, but not to execute some sql-statements before executing the first select. Does anybody has an idea how to handle this? Thanks in advance! bye, Markus

Markus Bschorer wrote: > IDS 9.4, RH9 > > we access some views in our database using Crystal Report in order to do > some reporting. > The problem is, that we have to access the database using the isolation > level "dirty read". > When accessing normal tables (not views), we could create a select-trigger > on these tables. From these triggers, a stored procedure was called that > sets the isolation level to dirty read. But there is no way to create a > select-trigger on a view. > Crystal-Reports only allows us to define a select-statement on tables or > views, but not to execute some sql-statements before executing the first > select. > > Does anybody has an idea how to handle this? Does Crystal allow you to generate temp tables en route, like ACE does? If so, you could consider creating an empty temp table which you subsequently ignore, using a stored procedure which primarily sets the isolation and coincidentally returns a result. Maybe... So, if that won't work - probably not - you can still think about whether a 'cursory' procedure like this can be used: CREATE PROCEDURE setisolation(i INTEGER) RETURNING INTEGER; SET ISOLATION TO DIRTY READ; RETURN i; END PROCEDURE; ...the following has not been near an instance of IDS... ...assume syntax errors are present! SELECT * FROM WhereEver, TABLE(SET(EXECUTE PROCEDURE setisolation(0) AS peculiar)) The idea is that the procedure is executed fairly early - probably - and generates a single row of data, so that the cross-product of one row with all the other rows is the same answer as you thought of in the first place, give or take the peculiar column. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Hi Jonathan, sorry, but this does not work. It seems, that the stored Procedure will be executed wthin the SELECT-Statement. But the SELECT-Statement itself will be executed using the current isolation mode. And this is committed read. Do you have another idea? Bye Markus "Jonathan Leffler" <jleffler@earthlink.net> schrieb im Newsbeitrag news:hHe1c.19227$aT1.9757@newsread1.news.pas.earthlink.net... > Markus Bschorer wrote: > > IDS 9.4, RH9 > > > > we access some views in our database using Crystal Report in order to do > > some reporting. > > The problem is, that we have to access the database using the isolation > > level "dirty read". > > When accessing normal tables (not views), we could create a select-trigger > > on these tables. From these triggers, a stored procedure was called that > > sets the isolation level to dirty read. But there is no way to create a > > select-trigger on a view. > > Crystal-Reports only allows us to define a select-statement on tables or > > views, but not to execute some sql-statements before executing the first > > select. > > > > Does anybody has an idea how to handle this? > > Does Crystal allow you to generate temp tables en route, like ACE > does? If so, you could consider creating an empty temp table which > you subsequently ignore, using a stored procedure which primarily sets > the isolation and coincidentally returns a result. Maybe... > > So, if that won't work - probably not - you can still think about > whether a 'cursory' procedure like this can be used: > > CREATE PROCEDURE setisolation(i INTEGER) RETURNING INTEGER; > SET ISOLATION TO DIRTY READ;> RETURN i; > END PROCEDURE; > > ...the following has not been near an instance of IDS... > ...assume syntax errors are present! > > SELECT * > FROM WhereEver, > TABLE(SET(EXECUTE PROCEDURE setisolation(0) AS peculiar))> > The idea is that the procedure is executed fairly early - probably - > and generates a single row of data, so that the cross-product of one > row with all the other rows is the same answer as you thought of in > the first place, give or take the peculiar column. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >

Pretty ingenious solution - using a (presumably BEFORE) select trigger to set "dirty read". However, can't this be dangerous for other non-report queries where you don't want "dirty read"? Anyway, a view is just a predefined select statement - it gets merged into the runtime statement... which means that a trigger on any of the underlying tables should do the trick, shouldn't it? Still, I'd recommend the flexibility of Stored procedures. Rudy "Markus Bschorer" <mb@worxbox.com> wrote in message news:<c230c6$ivh$06$1@news.t-online.com>... > Hi all, > > IDS 9.4, RH9 > > we access some views in our database using Crystal Report in order to do > some reporting. > The problem is, that we have to access the database using the isolation > level "dirty read". > When accessing normal tables (not views), we could create a select-trigger > on these tables. From these triggers, a stored procedure was called that > sets the isolation level to dirty read. But there is no way to create a > select-trigger on a view. > Crystal-Reports only allows us to define a select-statement on tables or > views, but not to execute some sql-statements before executing the first > select. > > Does anybody has an idea how to handle this? > > Thanks in advance! > bye, Markus