MySQL :: MySQL 8.4 C API Developer Guide :: 6.4.4 mysql_stmt_bind_named_param() (original) (raw)

6.4.4 mysql_stmt_bind_named_param()

Description

mysql_stmt_bind_named_param() sets up unnamed and named (query attributes) bind parameters for prepared statements.

This function supersedes the oldmysql_stmt_bind_param() function, which supported only unnamed parameters, and which has since been removed.

Arguments:

stmt: The statement handler. Statements must be prepared withmysql_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 isNULL. It fails if an invalidMYSQL_BIND type is used for any bind parameter arguments.

To store both named and unnamed bind user variables, callmysql_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 usingmysql_stmt_execute().
Reset the statement usingmysql_stmt_reset() or reprepare it with another query usingmysql_stmt_prepare().
Close the statement withmysql_stmt_close().

For a description of the members of theMYSQL_STMT andMYSQL_BIND structure and how they should be set to provide input values, seeSection 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 thebuffer_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 hasNULL 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;
}