Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.8Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 311.6Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

23.8.11.4 mysql_stmt_bind_param()

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 23.8.9, “C API Prepared Statement Data Structures”, describes the members of each MYSQL_BIND element and how they should be set to provide input values.

Return Values

Zero for success. Nonzero if an error occurred.

Errors
Example

See the Example in Section 23.8.11.10, “mysql_stmt_execute()”.


User Comments
  Posted by Sela Lerer on March 4, 2007
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.
  Posted by michael splendid on August 18, 2008
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

  Posted by Angus M on April 1, 2009
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.

  Posted by Evan Jones on September 17, 2009
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.

Sign Up Login You must be logged in to post a comment.