From 241c70bf6516bf08770fabcb1b86934c8da116c8 Mon Sep 17 00:00:00 2001 From: Adrian Grucza Date: Thu, 13 May 2021 00:15:02 +1000 Subject: [PATCH] Support fetching results from multiple refcursors * Multiple result sets are returned if multiple refcursors are found * Works with both ODBC CALL and PostgreSQL CALL syntaxes * Query must be executed in a transaction to avoid cursors being closed * Now works when output parameters are present * Added fetch-refcursors regression test --- statement.c | 150 +++++++++++++++++------------ test/expected/fetch-refcursors.out | 28 ++++++ test/src/fetch-refcursors-test.c | 110 +++++++++++++++++++++ test/tests | 3 +- 4 files changed, 231 insertions(+), 60 deletions(-) create mode 100644 test/expected/fetch-refcursors.out create mode 100644 test/src/fetch-refcursors-test.c diff --git a/statement.c b/statement.c index 9f47b40..a42f7ca 100644 --- a/statement.c +++ b/statement.c @@ -56,6 +56,9 @@ static const struct ,{ STMT_TYPE_DELETE, "DELETE" } + ,{ + STMT_TYPE_PROCCALL, "CALL" + } ,{ STMT_TYPE_PROCCALL, "{" } @@ -2235,81 +2238,110 @@ MYLOG(DETAIL_LOG_LEVEL, "!!%p->miscinfo=%x res=%p\n", self, self->miscinfo, firs if (NULL == SC_get_Curres(self)) SC_set_Curres(self, SC_get_Result(self)); - ipdopts = SC_get_IPDF(self); - has_out_para = FALSE; if (self->statement_type == STMT_TYPE_PROCCALL && (SC_get_errornumber(self) == STMT_OK || SC_get_errornumber(self) == STMT_INFO_ONLY)) { Int2 io, out; has_out_para = (CountParameters(self, NULL, &io, &out) > 0); -if (ci->fetch_refcursors) -{ - -MYLOG(DETAIL_LOG_LEVEL, "!!! numfield=%d field_type=%u\n", QR_NumResultCols(rhold.first), QR_get_field_type(rhold.first, 0)); - if (!has_out_para && - 0 < QR_NumResultCols(rhold.first) && - PG_TYPE_REFCURSOR == QR_get_field_type(rhold.first, 0)) - { - char fetch[128]; - int stmt_type = self->statement_type; - - STR_TO_NAME(self->cursor_name, QR_get_value_backend_text(rhold.first, 0, 0)); - QR_Destructor(rhold.first); - SC_init_Result(self); - SC_set_fetchcursor(self); - qi.result_in = NULL; - qi.cursor = SC_cursor_name(self); - qi.fetch_size = qi.row_size = ci->drivers.fetch_max; - SPRINTF_FIXED(fetch, "fetch " FORMAT_LEN " in \"%s\"", qi.fetch_size, SC_cursor_name(self)); - rhold.first = CC_send_query(conn, fetch, &qi, qflag | READ_ONLY_QUERY, SC_get_ancestor(self)); - if (NULL != rhold.first) - SC_set_Result(self, rhold.first); + if (has_out_para) + { /* get the return value of the procedure call */ + RETCODE ret; + HSTMT hstmt = (HSTMT) self; + + ipdopts = SC_get_IPDF(self); + self->bind_row = 0; + ret = SC_fetch(hstmt); +MYLOG(DETAIL_LOG_LEVEL, "!!SC_fetch return =%d\n", ret); + if (SQL_SUCCEEDED(ret)) + { + APDFields *apdopts = SC_get_APDF(self); + SQLULEN offset = apdopts->param_offset_ptr ? *apdopts->param_offset_ptr : 0; + ARDFields *ardopts = SC_get_ARDF(self); + const ParameterInfoClass *apara; + const ParameterImplClass *ipara; + int save_bind_size = ardopts->bind_size, gidx, num_p; + + ardopts->bind_size = apdopts->param_bind_type; + num_p = self->num_params; + if (ipdopts->allocated < num_p) + num_p = ipdopts->allocated; + for (i = 0, gidx = 0; i < num_p; i++) + { + ipara = ipdopts->parameters + i; + if (ipara->paramType == SQL_PARAM_OUTPUT || + ipara->paramType == SQL_PARAM_INPUT_OUTPUT) + { + apara = apdopts->parameters + i; + ret = PGAPI_GetData(hstmt, gidx + 1, apara->CType, apara->buffer + offset, apara->buflen, apara->used ? LENADDR_SHIFT(apara->used, offset) : NULL); + if (!SQL_SUCCEEDED(ret)) + { + SC_set_error(self, STMT_EXEC_ERROR, "GetData to Procedure return failed.", func); + break; + } + gidx++; + } + } + ardopts->bind_size = save_bind_size; /* restore */ + } + else + { + SC_set_error(self, STMT_EXEC_ERROR, "SC_fetch to get a Procedure return failed.", func); + } } -} - } - if (has_out_para) - { /* get the return value of the procedure call */ - RETCODE ret; - HSTMT hstmt = (HSTMT) self; - self->bind_row = 0; - ret = SC_fetch(hstmt); -MYLOG(DETAIL_LOG_LEVEL, "!!SC_fetch return =%d\n", ret); - if (SQL_SUCCEEDED(ret)) + if (ci->fetch_refcursors) { - APDFields *apdopts = SC_get_APDF(self); - SQLULEN offset = apdopts->param_offset_ptr ? *apdopts->param_offset_ptr : 0; - ARDFields *ardopts = SC_get_ARDF(self); - const ParameterInfoClass *apara; - const ParameterImplClass *ipara; - int save_bind_size = ardopts->bind_size, gidx, num_p; - - ardopts->bind_size = apdopts->param_bind_type; - num_p = self->num_params; - if (ipdopts->allocated < num_p) - num_p = ipdopts->allocated; - for (i = 0, gidx = 0; i < num_p; i++) + char fetch[128]; + QResultClass *last = NULL, *res; + + /* Iterate the columns in the result to look for refcursors */ + numcols = QR_NumResultCols(rhold.first); + for (i = 0; i < numcols; i++) { - ipara = ipdopts->parameters + i; - if (ipara->paramType == SQL_PARAM_OUTPUT || - ipara->paramType == SQL_PARAM_INPUT_OUTPUT) + MYLOG(DETAIL_LOG_LEVEL, "!!! numfield=%d field_type=%u\n", numcols, QR_get_field_type(rhold.first, i)); + if (PG_TYPE_REFCURSOR == QR_get_field_type(rhold.first, i)) { - apara = apdopts->parameters + i; - ret = PGAPI_GetData(hstmt, gidx + 1, apara->CType, apara->buffer + offset, apara->buflen, apara->used ? LENADDR_SHIFT(apara->used, offset) : NULL); - if (!SQL_SUCCEEDED(ret)) + if (!CC_is_in_trans(conn)) { - SC_set_error(self, STMT_EXEC_ERROR, "GetData to Procedure return failed.", func); + SC_set_error(self, STMT_EXEC_ERROR, "Query must be executed in a transaction when FetchRefcursors setting is enabled.", func); break; } - gidx++; + + STR_TO_NAME(self->cursor_name, QR_get_value_backend_text(rhold.first, 0, i)); + SC_set_fetchcursor(self); + qi.result_in = NULL; + qi.cursor = SC_cursor_name(self); + qi.fetch_size = qi.row_size = ci->drivers.fetch_max; + SPRINTF_FIXED(fetch, "fetch " FORMAT_LEN " in \"%s\"", qi.fetch_size, SC_cursor_name(self)); + res = CC_send_query(conn, fetch, &qi, qflag | READ_ONLY_QUERY, SC_get_ancestor(self)); + if (NULL != res) + { + if (NULL == last) + { + /* Reinitialise with result fetched from first refcursor */ + SC_init_Result(self); + SC_set_Result(self, res); + } + else + { + /* Add another result fetched from the next refcursor */ + QR_concat(last, res); + self->multi_statement = TRUE; + } + if (!QR_command_maybe_successful(res)) + { + SC_set_errorinfo(self, res, 0); + QR_Destructor(rhold.first); + break; + } + + last = res; + } } } - ardopts->bind_size = save_bind_size; /* restore */ - } - else - { - SC_set_error(self, STMT_EXEC_ERROR, "SC_fetch to get a Procedure return failed.", func); + if (last) + QR_Destructor(rhold.first); } } cleanup: diff --git a/test/expected/fetch-refcursors.out b/test/expected/fetch-refcursors.out new file mode 100644 index 0000000..fff9ee1 --- /dev/null +++ b/test/expected/fetch-refcursors.out @@ -0,0 +1,28 @@ +Creating procedure 'refproc' +connected +disconnecting + +-- TEST using FetchRefcursors=0 and SQL_ATTR_AUTOCOMMIT=1 +connected +Output param num_cursor is 2 +--1 Result set: +2 ref1 ref2 +disconnecting + +-- TEST using FetchRefcursors=1 and SQL_ATTR_AUTOCOMMIT=1 +connected +SQLExecute failed +HY000=Query must be executed in a transaction when FetchRefcursors setting is enabled. + +-- TEST using FetchRefcursors=1 and SQL_ATTR_AUTOCOMMIT=0 +connected +Output param num_cursor is 2 +--1 Result set: +1 foo +2 bar +3 foobar +--2 Result set: +foobar 3 +bar 2 +foo 1 +disconnecting diff --git a/test/src/fetch-refcursors-test.c b/test/src/fetch-refcursors-test.c new file mode 100644 index 0000000..dda841e --- /dev/null +++ b/test/src/fetch-refcursors-test.c @@ -0,0 +1,110 @@ +/* + * Test FetchRefcursors setting + */ + +#include "common.h" + + +static void print_all_results(HSTMT hstmt) +{ + int i; + int rc = SQL_SUCCESS; + for (i = 1; rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO; i++) + { + printf("--%d ", i); + print_result(hstmt); + + rc = SQLMoreResults(hstmt); + } + if (rc != SQL_NO_DATA) + CHECK_STMT_RESULT(rc, "SQLMoreResults failed", hstmt); +} + +static void setup_procedure() +{ + SQLRETURN rc; + HSTMT hstmt = SQL_NULL_HSTMT; + + printf("Creating procedure 'refproc'\n"); + + test_connect(); + + rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt); + CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn); + + rc = SQLExecDirect(hstmt, "create or replace procedure refproc" + "(inout num_cursor integer, inout ref1 refcursor default 'ref1', inout ref2 refcursor default 'ref2') as " + "$procedure$ \n" + "DECLARE \n" + "BEGIN \n" + "num_cursor := 2; \n" + "OPEN ref1 FOR SELECT id, t FROM testtab1 ORDER BY id ASC; \n" + "OPEN ref2 FOR SELECT t, id FROM testtab1 ORDER BY id DESC; \n" + "END; \n" + "$procedure$ \n" + "LANGUAGE plpgsql\n" + , SQL_NTS); + CHECK_STMT_RESULT(rc, "create procedure refproc failed", hstmt); + + rc = SQLFreeStmt(hstmt, SQL_CLOSE); + CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt); + + test_disconnect(); +} + +static void refcursor_test(char* connectparams, SQLPOINTER autocommit) +{ + SQLRETURN rc; + HSTMT hstmt = SQL_NULL_HSTMT; + int num_cursor = 0; + + printf("\n-- TEST using %s and SQL_ATTR_AUTOCOMMIT=%d\n", connectparams, autocommit); + + test_connect_ext(connectparams); + + /* Conditionally start a transaction */ + rc = SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, autocommit, SQL_IS_UINTEGER); + CHECK_STMT_RESULT(rc, "SQLSetConnectAttr failed", hstmt); + + rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt); + CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn); + + rc = SQLPrepare(hstmt, "CALL refproc(?)", SQL_NTS); + CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt); + + rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT_OUTPUT, + SQL_C_LONG, /* value type */ + SQL_INTEGER, /* param type */ + 0, /* column size */ + 0, /* dec digits */ + &num_cursor, /* param value ptr */ + 0, /* buffer len */ + NULL /* StrLen_or_IndPtr */); + CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt); + + rc = SQLExecute(hstmt); + if (!SQL_SUCCEEDED(rc)) + { + print_diag("SQLExecute failed", SQL_HANDLE_STMT, hstmt); + return; + } + + printf("Output param num_cursor is %d\n", num_cursor); + print_all_results(hstmt); + + rc = SQLFreeStmt(hstmt, SQL_CLOSE); + CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt); + + test_disconnect(); +} + +int main(int argc, char **argv) +{ + setup_procedure(); + + refcursor_test("FetchRefcursors=0", SQL_AUTOCOMMIT_ON); + refcursor_test("FetchRefcursors=1", SQL_AUTOCOMMIT_ON); + refcursor_test("FetchRefcursors=1", SQL_AUTOCOMMIT_OFF); + + return 0; +} diff --git a/test/tests b/test/tests index 2773bbf..bc99d32 100644 --- a/test/tests +++ b/test/tests @@ -52,4 +52,5 @@ TESTBINS = exe/connect-test \ exe/large-object-data-at-exec-test \ exe/odbc-escapes-test \ exe/wchar-char-test \ - exe/params-batch-exec-test + exe/params-batch-exec-test \ + exe/fetch-refcursors-test -- 2.39.5