Forum Stats

  • 3,874,324 Users
  • 2,266,719 Discussions
  • 7,911,813 Comments

Discussions

Connecting to a remote database using PHP

schilling399
schilling399 Member Posts: 4
edited Jul 21, 2015 8:03AM in PHP

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.

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Jun 19, 2015 11:32PM

    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
    schilling399 Member Posts: 4
    edited Jun 22, 2015 10:47AM

    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
    schilling399 Member Posts: 4
    edited Jun 22, 2015 4:44PM

    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
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Jul 7, 2015 12:51AM

    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
    schilling399 Member Posts: 4
    edited Jul 8, 2015 12:40PM

    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
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Jul 21, 2015 8:03AM

    Would probably be easier to use Linux!

This discussion has been closed.