 PHP : Function Reference : IBM DB2, Cloudscape and Apache Derby Functions : db2_set_option
Example 953. Setting one parameter with a connection resource
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('autocommit' => DB2_AUTOCOMMIT_ON);
/* Call the function using the correct resource, options array, and type values */
$result = db2_set_option($conn, $options, 1);
/* Check if all options could be set correctly */
if($result)
{
echo 'Options Set Successfully';
}
else
{
echo 'Could Not Set Options';
}
?>
The above example will output:
Options Set Successfully
Example 954. Setting multiple parameters with a connection resource
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF,
'binmode' => DB2_PASSTHRU,
'db2_attr_case' => DB2_CASE_UPPER,
'cursor' => DB2_SCROLLABLE);
/* Call the function using the correct resource, options array, and type values */
$result = db2_set_option($conn, $options, 1);
/* Check if all options could be set correctly */
if($result)
{
echo 'Options Set Successfully';
}
else
{
echo 'Could Not Set Options';
}
?>
The above example will output:
Options Set Successfully
Example 955. Setting multiple parameters with an invalid key
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF,
'MY_INVALID_KEY' => DB2_PASSTHRU,
'db2_attr_case' => DB2_CASE_UPPER,
'cursor' => DB2_SCROLLABLE);
/* Call the function using the correct resource, options array, and type values */
$result = db2_set_option($conn, $options, 1);
/* Check if all options could be set correctly */
if($result)
{
echo 'Options Set Successfully';
}
else
{
echo 'Could Not Set Options';
}
?>
The above example will output:
Could Not Set Options
Example 956. Setting multiple parameters with an invalid value
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF,
'binmode' => 'INVALID_VALUE',
'db2_attr_case' => DB2_CASE_UPPER,
'cursor' => DB2_SCROLLABLE);
/* Call the function using the correct resource, options array, and type values */
$result = db2_set_option($conn, $options, 1);
/* Check if all options could be set correctly */
if($result)
{
echo 'Options Set Successfully';
}
else
{
echo 'Could Not Set Options';
}
?>
The above example will output:
Could Not Set Options
Example 957. Setting multiple parameters with a connection resource and the wrong type
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF,
'binmode' => DB2_PASSTHRU,
'db2_attr_case' => DB2_CASE_UPPER,
'cursor' => DB2_SCROLLABLE);
/* Call the function using the correct resource, options array, and the wrong type value */
$result = db2_set_option($conn, $options, 2);
/* Check if all options could be set correctly */
if($result)
{
echo 'Options Set Successfully';
}
else
{
echo 'Could Not Set Options';
}
?>
The above example will output:
Could Not Set Options
Example 958. Setting multiple parameters with the wrong resource
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF,
'binmode' => DB2_PASSTHRU,
'db2_attr_case' => DB2_CASE_UPPER,
'cursor' => DB2_SCROLLABLE);
$stmt = db2_prepare($conn, 'SELECT * FROM EMPLOYEE');
/* Call the function using the wrong resource, and the correct options array, and type values */
$result = db2_set_option($stmt, $options, 1);
/* Check if all options could be set correctly */
if($result)
{
echo 'Options Set Successfully';
}
else
{
echo 'Could Not Set Options';
}
?>
The above example will output:
Could Not Set Options
Example 959. Putting it all together
<?php
/* Database Connection Parameters */
$database = 'SAMPLE';
$hostname = 'localhost';
$port = 50000;
$protocol = 'TCPIP';
$username = 'db2inst1';
$password = 'ibmdb2';
/* Connection String */
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;";
$conn_string .= "HOSTNAME=$hostname;PORT=$port;PROTOCOL=$protocol;";
$conn_string .= "UID=$username;PWD=$password;";
/* Obtain Connection Resource */
$conn = db2_connect($conn_string, '', '');
/* Create the associative options array with valid key-value pairs */
$options = array('db2_attr_case' => DB2_CASE_LOWER,
'cursor' => DB2_SCROLLABLE);
$stmt = db2_prepare($conn, 'SELECT * FROM EMPLOYEE WHERE EMPNO = ? OR EMPNO = ?');
/* Call the function using the correct resource, options array, and type values */
$option_result = db2_set_option($stmt, $options, 2);
$result = db2_execute($stmt, array('000130', '000140'));
/* Get Row 2 before Row 1 since Scrollable Cursor */
print_r(db2_fetch_assoc($stmt, 2));
print '<br /><br />';
print_r(db2_fetch_assoc($stmt, 1));
?>
The above example will output:
Array
(
[empno] => 000140
[firstnme] => HEATHER
[midinit] => A
[lastname] => NICHOLLS
[workdept] => C01
[phoneno] => 1793
[hiredate] => 1976-12-15
[job] => ANALYST
[edlevel] => 18
[sex] => F
[birthdate] => 1946-01-19
[salary] => 28420.00
[bonus] => 600.00
[comm] => 2274.00
)
Array
(
[empno] => 000130
[firstnme] => DELORES
[midinit] => M
[lastname] => QUINTANA
[workdept] => C01
[phoneno] => 4578
[hiredate] => 1971-07-28
[job] => ANALYST
[edlevel] => 16
[sex] => F
[birthdate] => 1925-09-15
[salary] => 23800.00
[bonus] => 500.00
[comm] => 1904.00
)
Example 960. i5/OS cursors are read-only
<?php
$conn = db2_connect("", "", "", array("i5_lib"=>"nobody"));
$stmt = db2_prepare($conn, 'select * from names where first = ?');
$name = "first2";
db2_bind_param($stmt, 1, "name", DB2_PARAM_IN);
$options = array("i5_fetch_only"=>DB2_I5_FETCH_ON);
db2_set_option($stmt,$options,0);
if (db2_execute($stmt)) {
while ($row = db2_fetch_array($stmt)) {
echo "{$row[0]} {$row[1]}";
}
}
?>
The above example will output:
first2 last2
rtejpar
Examples 3 and 4 should output 'Options Set Successfully.' This is because, only a single option's key/value is set incorrectly (other options are still correct), hence the function must return successfully. (Instead a PHP warning will most probably be issued).
|
|
Bookmark this on Delicious
Share on Facebook
Slashdot It!
Digg 
