<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <title>Binds a PHP variable to an SQL statement parameter</title> </head> <body><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="function.db2-autocommit.html">db2_autocommit</a></div> <div class="next" style="text-align: right; float: right;"><a href="function.db2-client-info.html">db2_client_info</a></div> <div class="up"><a href="ref.ibm-db2.html">IBM DB2 Functions</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div><hr /><div id="function.db2-bind-param" class="refentry"> <div class="refnamediv"> <h1 class="refname">db2_bind_param</h1> <p class="verinfo">(PECL ibm_db2 >= 1.0.0)</p><p class="refpurpose"><span class="refname">db2_bind_param</span> — <span class="dc-title"> Binds a PHP variable to an SQL statement parameter </span></p> </div> <div class="refsect1 description" id="refsect1-function.db2-bind-param-description"> <h3 class="title">Description</h3> <div class="methodsynopsis dc-description"> <span class="type">bool</span> <span class="methodname"><strong>db2_bind_param</strong></span> ( <span class="methodparam"><span class="type">resource</span> <code class="parameter">$stmt</code></span> , <span class="methodparam"><span class="type">int</span> <code class="parameter">$parameter-number</code></span> , <span class="methodparam"><span class="type">string</span> <code class="parameter">$variable-name</code></span> [, <span class="methodparam"><span class="type">int</span> <code class="parameter">$parameter-type</code></span> [, <span class="methodparam"><span class="type">int</span> <code class="parameter">$data-type</code><span class="initializer"> = 0</span></span> [, <span class="methodparam"><span class="type">int</span> <code class="parameter">$precision</code><span class="initializer"> = -1</span></span> [, <span class="methodparam"><span class="type">int</span> <code class="parameter">$scale</code><span class="initializer"> = 0</span></span> ]]]] )</div> <p class="para rdfs-comment"> Binds a PHP variable to an SQL statement parameter in a statement resource returned by <span class="function"><a href="function.db2-prepare.html" class="function">db2_prepare()</a></span>. This function gives you more control over the parameter type, data type, precision, and scale for the parameter than simply passing the variable as part of the optional input array to <span class="function"><a href="function.db2-execute.html" class="function">db2_execute()</a></span>. </p> </div> <div class="refsect1 parameters" id="refsect1-function.db2-bind-param-parameters"> <h3 class="title">Parameters</h3> <p class="para"> <dl> <dt> <span class="term"><em><code class="parameter">stmt</code></em></span> <dd> <p class="para"> A prepared statement returned from <span class="function"><a href="function.db2-prepare.html" class="function">db2_prepare()</a></span>. </p> </dd> </dt> <dt> <span class="term"><em><code class="parameter">parameter-number</code></em></span> <dd> <p class="para"> Specifies the 1-indexed position of the parameter in the prepared statement. </p> </dd> </dt> <dt> <span class="term"><em><code class="parameter">variable-name</code></em></span> <dd> <p class="para"> A string specifying the name of the PHP variable to bind to the parameter specified by <em><code class="parameter">parameter-number</code></em>. </p> </dd> </dt> <dt> <span class="term"><em><code class="parameter">parameter-type</code></em></span> <dd> <p class="para"> A constant specifying whether the PHP variable should be bound to the SQL parameter as an input parameter (<em>DB2_PARAM_IN</em>), an output parameter (<em>DB2_PARAM_OUT</em>), or as a parameter that accepts input and returns output (<em>DB2_PARAM_INOUT</em>). To avoid memory overhead, you can also specify <em>DB2_PARAM_FILE</em> to bind the PHP variable to the name of a file that contains large object (BLOB, CLOB, or DBCLOB) data. </p> </dd> </dt> <dt> <span class="term"><em><code class="parameter">data-type</code></em></span> <dd> <p class="para"> A constant specifying the SQL data type that the PHP variable should be bound as: one of <em>DB2_BINARY</em>, <em>DB2_CHAR</em>, <em>DB2_DOUBLE</em>, or <em>DB2_LONG</em> . </p> </dd> </dt> <dt> <span class="term"><em><code class="parameter">precision</code></em></span> <dd> <p class="para"> Specifies the precision with which the variable should be bound to the database. This parameter can also be used for retrieving XML output values from stored procedures. A non-negative value specifies the maximum size of the XML data that will be retrieved from the database. If this parameter is not used, a default of 1MB will be assumed for retrieving the XML output value from the stored procedure. </p> </dd> </dt> <dt> <span class="term"><em><code class="parameter">scale</code></em></span> <dd> <p class="para"> Specifies the scale with which the variable should be bound to the database. </p> </dd> </dt> </dl> </p> </div> <div class="refsect1 returnvalues" id="refsect1-function.db2-bind-param-returnvalues"> <h3 class="title">Return Values</h3> <p class="para"> Returns <strong><code>TRUE</code></strong> on success or <strong><code>FALSE</code></strong> on failure. </p> </div> <div class="refsect1 examples" id="refsect1-function.db2-bind-param-examples"> <h3 class="title">Examples</h3> <p class="para"> <div class="example" id="example-1140"> <p><strong>Example #1 Binding PHP variables to a prepared statement</strong></p> <div class="example-contents"><p> The SQL statement in the following example uses two input parameters in the WHERE clause. We call <span class="function"><strong>db2_bind_param()</strong></span> to bind two PHP variables to the corresponding SQL parameters. Notice that the PHP variables do not have to be declared or assigned before the call to <span class="function"><strong>db2_bind_param()</strong></span>; in the example, <em>$lower_limit</em> is assigned a value before the call to <span class="function"><strong>db2_bind_param()</strong></span>, but <em>$upper_limit</em> is assigned a value after the call to <span class="function"><strong>db2_bind_param()</strong></span>. The variables must be bound and, for parameters that accept input, must have any value assigned, before calling <span class="function"><a href="function.db2-execute.html" class="function">db2_execute()</a></span>. </p></div> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br /><br />$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">'SELECT name, breed, weight FROM animals<br /> WHERE weight > ? AND weight < ?'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_connect</span><span style="color: #007700">(</span><span style="color: #0000BB">$database</span><span style="color: #007700">, </span><span style="color: #0000BB">$user</span><span style="color: #007700">, </span><span style="color: #0000BB">$password</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_prepare</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// We can declare the variable before calling db2_bind_param()<br /></span><span style="color: #0000BB">$lower_limit </span><span style="color: #007700">= </span><span style="color: #0000BB">1</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">db2_bind_param</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">, </span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #DD0000">"lower_limit"</span><span style="color: #007700">, </span><span style="color: #0000BB">DB2_PARAM_IN</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">db2_bind_param</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">, </span><span style="color: #0000BB">2</span><span style="color: #007700">, </span><span style="color: #DD0000">"upper_limit"</span><span style="color: #007700">, </span><span style="color: #0000BB">DB2_PARAM_IN</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// We can also declare the variable after calling db2_bind_param()<br /></span><span style="color: #0000BB">$upper_limit </span><span style="color: #007700">= </span><span style="color: #0000BB">15.0</span><span style="color: #007700">;<br /><br />if (</span><span style="color: #0000BB">db2_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">)) {<br /> while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">)) {<br /> print </span><span style="color: #DD0000">"</span><span style="color: #007700">{</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #0000BB">0</span><span style="color: #007700">]}</span><span style="color: #DD0000">, </span><span style="color: #007700">{</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #0000BB">1</span><span style="color: #007700">]}</span><span style="color: #DD0000">, </span><span style="color: #007700">{</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #0000BB">2</span><span style="color: #007700">]}</span><span style="color: #DD0000">\n"</span><span style="color: #007700">;<br /> }<br />}<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents"><p>The above example will output:</p></div> <div class="example-contents screen"> <div class="cdata"><pre> Pook, cat, 3.2 Rickety Ride, goat, 9.7 Peaches, dog, 12.3 </pre></div> </div> </div> <div class="example" id="example-1141"> <p><strong>Example #2 Calling stored procedures with IN and OUT parameters</strong></p> <div class="example-contents"><p> The stored procedure match_animal in the following example accepts three different parameters: <ol type="1"> <li class="listitem"> <p class="para"> an input (IN) parameter that accepts the name of the first animal as input </p> </li> <li class="listitem"> <p class="para"> an input-output (INOUT) parameter that accepts the name of the second animal as input and returns the string <em>TRUE</em> if an animal in the database matches that name </p> </li> <li class="listitem"> <p class="para"> an output (OUT) parameter that returns the sum of the weight of the two identified animals </p> </li> </ol> In addition, the stored procedure returns a result set consisting of the animals listed in alphabetic order starting at the animal corresponding to the input value of the first parameter and ending at the animal corresponding to the input value of the second parameter. </p></div> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br /><br />$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">'CALL match_animal(?, ?, ?)'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_connect</span><span style="color: #007700">(</span><span style="color: #0000BB">$database</span><span style="color: #007700">, </span><span style="color: #0000BB">$user</span><span style="color: #007700">, </span><span style="color: #0000BB">$password</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_prepare</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #DD0000">"Peaches"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$second_name </span><span style="color: #007700">= </span><span style="color: #DD0000">"Rickety Ride"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$weight </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">db2_bind_param</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">, </span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #DD0000">"name"</span><span style="color: #007700">, </span><span style="color: #0000BB">DB2_PARAM_IN</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">db2_bind_param</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">, </span><span style="color: #0000BB">2</span><span style="color: #007700">, </span><span style="color: #DD0000">"second_name"</span><span style="color: #007700">, </span><span style="color: #0000BB">DB2_PARAM_INOUT</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">db2_bind_param</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">, </span><span style="color: #0000BB">3</span><span style="color: #007700">, </span><span style="color: #DD0000">"weight"</span><span style="color: #007700">, </span><span style="color: #0000BB">DB2_PARAM_OUT</span><span style="color: #007700">);<br /><br />print </span><span style="color: #DD0000">"Values of bound parameters _before_ CALL:\n"</span><span style="color: #007700">;<br />print </span><span style="color: #DD0000">" 1: </span><span style="color: #007700">{</span><span style="color: #0000BB">$name</span><span style="color: #007700">}</span><span style="color: #DD0000"> 2: </span><span style="color: #007700">{</span><span style="color: #0000BB">$second_name</span><span style="color: #007700">}</span><span style="color: #DD0000"> 3: </span><span style="color: #007700">{</span><span style="color: #0000BB">$weight</span><span style="color: #007700">}</span><span style="color: #DD0000">\n\n"</span><span style="color: #007700">;<br /><br />if (</span><span style="color: #0000BB">db2_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">)) {<br /> print </span><span style="color: #DD0000">"Values of bound parameters _after_ CALL:\n"</span><span style="color: #007700">;<br /> print </span><span style="color: #DD0000">" 1: </span><span style="color: #007700">{</span><span style="color: #0000BB">$name</span><span style="color: #007700">}</span><span style="color: #DD0000"> 2: </span><span style="color: #007700">{</span><span style="color: #0000BB">$second_name</span><span style="color: #007700">}</span><span style="color: #DD0000"> 3: </span><span style="color: #007700">{</span><span style="color: #0000BB">$weight</span><span style="color: #007700">}</span><span style="color: #DD0000">\n\n"</span><span style="color: #007700">;<br /><br /> print </span><span style="color: #DD0000">"Results:\n"</span><span style="color: #007700">;<br /> while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">)) {<br /> print </span><span style="color: #DD0000">" </span><span style="color: #007700">{</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #0000BB">0</span><span style="color: #007700">]}</span><span style="color: #DD0000">, </span><span style="color: #007700">{</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #0000BB">1</span><span style="color: #007700">]}</span><span style="color: #DD0000">, </span><span style="color: #007700">{</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #0000BB">2</span><span style="color: #007700">]}</span><span style="color: #DD0000">\n"</span><span style="color: #007700">;<br /> }<br />}<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents"><p>The above example will output:</p></div> <div class="example-contents screen"> <div class="cdata"><pre> Values of bound parameters _before_ CALL: 1: Peaches 2: Rickety Ride 3: 0 Values of bound parameters _after_ CALL: 1: Peaches 2: TRUE 3: 22 Results: Peaches, dog, 12.3 Pook, cat, 3.2 Rickety Ride, goat, 9.7 </pre></div> </div> </div> <div class="example" id="example-1142"> <p><strong>Example #3 Inserting a binary large object (BLOB) directly from a file</strong></p> <div class="example-contents"><p> The data for large objects are typically stored in files, such as XML documents or audio files. Rather than reading an entire file into a PHP variable, and then binding that PHP variable into an SQL statement, you can avoid some memory overhead by binding the file directly to the input parameter of your SQL statement. The following example demonstrates how to bind a file directly into a BLOB column. </p></div> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_prepare</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">"INSERT INTO animal_pictures(picture) VALUES (?)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$picture </span><span style="color: #007700">= </span><span style="color: #DD0000">"/opt/albums/spook/grooming.jpg"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$rc </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_bind_param</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">, </span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #DD0000">"picture"</span><span style="color: #007700">, </span><span style="color: #0000BB">DB2_PARAM_FILE</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$rc </span><span style="color: #007700">= </span><span style="color: #0000BB">db2_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> </div> </p> </div> <div class="refsect1 seealso" id="refsect1-function.db2-bind-param-seealso"> <h3 class="title">See Also</h3> <p class="para"> <ul class="simplelist"> <li class="member"> <span class="function"><a href="function.db2-execute.html" class="function" rel="rdfs-seeAlso">db2_execute()</a> - Executes a prepared SQL statement</span></li> <li class="member"> <span class="function"><a href="function.db2-prepare.html" class="function" rel="rdfs-seeAlso">db2_prepare()</a> - Prepares an SQL statement to be executed</span></li> </ul> </p> </div> </div><hr /><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="function.db2-autocommit.html">db2_autocommit</a></div> <div class="next" style="text-align: right; float: right;"><a href="function.db2-client-info.html">db2_client_info</a></div> <div class="up"><a href="ref.ibm-db2.html">IBM DB2 Functions</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div></body></html>