Re: Access, pass-through queries and isolation level

>> 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 why I 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 LEVEL SERIALIZABLE;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