Connect PDO with Oracle database

Question

I am new to Oracle, installed the Oracle today the 11g Express Edition. Then I installed Java SDK, and then the free Oracle SQL Developer. I connected with system account and created a username and table as defined below. I don't exactly know how Oracle works, I think instead of database name, usernames are used. So below are details.

Username/Connection/Database = CustomSearch
Table = Reservation_General_2

There are some columns inside that table and some data. but the point is I cant connect to Oracle Server.

Here is how I tried to connect to database server.

<?php
/**
 * Created by PhpStorm.
 * User: HaiderHassan
 * Date: 9/3/14
 * Time: 9:52 PM
 */
header('Access-Control-Allow-Origin: *');
$tns = "
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
       ";
try {
    $conn = new PDO("oci:dbname=".$tns, 'customsearch', 'babaji');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Problem is when I try to open that page, I get this error.

ERROR: could not find driver

These are my connection settings when I connect from Oracle Sql Developer.

What am I doing wrong, what steps should I take to fix this issue?

Update

I added the driver by removing semicolon from the php.ini file

extension=php_pdo_oci.dll 

But I started getting this error.

The program can't start because OCI.dll is missing from your computer. Try reinstalling the program to fix this problem.

I have to click 4 time OK for different alert boxes that shows up. I also downloaded oci.dll and copied it to the windows/system32, but still getting this error. What to do?

Update

I uninstalled XAMPP and followed this guide to install Apache and PHP separately,

http://www.oracle.com/technetwork/articles/dsl/technote-php-instant-12c-2088811.html

and then I tried my luck. That driver Problem went away but there is new problem

ERROR: SQLSTATE[HY000]: pdo_oci_handle_factory: ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor (ext\pdo_oci\oci_driver.c:635)

Here below is my new connection String.

try {
    $conn = new PDO('oci:dbname=//localhost:1521/xe/ORCL', 'customsearch', 'babaji');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

I tried to follow this answer on Stack Overflow for making a connection string.

http://stackoverflow.com/questions/11970261/connect-oracle-with-pdo-with-sid-and-instance-name

Update 2

Also tried to check if drivers installed. I used this code

foreach(PDO::getAvailableDrivers() as $driver)
    echo $driver, '\n';

Got this code from this below link

http://stackoverflow.com/questions/23239433/could-not-connect-to-oracle-using-pdo

it echoes this below line

oci\n

So this means that it is installed or this means some drivers are missing?

Update 3

Again rolled back to old connection just changed some stuff in that connection and seems like connection to oracle worked.

try {
    $conn = new PDO("oci:dbname=".$tns, 'customsearch', 'babaji');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo 'Connected to database';
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

with this I get the message 'Connected to database', means echo works because there is no error given by PDO.

But problem is now my query is not working? What happened to my query? Or will I have to change the syntax of the query also as I connected to Oracle? Or is the connection still not working?

Answer 1

Check PDO and OCI drivers installed properly or not

Try with following code

class PDOConnection {

    private $dbh;

    function __construct() {
        try {

            $server         = "127.0.0.1";
            $db_username    = "SYSTEM";
            $db_password    = "Oracle_1";
            $service_name   = "ORCL";
            $sid            = "ORCL";
            $port           = 1521;
            $dbtns          = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = $server)(PORT = $port)) (CONNECT_DATA = (SERVICE_NAME = $service_name) (SID = $sid)))";

            //$this->dbh = new PDO("mysql:host=".$server.";dbname=".dbname, $db_username, $db_password);

            $this->dbh = new PDO("oci:dbname=" . $dbtns . ";charset=utf8", $db_username, $db_password, array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC));

        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    public function select($sql) {
        $sql_stmt = $this->dbh->prepare($sql);
        $sql_stmt->execute();
        $result = $sql_stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    public function insert($sql) {
        $sql_stmt = $this->dbh->prepare($sql);
        try {
            $result = $sql_stmt->execute();
        } catch (PDOException $e) {
            trigger_error('Error occured while trying to insert into the DB:' . $e->getMessage(), E_USER_ERROR);
        }
        if ($result) {
            return $sql_stmt->rowCount();
        }
    }

    function __destruct() {
        $this->dbh = NULL;
    }

}

$dbh = new PDOConnection();

$dbh->select($select_sql);
$dbh->insert($insert_sql);