<!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>
