As of 09/29/2015 when using the ibm_db2 driver and specifying an invalid library list with the 'i5_libl' option, the connection will be successfully created but with a default library list. What happens is the connection is made and THEN the library list is attempted to be changed. The connection will be a success but the library list change will fail without "db2_conn_error()" or "db2_conn_errormsg()" reporting anything and a default library list will be used. However, "db2_stmt_error()" and "db2_stmt_errormsg()" will register with a value. Also if error reporting is enable, the warning will be output to the screen. This is not a bug, rather intended behavior according to Zend (I opened a ticked) and depending on which library list is used/swapped could lead to major problems (development VS production). I stumbled upon this behavior when an anomaly (which I still can't explain) was imploding my library list with the character 'Z'. I realized I was in my development environment but accessing production data as a result. Going forward I will make sure to add this additional check for good measure.
See below to recreate behavior:
<?php
ini_set("display_errors", 1);
$systemName = 'systemName';
$userID = 'userName';
$password = 'password';
$options['i5_libl'] = implode('Z', array(
'INVALID',
'LIB',
'LIST',
'IMPLODED',
'WITH',
'THE',
'LETTER',
'Z'
));
$options['i5_naming'] = DB2_I5_NAMING_ON;
$conn = db2_connect($systemName, $userID, $password, $options);
//This line causes "ini_set("display_errors", 1)" to dislay this warning on the screen:
//Warning: db2_connect(): Statement Execute Failed in /PATH/TO/FILE/test.php on line 58
if (db2_stmt_error()) { //Evaluates to true
echo "error ID: " . (db2_stmt_error()); //Displays error code: 38501
echo "<br>error message: " . (db2_stmt_errormsg()); //Displays: Trigger program or external routine detected an error. SQLCODE=-443
}
echo "<br />|".db2_conn_error()." ||| ".db2_conn_errormsg()."|<br />"; //Only "| ||| |" is displayed
print_r($conn); //A resource ID is displayed
echo "<br />";
if(isset($conn) && $conn === true){
echo "Boolean true<br />";
//never executes, but not expected since either false or a resource ID should be returned.
//Plus it is clear $conn contains a resource ID from above.
}
if(isset($conn) && $conn == true){
echo "Non-Boolean true 2<br />";
//This always executes regardless of an accurate library list or not
//I suppose "if($conn)" would evaluate to non-boolean true so this makes sense.
}
if(isset($conn) && $conn == "true"){
echo "String true";
//Never executes, but not expected.
}
if(isset($conn) && $conn === false){
echo "Boolean false<br />";
//never executes because the connection itself was a success.
//If invalid credentials were provided this executes.
}
if(isset($conn) && $conn == false){
echo "Non-Boolean false 2<br />";
//never executes because the connection itself was a success.
//Added this here because I was unsure if a boolean false would be returned or not.
//If invalid credentials were provided this executes as well.
}
if(isset($conn) && $conn == "false"){
echo "String false";
//never executes because the connection itself was a success.
//Added this here because I was unsure if a String "false" would be returned
}
?>
db2_connect
(PECL ibm_db2 >= 1.0.0)
db2_connect — Returns a connection to a database
Beschreibung
db2_connect(
string
?string
?string
array
): resource|false
string
$database,?string
$username,?string
$password,array
$options = []): resource|false
Creates a new connection to an IBM DB2 Universal Database, IBM Cloudscape, or Apache Derby database.
Parameter-Liste
-
database -
For a cataloged connection to a database,
databaserepresents the database alias in the DB2 client catalog.For an uncataloged connection to a database,
databaserepresents a complete connection string in the following format:DATABASE=
where the parameters represent the following values:database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;-
database -
The name of the database.
-
hostname -
The hostname or IP address of the database server.
-
port -
The TCP/IP port on which the database is listening for requests.
-
username -
The username with which you are connecting to the database.
-
password -
The password with which you are connecting to the database.
-
-
username -
The username with which you are connecting to the database.
For uncataloged connections, you must pass a
nullvalue or empty string. -
password -
The password with which you are connecting to the database.
For uncataloged connections, you must pass a
nullvalue or empty string. -
options -
An associative array of connection options that affect the behavior of the connection, where valid array keys include:
-
autocommit -
Passing the
DB2_AUTOCOMMIT_ONvalue turns autocommit on for this connection handle.Passing the
DB2_AUTOCOMMIT_OFFvalue turns autocommit off for this connection handle. -
DB2_ATTR_CASE -
Passing the
DB2_CASE_NATURALvalue specifies that column names are returned in natural case.Passing the
DB2_CASE_LOWERvalue specifies that column names are returned in lower case.Passing the
DB2_CASE_UPPERvalue specifies that column names are returned in upper case. -
CURSOR -
Passing the
DB2_FORWARD_ONLYvalue specifies a forward-only cursor for a statement resource. This is the default cursor type and is supported on all database servers.Passing the
DB2_SCROLLABLEvalue specifies a scrollable cursor for a statement resource. This mode enables random access to rows in a result set, but currently is supported only by IBM DB2 Universal Database.
The following new option is available in ibm_db2 version 1.7.0 and later.
-
trustedcontext -
Passing the DB2_TRUSTED_CONTEXT_ENABLE value turns trusted context on for this connection handle. This parameter cannot be set using db2_set_option().
This key works only if the database is cataloged (even if the database is local), or if you specify the full DSN when you create the connection.
To catalog the database, use following commands:
db2 catalog tcpip node loopback remote <SERVERNAME> server <SERVICENAME> db2 catalog database <LOCALDBNAME> as <REMOTEDBNAME> at node loopback db2 "update dbm cfg using svcename <SERVICENAME>" db2set DB2COMM=TCPIP
The following new i5/OS options are available in ibm_db2 version 1.5.1 and later.
-
i5_lib -
A character value that indicates the default library that will be used for resolving unqualified file references. This is not valid if the connection is using system naming mode.
-
i5_naming -
DB2_I5_NAMING_ONvalue turns on DB2 UDB CLI iSeries system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job.DB2_I5_NAMING_OFFvalue turns off DB2 UDB CLI default naming mode, which is SQL naming. Files are qualified using the period (.) delimiter. Unqualified files are resolved using either the default library or the current user ID. -
i5_commit -
The
i5_commitattribute should be set before the db2_connect(). If the value is changed after the connection has been established, and the connection is to a remote data source, the change does not take effect until the next successful db2_connect() for the connection handle.Hinweis:
The php.ini setting
ibm_db2.i5_allow_commit==0 orDB2_I5_TXN_NO_COMMITis the default, but may be overridden with thei5_commitoption.DB2_I5_TXN_NO_COMMIT- Commitment control is not used.DB2_I5_TXN_READ_UNCOMMITTED- Dirty reads, nonrepeatable reads, and phantoms are possible.DB2_I5_TXN_READ_COMMITTED- Dirty reads are not possible. Nonrepeatable reads, and phantoms are possible.DB2_I5_TXN_REPEATABLE_READ- Dirty reads and nonrepeatable reads are not possible. Phantoms are possible.DB2_I5_TXN_SERIALIZABLE- Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible -
i5_query_optimize -
DB2_FIRST_IOAll queries are optimized with the goal of returning the first page of output as fast as possible. This goal works well when the output is controlled by a user who is most likely to cancel the query after viewing the first page of output data. Queries coded with an OPTIMIZE FOR nnn ROWS clause honor the goal specified by the clause.DB2_ALL_IOAll queries are optimized with the goal of running the entire query to completion in the shortest amount of elapsed time. This is a good option when the output of a query is being written to a file or report, or the interface is queuing the output data. Queries coded with an OPTIMIZE FOR nnn ROWS clause honor the goal specified by the clause. This is the default. -
i5_dbcs_alloc -
DB2_I5_DBCS_ALLOC_ONvalue turns on DB2 6X allocation scheme for DBCS translation column size growth.DB2_I5_DBCS_ALLOC_OFFvalue turns off DB2 6X allocation scheme for DBCS translation column size growth.Note: php.ini setting
ibm_db2.i5_dbcs_alloc==0 orDB2_I5_DBCS_ALLOC_OFFis the default, but may be overridden with thei5_dbcs_allocoption. -
i5_date_fmt -
DB2_I5_FMT_ISO- The International Organization for Standardization (ISO) date format yyyy-mm-dd is used. This is the default.DB2_I5_FMT_USA- The United States date format mm/dd/yyyy is used.DB2_I5_FMT_EUR- The European date format dd.mm.yyyy is used.DB2_I5_FMT_JIS- The Japanese Industrial Standard date format yyyy-mm-dd is used.DB2_I5_FMT_MDY- The date format mm/dd/yyyy is used.DB2_I5_FMT_DMY- The date format dd/mm/yyyy is used.DB2_I5_FMT_YMD- The date format yy/mm/dd is used.DB2_I5_FMT_JUL- The Julian date format yy/ddd is used.DB2_I5_FMT_JOB- The job default is used. -
i5_date_sep -
DB2_I5_SEP_SLASH- A slash ( / ) is used as the date separator. This is the default.DB2_I5_SEP_DASH- A dash ( - ) is used as the date separator.DB2_I5_SEP_PERIOD- A period ( . ) is used as the date separator.DB2_I5_SEP_COMMA- A comma ( , ) is used as the date separator.DB2_I5_SEP_BLANK- A blank is used as the date separator.DB2_I5_SEP_JOB- The job default is used -
i5_time_fmt -
DB2_I5_FMT_ISO- The International Organization for Standardization (ISO) time format hh.mm.ss is used. This is the default.DB2_I5_FMT_USA- The United States time format hh:mmxx is used, where xx is AM or PM.DB2_I5_FMT_EUR- The European time format hh.mm.ss is used.DB2_I5_FMT_JIS- The Japanese Industrial Standard time format hh:mm:ss is used.DB2_I5_FMT_HMS- The hh:mm:ss format is used. -
i5_time_sep -
DB2_I5_SEP_COLON- A colon ( : ) is used as the time separator. This is the default.DB2_I5_SEP_PERIOD- A period ( . ) is used as the time separator.DB2_I5_SEP_COMMA- A comma ( , ) is used as the time separator.DB2_I5_SEP_BLANK- A blank is used as the time separator.DB2_I5_SEP_JOB- The job default is used. -
i5_decimal_sep -
DB2_I5_SEP_PERIOD- A period ( . ) is used as the decimal separator. This is the default.DB2_I5_SEP_COMMA- A comma ( , ) is used as the decimal separator.DB2_I5_SEP_JOB- The job default is used.
The following new i5/OS option is available in ibm_db2 version 1.8.0 and later.
-
i5_libl -
A character value that indicates the library list that will be used for resolving unqualified file references. Specify the library list elements separated by blanks 'i5_libl'=>"MYLIB YOURLIB ANYLIB".
Hinweis:
i5_liblcalls qsys2/qcmdexc('cmd',cmdlen), which is only available in i5/OS V5R4 and later.
-
Rückgabewerte
Returns a connection handle resource if the connection attempt is
successful. If the connection attempt fails, db2_connect()
returns false.
Beispiele
Beispiel #1 Creating a cataloged connection
Cataloged connections require you to have previously cataloged the target database through the DB2 Command Line Processor (CLP) or DB2 Configuration Assistant.
<?php
$database = 'SAMPLE';
$user = 'db2inst1';
$password = 'ibmdb2';
$conn = db2_connect($database, $user, $password);
if ($conn) {
echo "Connection succeeded.";
db2_close($conn);
}
else {
echo "Connection failed.";
}
?>Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
Connection succeeded.
Beispiel #2 Creating an uncataloged connection
An uncataloged connection enables you to dynamically connect to a database.
<?php
$database = 'SAMPLE';
$user = 'db2inst1';
$password = 'ibmdb2';
$hostname = 'localhost';
$port = 50000;
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" .
"HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
$conn = db2_connect($conn_string, '', '');
if ($conn) {
echo "Connection succeeded.";
db2_close($conn);
}
else {
echo "Connection failed.";
}
?>Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
Connection succeeded.
Beispiel #3 Creating a connection with autocommit off by default
Passing an array of options to db2_connect() enables you to modify the default behavior of the connection handle.
<?php
$database = 'SAMPLE';
$user = 'db2inst1';
$password = 'ibmdb2';
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF);
$conn = db2_connect($database, $user, $password, $options);
if ($conn) {
echo "Connection succeeded.\n";
if (db2_autocommit($conn)) {
echo "Autocommit is on.\n";
}
else {
echo "Autocommit is off.\n";
}
db2_close($conn);
}
else {
echo "Connection failed.";
}
?>Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
Connection succeeded. Autocommit is off.
Beispiel #4 i5/OS best performance
To achieve best performance for your i5/OS ibm_db2 1.5.1 PHP application use the default host, userid, and password for your db2_connect().
<?php
$library = "ADC";
$i5 = db2_connect("", "", "", array("i5_lib"=>"qsys2"));
$result = db2_exec($i5,
"select * from systables where table_schema = '$library'");
while ($row = db2_fetch_both($result)) {
echo $row['TABLE_NAME']."</br>";
}
db2_close($i5);
?>Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
ANIMALS NAMES PICTURES
Beispiel #5 Using trusted context
The following example shows how to enable trusted context, switch users, and get the current user ID.
<?php
$database = "SAMPLE";
$hostname = "localhost";
$port = 50000;
$authID = "db2inst1";
$auth_pass = "ibmdb2";
$tc_user = "tcuser";
$tc_pass = "tcpassword";
$dsn = "DATABASE=$database;HOSTNAME=$hostname;PORT=$port;
PROTOCOL=TCPIP;UID=$authID;PWD=$auth_pass;";
$options = array ("trustedcontext" => DB2_TRUSTED_CONTEXT_ENABLE);
$tc_conn = db2_connect($dsn, "", "", $options);
if($tc_conn) {
echo "Explicit trusted connection succeeded.\n";
if(db2_get_option($tc_conn, "trustedcontext")) {
$userBefore = db2_get_option($tc_conn, "trusted_user");
//Do some work as user 1.
//Switching to trusted user.
$parameters = array("trusted_user" => $tc_user,
"trusted_password" => $tcuser_pass);
$res = db2_set_option ($tc_conn, $parameters, 1);
$userAfter = db2_get_option($tc_conn, "trusted_user");
//Do more work as trusted user.
if($userBefore != $userAfter) {
echo "User has been switched." . "\n";
}
}
db2_close($tc_conn);
}
else {
echo "Explicit trusted connection failed.\n";
}
?>Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
Explicit trusted connection succeeded. User has been switched.
Siehe auch
- db2_close() - Closes a database connection
- db2_pconnect() - Returns a persistent connection to a database
add a note
User Contributed Notes 1 note
d dot lanza38 at gmail dot com ¶
9 years ago
