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.earth link.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