Question 302733 in PHP Versions Question posted in
You can find a list of all versions here.

Php versions – Understanding mysql_stmt_bind_named_param() – MySQL C API

I refer to mysql_stmt_bind_named_param()

I don’t understand the example in the documentation. It uses some traceparent which is never used. I come from PHP where bind variables are using the :bindName syntax.

I think I am completely misunderstanding how it works. I would like to name each bind variable, so the code becomes more readable and also less buggy [just imagine an INSERT with 10 fields.]

Is there a way I can run instead of INSERT INTO foo SET bar_int = ?, barChar = ? something like INSERT INTO foo SET bar_int = :intValue, barChar = :charValue with the function mysql_stmt_bind_named_param()?

And if yes, how to set the parameters for the function? mysql_stmt_bind_named_param() is quite new and I didn’t find anything in internet – as mentioned above, the sample in the documentation confuses me instead of clarifying.

Update after Paul T’s comment:

  1. The file in the See Also section doesn’t exist. The documentation is very poor and not maintained.
  2. In MySQL 8.2 version the mentioned file is in testclients/mysql_client_test.cc and it confuses as well.

It looks to me like it’s not supported. No idea what Oracle has implemented.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I wrote this small extension for the MySQL C API which supports the real bind variables behaviour using flexible delimiters:

    const char * insertCommand { "INSERT INTO foo SET 
    bar_int = :barInt, bar_char = :barChar, bar_date = :barDate " };
MYSQL_STMT * preparedInsertStatement = mysql_stmt_init( mysqlConnection );

int  intBar     { 2804 };
char intChar [] { "Some-Text" };
size_t intCharLength = strlen(intChar);
MYSQL_TIME dateTime {};
  ... Set the <dateTime> members ...
    
FaF::MySqlExtBind fafExtBind( preparedInsertStatement, insertCommand );

fafExtBind.assignBindData( "barChar", MYSQL_TYPE_STRING,   intChar, &intCharLength );
fafExtBind.assignBindData( "barInt",  MYSQL_TYPE_LONG,     static_cast<void *>(&intBar) );
fafExtBind.assignBindData( "barDate", MYSQL_TYPE_DATETIME, &dateTime );

mysqlErrorCode = fafExtBind.executeBind();

    Using your own delimiters:

    FaF::MySqlExtBind::setDelimiters( ":\{", "\}" );

  2. 0

    You misunderstand what this new "named parameter" feature is for. I agree they have not explained it well on the page you linked to.

    They called this feature "named parameter" which of course makes people assume it’s about adding labels as the parameter placeholder as is supported by some other SQL products (e.g. Oracle).

    That’s not what it’s about. This feature does not implement the Oracle-like style of using a label in a parameter placeholder. MySQL still supports only ? as a positional (not named) parameter placeholder.

    The feature they call "named parameter" means it allows your query to access a Query Attribute, which is a new feature. Query attributes are supposed to be used for query metadata, not passing values as parameters to a prepared statement.

    You might like to read this blog to get an independent review of the query attribute feature:

    Then read these pages in the manual:

    The latter manual page makes it more clear how to use the name argument in the C API:

    • name: The address of an array of character pointers, each pointing to a null-terminated string defining an attribute name. The array should contain n_params elements, one for each attribute. Query attribute names are transmitted using the character set indicated by the character_set_client system variable.

    Remember, this is for binding query attributes, not traditional query parameters.

    You can also see in the example in https://dev.mysql.com/doc/c-api/8.2/en/mysql-stmt-bind-named-param.html, the query has both a traditional query parameter, for which the name array element is a null pointer, and a named query attribute, for which the name array element is string to name the query attribute.

    const char *names[2] = {nullptr, "traceparent"};

    I infer that if you only use traditional query parameters, you would still need this array, and it must have as many elements as the length of your binds array, but for traditional query parameters, they would all be null pointers.

    Editorial:

    I have no idea what’s gone wrong with MySQL developers. There is almost no way they could have made this feature more confusing or inconvenient to use.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search