Connecting to a remote database using PHP

schilling399

Hello experts,

I've looked into many options so far and haven't come very far in my endeavor. I'm building a web based dashboard for a company I am contracted to and I'm having a very difficult time connecting to an oracle database from my dashboard. I'm attempting to use PHP to create a connection to the database. Then I'd like to query the database and present the results on the dashboard. The database is password protected and likewise the server hosting the database is also password protected. I'm not finding much help in other places I've looked and this is the only bump in the road so far for my dashboard build.

I was told to look into using the database URI but I wasn't sure if that would work since the database is not on the web server that the dashboard will be hosted on. I've also looked into using the PDO::__construct() method but its unclear what names and information I should be using for parameters.

I'm really needing some help on this and any help or advice is appreciated.

Christopher Jones-Oracle

As a start you could try something like the following.  The DBA should be able to give you the connect string or service name & credentials. You can check the PHP manual for examples, or look at the PDO_OCI test suite.

<?php

try {
    $dbh = new PDO('oci:dbname=localhost/XE', 'hr', 'welcome');
  } 
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

try {
     $s = $dbh->prepare("select city from locations");
     $s->execute();
     while ($r = $s->fetch(PDO::FETCH_ASSOC)) {
          echo $r['CITY'] . "<br>";
     }
} 
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}
?>

However my general recommendation is to use OCI8 instead of PDO_OCI, since OCI8 has better features & scalability. There is a discussion on connect strings in http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html that also applies to PDO_OCI, you just to use the additional "oci:dbname=" prefix for PDO_OCI

schilling399

Yeah, I'd rather use the oci_connect too. I installed the Oracle Instant Client and SQL*Plus. I also created a specific tnsnames.ora file just for my test dashboard (I'm creating the dashboard and testing it from my own local machine and when it is finished being built I will move it to our company's web server). When I use the sqlplus command from a command prompt it gives errors. I'm wondering if the listener on the remote database's host server needs to be configured to accept remote connections from my local machine. This is from the command prompt:

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 22 08:45:43 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:

ORA-12545: Connect failed because target host or object does not exist

Enter user-name: user

Enter password:

ERROR:

ORA-12560: TNS:protocol adapter error

Not sure what is causing this. Also if I were to use a connection string, how do I type that into my PHP.

schilling399

So I looked into the error logs and they are saying:

Fatal error: Call to undefined function oci_connect() in C:\inetpub\wwwroot\testconnection.php on line 8

I've included the oci.dll, ociw32.dll, and the php_oci8_12c.dll files in the php.ini and still same error.

Christopher Jones-Oracle

Check out the steps in http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

It looks like you don't have Oracle libraries in PATH.

schilling399

I appreciate your time, but with all the problems we were having my team decided we were going to slash the PHP idea and try developing with ASP.NET MVC. Again thank you for your help.

Christopher Jones-Oracle

Would probably be easier to use Linux!