Re: Access, pass-through queries and isolation level

--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:
> Hello,
>
> I'm not sure if this problem is related to ODBC or
> Access...
>
> Imagine we have the following function: (Note: I
> know about the serial type, this is juste for
> illustration purpose...)
>
> ----------------------------------
>
> CREATE OR REPLACE FUNCTION public.func_test_insert()
> RETURNS int8 AS
> '
> DECLARE
>    next_id   int8;
> BEGIN
>
>    next_id = max(func_test_data.id)+1;
>    insert into public."func_test_data"
> values(next_id);
>
>    RETURN next_id;
> END;
> '
>   LANGUAGE 'plpgsql' VOLATILE;
>
> ----------------------------------
>
>
> From the query tool of pgAdmin, or with a
> pass-through query in Access 2000, I can call:
>
>    select * from public."func_test_insert"();
>
> ... without a problem. I get the inserted id back.
>
>
> Now if I want to change the isolation level before:
>
>    set transaction isolation level serializable;
>    select * from public."func_test_insert"();
>
> ... I cannot get the inserted id back in Access,
> from the pass-through query. Apprently, since the
> first query does not return any row, Access
> considers the whole query does not return anything.

Are you sending the two statements as separate queries
in your code?  If so, try sending both as a single
query, i.e. one string, with a semicolon separating
the two.  Access doesn't know you are sending two
statements in one, and Postgres will handle them just fine.

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree