From 0baeeb567109ad23ce5c6656d83979955614cdbd Mon Sep 17 00:00:00 2001 From: Adrian Grucza Date: Wed, 26 May 2021 15:14:16 +1000 Subject: [PATCH] Named parameter binding support --- convert.c | 13 ++++- qresult.c | 16 ++++++ qresult.h | 1 + statement.c | 12 ++++- test/expected/odbc-escapes.out | 40 ++++++++++++++ test/expected/odbc-escapes_1.out | 40 ++++++++++++++ test/src/odbc-escapes-test.c | 89 ++++++++++++++++++++++++-------- 7 files changed, 187 insertions(+), 24 deletions(-) diff --git a/convert.c b/convert.c index 2450cd7..d94fdb6 100644 --- a/convert.c +++ b/convert.c @@ -4512,7 +4512,7 @@ MYLOG(DETAIL_LOG_LEVEL, "para:%d(%d,%d)\n", param_number, ipdopts->allocated, ap return SQL_ERROR; } -MYLOG(DETAIL_LOG_LEVEL, "ipara=%p paramType=%d %d proc_return=%d\n", ipara, ipara ? ipara->paramType : -1, PG_VERSION_LT(conn, 8.1), qb->proc_return); +MYLOG(DETAIL_LOG_LEVEL, "ipara=%p paramName=%s paramType=%d %d proc_return=%d\n", ipara, ipara ? PRINT_NAME(ipara->paramName) : PRINT_NULL, ipara ? ipara->paramType : -1, PG_VERSION_LT(conn, 8.1), qb->proc_return); if (param_number < qb->proc_return) { if (ipara && SQL_PARAM_OUTPUT != ipara->paramType) @@ -4558,6 +4558,17 @@ MYLOG(DETAIL_LOG_LEVEL, "ipara=%p paramType=%d %d proc_return=%d\n", ipara, ipar return SQL_SUCCESS_WITH_INFO; } } + else + { + /* For procedures, use named notation if a parameter name is specified */ + if (!req_bind && ipara && NAME_IS_VALID(ipara->paramName) && + qp && qp->statement_type == STMT_TYPE_PROCCALL) + { + char named_notation[COLUMN_NAME_STORAGE_LEN + 7]; + SPRINTF_FIXED(named_notation, "\"%s\" := ", GET_NAME(ipara->paramName)); + CVT_APPEND_STR(qb, named_notation); + } + } if ((!apara || !ipara) && qb->param_mode == RPM_FAKE_PARAMS) { diff --git a/qresult.c b/qresult.c index db7901f..f2e1842 100644 --- a/qresult.c +++ b/qresult.c @@ -1466,3 +1466,19 @@ MYLOG(DETAIL_LOG_LEVEL, "tupleField=%p\n", self->tupleField); return TRUE; } + +int +QR_search_by_fieldname(QResultClass *self, const char *name) +{ + int i; + char *col_name; + + for (i = 0; i < QR_NumResultCols(self); i++) + { + col_name = QR_get_fieldname(self, i); + if (strcmp(col_name, name) == 0) + return i; + } + + return -1; +} diff --git a/qresult.h b/qresult.h index 0fb812c..a037291 100644 --- a/qresult.h +++ b/qresult.h @@ -249,6 +249,7 @@ void QR_set_cursor(QResultClass *self, const char *name); SQLLEN getNthValid(const QResultClass *self, SQLLEN sta, UWORD orientation, SQLULEN nth, SQLLEN *nearest); SQLLEN QR_move_cursor_to_last(QResultClass *self, StatementClass *stmt); BOOL QR_get_last_bookmark(const QResultClass *self, Int4 index, KeySet *keyset); +int QR_search_by_fieldname(const QResultClass *self, const char *name); #define QR_MALLOC_return_with_error(t, tp, s, a, m, r) \ do { \ diff --git a/statement.c b/statement.c index a42f7ca..e7f816f 100644 --- a/statement.c +++ b/statement.c @@ -2268,12 +2268,22 @@ MYLOG(DETAIL_LOG_LEVEL, "!!SC_fetch return =%d\n", ret); num_p = ipdopts->allocated; for (i = 0, gidx = 0; i < num_p; i++) { + int icol = gidx; ipara = ipdopts->parameters + i; if (ipara->paramType == SQL_PARAM_OUTPUT || ipara->paramType == SQL_PARAM_INPUT_OUTPUT) { + if (NAME_IS_VALID(ipara->paramName)) + { + icol = QR_search_by_fieldname(rhold.first, GET_NAME(ipara->paramName)); + if (icol < 0) + { + SC_set_error(self, STMT_EXEC_ERROR, "Named output parameter does not exist.", func); + break; + } + } 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); + ret = PGAPI_GetData(hstmt, icol + 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); diff --git a/test/expected/odbc-escapes.out b/test/expected/odbc-escapes.out index 64fd927..7e6c391 100644 --- a/test/expected/odbc-escapes.out +++ b/test/expected/odbc-escapes.out @@ -71,6 +71,26 @@ Result set: 6.8 7 2017-02-24 11:34:46 OUT params: 6.8 : 7 : 2017-02-24 11:34:46 +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (e) is an OUT parameter +Param 2 (a) is an OUT parameter +Param 3 (b): 2017-02-23 11:34:46 +Param 4 (c) is an I-O parameter +Param 5 (d): 3.4 +Result set: +6.8 7 2017-02-24 11:34:46 +OUT params: 6.8 : 7 : 2017-02-24 11:34:46 + +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (b): 2017-02-23 11:34:46 +Param 2 (c) is an I-O parameter +Param 3 (d): 3.4 +Param 4 (e) is an OUT parameter +Param 5 (a) is an OUT parameter +Result set: +6.8 7 2017-02-24 11:34:46 +OUT params: 6.8 : 7 : 2017-02-24 11:34:46 + -- TEST using SQLExecDirect Query: SELECT {fn CONCAT(?, ?) } @@ -141,4 +161,24 @@ Param 5 is an OUT parameter Result set: 6.8 7 2017-02-24 11:34:46 OUT params: 6.8 : 7 : 2017-02-24 11:34:46 + +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (e) is an OUT parameter +Param 2 (a) is an OUT parameter +Param 3 (b): 2017-02-23 11:34:46 +Param 4 (c) is an I-O parameter +Param 5 (d): 3.4 +Result set: +6.8 7 2017-02-24 11:34:46 +OUT params: 6.8 : 7 : 2017-02-24 11:34:46 + +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (b): 2017-02-23 11:34:46 +Param 2 (c) is an I-O parameter +Param 3 (d): 3.4 +Param 4 (e) is an OUT parameter +Param 5 (a) is an OUT parameter +Result set: +6.8 7 2017-02-24 11:34:46 +OUT params: 6.8 : 7 : 2017-02-24 11:34:46 disconnecting diff --git a/test/expected/odbc-escapes_1.out b/test/expected/odbc-escapes_1.out index 0452adc..fd60cc6 100644 --- a/test/expected/odbc-escapes_1.out +++ b/test/expected/odbc-escapes_1.out @@ -71,6 +71,26 @@ Result set: 6.7999999999999998 7 2017-02-24 11:34:46 OUT params: 6.7999999999999998 : 7 : 2017-02-24 11:34:46 +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (e) is an OUT parameter +Param 2 (a) is an OUT parameter +Param 3 (b): 2017-02-23 11:34:46 +Param 4 (c) is an I-O parameter +Param 5 (d): 3.4 +Result set: +6.7999999999999998 7 2017-02-24 11:34:46 +OUT params: 6.7999999999999998 : 7 : 2017-02-24 11:34:46 + +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (b): 2017-02-23 11:34:46 +Param 2 (c) is an I-O parameter +Param 3 (d): 3.4 +Param 4 (e) is an OUT parameter +Param 5 (a) is an OUT parameter +Result set: +6.7999999999999998 7 2017-02-24 11:34:46 +OUT params: 6.7999999999999998 : 7 : 2017-02-24 11:34:46 + -- TEST using SQLExecDirect Query: SELECT {fn CONCAT(?, ?) } @@ -141,4 +161,24 @@ Param 5 is an OUT parameter Result set: 6.7999999999999998 7 2017-02-24 11:34:46 OUT params: 6.7999999999999998 : 7 : 2017-02-24 11:34:46 + +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (e) is an OUT parameter +Param 2 (a) is an OUT parameter +Param 3 (b): 2017-02-23 11:34:46 +Param 4 (c) is an I-O parameter +Param 5 (d): 3.4 +Result set: +6.7999999999999998 7 2017-02-24 11:34:46 +OUT params: 6.7999999999999998 : 7 : 2017-02-24 11:34:46 + +Query: {call a_b_c_d_e(?, ?, ?, ?, ?)} +Param 1 (b): 2017-02-23 11:34:46 +Param 2 (c) is an I-O parameter +Param 3 (d): 3.4 +Param 4 (e) is an OUT parameter +Param 5 (a) is an OUT parameter +Result set: +6.7999999999999998 7 2017-02-24 11:34:46 +OUT params: 6.7999999999999998 : 7 : 2017-02-24 11:34:46 disconnecting diff --git a/test/src/odbc-escapes-test.c b/test/src/odbc-escapes-test.c index d2e2baa..24a3056 100644 --- a/test/src/odbc-escapes-test.c +++ b/test/src/odbc-escapes-test.c @@ -9,9 +9,18 @@ #include "common.h" +static void +setParamName(HSTMT hstmt, int paramno, const char *paramname) +{ + SQLHDESC hIpd = NULL; + + SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_DESC, &hIpd, 0, 0); + SQLSetDescField(hIpd, paramno, SQL_DESC_NAME, paramname, SQL_NTS); +} + /* bind string param as CHAR */ static void -bindParamString(HSTMT hstmt, int paramno, char *str) +bindParamString(HSTMT hstmt, int paramno, const char *paramname, char *str) { SQLRETURN rc; static SQLLEN cbParams[10]; @@ -26,11 +35,16 @@ bindParamString(HSTMT hstmt, int paramno, char *str) 0, /* buffer len */ &cbParams[paramno] /* StrLen_or_IndPtr */); CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt); - printf("Param %d: %s\n", paramno, str); + setParamName(hstmt, paramno, paramname); + + if (paramname) + printf("Param %d (%s): %s\n", paramno, paramname, str); + else + printf("Param %d: %s\n", paramno, str); } static void -bindOutParamString(HSTMT hstmt, int paramno, char *outbuf, int outbuflen, BOOL inout) +bindOutParamString(HSTMT hstmt, int paramno, const char *paramname, char *outbuf, int outbuflen, BOOL inout) { SQLRETURN rc; static SQLLEN cbParams[10]; @@ -45,7 +59,12 @@ bindOutParamString(HSTMT hstmt, int paramno, char *outbuf, int outbuflen, BOOL i outbuflen, /* buffer len */ &cbParams[paramno] /* StrLen_or_IndPtr */); CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt); - printf("Param %d is an %s parameter\n", paramno, inout ? "I-O": "OUT"); + setParamName(hstmt, paramno, paramname); + + if (paramname) + printf("Param %d (%s) is an %s parameter\n", paramno, paramname, inout ? "I-O": "OUT"); + else + printf("Param %d is an %s parameter\n", paramno, inout ? "I-O": "OUT"); } static BOOL execDirectMode = 0; @@ -90,21 +109,21 @@ static void escape_test(HSTMT hstmt) /* CONCAT */ prepareQuery(hstmt, "SELECT {fn CONCAT(?, ?) }"); - bindParamString(hstmt, 1, "foo"); - bindParamString(hstmt, 2, "bar"); + bindParamString(hstmt, 1, NULL, "foo"); + bindParamString(hstmt, 2, NULL, "bar"); executeQuery(hstmt); /* LOCATE */ prepareQuery(hstmt, "SELECT {fn LOCATE(?, ?, 2) }"); - bindParamString(hstmt, 1, "needle"); - bindParamString(hstmt, 2, "this is a needle in an ol' haystack"); + bindParamString(hstmt, 1, NULL, "needle"); + bindParamString(hstmt, 2, NULL, "this is a needle in an ol' haystack"); executeQuery(hstmt); /* LOCATE(SUBSTRING, SUBSTRING) */ prepareQuery(hstmt, "SELECT {fn LOCATE({fn SUBSTRING(?, 2, 4)}, {fn SUBSTRING(?, 3)}, 3) }"); /* using the same parameters */ - bindParamString(hstmt, 1, "needle"); - bindParamString(hstmt, 2, "this is a needle in an ol' haystack"); + bindParamString(hstmt, 1, NULL, "needle"); + bindParamString(hstmt, 2, NULL, "this is a needle in an ol' haystack"); executeQuery(hstmt); /* SPACE */ @@ -114,26 +133,26 @@ static void escape_test(HSTMT hstmt) /**** CALL escapes ****/ prepareQuery(hstmt, "{ call length(?) }"); - bindParamString(hstmt, 1, "foobar"); + bindParamString(hstmt, 1, NULL, "foobar"); executeQuery(hstmt); prepareQuery(hstmt, "{ call right(?, ?) }"); - bindParamString(hstmt, 1, "foobar"); - bindParamString(hstmt, 2, "3"); + bindParamString(hstmt, 1, NULL, "foobar"); + bindParamString(hstmt, 2, NULL, "3"); executeQuery(hstmt); prepareQuery(hstmt, "{ ? = call length('foo') }"); memset(outbuf1, 0, sizeof(outbuf1)); - bindOutParamString(hstmt, 1, outbuf1, sizeof(outbuf1) - 1, 0); + bindOutParamString(hstmt, 1, NULL, outbuf1, sizeof(outbuf1) - 1, 0); executeQuery(hstmt); printf("OUT param: %s\n", outbuf1); /* It's preferable to cast VARIADIC any fields */ prepareQuery(hstmt, "{ ? = call concat(?::text, ?::text) }"); memset(outbuf1, 0, sizeof(outbuf1)); - bindOutParamString(hstmt, 1, outbuf1, sizeof(outbuf1) - 1, 0); - bindParamString(hstmt, 2, "foo"); - bindParamString(hstmt, 3, "bar"); + bindOutParamString(hstmt, 1, NULL, outbuf1, sizeof(outbuf1) - 1, 0); + bindParamString(hstmt, 2, NULL, "foo"); + bindParamString(hstmt, 3, NULL, "bar"); if (variadic_test_success) executeQuery(hstmt); else @@ -154,13 +173,39 @@ static void escape_test(HSTMT hstmt) /**** call procedure with out and i-o parameters ****/ prepareQuery(hstmt, "{call a_b_c_d_e(?, ?, ?, ?, ?)}"); memset(outbuf1, 0, sizeof(outbuf1)); - bindOutParamString(hstmt, 1, outbuf1, sizeof(outbuf1) - 1, 0); - bindParamString(hstmt, 2, "2017-02-23 11:34:46"); + bindOutParamString(hstmt, 1, NULL, outbuf1, sizeof(outbuf1) - 1, 0); + bindParamString(hstmt, 2, NULL, "2017-02-23 11:34:46"); strcpy(outbuf3, "4"); - bindOutParamString(hstmt, 3, outbuf3, sizeof(outbuf3) - 1, 1); - bindParamString(hstmt, 4, "3.4"); + bindOutParamString(hstmt, 3, NULL, outbuf3, sizeof(outbuf3) - 1, 1); + bindParamString(hstmt, 4, NULL, "3.4"); memset(outbuf5, 0, sizeof(outbuf5)); - bindOutParamString(hstmt, 5, outbuf5, sizeof(outbuf5) - 1, 0); + bindOutParamString(hstmt, 5, NULL, outbuf5, sizeof(outbuf5) - 1, 0); + executeQuery(hstmt); + printf("OUT params: %s : %s : %s\n", outbuf1, outbuf3, outbuf5); + + /**** call procedure parameters by name (e,a,b,c,d) ****/ + prepareQuery(hstmt, "{call a_b_c_d_e(?, ?, ?, ?, ?)}"); + memset(outbuf5, 0, sizeof(outbuf5)); + bindOutParamString(hstmt, 1, "e", outbuf5, sizeof(outbuf5) - 1, 0); + memset(outbuf1, 0, sizeof(outbuf1)); + bindOutParamString(hstmt, 2, "a", outbuf1, sizeof(outbuf1) - 1, 0); + bindParamString(hstmt, 3, "b", "2017-02-23 11:34:46"); + strcpy(outbuf3, "4"); + bindOutParamString(hstmt, 4, "c", outbuf3, sizeof(outbuf3) - 1, 1); + bindParamString(hstmt, 5, "d", "3.4"); + executeQuery(hstmt); + printf("OUT params: %s : %s : %s\n", outbuf1, outbuf3, outbuf5); + + /**** call procedure parameters by name (b,c,d,e,a) ****/ + prepareQuery(hstmt, "{call a_b_c_d_e(?, ?, ?, ?, ?)}"); + bindParamString(hstmt, 1, "b", "2017-02-23 11:34:46"); + strcpy(outbuf3, "4"); + bindOutParamString(hstmt, 2, "c", outbuf3, sizeof(outbuf3) - 1, 1); + bindParamString(hstmt, 3, "d", "3.4"); + memset(outbuf5, 0, sizeof(outbuf5)); + bindOutParamString(hstmt, 4, "e", outbuf5, sizeof(outbuf5) - 1, 0); + memset(outbuf1, 0, sizeof(outbuf1)); + bindOutParamString(hstmt, 5, "a", outbuf1, sizeof(outbuf1) - 1, 0); executeQuery(hstmt); printf("OUT params: %s : %s : %s\n", outbuf1, outbuf3, outbuf5); } -- 2.39.5