Обсуждение: Access, pass-through queries and isolation level

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;

----------------------------------

--- 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

I'm sending the two statements in one single pass-through query. Both statements are separated by a semicolon. I even
triedto remove the carridge return at the end of the first statement, but of course, it has no effect. 

Postgres handles the sql commands fine. But I'm not able to get the return value from "func_test_insert", as soon as I
addan sql command BEFORE the function call. BUT, if I add this SQL command AFTER the function call, it's OK.
Apparently,the first statement is considered to be the one that returns data. All others are executed, but their return
valueis discarded. I guess this is an Access issue, more than an ODBC one, right? 

-----Message d'origine-----
De : Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Envoyé : jeudi, 6. novembre 2003 16:35
À : Philippe Lang; pgsql-odbc@postgresql.org
Objet : Re: [ODBC] Access, pass-through queries and isolation level

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

--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:
> I'm sending the two statements in one single
> pass-through query. Both statements are separated by
> a semicolon. I even tried to remove the carridge
> return at the end of the first statement, but of
> course, it has no effect.
>
> Postgres handles the sql commands fine. But I'm not
> able to get the return value from
> "func_test_insert", as soon as I add an sql command
> BEFORE the function call. BUT, if I add this SQL
> command AFTER the function call, it's OK.
> Apparently, the first statement is considered to be
> the one that returns data. All others are executed,
> but their return value is discarded. I guess this is
> an Access issue, more than an ODBC one, right?

By elimination, I would say sit has to be an ODBC
issue.  In a pass through query, Access has no clue
about the meaning of what you are sending, and
PostgreSQL will certainly return the value you want.

If you haven't done so already, turn on ODBC logging,
and try posting the relevant sections to this list.
Someone knowledgable (that counts me out) might be
able to do something with that.

>
> -----Message d'origine-----
> De : Jeff Eckermann
> [mailto:jeff_eckermann@yahoo.com]
> Envoy� : jeudi, 6. novembre 2003 16:35
> � : Philippe Lang; pgsql-odbc@postgresql.org
> Objet : Re: [ODBC] Access, pass-through queries and
> isolation level
>
> > 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

On further thought, you may also want to debug your
Access code to make sure that the ReturnsRecords
property is not being reset somehow.

--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:
> I'm sending the two statements in one single
> pass-through query. Both statements are separated by
> a semicolon. I even tried to remove the carridge
> return at the end of the first statement, but of
> course, it has no effect.
>
> Postgres handles the sql commands fine. But I'm not
> able to get the return value from
> "func_test_insert", as soon as I add an sql command
> BEFORE the function call. BUT, if I add this SQL
> command AFTER the function call, it's OK.
> Apparently, the first statement is considered to be
> the one that returns data. All others are executed,
> but their return value is discarded. I guess this is
> an Access issue, more than an ODBC one, right?
>
> -----Message d'origine-----
> De : Jeff Eckermann
> [mailto:jeff_eckermann@yahoo.com]
> Envoy� : jeudi, 6. novembre 2003 16:35
> � : Philippe Lang; pgsql-odbc@postgresql.org
> Objet : Re: [ODBC] Access, pass-through queries and
> isolation level
>
> > 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.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
settings


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

>> I'm sending the two statements in one single
>> pass-through query. Both statements are separated by
>> a semicolon. I even tried to remove the carridge
>> return at the end of the first statement, but of
>> course, it has no effect.
>>
>> Postgres handles the sql commands fine. But I'm not
>> able to get the return value from
>> "func_test_insert", as soon as I add an sql command
>> BEFORE the function call. BUT, if I add this SQL
>> command AFTER the function call, it's OK.
>> Apparently, the first statement is considered to be
>> the one that returns data. All others are executed,
>> but their return value is discarded. I guess this is
>> an Access issue, more than an ODBC one, right?
>
>By elimination, I would say sit has to be an ODBC
>issue.  In a pass through query, Access has no clue
>about the meaning of what you are sending, and
>PostgreSQL will certainly return the value you want.
>
>If you haven't done so already, turn on ODBC logging,
>and try posting the relevant sections to this list.
>Someone knowledgable (that counts me out) might be
>able to do something with that.


Here we are...

I understand more or less that the second query is not recognized as a select statement... That's apparently the reason
whyI cannot get the value back in Access. 

Are "multiple result sets" supposed to work with the ODBC driver?


--------------------------------------------

 First Log:

 Query:
 select * from public."func_test_insert"();


 -> Works fine.

--------------------------------------------

[2620]**** PGAPI_ExecDirect: hstmt=159941280, statement='select * from public."func_test_insert"();'
[2620]PGAPI_ExecDirect: calling PGAPI_Execute...
[2620]PGAPI_Execute: entering...
[2620]PGAPI_Execute: clear errors...
[2620]recycle statement: self= 159941280
[2620]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=46, stmt='select * from
public."func_test_insert"();'
[2620]   stmt_with_params = 'select * from public."func_test_insert"();

'
[2620]       Sending SELECT statement on stmt=159941280, cursor_name='SQL_CUR098882A0'
[2620]send_query(): conn=159922072, query='select * from public."func_test_insert"();

'
[2620]send_query: done sending query
[2620]in QR_Constructor
[2620]exit QR_Constructor
[2620]read 57, global_socket_buffersize=4096
[2620]send_query: got id = 'P'
[2620]send_query: got id = 'T'
[2620]QR_fetch_tuples: cursor = '', self->cursor=0
[2620]num_fields = 1
[2620]READING ATTTYPMOD
[2620]CI_read_fields: fieldname='func_test_insert', adtid=20, adtsize=8, atttypmod=-1
[2620]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[2620]MALLOC: tuple_size = 100, size = 800
[2620]next_tuple: inTuples = true, falling through: fcount = 101, fetch_count = 101
[2620]qresult: len=5, buffer='12395'
[2620]end of tuple list -- setting inUse to false: this = 159916592
[2620]_next_tuple: 'C' fetch_total = 1 & this_fetch = 1
[2620]send_query: got id = 'Z'
[2620]     done sending the query:
[2620]extend_column_bindings: entering ... self=159941368, bindings_allocated=0, num_columns=1
[2620]exit extend_column_bindings
[2620]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[2620][SQLNumResultCols][2620]PGAPI_NumResultCols: entering...
[2620]SC_pre_execute: status = 3
[2620]PGAPI_NumResultCols: result = 159916592, status = 3, numcols = 1
[2620][SQLGetInfo(30)][2620]PGAPI_GetInfo: entering...fInfoType=17
[2620]PGAPI_GetInfo: p='PostgreSQL', len=0, value=0, cbMax=200
[2620][SQLDescribeCol][2620]PGAPI_DescribeCol: entering.1..
[2620]SC_pre_execute: status = 3
[2620]**** PGAPI_DescribeCol: res = 159916592, stmt->status = 3, !finished=0, !premature=1
[2620]describeCol: col 0 fieldname = 'func_test_insert'
[2620]describeCol: col 0 fieldtype = 20
[2620]describeCol: col 0 column_size = 19
[2620]describeCol: col 0 *pfSqlType = 2
[2620]describeCol: col 0  *pcbColDef = 19
[2620]describeCol: col 0  *pibScale = 0
[2620]describeCol: col 0  *pfNullable = 1
[2620][[SQLFetch]][2620]PGAPI_ExtendedFetch: stmt=159941280
[2620]SQL_FETCH_NEXT: num_tuples=1, currtuple=-1
[2620]PGAPI_ExtendedFetch: new currTuple = -1
[2620]manual_result = 0, use_declarefetch = 0
[2620]**** SC_fetch: manual_result
[2620]fetch: cols=1, lf=0, opts = 159941368, opts->bindings = 159916392, buffer[] = 0
[2620][SQLGetData][2620]PGAPI_GetData: enter, stmt=159941280
[2620]     num_rows = 1
[2620]     value = '12395'
[2620]**** PGAPI_GetData: icol = 0, fCType = 1, field_type = 20, value = '12395'
[2620]copy_and_convert: field_type = 20, fctype = 1, value = '12395', cbValueMax=201
[2620]DEFAULT: len = 5, ptr = '12395'
[2620]    SQL_C_CHAR, default: len = 5, cbValueMax = 201, rgbValueBindRow = '12395'
[2620][[SQLFetch]][2620]PGAPI_ExtendedFetch: stmt=159941280
[2620]SQL_FETCH_NEXT: num_tuples=1, currtuple=0
[2620][[SQLFreeHandle]][2620]PGAPI_FreeStmt: entering...hstmt=159941280, fOption=1
[2620]QResult: in DESTRUCTOR
[2620]QResult: free memory in, fcount=1
[2620]row = 0, num_fields = 1
[2620]free [lf=0] 159916824
[2620]QResult: free memory out
[2620]QResult: exit DESTRUCTOR
[2620]SC_Destructor: self=159941280, self->result=0, self->hdbc=159922072
[2620]reset_a_column_binding: entering ... self=159941368, bindings_allocated=1, icol=1
[2620]APD_free_params:  ENTER, self=159941424
[2620]IPD_free_params:  ENTER, self=159941452
[2620]IPD_free_params:  EXIT
[2620]SC_Destructor: EXIT
[2620][SQLDisconnect][2620]PGAPI_Disconnect: entering...
[2620]PGAPI_Disconnect: about to CC_cleanup
[2620]in CC_Cleanup, self=159922072
[2620]after CC_abort
[2620]SOCK_Destructor
[2620]after SOCK destructor
[2620]exit CC_Cleanup
[2620]PGAPI_Disconnect: done CC_cleanup
[2620]PGAPI_Disconnect: returning...
[2620][[SQLFreeHandle]][2620]PGAPI_FreeConnect: entering...
[2620]**** in PGAPI_FreeConnect: hdbc=159922072
[2620]enter CC_Destructor, self=159922072
[2620]in CC_Cleanup, self=159922072
[2620]after SOCK destructor
[2620]exit CC_Cleanup
[2620]after CC_Cleanup
[2620]after free statement holders
[2620]exit CC_Destructor
[2620]PGAPI_FreeConnect: returning...
[2620][[SQLFreeHandle]][2620]**** in PGAPI_FreeEnv: env = 159915920 **
[2620]in EN_Destructor, self=159915920
[2620]exit EN_Destructor: rv = 1
[2620]   ok




