WL#15950: Support query parameters for prepared statements

oscpache · oscpache · commit 76459d96951a · 2023-11-17T09:35:44.000-06:00
With this WL, support for query attributes for prepared
statements is added, in particular for the C-EXT since
pure Python already supports it.

Additionally, we take this implementation as an excuse
to enable OpenTelemetry trace context propagation when
working with prepared statements in the C-EXT. Previously
it wasn't possible for the C-EXT since support for query
attributes for prepared statements was missing, but now
context propagation for this kind of statements is supported.

Change-Id: Ic119b05765788d502f26eb2feaec492fa788edfd
diff --git a/CHANGES.txt b/CHANGES.txt
@@ -14,6 +14,7 @@ v8.3.0
 - WL#16015: Remove use of removed COM_ commands
 - WL#15983: Stop using mysql_ssl_set api
 - WL#15982: Remove use of mysql_shutdown
+- WL#15950: Support query parameters for prepared statements
 - WL#15942: Improve type hints and standardize byte type handling
 - WL#15836: Split mysql and mysqlx into different packages
 - WL#15523: Support Python DB API asynchronous execution
diff --git a/CONTRIBUTING.rst b/CONTRIBUTING.rst
@@ -21,7 +21,7 @@ Contributing Code
 
 Contributing to this project is easy. You just need to follow these steps.
 
-- Make sure you have a user account at `bugs.mysql.com <https://bugs.mysql.com>`_. You will need to reference this user account when you submit your Oracle Contributor Agreement (OCA).
+- Make sure you have a user account at `bugs.mysql.com <https://bugs.mysql.com>`_. You will need to reference this user account when you submit your Oracle Contributor Agreement (a.k.a. OCA).
 - Sign the Oracle Contributor Agreement. You can find instructions for doing that at the `OCA Page <https://oca.opensource.oracle.com/>`_.
 - Develop your pull request. Make sure you are aware of the `requirements <https://dev.mysql.com/doc/dev/connector-python/8.0/requirements.html>`_ for the project.
 - Validate your pull request by including tests that sufficiently cover the functionality you are adding.
diff --git a/mysql-connector-python/lib/mysql/connector/connection_cext.py b/mysql-connector-python/lib/mysql/connector/connection_cext.py
@@ -606,12 +606,13 @@ def cmd_stmt_prepare(self, statement: bytes) -> CMySQLPrepStmt:
         except MySQLInterfaceError as err:
             raise InterfaceError(str(err)) from err
 
+    @with_context_propagation
     def cmd_stmt_execute(  # type: ignore[override]
         self, statement_id: CMySQLPrepStmt, *args: Any
     ) -> Optional[Union[CextEofPacketType, CextResultType]]:
         """Executes the prepared statement"""
         try:
-            statement_id.stmt_execute(*args)
+            statement_id.stmt_execute(*args, query_attrs=self.query_attrs)
         except MySQLInterfaceError as err:
             raise InterfaceError(str(err)) from err
 
diff --git a/mysql-connector-python/src/include/mysql_capi.h b/mysql-connector-python/src/include/mysql_capi.h
@@ -294,7 +294,7 @@ PyObject *
 MySQL_stmt_prepare(MySQL *self, PyObject *stmt);
 
 PyObject *
-MySQLPrepStmt_execute(MySQLPrepStmt *self, PyObject *args);
+MySQLPrepStmt_execute(MySQLPrepStmt *self, PyObject *args, PyObject *kwds);
 
 PyObject *
 MySQLPrepStmt_handle_result(MySQLPrepStmt *self);
diff --git a/mysql-connector-python/src/mysql_capi.c b/mysql-connector-python/src/mysql_capi.c
@@ -3216,19 +3216,42 @@ MySQLPrepStmt_dealloc(MySQLPrepStmt *self)
     @retval PyBool_type OK
 */
 PyObject *
-MySQLPrepStmt_execute(MySQLPrepStmt *self, PyObject *args)
+MySQLPrepStmt_execute(MySQLPrepStmt *self, PyObject *args, PyObject *kwds)
 {
-    Py_ssize_t size = PyTuple_Size(args);
+
+    static const char* key_query_attrs = "query_attrs";
+    PyObject *query_attrs = PyDict_GetItemString(kwds, key_query_attrs);  // returns borrowed reference
+
+    Py_ssize_t size_unnamed_params = PyTuple_Size(args);
+    Py_ssize_t size = size_unnamed_params + (query_attrs != NULL? PyList_Size(query_attrs): 0);
+
     MYSQL_BIND *mbinds = calloc(size, sizeof(MYSQL_BIND));
     struct MySQL_binding *bindings = calloc(size, sizeof(struct MySQL_binding));
+    const char **names = calloc(size, sizeof(char *));
+
     PyObject *value;
     PyObject *retval = NULL;
     int i = 0, res = 0;
+    bool params_bind_ans;
 
     for (i = 0; i < size; i++) {
         struct MySQL_binding *pbind = &bindings[i];
         MYSQL_BIND *mbind = &mbinds[i];
-        value = PyTuple_GetItem(args, i);
+
+        if (i < size_unnamed_params)
+        {
+            // handle unnamed parameter
+            names[i] = NULL;  // Name being NULL indicates that the parameter is unnamed
+            value = PyTuple_GetItem(args, i);
+        }
+        else
+        {
+            // handle query attribute
+            PyObject *attr_tuple = PyList_GetItem(query_attrs, i - size_unnamed_params);  // returns borrowed reference
+            PyObject *attr_name = PyTuple_GetItem(attr_tuple, 0);  // returns borrowed reference
+            names[i] = PyUnicode_AsUTF8(attr_name);
+            value = PyTuple_GetItem(attr_tuple, 1);
+        }
 
         if (value == NULL) {
             goto cleanup;
@@ -3392,7 +3415,14 @@ MySQLPrepStmt_execute(MySQLPrepStmt *self, PyObject *args)
         }
     }
 
-    if (mysql_stmt_bind_param(self->stmt, mbinds)) {
+    #if MYSQL_VERSION_ID >= 80300
+        params_bind_ans = mysql_stmt_bind_named_param(self->stmt, mbinds, (int)size, names);
+    #else
+        params_bind_ans = mysql_stmt_bind_param(self->stmt, mbinds);
+    #endif
+
+    if (params_bind_ans)
+    {
         retval = PyErr_Format(MySQLInterfaceError, (const char *)"Bind the parameters: %s",
                               mysql_stmt_error(self->stmt));
         goto cleanup;
diff --git a/mysql-connector-python/src/mysql_connector.c b/mysql-connector-python/src/mysql_connector.c
@@ -244,7 +244,7 @@ static PyMemberDef MySQLPrepStmt_members[] = {
 };
 
 static PyMethodDef MySQLPrepStmt_methods[] = {
-    {"stmt_execute", (PyCFunction)MySQLPrepStmt_execute, METH_VARARGS,
+    {"stmt_execute", (PyCFunction)MySQLPrepStmt_execute, METH_VARARGS | METH_KEYWORDS,
      "Executes the prepared statement"},
     {"fetch_fields", (PyCFunction)MySQLPrepStmt_fetch_fields, METH_VARARGS,
      "Fetch information about fields in result set"},
diff --git a/mysql-connector-python/tests/otel/run_context_propagation.py b/mysql-connector-python/tests/otel/run_context_propagation.py
diff --git a/mysql-connector-python/tests/test_connection.py b/mysql-connector-python/tests/test_connection.py
