public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] Automatic fetching of refcursors 3+ messages / 2 participants [nested] [flat]
* [PATCH] Automatic fetching of refcursors @ 2021-05-17 02:20 Adrian Grucza <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Grucza @ 2021-05-17 02:20 UTC (permalink / raw) To: [email protected] Hi all, I've created a patch that enables automatic fetching of refcursors from a function/procedure. Functions/procedures can return one or more refcursors, and the client application can then consume them the way multiple result sets are normally consumed via ODBC. Below is a summary of the attached patches: 0001 (`regress.ps1` changes unrelated to refcursors) * Allow installation and testing of a debug build of the driver (useful for debugging) * Fixed a bug in `regress.ps1` where accepting the default password of `postgres` did not work 0002 * Added a FetchRefcursors setting * When FetchRefcursors is enabled, executes some refcursor code in `statement.c` that already existed but was not compiled in due to REFCUR_SUPPORT not being #defined 0003 * Enhanced refcursor code to support multiple refcursors * Fixed an issue where output parameter bindings were not processed when using refcursors * Added a `fetch-refcursors` regression test Notes: * Client application must execute the statement in a transaction to avoid cursors being closed prematurely (i.e. turn auto-commit off) * Works with both ODBC CALL and PostgreSQL CALL syntaxes * A search through the mailing list archive shows that refcursor support is a feature that multiple people have wanted Regards, Adrian Grucza Technical Lead Tel: +61390185800 [email protected] www.iress.com Level 16, 385 Bourke Street, Melbourne, Victoria, 3000 The contents of this email originated from Iress. For this purpose Iress includes Iress Limited and/or any of its subsidiaries, holding companies and trading entities. If you have received this email in error please notify the sender immediately and delete this email. nosig Attachments: [application/octet-stream] 0001-Add-DriverConfiguration-param-fix-default-passwd.patch (2.0K, 3-0001-Add-DriverConfiguration-param-fix-default-passwd.patch) download | inline diff: From bc4d0256c3d24d58460043fbdadb383e20475580 Mon Sep 17 00:00:00 2001 From: Adrian Grucza <[email protected]> Date: Wed, 12 May 2021 16:50:42 +1000 Subject: [PATCH 1/3] Add DriverConfiguration param; fix default passwd --- winbuild/regress.ps1 | 11 +++++++---- 1 file changed, 7 insertions(+), 4 deletions(-) diff --git a/winbuild/regress.ps1 b/winbuild/regress.ps1 index b5e1f85..f464a4a 100644 --- a/winbuild/regress.ps1 +++ b/winbuild/regress.ps1 @@ -27,7 +27,9 @@ automatically unless this option is specified. Currently "4.0", "12.0" or "14.0" is available. .PARAMETER Configuration - Specify "Release"(default) or "Debug". + Specify the configuration used to build the regression tests. "Release"(default) or "Debug". +.PARAMETER DriverConfiguration + Specify the configuration of the driver to test. "Release"(default) or "Debug". .PARAMETER BuildConfigPath Specify the configuration xml file name if you want to use the configuration file other than standard one. @@ -74,6 +76,7 @@ Param( [string]$MSToolsVersion, [ValidateSet("Debug", "Release")] [String]$Configuration="Release", +[String]$DriverConfiguration="Release", [string]$BuildConfigPath, [ValidateSet("off", "on", "both")] [string]$DeclareFetch="on", @@ -263,7 +266,7 @@ function SpecialDsn($testdsn, $testdriver) $uid = $in } $in = read-host -assecurestring "Password [$passwd]" - if ("$in" -ne "") { + if ($in.Length -ne 0) { $ptr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($in) $passwd = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ptr) } @@ -394,12 +397,12 @@ foreach ($pl in $pary) { "Win32" { $targetdir="test_x86" $bit="32-bit" - $dlldir="$objbase\x86_${ansi_dir_part}_Release" + $dlldir="$objbase\x86_${ansi_dir_part}_$DriverConfiguration" } default { $targetdir="test_x64" $bit="64-bit" - $dlldir="$objbase\x64_${ansi_dir_part}_Release" + $dlldir="$objbase\x64_${ansi_dir_part}_$DriverConfiguration" } } pushd $pushdir\$targetdir -- 2.31.1.windows.1 [application/octet-stream] 0002-Remove-REFCUR_SUPPORT-add-FetchRefcursors-setting.patch (9.9K, 4-0002-Remove-REFCUR_SUPPORT-add-FetchRefcursors-setting.patch) download | inline diff: From 173311a0a6ba59955bc0a24cff6970b3c0007959 Mon Sep 17 00:00:00 2001 From: Adrian Grucza <[email protected]> Date: Wed, 12 May 2021 23:16:35 +1000 Subject: [PATCH 2/3] Remove REFCUR_SUPPORT, add FetchRefcursors setting --- dlg_specific.c | 18 ++++++++++++++++++ dlg_specific.h | 4 ++++ dlg_wingui.c | 2 ++ psqlodbc.h | 1 + psqlodbc.rc | 4 ++++ resource.h | 3 ++- statement.c | 27 ++++++++++++--------------- 7 files changed, 43 insertions(+), 16 deletions(-) diff --git a/dlg_specific.c b/dlg_specific.c index 3f2192c..45662a6 100644 --- a/dlg_specific.c +++ b/dlg_specific.c @@ -369,6 +369,7 @@ MYLOG(DETAIL_LOG_LEVEL, "hlen=" FORMAT_SSIZE_T "\n", hlen); "%s" /* INIKEEPALIVE TIME/INTERVAL */ ABBR_NUMERIC_AS "=%d;" INI_OPTIONAL_ERRORS "=%d;" + INI_FETCHREFCURSORS "=%d;" #ifdef _HANDLE_ENLIST_IN_DTC_ INI_XAOPT "=%d" /* XAOPT */ #endif /* _HANDLE_ENLIST_IN_DTC_ */ @@ -403,6 +404,7 @@ MYLOG(DETAIL_LOG_LEVEL, "hlen=" FORMAT_SSIZE_T "\n", hlen); ,makeKeepaliveConnectString(keepaliveStr, sizeof(keepaliveStr), ci, FALSE) ,ci->numeric_as ,ci->optional_errors + ,ci->fetch_refcursors #ifdef _HANDLE_ENLIST_IN_DTC_ ,ci->xa_opt #endif /* _HANDLE_ENLIST_IN_DTC_ */ @@ -461,6 +463,8 @@ MYLOG(DETAIL_LOG_LEVEL, "hlen=" FORMAT_SSIZE_T "\n", hlen); flag |= BIT_LOWERCASEIDENTIFIER; if (ci->optional_errors) flag |= BIT_OPTIONALERRORS; + if (ci->fetch_refcursors) + flag |= BIT_FETCHREFCURSORS; if (ci->sslmode[0]) { @@ -583,6 +587,7 @@ unfoldCXAttribute(ConnInfo *ci, const char *value) ci->use_server_side_prepare = (char)((flag & BIT_USESERVERSIDEPREPARE) != 0); ci->lower_case_identifier = (char)((flag & BIT_LOWERCASEIDENTIFIER) != 0); ci->optional_errors = (char)((flag & BIT_OPTIONALERRORS) != 0); + ci->fetch_refcursors = (char)((flag & BIT_FETCHREFCURSORS) != 0); } BOOL @@ -793,6 +798,8 @@ copyConnAttributes(ConnInfo *ci, const char *attribute, const char *value) ci->drivers.bools_as_char = atoi(value); else if (stricmp(attribute, INI_EXTRASYSTABLEPREFIXES) == 0 || stricmp(attribute, ABBR_EXTRASYSTABLEPREFIXES) == 0) STRCPY_FIXED(ci->drivers.extra_systable_prefixes, value); + else if (stricmp(attribute, INI_FETCHREFCURSORS) == 0 || stricmp(attribute, ABBR_FETCHREFCURSORS) == 0) + ci->fetch_refcursors = atoi(value); else found = FALSE; @@ -842,6 +849,7 @@ getCiDefaults(ConnInfo *ci) ci->wcs_debug = 1; } ci->disable_convert_func = 0; + ci->fetch_refcursors = DEFAULT_FETCHREFCURSORS; #ifdef _HANDLE_ENLIST_IN_DTC_ ci->xa_opt = DEFAULT_XAOPT; #endif /* _HANDLE_ENLIST_IN_DTC_ */ @@ -1082,6 +1090,9 @@ MYLOG(0, "drivername=%s\n", drivername); if (SQLGetPrivateProfileString(DSN, INI_SSLMODE, NULL_STRING, temp, sizeof(temp), ODBC_INI) > 0) STRCPY_FIXED(ci->sslmode, temp); + if (SQLGetPrivateProfileString(DSN, INI_FETCHREFCURSORS, NULL_STRING, temp, sizeof(temp), ODBC_INI) > 0) + ci->fetch_refcursors = atoi(temp); + #ifdef _HANDLE_ENLIST_IN_DTC_ if (SQLGetPrivateProfileString(DSN, INI_XAOPT, NULL_STRING, temp, sizeof(temp), ODBC_INI) > 0) ci->xa_opt = atoi(temp); @@ -1374,6 +1385,11 @@ writeDSNinfo(const ConnInfo *ci) INI_IGNORETIMEOUT, temp, ODBC_INI); + ITOA_FIXED(temp, ci->fetch_refcursors); + SQLWritePrivateProfileString(DSN, + INI_FETCHREFCURSORS, + temp, + ODBC_INI); #ifdef _HANDLE_ENLIST_IN_DTC_ ITOA_FIXED(temp, ci->xa_opt); SQLWritePrivateProfileString(DSN, INI_XAOPT, temp, ODBC_INI); @@ -1789,6 +1805,7 @@ CC_conninfo_init(ConnInfo *conninfo, UInt4 option) conninfo->batch_size = DEFAULT_BATCH_SIZE; conninfo->ignore_timeout = DEFAULT_IGNORETIMEOUT; conninfo->wcs_debug = -1; + conninfo->fetch_refcursors = -1; #ifdef _HANDLE_ENLIST_IN_DTC_ conninfo->xa_opt = -1; #endif /* _HANDLE_ENLIST_IN_DTC_ */ @@ -1890,6 +1907,7 @@ CC_copy_conninfo(ConnInfo *ci, const ConnInfo *sci) CORR_VALCPY(keepalive_interval); CORR_VALCPY(batch_size); CORR_VALCPY(ignore_timeout); + CORR_VALCPY(fetch_refcursors); #ifdef _HANDLE_ENLIST_IN_DTC_ CORR_VALCPY(xa_opt); #endif diff --git a/dlg_specific.h b/dlg_specific.h index c585750..87d32c7 100644 --- a/dlg_specific.h +++ b/dlg_specific.h @@ -173,6 +173,8 @@ extern "C" { #define INI_IGNORETIMEOUT "IgnoreTimeout" #define ABBR_IGNORETIMEOUT "D9" #define INI_DTCLOG "Dtclog" +#define INI_FETCHREFCURSORS "FetchRefcursors" +#define ABBR_FETCHREFCURSORS "DA" /* "PreferLibpq", abbreviated "D4", used to mean whether to prefer libpq. * libpq is now required #define INI_PREFERLIBPQ "PreferLibpq" @@ -220,6 +222,7 @@ extern "C" { #define BIT_USESERVERSIDEPREPARE (1L<<25) #define BIT_LOWERCASEIDENTIFIER (1L<<26) #define BIT_OPTIONALERRORS (1L<<27) +#define BIT_FETCHREFCURSORS (1L<<28) #define EFFECTIVE_BIT_COUNT 28 @@ -274,6 +277,7 @@ extern "C" { #define DEFAULT_OPTIONAL_ERRORS 0 #define DEFAULT_BATCH_SIZE 100 #define DEFAULT_IGNORETIMEOUT 0 +#define DEFAULT_FETCHREFCURSORS 0 #ifdef _HANDLE_ENLIST_IN_DTC_ #define DEFAULT_XAOPT 1 diff --git a/dlg_wingui.c b/dlg_wingui.c index 276d8f1..c348ccc 100644 --- a/dlg_wingui.c +++ b/dlg_wingui.c @@ -571,6 +571,7 @@ ds_options_update(HWND hdlg, ConnInfo *ci) ci->allow_keyset = IsDlgButtonChecked(hdlg, DS_UPDATABLECURSORS); ci->use_server_side_prepare = IsDlgButtonChecked(hdlg, DS_SERVERSIDEPREPARE); ci->bytea_as_longvarbinary = IsDlgButtonChecked(hdlg, DS_BYTEAASLONGVARBINARY); + ci->fetch_refcursors = IsDlgButtonChecked(hdlg, DS_FETCH_REFCURSORS); /*ci->lower_case_identifier = IsDlgButtonChecked(hdlg, DS_LOWERCASEIDENTIFIER);*/ /* OID Options */ @@ -721,6 +722,7 @@ ds_options2Proc(HWND hdlg, CheckDlgButton(hdlg, DS_UPDATABLECURSORS, ci->allow_keyset); CheckDlgButton(hdlg, DS_SERVERSIDEPREPARE, ci->use_server_side_prepare); CheckDlgButton(hdlg, DS_BYTEAASLONGVARBINARY, ci->bytea_as_longvarbinary); + CheckDlgButton(hdlg, DS_FETCH_REFCURSORS, ci->fetch_refcursors); /*CheckDlgButton(hdlg, DS_LOWERCASEIDENTIFIER, ci->lower_case_identifier);*/ EnableWindow(GetDlgItem(hdlg, DS_FAKEOIDINDEX), atoi(ci->show_oid_column)); diff --git a/psqlodbc.h b/psqlodbc.h index f41ff9b..ca8bab5 100644 --- a/psqlodbc.h +++ b/psqlodbc.h @@ -645,6 +645,7 @@ typedef struct signed char numeric_as; signed char optional_errors; signed char ignore_timeout; + signed char fetch_refcursors; UInt4 extra_opts; Int4 keepalive_idle; Int4 keepalive_interval; diff --git a/psqlodbc.rc b/psqlodbc.rc index 2c76ed6..75269c9 100644 --- a/psqlodbc.rc +++ b/psqlodbc.rc @@ -221,6 +221,8 @@ BEGIN PUSHBUTTON "�K�p",IDAPPLY,128,ENDLINE_Y,50,14 CONTROL "bytea��LO�Ƃ��Ĉ���",DS_BYTEAASLONGVARBINARY,"Button", BS_AUTOCHECKBOX | WS_TABSTOP,15,85,87,10 + CONTROL "�erefcursor���猋�ʂ��擾���܂�",DS_FETCH_REFCURSORS, + "Button",BS_AUTOCHECKBOX | WS_TABSTOP,139,85,140,10 END #define DTC_GRP_X 10 @@ -638,6 +640,8 @@ BEGIN GROUPBOX "Int8 As",IDC_STATIC,5,97,256,25 CONTROL "default",DS_INT8_AS_DEFAULT,"Button",BS_AUTORADIOBUTTON | WS_GROUP,12,107,40,10 + CONTROL "Fetch result from each refcursor",DS_FETCH_REFCURSORS, + "Button",BS_AUTOCHECKBOX | WS_TABSTOP,163,84,122,10 CONTROL "bigint",DS_INT8_AS_BIGINT,"Button",BS_AUTORADIOBUTTON | WS_TABSTOP,55,107,35,10 CONTROL "numeric",DS_INT8_AS_NUMERIC,"Button",BS_AUTORADIOBUTTON | diff --git a/resource.h b/resource.h index fac1fbb..483e623 100644 --- a/resource.h +++ b/resource.h @@ -119,6 +119,7 @@ #define DS_NUMERIC_AS_LONGVARCHAR 1111 #define DS_BATCH_SIZE 1112 #define DS_IGNORETIMEOUT 1113 +#define DS_FETCH_REFCURSORS 1114 // Next default values for new objects // @@ -126,7 +127,7 @@ #ifndef APSTUDIO_READONLY_SYMBOLS #define _APS_NEXT_RESOURCE_VALUE 106 #define _APS_NEXT_COMMAND_VALUE 40001 -#define _APS_NEXT_CONTROL_VALUE 1114 +#define _APS_NEXT_CONTROL_VALUE 1115 #define _APS_NEXT_SYMED_VALUE 101 #endif #endif diff --git a/statement.c b/statement.c index 972aa50..9f47b40 100644 --- a/statement.c +++ b/statement.c @@ -2243,33 +2243,30 @@ MYLOG(DETAIL_LOG_LEVEL, "!!%p->miscinfo=%x res=%p\n", self, self->miscinfo, firs { Int2 io, out; has_out_para = (CountParameters(self, NULL, &io, &out) > 0); -/* - * I'm not sure if the following REFCUR_SUPPORT stuff is valuable - * or not. - */ -#ifdef REFCUR_SUPPORT +if (ci->fetch_refcursors) +{ -MYLOG(DETAIL_LOG_LEVEL, "!!! numfield=%d field_type=%u\n", QR_NumResultCols(res), QR_get_field_type(res, 0)); +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(res) && - PG_TYPE_REFCURSOR == QR_get_field_type(res, 0)) + 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(res, 0, 0)); - QR_Destructor(res); + 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.cache_size = qi.row_size = ci->drivers.fetch_max; + 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) - SC_set_Result(self, res); + 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); } -#endif /* REFCUR_SUPPORT */ +} } if (has_out_para) { /* get the return value of the procedure call */ -- 2.31.1.windows.1 [application/octet-stream] 0003-Support-fetching-results-from-multiple-refcursors.patch (11.2K, 5-0003-Support-fetching-results-from-multiple-refcursors.patch) download | inline diff: From ba8dba0f60846b6b5e129d48e2b4e66f7c6bf3b7 Mon Sep 17 00:00:00 2001 From: Adrian Grucza <[email protected]> Date: Thu, 13 May 2021 00:15:02 +1000 Subject: [PATCH 3/3] 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..b67c86f --- /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.31.1.windows.1 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCH] Automatic fetching of refcursors @ 2021-05-17 06:35 Inoue,Hiroshi <[email protected]> parent: Adrian Grucza <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Inoue,Hiroshi @ 2021-05-17 06:35 UTC (permalink / raw) To: Adrian Grucza <[email protected]>; +Cc: [email protected] Hi Adrian, I would take care of your patches. Thanks. Hiroshi Inoue 2021年5月17日(月) 11:21 Adrian Grucza <[email protected]>: > Hi all, > > I've created a patch that enables automatic fetching of refcursors from a > function/procedure. Functions/procedures can return one or more refcursors, > and the client application can then consume them the way multiple result > sets are normally consumed via ODBC. > > Below is a summary of the attached patches: > > 0001 (`regress.ps1` changes unrelated to refcursors) > * Allow installation and testing of a debug build of the driver (useful > for debugging) > * Fixed a bug in `regress.ps1` where accepting the default password of > `postgres` did not work > > 0002 > * Added a FetchRefcursors setting > * When FetchRefcursors is enabled, executes some refcursor code in > `statement.c` that already existed but was not compiled in due to > REFCUR_SUPPORT not being #defined > > 0003 > * Enhanced refcursor code to support multiple refcursors > * Fixed an issue where output parameter bindings were not processed when > using refcursors > * Added a `fetch-refcursors` regression test > > Notes: > * Client application must execute the statement in a transaction to > avoid cursors being closed prematurely (i.e. turn auto-commit off) > * Works with both ODBC CALL and PostgreSQL CALL syntaxes > * A search through the mailing list archive shows that refcursor support > is a feature that multiple people have wanted > > Regards, > [image: iress.com] <https://www.iress.com/; > Adrian Grucza > Technical Lead > Tel: +61390185800 > *[email protected]* <[email protected]> > *www.iress.com* <https://www.iress.com/; > Level 16, 385 Bourke Street, Melbourne, Victoria, 3000 > The contents of this email originated from Iress. For this purpose Iress > includes Iress Limited and/or any of its subsidiaries, holding companies > and trading entities. If you have received this email in error please > notify the sender immediately and delete this email. > nosig > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCH] Automatic fetching of refcursors @ 2021-05-19 12:54 Inoue,Hiroshi <[email protected]> parent: Inoue,Hiroshi <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Inoue,Hiroshi @ 2021-05-19 12:54 UTC (permalink / raw) To: Adrian Grucza <[email protected]>; +Cc: [email protected] Hi Adrian, As for the patch 0001, is DriverConfiguration necessary? For example, how about the following patch? diff --git a/winbuild/regress.ps1 b/winbuild/regress.ps1 index b5e1f85..f83a411 100644 --- a/winbuild/regress.ps1 +++ b/winbuild/regress.ps1 @@ -27,7 +27,7 @@ automatically unless this option is specified. Currently "4.0", "12.0" or "14.0" is available. .PARAMETER Configuration - Specify "Release"(default) or "Debug". + Specify the configuration used for the regression tests. "Release"(default) or "Debug". .PARAMETER BuildConfigPath Specify the configuration xml file name if you want to use the configuration file other than standard one. @@ -263,7 +263,7 @@ function SpecialDsn($testdsn, $testdriver) $uid = $in } $in = read-host -assecurestring "Password [$passwd]" - if ("$in" -ne "") { + if ($in.Length -ne 0) { $ptr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($in) $passwd = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ptr) } @@ -394,12 +394,12 @@ foreach ($pl in $pary) { "Win32" { $targetdir="test_x86" $bit="32-bit" - $dlldir="$objbase\x86_${ansi_dir_part}_Release" + $dlldir="$objbase\x86_${ansi_dir_part}_$Configuration" } default { $targetdir="test_x64" $bit="64-bit" - $dlldir="$objbase\x64_${ansi_dir_part}_Release" + $dlldir="$objbase\x64_${ansi_dir_part}_$Configuration" } } pushd $pushdir\$targetdir regards, Hiroshi Inoue 2021年5月17日(月) 15:35 Inoue,Hiroshi <[email protected]>: > Hi Adrian, > > I would take care of your patches. > > Thanks. > Hiroshi Inoue > > 2021年5月17日(月) 11:21 Adrian Grucza <[email protected]>: > >> Hi all, >> >> I've created a patch that enables automatic fetching of refcursors from a >> function/procedure. Functions/procedures can return one or more refcursors, >> and the client application can then consume them the way multiple result >> sets are normally consumed via ODBC. >> >> Below is a summary of the attached patches: >> >> 0001 (`regress.ps1` changes unrelated to refcursors) >> * Allow installation and testing of a debug build of the driver (useful >> for debugging) >> * Fixed a bug in `regress.ps1` where accepting the default password of >> `postgres` did not work >> >> 0002 >> * Added a FetchRefcursors setting >> * When FetchRefcursors is enabled, executes some refcursor code in >> `statement.c` that already existed but was not compiled in due to >> REFCUR_SUPPORT not being #defined >> >> 0003 >> * Enhanced refcursor code to support multiple refcursors >> * Fixed an issue where output parameter bindings were not processed >> when using refcursors >> * Added a `fetch-refcursors` regression test >> >> Notes: >> * Client application must execute the statement in a transaction to >> avoid cursors being closed prematurely (i.e. turn auto-commit off) >> * Works with both ODBC CALL and PostgreSQL CALL syntaxes >> * A search through the mailing list archive shows that refcursor >> support is a feature that multiple people have wanted >> >> Regards, >> [image: iress.com] <https://www.iress.com/; >> Adrian Grucza >> Technical Lead >> Tel: +61390185800 >> *[email protected]* <[email protected]> >> *www.iress.com* <https://www.iress.com/; >> Level 16, 385 Bourke Street, Melbourne, Victoria, 3000 >> The contents of this email originated from Iress. For this purpose Iress >> includes Iress Limited and/or any of its subsidiaries, holding companies >> and trading entities. If you have received this email in error please >> notify the sender immediately and delete this email. >> nosig >> > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-05-19 12:54 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-05-17 02:20 [PATCH] Automatic fetching of refcursors Adrian Grucza <[email protected]> 2021-05-17 06:35 ` Inoue,Hiroshi <[email protected]> 2021-05-19 12:54 ` Inoue,Hiroshi <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox