db2 driver confuses schema name for database name

[NattyNarwhal]

Description

When trying to set the database name when connecting, the Db2 driver mixes up the DSN (containing the host information) and the database name. This is obviously invalid, and will fail to connect.

Environment

• ADOdb version: 5.22.7
• Driver or Module: Db2
• Database type and version: Db2
• PHP version: 8.3.7
• Platform: IBM i 7.5

• I have tested that the problem is reproducible in the latest release / master branch / latest hotfix branch
  ⚠️ Keep only the applicable option(s), i.e. where you actually tested, and remove the others

Steps to reproduce

$adoDbDir = "vendor/adodb/adodb-php";
require "$adoDbDir/adodb.inc.php";

$db = newAdoConnection("db2");
var_dump($db->connect("*LOCAL", "username", "password", "QIWS"));

$sql = "select * from qcustcdt"; // this table in QIWS
$array = $db->getAll($sql);

print_r($array);

Resulting error spew:

Warning: Because you provided user,password and database, DSN connection parameters were discarded
Catalogued connection using parameters: DB=QIWS / UID=username / PWD=password
PHP Warning:  db2_connect(): Connection failure userid (username) in /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php on line 167
PHP Stack trace:
PHP   1. {main}() /home/calvin/adodbtest/index.php:0
PHP   2. ADOConnection->Connect($argHostname = '*LOCAL', $argUsername = 'username', $argPassword = 'password', $argDatabaseName = 'QIWS', $forceNew = *uninitialized*) /home/calvin/adodbtest/index.php:18
PHP   3. ADODB_db2->_connect($argDSN = '*LOCAL', $argUsername = 'username', $argPassword = 'password', $argDatabasename = 'QIWS') /home/calvin/adodbtest/vendor/adodb/adodb-php/adodb.inc.php:942
PHP   4. ADODB_db2->doDB2Connect($argDSN = '*LOCAL', $argUsername = 'username', $argPassword = 'password', $argDatabasename = 'QIWS', $persistent = *uninitialized*) /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php:96
PHP   5. db2_connect($database = 'QIWS', $username = 'username', $password = 'password', $options = []) /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php:167
PHP Warning:  db2_connect(): Relational database QIWS not in relational database directory. SQLCODE=-950 in /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php on line 167
PHP Stack trace:
PHP   1. {main}() /home/calvin/adodbtest/index.php:0
PHP   2. ADOConnection->Connect($argHostname = '*LOCAL', $argUsername = 'username', $argPassword = 'password', $argDatabaseName = 'QIWS', $forceNew = *uninitialized*) /home/calvin/adodbtest/index.php:18
PHP   3. ADODB_db2->_connect($argDSN = '*LOCAL', $argUsername = 'username', $argPassword = 'password', $argDatabasename = 'QIWS') /home/calvin/adodbtest/vendor/adodb/adodb-php/adodb.inc.php:942
PHP   4. ADODB_db2->doDB2Connect($argDSN = '*LOCAL', $argUsername = 'username', $argPassword = 'password', $argDatabasename = 'QIWS', $persistent = *uninitialized*) /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php:96
PHP   5. db2_connect($database = 'QIWS', $username = 'username', $password = 'password', $options = []) /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php:167
*LOCAL: Relational database QIWS not in relational database directory. SQLCODE=-950

Expected behavior

That it would connect to the specified system and set the default database.

Additional context

The old pre-rewrite version of the driver in 5.20 seemed to use the DSN intact and called SET SCHEMA after connecting:

ADOdb/drivers/adodb-db2.inc.php

Lines 72 to 100 in 4011379

	function _connect($argDSN, $argUsername, $argPassword, $argDatabasename)
	{
	if (!function_exists('db2_connect')) {
	ADOConnection::outp("Warning: The old ODBC based DB2 driver has been renamed 'odbc_db2'. This ADOdb driver calls PHP's native db2 extension which is not installed.");
	return null;
	}
	// This needs to be set before the connect().
	// Replaces the odbc_binmode() call that was in Execute()
	ini_set('ibm_db2.binmode', $this->binmode);
	if ($argDatabasename && empty($argDSN)) {
	if (stripos($argDatabasename,'UID=') && stripos($argDatabasename,'PWD=')) $this->_connectionID = db2_connect($argDatabasename,null,null);
	else $this->_connectionID = db2_connect($argDatabasename,$argUsername,$argPassword);
	} else {
	if ($argDatabasename) $schema = $argDatabasename;
	if (stripos($argDSN,'UID=') && stripos($argDSN,'PWD=')) $this->_connectionID = db2_connect($argDSN,null,null);
	else $this->_connectionID = db2_connect($argDSN,$argUsername,$argPassword);
	}
	// For db2_connect(), there is an optional 4th arg. If present, it must be
	// an array of valid options. So far, we don't use them.
	$this->_errorMsg = @db2_conn_errormsg();
	if (isset($this->connectStmt)) $this->Execute($this->connectStmt);
	if ($this->_connectionID && isset($schema)) $this->Execute("SET SCHEMA=$schema");
	return $this->_connectionID != false;
	}

[dregad]

I'm sorry but I have zero knowledge of Db2, and no access to an instance of it for testing either, so I won't be able to help you much. Maybe @mnewnham will care to reply.

All I can say is that the following error is from PHP's db2_connect() function

PHP Warning: db2_connect(): Relational database QIWS not in relational database directory. SQLCODE=-950 in /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php on line 167

Did you try using an actual DSN to connect ?
DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;

[mnewnham]

I'll take a look. I don't have a DB2 system myself at the moment either though.

[mnewnham]

probably a dupe of #1031. Please check the fix for that

[dregad]

Thanks for looking into it @mnewnham

@NattyNarwhal please test Mark's proposed fix in #1034 and let us know if it fixes your problem.

[NattyNarwhal]

Using a full connection string doesn't work (tried with the PR). Note that there are some IBM i specific things with those; I've also never seen anyone use it like that.

LOG: Uncatalogued connection using DSN: DATABASE=QIWS;HOSTNAME=*LOCAL;UID=CALVIN;PWD=[...]
PHP Warning:  db2_connect(): Connection failure userid () in /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php on line 179
PHP Warning:  db2_connect(): Error occurred in SQL Call Level Interface SQLCODE=-99999 in /home/calvin/adodbtest/vendor/adodb/adodb-php/drivers/adodb-db2.inc.php on line 179

I'll review the PR as well.