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.