Using ADOdb with ODBC and Oracle

Question

I've been given access to an Oracle Server via ODBC and tested the connection using Oracle SQL Developer. These are the connection constants I've set in PHP:

define('APP_DB_HOST',       '192.168.1.1');
define('APP_DB_PORT',       '1521');
define('APP_DB_USER',       'MyUser');
define('APP_DB_PASS',       'MyPass');
define('APP_DB_SID',        'MyDatabaseSID');
define('APP_DB_SCHEMA',     'MyDatabaseSchema');

With ADOdb/ODBC, I should be able to use the below, so that I don't need to involve a tnsnames.ora entry:enter link description here

$dsn = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='.APP_DB_HOST.')(PORT='.APP_DB_PORT.'))(CONNECT_DATA=(SERVICE_NAME='.APP_DB_SID.')));User Id='.APP_DB_USER.';Password='.APP_DB_PASS.';';
$db->PConnect($dsn, APP_DB_USER, APP_DB_PASS, APP_DB_SCHEMA);

I get the ADOdb Warning:

Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in [..]/adodb/drivers/adodb-odbc_oracle.inc.php on line 87

Have others done this before, and if so, how so?

Beyond ADOdb, if anybody has a less complicated method of connecting by ODBC to Oracle with PHP7.4+, please do share.

Answer 1

If that is an option for you, I would strongly recommend to connect using the native oci8 driver, instead of relying on ODBC. Refer to ADOdb documentation for connection examples.

If you're stuck with ODBC, then I believe you need to adjust your DSN to specify the name of the driver you want to use in the connection string, e.g. Oracle in instantclient_19_6

Driver={OdbcDriverName};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=xxx))(CONNECT_DATA=(SERVICE_NAME=xxx)))

In my opinion it's better to specify the user id and password in the function call, i.e. $db->connect($dsn, $username, $password), but if you must have it in the connection string, you may want to try it with uid=xxx;pwd=xxx instead of User Id=xxx;Password=xxx

As an alternative, you could also create a System DSN in your ODBC configuration, and just refer to it by name, instead of hardcoding the TNS connection string directly in your code.

See also Create a DSN for the function odbc_connect for Oracle.

Answer 2

I would also suggest you to move to the OCI8 native driver if you have a bit of time and not too much refactoring to do.

Your server should already have an Oracle client installed (to make the current TNS and ODBC work), so the only thing to do is to install the OCI8 Oracle DDL PHP extension. Just copy it to php/ext/ and load it in your php.ini

In the meantime, you can test if the following code works:

require_once("include/adodb5/adodb.inc.php"); //depends on your adodb folder
$conn = NewADOConnection("oci8");
$conn->connect(APP_DB_HOST, APP_DB_USER, APP_DB_PASS, APP_DB_SID);

For info, you can check if your system admin has already installed the OCI8 extension by looking for the OCI8 section in the PHPINFO :