6.4.4 mysql_stmt_bind_named_param()

bool
mysql_stmt_bind_named_param(MYSQL_STMT *stmt,
                            MYSQL_BIND *binds, 
                            unsigned n_params,
                            const char **names)

Description

mysql_stmt_bind_named_param() sets up unnamed and named (query attributes) bind parameters for prepared statements. It supersedes the existing mysql_stmt_bind_param() function, which only supports unnamed parameters.

Arguments:

• stmt: The statement handler. Statements must be prepared with mysql_stmt_prepare().

• binds: An array of named and unnamed bind parameters. In the absence of named parameters, pass in NULL as the array of bind parameter names.

• n_params: Number of items within arrays.

• names: An array of bind parameter names.

mysql_stmt_bind_named_param() requires an input of array MYSQL_BIND structures and the matching names array. It succeeds without any effect (in corner cases) if the bind argument count parameter is zero or if the bind array pointer is NULL. It fails if an invalid MYSQL_BIND type is used for any bind parameter arguments.

To store both named and unnamed bind user variables, call mysql_stmt_bind_named_param() after mysql_stmt_prepare(). Subsequent to binding the variables, you can set and change them repeatedly.

Additional actions to consider are:

• Execute the statement using mysql_stmt_execute().

• Reset the statement using mysql_stmt_reset() or reprepare it with another query using mysql_stmt_prepare().

• Close the statement with mysql_stmt_close().

For a description of the members of the MYSQL_STMT and MYSQL_BIND structure and how they should be set to provide input values, see Section 6.2, “C API Prepared Statement Data Structures”.

Return Values

Zero for success. Nonzero if an error occurred.

Errors

• CR_UNSUPPORTED_PARAM_TYPE

  The conversion is not supported. Possibly the buffer_type value is invalid or is not one of the supported types.

• CR_OUT_OF_MEMORY

  Out of memory.

• CR_UNKNOWN_ERROR

  An unknown error occurred.

Example

The following example demonstrates binding one unnamed and one named parameter. The unnamed parameter has NULL in a matching names array slot:.

// Compile example on Linux with a command similar to:
// gcc example.c --std=c99 -I/usr/local/mysql/include -L/usr/local/mysql/lib -lmysqlclient -o example

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main(int argc, char *argv[]) {

  // variable declarations
  MYSQL *mysql;
  MYSQL_STMT *stmt;
  int int_data = 4;              // unnamed input parameter value
  int int_parentid = 1329494394; // named ('traceparent') input parameter value
  MYSQL_BIND params[2];
  const char *names[2] = {NULL, "traceparent"};
  int rc;

  MYSQL_BIND rbind[1];
  int result_val;

  // connect to the database server
  mysql = mysql_init(NULL);
  if (mysql == NULL) {
    fprintf(stderr, "%s\n", mysql_error(mysql));
    return 1;
  }
  if (mysql_real_connect(mysql, "127.0.0.1", "root", "password", NULL, 0, NULL,
                         0) == NULL) {
    fprintf(stderr, "%s\n", mysql_error(mysql));
    mysql_close(mysql);
    return 1;
  }

  // create a prepared statement
  stmt = mysql_stmt_init(mysql);
  if (stmt == NULL) {
    fprintf(stderr, "%s\n", mysql_error(mysql));
    mysql_close(mysql);
    return 1;
  }
  const char *query = "SELECT POW(?,2) AS square";
  if (mysql_stmt_prepare(stmt, query, strlen(query))) {
    mysql_stmt_close(stmt);
    mysql_close(mysql);
    return 1;
  }

  // bind the prepared statement parameters
  memset(params, 0, sizeof(params));

  params[0].buffer_type = MYSQL_TYPE_LONG;
  params[0].buffer = (char *)&int_data;
  params[0].length = NULL;
  params[0].is_null = NULL;

  params[1].buffer_type = MYSQL_TYPE_LONG;
  params[1].buffer = (char *)&int_parentid;
  params[1].length = NULL;
  params[1].is_null = NULL;

  rc = mysql_stmt_bind_named_param(stmt, params,
                                   sizeof(params) / sizeof(params[0]), names);
  if (rc != 0) {
    fprintf(stderr, "%s\n", mysql_error(mysql));
    mysql_stmt_close(stmt);
    mysql_close(mysql);
    return 1;
  }

  // execute the prepared statement
  rc = mysql_stmt_execute(stmt);
  if (rc != 0) {
    fprintf(stderr, "[%d] %s\n", mysql_stmt_errno(stmt),
            mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    mysql_close(mysql);
    return 1;
  }

  // bind and fetch the result parameter
  memset(rbind, 0, sizeof(rbind));
  rbind[0].buffer_type = MYSQL_TYPE_LONG;
  rbind[0].buffer = (char *)&result_val;
  rc = mysql_stmt_bind_result(stmt, rbind);
  if (rc != 0) {
    fprintf(stderr, "[%d] %s\n", mysql_stmt_errno(stmt),
            mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    mysql_close(mysql);
    return 1;
  }

  rc = mysql_stmt_fetch(stmt);
  if (rc != 0) {
    fprintf(stderr, "[%d] %s\n", mysql_stmt_errno(stmt),
            mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    mysql_close(mysql);
    return 1;
  }

  // expect POW(4, 2), i.e. 4 squared being 16
  if (result_val != 16) {
    printf("Unexpected result!\n");
  } else {
    printf("Success!\n");
  }

  mysql_stmt_close(stmt);
  mysql_close(mysql);
  return 0;
}

After executing, binding the results, and fetching data, the result of the statement on success is 16 (that is, 4 squared).

See Also

See mysql_stmt_send_long_data() for sending long text or blob data in pieces. Refer to the file tests/mysql_client_test.c for complete examples. This file can be obtained from a MySQL source distribution or from the source repository (see Installing MySQL from Source).