- 20.10.7 C API Prepared Statement Function Descriptions
- 20.10.7.1 mysql_stmt_affected_rows()
- 20.10.7.2 mysql_stmt_attr_get()
- 20.10.7.3 mysql_stmt_attr_set()
- 20.10.7.4 mysql_stmt_bind_param()
- 20.10.7.5 mysql_stmt_bind_result()
- 20.10.7.6 mysql_stmt_close()
- 20.10.7.7 mysql_stmt_data_seek()
- 20.10.7.8 mysql_stmt_errno()
- 20.10.7.9 mysql_stmt_error()
- 20.10.7.10 mysql_stmt_execute()
- 20.10.7.11 mysql_stmt_fetch()
- 20.10.7.12 mysql_stmt_fetch_column()
- 20.10.7.13 mysql_stmt_field_count()
- 20.10.7.14 mysql_stmt_free_result()
- 20.10.7.15 mysql_stmt_init()
- 20.10.7.16 mysql_stmt_insert_id()
- 20.10.7.17 mysql_stmt_num_rows()
- 20.10.7.18 mysql_stmt_param_count()
- 20.10.7.19 mysql_stmt_param_metadata()
- 20.10.7.20 mysql_stmt_prepare()
- 20.10.7.21 mysql_stmt_reset()
- 20.10.7.22 mysql_stmt_result_metadata()
- 20.10.7.23 mysql_stmt_row_seek()
- 20.10.7.24 mysql_stmt_row_tell()
- 20.10.7.25 mysql_stmt_send_long_data()
- 20.10.7.26 mysql_stmt_sqlstate()
- 20.10.7.27 mysql_stmt_store_result()
my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt,
MYSQL_BIND *bind)
Description
mysql_stmt_bind_param() is used
to bind input data for the parameter markers in the SQL
statement that was passed to
mysql_stmt_prepare(). It uses
MYSQL_BIND structures to supply the data.
bind is the address of an array of
MYSQL_BIND structures. The client library
expects the array to contain one element for each
“?” parameter marker that is
present in the query.
Suppose that you prepare the following statement:
INSERT INTO mytbl VALUES(?,?,?)
When you bind the parameters, the array of
MYSQL_BIND structures must contain three
elements, and can be declared like this:
MYSQL_BIND bind[3];
Section 20.10.5, “C API Prepared Statement Data types”, describes
the members of each MYSQL_BIND element and
how they should be set to provide input values.
Return Values
Zero if the bind operation was successful. Nonzero if an error occurred.
Errors
-
The conversion is not supported. Possibly the
buffer_typevalue is illegal or is not one of the supported types. -
Out of memory.
-
An unknown error occurred.
Example
For the usage of
mysql_stmt_bind_param(), refer
to the Example from Section 20.10.7.10, “mysql_stmt_execute()”.
User Comments
The MYSQL_BIND array is only pointed to by the MYSQL_STMT so it must not be changed or freed until the MYSQL_STMT is either not used any more or rebound to a new MYSQL_BIND array.
The length of the BIND structure in v4.1.xx is 55 bytes.
The boundary must be aligned as per:
http://bugs.mysql.com/bug.php?id=8550
"... buffers that will store integer types need to be properly aligned, and unless you allocate every single buffer with an own "malloc" you risk getting an alignment error (sigbus)."
I found that adding a single byte variable to the declaration at the end like:
my_bool pad; /* Align boundary */
bringing the total bytes in the structure up to 56, cures the error I was getting:
Error: 2036 (CR_UNSUPPORTED_PARAM_TYPE)
Using unsupported buffer type: 0 (parameter: 2)
Because the variable buffer_type was not being read from the second element of the array in the expected location.
In Ver 5.1, this is not needed.
Mike Trader
For the most part, prepared statements are used to call repeated INSERTs or UPDATEs. Seeing as your going to be marshaling a lot of data through these MYSQL_BIND structures, you have to ask yourself one question: should I bind to a new MYSQL_BIND array on each execution, or just bind once and do a lot of memcpy()s into the MYSQL_BIND::buffer member? So the question is really about how fast mysql_stmt_bind_*() calls are.
And since the overwhelming majority of time spent writing to a database is tied up in socket communications, the bottom-line is does mysql_stmt_bind_*() communicate with the server? I asked that question, and could not get a theoretical answer, so I went for an empirical one. The short answer is: "I guess not". And this is how I arrived at it.
I made a bogus project on a Linux machine that connects to a Linux MySQL 5.0.66 server across a TCP/IP LAN. Essentially what I tried to do was rotate sets of data through a MYSQL_BIND, and then repeatedly call only mysql_stmt_bind_param()--no mysql_stmt_execute(). Here's the loop:
bind.length = &length;
for (uint64_t i = 0; i < n; i++) {
const bindtype &__bindtype = _bindtype[i%6];
bind.buffer_type = __bindtype.m_type;
bind.buffer = __bindtype.m_buf;
bind.buffer_length = __bindtype.m_nLength;
length = __bindtype.m_nLength;
mysql_stmt_bind_param(pStmt, &bind);
}
By choosing a really big n, this loop took 52 seconds to run. When I ran a loop that had all of the above in it, except the mysql_stmt_bind_param(), it took 25 seconds to run. Futhermore, I monitored the network interface, through which ran the connection to the server, and counted 9,501 bytes were transmitted while this benchmark ran. Since n was 10 billion, that means that the most mysql_stmt_bind_param() could have transmitted to the server was 9.501μb per call.
Unfortunately, this doesn't prove that mysql_stmt_bind_param() doesn't access the server, as no benchmark could. However, it does seem safe to indulge in rebinding parameters without fear of creating a bottleneck.
The first comment does not appear to be correct. The comment I am referring to states:
The MYSQL_BIND array is only pointed to by the MYSQL_STMT so it must not be changed or freed until the MYSQL_STMT is either not used any more or rebound to a new MYSQL_BIND array.
This is not true, at least on my Ubuntu system using MySQL 5.0 and 5.1 client libraries. I've created a test program which allocates the MYSQL_BIND array using malloc, then frees it immediately after calling mysql_stmt_bind_param. Running this test with valgrind shows no errors, and it executes correctly. Thus, it is safe to either use a local MYSQL_BIND array, or free it immediately after binding.
Add your own comment.