--------------------------------------------

 Second Log:

 Query:
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 select * from public."func_test_insert"();


 -> No return value, since the query is not
    recognized as a select statement...

--------------------------------------------


[2620]**** PGAPI_ExecDirect: hstmt=144736928, statement='SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;select * from
public."func_test_insert"();'
[2620]PGAPI_ExecDirect: calling PGAPI_Execute...
[2620]PGAPI_Execute: entering...
[2620]PGAPI_Execute: clear errors...
[2620]recycle statement: self= 144736928
[2620]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=93, stmt='SET TRANSACTION ISOLATION
LEVELSERIALIZABLE;select * from public."func_test_insert"();' 
[2620]   stmt_with_params = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select * from public."func_test_insert"();

'
[2620]      it's NOT a select statement: stmt=144736928
[2620]send_query(): conn=144717720, query='SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select * from public."func_test_insert"();

'
[2620]send_query: done sending query
[2620]in QR_Constructor
[2620]exit QR_Constructor
[2620]read 62, global_socket_buffersize=4096
[2620]send_query: got id = 'C'
[2620]send_query: ok - 'C' - SET
[2620]send_query: setting cmdbuffer = 'SET'
[2620]send_query: returning res = 144712240
[2620]send_query: got id = 'P'
[2620]send_query: got id = 'T'
[2620]in QR_Constructor
[2620]exit QR_Constructor
[2620]send_query: 'T' no result_in: res = 144742232
[2620]QR_fetch_tuples: cursor = '', self->cursor=0
[2620]num_fields = 1
[2620]READING ATTTYPMOD
[2620]CI_read_fields: fieldname='func_test_insert', adtid=20, adtsize=8, atttypmod=-1
[2620]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[2620]MALLOC: tuple_size = 100, size = 800
[2620]next_tuple: inTuples = true, falling through: fcount = 101, fetch_count = 101
[2620]qresult: len=5, buffer='12393'
[2620]end of tuple list -- setting inUse to false: this = 144742232
[2620]_next_tuple: 'C' fetch_total = 1 & this_fetch = 1
[2620]send_query: got id = 'Z'
[2620]extend_column_bindings: entering ... self=144737016, bindings_allocated=0, num_columns=1
[2620]exit extend_column_bindings
[2620]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[2620][SQLNumResultCols][2620]PGAPI_NumResultCols: entering...
[2620]SC_pre_execute: status = 3
[2620]PGAPI_NumResultCols: result = 144712240, status = 3, numcols = 0
[2620][SQLMoreResults][2620]PGAPI_MoreResults: entering...
[2620][SQLMoreResults][2620]PGAPI_MoreResults: entering...
[2620][[SQLFreeHandle]][2620]PGAPI_FreeStmt: entering...hstmt=144736928, fOption=1
[2620]QResult: in DESTRUCTOR
[2620]QResult: free memory in, fcount=0
[2620]QResult: free memory out
[2620]QResult: in DESTRUCTOR
[2620]QResult: free memory in, fcount=1
[2620]row = 0, num_fields = 1
[2620]free [lf=0] 144712496
[2620]QResult: free memory out
[2620]QResult: exit DESTRUCTOR
[2620]QResult: exit DESTRUCTOR
[2620]SC_Destructor: self=144736928, self->result=0, self->hdbc=144717720
[2620]reset_a_column_binding: entering ... self=144737016, bindings_allocated=1, icol=1
[2620]APD_free_params:  ENTER, self=144737072
[2620]IPD_free_params:  ENTER, self=144737100
[2620]IPD_free_params:  EXIT
[2620]SC_Destructor: EXIT
[2620][SQLDisconnect][2620]PGAPI_Disconnect: entering...
[2620]PGAPI_Disconnect: about to CC_cleanup
[2620]in CC_Cleanup, self=144717720
[2620]after CC_abort
[2620]SOCK_Destructor
[2620]after SOCK destructor
[2620]exit CC_Cleanup
[2620]PGAPI_Disconnect: done CC_cleanup
[2620]PGAPI_Disconnect: returning...
[2620][[SQLFreeHandle]][2620]PGAPI_FreeConnect: entering...
[2620]**** in PGAPI_FreeConnect: hdbc=144717720
[2620]enter CC_Destructor, self=144717720
[2620]in CC_Cleanup, self=144717720
[2620]after SOCK destructor
[2620]exit CC_Cleanup
[2620]after CC_Cleanup
[2620]after free statement holders
[2620]exit CC_Destructor
[2620]PGAPI_FreeConnect: returning...
[2620][[SQLFreeHandle]][2620]**** in PGAPI_FreeEnv: env = 144711568 **
[2620]in EN_Destructor, self=144711568
[2620]exit EN_Destructor: rv = 1
[2620]   ok



-------------------------
Philippe Lang
Attik System
http://www.attiksystem.ch

> -----Original Message-----
> From: Philippe Lang
>
> Here we are...
>
> I understand more or less that the second query is not
> recognized as a select statement... That's apparently the
> reason why I cannot get the value back in Access.
>
> Are "multiple result sets" supposed to work with the ODBC driver?

Yes.
Psqlodbc driver returns 2 results for the query(SET ..;SELECT ..).
An SQLMoreResults call is needed to see the second result.

regards,
Hiroshi Inoue

OK, I understand now. So this is really an Access issue.

As Jeff says:

>... Which is outside of the scope of what we normally do
>in Access...  I suppose Philippe can reference
>psqlodbc.dll and issue the call in his VB code, but
>that is kind of nasty.  Being able to transparently
>use SQL is the main attraction of a driver, at least
>for most of us.

I use Access for its great RAD features, so I'll try to solve my "isolation level" problems differently, for example by
usinga "lock table" inside the function. I wish there was a way to manage multiple result sets in Access more easily... 

Thanks for your help, and of course, thanks for the ODBC driver!

Philippe


-----Message d'origine-----
De : Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Envoyé : vendredi, 7. novembre 2003 22:57
À : Philippe Lang
Cc : 'Jeff Eckermann'; pgsql-odbc@postgresql.org
Objet : RE: [ODBC] Access, pass-through queries and isolation level


> -----Original Message-----
> From: Philippe Lang
>
> Here we are...
>
> I understand more or less that the second query is not
> recognized as a select statement... That's apparently the
> reason why I cannot get the value back in Access.
>
> Are "multiple result sets" supposed to work with the ODBC driver?

Yes.
Psqlodbc driver returns 2 results for the query(SET ..;SELECT ..).
An SQLMoreResults call is needed to see the second result.

regards,
Hiroshi Inoue