DB Connection in PHP

Jeyaseelansarc · #61 (**permalink**) 04-24-2008, 12:01 AM

oci_new_connect() establishes a new connection to an Oracle server and logs on. Unlike oci_connect() and oci_pconnect(), oci_new_connect() does not cache connections and will always return a brand-new freshly opened connection handle. This is useful if your application needs transactional isolation between two sets of queries.

Jeyaseelansarc · #62 (**permalink**) 04-25-2008, 06:11 AM

oci_pconnect() creates a persistent connection to an Oracle server and logs on. Persistent connections are cached and re-used between requests, resulting in reduced overhead on each page load; a typical PHP application will have a single persistent connection open against an Oracle server per Apache child process

Jeyaseelansarc · #63 (**permalink**) 04-25-2008, 06:11 AM

In PHP versions before 5.0.0 you must use ociplogon() instead. This name still can be used, it was left as alias of oci_pconnect() for downwards compatability. This, however, is deprecated and not recommended.

Jeyaseelansarc · #64 (**permalink**) 04-26-2008, 02:06 AM

oci_commit() commits all outstanding statements for the active transaction on the Oracle connection connection.

PHP Code:

  <?php

    // Login to Oracle server

    $conn = oci_connect('scott', 'tiger');

     

    // Parse SQL

    $stmt = oci_parse($conn, "

                              INSERT INTO 

                                         employees (name, surname) 

                                   VALUES 

                                         ('Maxim', 'Maletsky')

                             ");

 
    /* Execute statement

       OCI_DEFAULT tells oci_execute() 

       not to commit statement immediately */

    oci_execute($stmt, OCI_DEFAULT);

 
    /*

    ....

    Parsing and executing other statements here ...

    ....

    */

    

    // Commit transaction

    $committed = oci_commit($conn);

 
    // Test whether commit was successful. If error occurred, return error message

    if (!$committed) {

        $error = oci_error($conn);

        echo 'Commit failed. Oracle reports: ' . $error['message'];

    }

 
?>

Jeyaseelansarc · #65 (**permalink**) 04-26-2008, 02:07 AM

oci_rollback() rolls back all outstanding statements for Oracle connection connection.

Jeyaseelansarc · #66 (**permalink**) 04-28-2008, 05:31 AM

oci_define_by_name() defines PHP variables for fetches of SQL-Columns. Take into consideration that Oracle uses ALL-UPPERCASE column names, whereby in your select you can also use lowercase.

If you need to define an abstract datatype (LOB/ROWID/BFILE) you must allocate it first using oci_new_descriptor().

Jeyaseelansarc · #67 (**permalink**) 04-28-2008, 05:32 AM

oci_define_by_name() example

PHP Code:

  <?php

/* oci_define_by_name example - thies at thieso dot net (980219) */

 
$conn = oci_connect("scott", "tiger");

 
$stmt = oci_parse($conn, "SELECT empno, ename FROM emp");

 
/* the define MUST be done BEFORE oci_execute! */

 
oci_define_by_name($stmt, "EMPNO", $empno);

oci_define_by_name($stmt, "ENAME", $ename);

 
oci_execute($stmt);

 
while (oci_fetch($stmt)) {

    echo "empno:" . $empno . "\n";

    echo "ename:" . $ename . "\n";

}

 
oci_free_statement($stmt);

oci_close($conn);

?>

Jeyaseelansarc · #68 (**permalink**) 04-28-2008, 05:33 AM

oci_new_descriptor() allocates resources to hold descriptor or LOB locator. Valid values for type are: OCI_D_FILE, OCI_D_LOB and OCI_D_ROWID.

Jeyaseelansarc · #69 (**permalink**) 04-28-2008, 05:34 AM

oci_bind_by_name() binds the PHP variable variable to the Oracle placeholder ph_name. Whether it will be used for input or output will be determined at run-time and the necessary storage space will be allocated.

If you need to bind an abstract datatype (LOB/ROWID/BFILE) you need to allocate it first using the oci_new_descriptor() function. The length is not used for abstract datatypes and should be set to -1. The type parameter tells Oracle which descriptor is used. Possible values are:

OCI_B_FILE - for BFILEs;

OCI_B_CFILE - for CFILEs;

OCI_B_CLOB - for CLOBs;

OCI_B_BLOB - for BLOBs;

OCI_B_ROWID - for ROWIDs;

OCI_B_NTY - for named datatypes;

OCI_B_CURSOR -
for cursors, that were created before with oci_new_cursor().

Jeyaseelansarc · #70 (**permalink**) 04-28-2008, 05:35 AM

oci_bind_by_name() example

PHP Code:

  <?php

    $connection = oci_connect('apelsin','kanistra');

    $query = "INSERT INTO test_table VALUES(:id, :text)";

 
    $statement = oci_parse($query);

    oci_bind_by_name($statement, ":id", 1);

    oci_bind_by_name($statement, ":text", "trailing spaces follow     ");

    oci_execute($statement);

    /*

     This code will insert into DB string 'trailing spaces follow', without

     trailing spaces

    */

?>

Jeyaseelansarc · #71 (**permalink**) 04-28-2008, 05:35 AM

Do not use magic_quotes_gpc or addslashes() and oci_bind_by_name() simultaneously as no quoting is needed and any magically applied quotes will be written into your database as oci_bind_by_name() is not able to distinguish magically added quotings from those added intentionally.

Jeyaseelansarc · #72 (**permalink**) 04-28-2008, 11:50 PM

oci_new_cursor() allocates a new statement handle on the specified connection.

Using REF CURSOR in an Oracle's stored procedure

PHP Code:

  <?php   

// suppose your stored procedure info.output returns a ref cursor in :data

 
$conn = oci_connect("scott", "tiger");

$curs = oci_new_cursor($conn);

$stmt = oci_parse($conn, "begin info.output(:data); end;");

 
oci_bind_by_name($stmt, "data", $curs, -1, OCI_B_CURSOR);

oci_execute($stmt);

oci_execute($curs);

 
while ($data = oci_fetch_row($curs)) {

    var_dump($data);

}

 

oci_free_statement($stmt);

oci_free_statement($curs);

oci_close($conn);

?>

Jeyaseelansarc · #73 (**permalink**) 04-28-2008, 11:51 PM

Here Using REF CURSOR in an Oracle's select statement

PHP Code:

  <?php   

echo "<html><body>";

$conn = oci_connect("scott", "tiger");

$count_cursor = "CURSOR(select count(empno) num_emps from emp " .

                "where emp.deptno = dept.deptno) as EMPCNT from dept";

$stmt = oci_parse($conn, "select deptno,dname,$count_cursor");

 
oci_execute($stmt);

echo "<table border=\"1\">";

echo "<tr>";

echo "<th>DEPT NAME</th>";

echo "<th>DEPT #</th>";

echo "<th># EMPLOYEES</th>";

echo "</tr>";

 
while ($data = oci_fetch_assoc($stmt)) {

    echo "<tr>";

    $dname  = $data["DNAME"];

    $deptno = $data["DEPTNO"];

    echo "<td>$dname</td>";

    echo "<td>$deptno</td>";

    oci_execute($data["EMPCNT"]);

    while ($subdata = oci_fetch_assoc($data["EMPCNT"])) {

        $num_emps = $subdata["NUM_EMPS"];

        echo  "<td>$num_emps</td>";

    }

    echo "</tr>";

}

echo "</table>";

echo "</body></html>";

oci_free_statement($stmt);

oci_close($conn);

?>

Jeyaseelansarc · #74 (**permalink**) 04-28-2008, 11:53 PM

oci_new_collection() uses to allocate new collection object in Oracle.

Jeyaseelansarc · #75 (**permalink**) 04-28-2008, 11:54 PM

oci_server_version() returns a string with version information of the Oracle server, which uses connection connection or returns FALSE on error.

Example

PHP Code:

  <?php

    $conn = oci_connect("scott", "tiger");

    echo "Server Version: " . oci_server_version($conn);

    oci_close($conn);

?>

Jeyaseelansarc · #76 (**permalink**) 04-29-2008, 11:47 PM

in oracle via php oci_internal_debug() function enables or disables internal debug output.

Jeyaseelansarc · #77 (**permalink**) 04-29-2008, 11:48 PM

oci_password_change() Changes password of Oracle's user

Jeyaseelansarc · #78 (**permalink**) 04-29-2008, 11:49 PM

oci_statement_type() returns the query type of statement statement as one of the following values:

SELECT

UPDATE

DELETE

INSERT

CREATE

DROP

ALTER

BEGIN

DECLARE

UNKNOWN

Jeyaseelansarc · #79 (**permalink**) 04-29-2008, 11:49 PM

oci_statement_type() example

PHP Code:

  <?php

    $conn = oci_connect("scott", "tiger");

    $sql  = "delete from emp where deptno = 10";

   

    $stmt = oci_parse($conn, $sql);

    if (oci_statement_type($stmt) == "DELETE") {

        die("You are not allowed to delete from this table<br />");

    }

   

    oci_close($conn);

?>