porting MRBS Database Oracle 12c

[Sudheert7]

Need help in porting to Oracle 12c , details on files to be modified.

I am new to PHP , have written stand alone pages using oci_connect. Tried following..

Modified DBFactory.php added case oracle by copy pasting "pgsql" case 'oracle': return new DB_oracle($db_host, $db_username, $db_password, $db_name, $persist, $db_port, $db_options); break;

Added file DB_oracle.php containing class DB_oracle extends DB

Failedin DB.php, as connection parameters is different for Oracle.

$this->dbh = new PDO(static::DB_DBO_DRIVER.":${hostpart}port=$db_port;dbname=$db_name", $db_username, $db_password, array(PDO::ATTR_PERSISTENT => ($persist ? true : false), PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); $this->command("SET NAMES 'UTF8'");

Please advice..

Thank You Sudheer

[campbell-m]

I think you'd be better off having

class DB_oracle extends DB_mysql

since, as far as I am aware, Oracle is a superset of MySQL. I think you'll need to set

  const DB_DBO_DRIVER = "oci";

[campbell-m]

It may be that you find that you can't redeclare the constant DB_DBO_DRIVER, in which case we'll need to look at restructuring the DB classes and declaring the driver as a protected property. Happy to receive a PR when you've got it working.

[Sudheert7]

This did not work class DB_oracle extends DB_mysql

copied DB_pgsql.php to DB_oracle.php

in file DB_oracle.php changed

`class DB_pgsql extends DB

const DB_DEFAULT_PORT = 5432; const DB_DBO_DRIVER = "pgsql";`

To

`class DB_oracle extends DB

const DB_DEFAULT_PORT = 1521; const DB_DBO_DRIVER = "oci"; `

https://www.php.net/manual/en/ref.pdo-oci.connection.php Connect using the Oracle Instant Client: oci:dbname=//localhost:1521/mydb

in DB.php line 95 changed

$this->dbh = new PDO( static::DB_DBO_DRIVER . ":{$hostpart}port=$db_port;dbname=$db_name", $db_username, $db_password, $options );

To

$this->dbh = new PDO("oci:dbname=//192.168.1.21:1521/orcl", $db_username, $db_password);

Connection worked. Uncaught exception 'MRBS\DBException' in D:\Devel\wamp64\www\mrbs\web\lib\MRBS\DB_oracle.php at line 241 Unexpected result from SELECT COUNT(*) query.

[campbell-m]

I think you're probably getting that uncaught exception because you have based DB_oracle on DB_pgsql instead of DB_mysql. As far as I am aware Oracle is much closer to MySQL than it is to PostgreSQL. Ideally DB_oracle needs to extend DB_mysql as I suggested. But I think that won't be possible because the driver and port are declared as constants instead of as protected properties.

Try copying DB_mysql.php to DB_oracle.php and then make your changes. If that works we can restructure the DB classes so that extending DB_mysql works.

[Sudheert7]

Try copying DB_mysql.php to DB_oracle.php and then make your changes. If that works we can restructure the DB classes so that extending DB_mysql works.

E_USER_WARNING in D:\Devel\wamp64\www\mrbs\web\lib\MRBS\DB.php at line 107 SQLSTATE[42S02]: pdo_oci_handle_factory: ORA-12154: TNS:could not resolve the connect identifier specified (ext\pdo_oci\oci_driver.c:728)

Working on it.. will update later..

[Sudheert7]

Connecting to Oracle 12c 👍 (Did not create tables)

copied DB_pgsql.php to DB_oracle.php

Used DB_pgsql.php instead of DB_mysql.php because editing DB_pgsql.php is easier then DB_mysql.php due to multiple version checks for MySQL and branched versions MariaDB, Percona.

changes on DB_oracle.php (copy of DB_pgsql.php):

class DB_oracle extends DB_mysql

const DB_DEFAULT_PORT = 1521;
const DB_DBO_DRIVER   = "oci"; 
private const MIN_VERSION   = '12';

Line 198 and 205

// Just returns a version number, eg "9.2.24"
  private function versionNumber() : string
  {
    return $this->query1('SELECT version FROM v$instance');    
  }

  // Check if a table exists
 public function table_exists(string $table) : bool
 {
   $res = $this->query1("SELECT table_name FROM user_tables", array($table));

   return !($res == -1);
 }

Changes made to DB.php

// Establish a database connection.
 //Changes: check if PDO Database driver is oci
    if (!isset($db_host) || ($db_host == "")) {
      $hostpart = "";
    } else if (static::DB_DBO_DRIVER=="oci"){
      $hostpart = "dbname=//$db_host:$db_port/$db_name";
    } else {
      $hostpart = "host=$db_host;";
    }
    $default_options = array(
      PDO::ATTR_PERSISTENT => $persist,
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    );
    // The LHS of the array + operator overrides the RHS if the keys are the same
    $options = (empty($driver_options)) ? $default_options : $driver_options + $default_options;

    //PDO For MYSQL - $this->dbh = new PDO("mysql:host= ...")
    //PDO For Oracle - $this->dbh = new PDO("oci:dbname= ...")

    if (static::DB_DBO_DRIVER=="oci"){
      $this->dbh = new PDO(
        //"oci:dbname=//192.168.125.21:1521/orcl",
        static::DB_DBO_DRIVER . ":$hostpart",
        $db_username,
        $db_password
      );
    } else {
      $this->dbh = new PDO(
        static::DB_DBO_DRIVER . ":{$hostpart}port=$db_port;dbname=$db_name",
        $db_username,
        $db_password,
        $options
      );
      $this->command("SET NAMES '" . static::DB_CHARSET . "'");       
    }  

  }

[campbell-m]

(Did not create tables)

Yes, you need to create the tables manually.

Used DB_pgsql.php instead of DB_mysql.php

Whilst you may have established a connection, I think you will come across errors when you run MRBS because you are using DB_pgsql.php instead of DB_mysql.php. That's because the PostgreSQL syntax differs from MySQL (and presumably Oracle) in places.

[campbell-m]

Just wondering if you managed to get any further with this?

[Sudheert7]

Hi, Sorry did not spend time on this,as it will take time to understand code and modify, I am using Laravel.

[campbell-m]

Is it still of interest? If so, I could modify MRBS so that it should work with Oracle, but I don't have an Oracle system to test against.

[Sudheert7]

Hi, Sorry for late reply, I switched to Laravel, many templates, modules and tutorials are available online for laravel.

Oracle cloud provides free account also they have Oracle Linux available for download. wanted to spend some time to check how to make MRBS work with oracle and postgresql dynamically in same setup by changing just driver. Due to ongoing work will not be able to spend much time. In future if possible will give a try.