ADG_REDIRECT_DML in Oracle Active Data Guard 19c

on
0

Using PHP with Oracle database

on

Purpose
This tutorial shows you how to use PHP with Oracle Database 10g/11g.

Overview
PHP is a popular web scripting language, and is often used to create database-driven web sites.

Prerequisites
 - installed Oracle database 10g/11g
 - installed Apache
 - installed php5 
 - oci8 extension - write at the end of the file php.ini: extension=oci8.so
 - in the case, that you want to connect to the database as sysdba you need to also allow this possibility. Modify file php.ini: oci8.privileged_connect = On

Using PHP OCI8 with Oracle Database

 1. Create connection to the Oracle database with functions for querying and executing commands in the database:
  • oci_connect() 
    • contains username, password and connect string to the database
  • oci_close() 
    • close the connection. Any standard connections not explicitly closed are automatically released when the script ends.
  • PHP code of the script dbUtil.php (this script should be called always at the beginnig of the php page)

<?php
class DBUtil {
private $connection;
public function __construct() {
$this->connection = oci_connect(<login_name>, <password>,<connect_string>);

//example:
//$this->connection = oci_connect('hr','hr','(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=51521))(CONNECT_DATA=(SERVICE_NAME = orcl)))');

if (!$this->connection) {
   $e = oci_error();
   var_dump($e);
}
}

public function __destruct() {
if ($this->connection) {
oci_close($this->connection);
}
    }  
   
    public function query($sql, $printSql = false) {
    if ($this->connection) {
    // Prepare the statement
    $stid = oci_parse($this->connection, $sql);

  if($printSql) {
echo $sql;
}

if (!$stid) {
   $e = oci_error($this->connection);
   var_dump($e);
}

// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
   $e = oci_error($stid);
   var_dump($e);
}

$result = array();

  while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
  $result[] = $row;
}
     
oci_free_statement($stid);
return $result;
    }
}

    public function execute($sql, $printSql = false) {
    if ($this->connection) {
    // Prepare the statement
$stid = oci_parse($this->connection, $sql);

if($printSql) {
echo $sql;
}

if (!$stid) {
   $e = oci_error($this->connection);
   var_dump($e);
}

// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
   $e = oci_error($stid);
   var_dump($e);
}
return $r;
    }
}
}

?>


  • now you can call this script and use your database in your php page
<?php
require_once 'dbUtil.php';
$db = new DBUtil();
?>


  • how to query your database
<?php

require_once 'dbUtil.php';
$db = new DBUtil();

$sql_query = $db -> query("select username from dba_users");

foreach ($sql_query as $rownum=>$row) {
print $row['USERNAME']."<br />";
}
?>


  • how to execute query in your database. Table will be created under user which was used for the connection to the database, otherwise you have to specify the schema user.
<?php

require_once 'dbUtil.php';
$db = new DBUtil();

//create table test
$sql = $db -> execute("create table test (name char(20))");

if ($sql == true) {

print "<p>Table created.</p>";

//insert rows to the table test
$sql = $db -> execute("insert into test(name) values ('test_name_1')");
$sql = $db -> execute("insert into test(name) values ('test_name_2')");

//check, if the rows were inserted
$query = $db -> query("select name from test");
print "<p>Result from the table test: <br />";
foreach ($query as $rownum=>$row) {
print $row['NAME']."<br />";
}
print "</p>";

//drop table test
$sql = $db -> execute("drop table test");
if ($sql == true) {
print "<p>Table dropped</p>";
}
}

?>

    Comments

    Post a Comment