public inbox for [email protected]
help / color / mirror / Atom feedNamed parameter binding support
2+ messages / 2 participants
[nested] [flat]
* Named parameter binding support
@ 2021-05-26 05:29 Adrian Grucza <[email protected]>
2021-05-27 22:38 ` Re: Named parameter binding support Inoue,Hiroshi <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Adrian Grucza @ 2021-05-26 05:29 UTC (permalink / raw)
To: [email protected]
Hi all,
I've attached a patch that adds support for named parameter binding when
calling procedures or functions. Applications can use SQLSetDescField to
specify parameter names, as described at
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/binding-parameters-by-name-named-par...
Notes:
* Works for input and output parameters
* Regression tests enhanced to include binding parameters by name
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-Named-parameter-binding-support.patch (13.8K, 3-0001-Named-parameter-binding-support.patch)
download | inline diff:
From 67281e2d8e49b0ae66d6fdd78cc5684390fd12ea Mon Sep 17 00:00:00 2001
From: Adrian Grucza <[email protected]>
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.31.1.windows.1
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Named parameter binding support
2021-05-26 05:29 Named parameter binding support Adrian Grucza <[email protected]>
@ 2021-05-27 22:38 ` Inoue,Hiroshi <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Inoue,Hiroshi @ 2021-05-27 22:38 UTC (permalink / raw)
To: Adrian Grucza <[email protected]>; +Cc: [email protected]
Hi Adrian,
I would take care of your patch.
Thanks.
Hiroshi Inoue
2021年5月26日(水) 14:29 Adrian Grucza <[email protected]>:
> Hi all,
>
> I've attached a patch that adds support for named parameter binding when
> calling procedures or functions. Applications can use SQLSetDescField to
> specify parameter names, as described at
> https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/binding-parameters-by-name-named-par...
>
> Notes:
> * Works for input and output parameters
> * Regression tests enhanced to include binding parameters by name
>
> 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] 2+ messages in thread
end of thread, other threads:[~2021-05-27 22:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-26 05:29 Named parameter binding support Adrian Grucza <[email protected]>
2021-05-27 22:38 ` 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