Re: Access, pass-through queries and isolation level
От | Philippe Lang |
---|---|
Тема | Re: Access, pass-through queries and isolation level |
Дата | |
Msg-id | 6C0CF58A187DA5479245E0830AF84F420AF74D@poweredge.attiksystem.ch обсуждение исходный текст |
Ответ на | Access, pass-through queries and isolation level ("Philippe Lang" <philippe.lang@attiksystem.ch>) |
Ответы |
Re: Access, pass-through queries and isolation level
("Hiroshi Inoue" <inoue@tpf.co.jp>)
|
Список | pgsql-odbc |
>> 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
В списке pgsql-odbc по дате отправления:
Предыдущее
От: Steve WamplerДата:
Сообщение: Help with hang on empty query (PG 7.3.2, psqlodbc-07.03.0200)