Hướng dẫn php connect oracle database

Review the code in $HOME/public_html/bind.php

$myeid is ' . $myeid . '
';
  print '';
  while ($row = oci_fetch_array($s, OCI_RETURN_NULLS+OCI_ASSOC)) {
    print '';
    foreach ($row as $item) {
      print '';
    }
    print '';
  }
  print '
'.($item?htmlentities($item):' ').'
';
}

// Create connection to Oracle
$c = oci_connect("phphol", "welcome", "//localhost/orcl");

// Use bind variable to improve resuability, 
// and to remove SQL Injection attacks.
$query = 'select * from employees where employee_id = :eidbv';
$s = oci_parse($c, $query);

$myeid = 101;
oci_bind_by_name($s, ":EIDBV", $myeid);
oci_execute($s);
do_fetch($myeid, $s);

// Redo query without reparsing SQL statement
$myeid = 104;
oci_execute($s);
do_fetch($myeid, $s);

// Close the Oracle connection
oci_close($c);

?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phphol/bind.php

The $myeid variable is bound to the :eidbv bind variable so when the query is re-executed the new value of $myeid is passed to the query. This allows you to execute the statement again, without reparsing it with the new value, and can improve performance of your code.

If you don't see the returned rows, you may have deleted these employees in the web application part of the tutorial. Use SQL*Plus to query the EMPLOYEE_ID column of the EMPLOYEES table, and edit bind.php to use IDs that exist in the table.

Start SQL*Plus and create a new table:

sqlplus phphol/welcome
create table mytable (col1 date);

Review the code in $HOME/public_html/trans_rollback.php

The OCI_DEFAULT parameter overrides the basic behavior of oci_execute().

From your Web browser, enter the following URL to display the output:

http://localhost/~phphol/trans_rollback.php

This script inserts a row into the table.

Because there is no automatic or explicit commit, the data is rolled back by PHP when the script finishes. To see that the data has not been committed, query the table to see if there are any inserted rows. From your SQL*Plus session, enter the following commands to select any rows from the mytable table:

select to_char(col1, 'DD-MON-YY HH:MI:SS') time from mytable;

Review the code in $HOME/public_html/trans_autocommit.php

This script differs from trans1.php in that there is no OCI_DEFAULT when the data is inserted.  This means the new data is committed by the oci_execute() call.

From your Web browser, enter the following URL to display the output:

http://localhost/~phphol/trans_autocommit.php

The data is now committed.

From your SQL*Plus session, enter the following command to select any rows from the mytable table:

select to_char(col1, 'DD-MON-YY HH:MI:SS') time from mytable;

If you reloaded the PHP script more than once, a row from each execution is inserted.

You can compare the performance difference between committing each row individually versus at the end of the transaction.

To test the difference, review the code in $HOME/public_html/trans_time_autocommit.php

This code commits on each insert.

";
}
function do_delete($conn)
{
  $stmt = "delete from mytable";
  $s = oci_parse($conn, $stmt);
  $r = oci_execute($s);
}

// Program starts here
$c = oci_connect("phphol", "welcome", "//localhost/orcl");

$starttime = microtime(TRUE);
for ($i = 0; $i < 10000; $i++) {
  do_insert($c);
}
$endtime = microtime(TRUE) - $starttime;
echo "Time was ".round($endtime,3)." seconds
";

do_row_check($c);  // Check insert done
do_delete($c);     // cleanup committed rows

?>

Load the URL http://localhost/~phphol/trans_time_autocommit.php several times and see how long it takes to insert the 10,000 rows.

The bigger row count shown in the first query is from the previous insertion in trans_autocommit.php.

Note: Your time values may differ depending on the hardware resources you are using.

Now review the trans_time_explicit.php script. The only difference in this script is that in the do_insert() function OCI_DEFAULT has been added so it doesn't automatically commit, and an explicit commit has been added after the insertion loop:

...

function do_insert($conn) {
  $stmt = "insert into mytable values (to_date('01-JAN-08 10:20:35', 
      'DD:MON:YY HH24:MI:SS'))";
  $s = oci_parse($conn, $stmt);
  $r = oci_execute($s, OCI_DEFAULT);  // Don't commit
}

...

$starttime = microtime(TRUE);
for ($i = 0; $i < 10000; $i++) {
  do_insert($c);
}
oci_commit($c);
$endtime = microtime(TRUE) - $starttime;

...

Load the URL http://localhost/~phphol/trans_time_explicit.php. The insertion time is less.

In general you want all or none of your data committed. Doing your own transaction control has performance and data-integrity benefits.

Note: Your time values may differ depending on the hardware resources you are using.

Start SQL*Plus and create a new table, ptab with the following command:

sqlplus phphol/welcome
create table ptab (mydata varchar(20), myid number);

In SQL*Plus, create a stored procedure, myproc, to insert data into the ptab table, with the following commands:

create or replace procedure
myproc(d_p in varchar2, i_p in number) as
begin
  insert into ptab (mydata, myid) values (d_p, i_p);
end;
/

Review the code in $HOME/public_html/proc.php

From a Web browser, enter the following URL to display the output:

http://localhost/~phphol/proc.php

The PHP script has created a new row in the ptab table by calling the stored procedure myproc. The table ptab has a new row with the values "mydata" and 123.

Switch to your SQL*Plus session and query the table to show the new row:

select * from ptab;

Extend proc.php to use a bind variable. Change proc.php to the following (changes are in bold):

:bv)");
$v = 456;
oci_bind_by_name($s, ":bv", $v);
oci_execute($s);
echo "Completed";

?>

The oci_bind_by_name() function binds the PHP variable $v to ":bv" and experiment changing the value inserted by changing the value in $v.

Rerun the following URL:

http://localhost/~phphol/proc.php

Switch to your SQL*Plus session and query the table again to show the new row:

PL/SQL stored functions are also commonly used in Oracle. In SQL*Plus, create a PL/SQL stored function myfunc()to insert a row into the ptab table, and return double the inserted value:

create or replace function
myfunc(d_p in varchar2, i_p in number) return number as
begin
  insert into ptab (mydata, myid) values (d_p, i_p);
  return (i_p * 2);
end;
/

Review the code in $HOME/public_html/func.php

\n";
echo "Completed";

?>

Because a value is being returned, the optional length parameter to oci_bind_by_name() is set to 10 so PHP can allocate the correct amount of memory to hold up to 10 digits

Rerun the following URL:

http://localhost/~phphol/func.php

Review the SQL in $HOME/public_html/fetch_prefetch.sql

set echo on
drop table bigtab;
create table bigtab (mycol varchar2(20));
begin
  for i in 1..20000
  loop
   insert into bigtab (mycol) values (dbms_random.string('A',20));
  end loop;
end;
/
commit;
exit

This script creates a table with a large number of rows. From your sqlplus session, run the following:

connect phphol/welcome

@fetch_prefetch

Review the code in $HOME/public_html/fetch_prefetch.php

require('helper.php');

function do_prefetch($c, $pf)
{
  $stid = oci_parse($c, "select mycol from bigtab");
  oci_execute($stid);
  oci_set_prefetch($stid, $pf);
  oci_fetch_all($stid, $res);
  return $res;
}

$c = oci_connect("phphol", "welcome", "//localhost/orcl");
$pf_a = array(1, 10, 500, 2000); // Prefetch values to test 
foreach ($pf_a as $pf_num)
{
  $start = currTime();
  $r = do_prefetch($c, $pf_num);
  $t = elapsedTime($start);
  print "Prefetch $pf_num - Elapsed time is: " . round($t, 3) .
        " seconds
\n";
}

?>

This performs the same query with different prefetch sizes. Prefetching is a form of internal row buffering. The number of rows in the buffer is the prefetch value. The larger the prefetch value, the fewer the number of physical database access are needed to return all data to PHP, because each underlying physical request to the database returns more than one row. This can help improve performance. PHP code does not need to change to handle different prefetch sizes. The buffering is handled by Oracle code.

The included script helper.php contains the simple currTime() and elapsedTime() timing functions, as well as some additional setup functions used later.

Load the following URL to display the output:

http://localhost/~phphol/fetch_prefetch.php

Reload a few times to see the average times. Your time values may differ depending on your hardware resources, and so on.

The default prefetch size can be set in PHP's initialization file, php.ini. Prior to PHP 5.3, the default prefetch size was 10 rows. In 5.3, it is 100 rows. You should choose a suitable default value for your application, and use oci_set_prefetch() for specific queries that need a different value.

When using Oracle Database 11g Release 2 client libraries, row prefetching also benefits fetching from REF CURSORS.

This section shows the oci_bind_array_by_name() function that allows a PHP array to be retrieved from, or passed to, a PL/SQL procedure.

Review the SQL in $HOME/public_html/fetch_bulk.sql

set echo on
create or replace package fetchperfpkg as
  type arrtype is table of varchar2(20) index by pls_integer;
  procedure selbulk(p1 out arrtype);
end fetchperfpkg;
/
create or replace package body fetchperfpkg as
  procedure selbulk(p1 out arrtype) is
  begin
   select  mycol bulk collect
     into  p1
     from  bigtab;
  end selbulk;
end fetchperfpkg;
/
show errors
exit

This script creates a PL/SQL package that fetches from BIGTAB using a PL/SQL BULK COLLECT statement, and returns the results in a PL/SQL array. From your sqlplus session, run the following:

sqlplus phphol/welcome
@fetch_bulk

Review the code in $HOME/public_html/fetch_bulk.php

require('helper.php');

function do_sel_bulk($c)
{
  $s = oci_parse($c, "begin fetchperfpkg.selbulk(:a1); end;");
  oci_bind_array_by_name($s, ":a1", $res, 20000, 20, SQLT_CHR);
  oci_execute($s);
  return($res);
}

$c = oci_connect("phphol", "welcome", "//localhost/orcl");

$start = currTime();
$r = do_sel_bulk($c);
$t = elapsedTime($start);
print "Bulk collect - Elapsed time is: " . round($t, 3) . " seconds\n
";

?>    

This code calls the PL/SQL package and binds a PHP variable to hold the returned data array. No OCI8 fetch call is needed.

Load the following URL to display the output:

http://localhost/~phphol/fetch_bulk.php

Reload a few times to see the average times.

Array binding is a useful technique to reduce database overhead when inserting or retrieving data.

This example doesn't print the returned results. If you want to see them, add "var_dump($res);" before the function return statement. The output shows the random 20-character data strings created by fetch_prefetch.sql, which you ran earlier.

Before doing this section create a table to store a BLOB. In SQL*Plus logged in as phphol, execute the following commands:

sqlplus phphol/welcome
create table btab (blobid number, blobdata blob);

Review the code in $HOME/public_html/blobins.php

if (!isset($_FILES['lob_upload'])) {
// If nothing uploaded, display the upload form
?>
      method="POST" enctype="multipart/form-data">
Image filename: 




} // closing brace from 'if' in earlier PHP code
else {
  // else script was called with data to upload

  $myblobid = 1; // should really be a unique id e.g. a sequence number

  $conn = oci_connect("phphol", "welcome", "//localhost/orcl");
  // Delete any existing BLOB
  $query = 'delete from btab where blobid = :myblobid';
  $stmt = oci_parse ($conn, $query);
  oci_bind_by_name($stmt, ':myblobid', $myblobid);
  $e = oci_execute($stmt);
  // Insert the BLOB from PHP's temporary upload area
  $lob = oci_new_descriptor($conn, OCI_D_LOB);
  $stmt = oci_parse($conn, 'insert into btab (blobid, blobdata) '
        .'values(:myblobid, empty_blob()) returning blobdata into :blobdata');
  oci_bind_by_name($stmt, ':myblobid', $myblobid);
  oci_bind_by_name($stmt, ':blobdata', $lob, -1, OCI_B_BLOB);
  oci_execute($stmt, OCI_DEFAULT);  // Note OCI_DEFAULT
  if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
    oci_commit($conn);
    echo "BLOB uploaded";
  }
  else {
    echo "Couldn't upload BLOB\n";
  }
  $lob->free();
}
?>

This shows HTML code embedded in multiple PHP blocks. In particular, a PHP 'if' statement encloses the HTML code.The first time the script is loaded, the HTML upload form is shown. PHP has populated the form action name to call the same script again.

There is a direct relationship between the HTML form name name="lob_upload" and the special PHP variable $_FILES['lob_upload']. When the form is called with data, the script deletes any existing image from the table, and inserts the new picture.

The script shows the use of oci_new_descriptor() which is bound to the empty_blob() location. The LOB->savefile() method inserts the picture to the newly created row. Note the OCI_DEFAULT option to oci_execute() is necessary for the subsequent LOB method to work.

From your Web browser, enter the following URL to display the output:

http://localhost/~phphol/blobins.php

It shows a Web form with Browse and Upload buttons. Click Browse.

Select oracle.jpg from the /home/phphol/public_html directory and click Open.

Click Upload.

The form action calls the script a second time, but now the special variable $_FILES['lob_upload'] is set and picture is uploaded. The successful echo message is displayed.

The image has been uploaded to the Web server.

To show the image, review the code in $HOME/public_html/blobview.php

load();

header("Content-type: image/JPEG");
echo $result;

oci_close($conn);

?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phphol/blobview.php

Make sure there is no whitespace before and no echo statements in the script, because otherwise the wrong HTTP header will be sent and the browser won't display the image properly. If you have problems, comment out the header() function call and see what is displayed.

From your Web browser, enter the following URL to display the basic output:

http://localhost/~phphol/app_info.php

Review the SQL in $HOME/public_html/app_info.sql

col sql_text format a20
col module format a10
col action format a20

select sql_text, module, action
from v$sqlarea
where module = 'Home Page';

exit

This script shows SQL statements previously executed in the database by the Home Page module.

Open a terminal window and enter the following commands to run the SQL script.

sqlplus phphol/welcome
@app_info.sql

The SQL*Plus script shows the meta data is saved with the statement. The meta data can be used for tracing or identifying the location in a PHP application of a misbehaving SQL statement. Note only the application information from the first execution of a statement is stored in V$SQLAREA.

Review the code in $HOME/public_html/ajax_id.php

This file simply echoes the parameter passed in.

if (!isset($_GET['id'])) {
  $id = 'No id passed';
} 
else {
  $id = $_GET['id']; 
}

echo "Id was: ", htmlentities($id); 

?>

From your browser, enter the following URL to display the output:

http://localhost/~phphol/ajax_id.php?id=185

Review the code in $HOME/public_html/ajax_id.html

This file contains a JavaScript function, makeRequest().

  



Employee Lookup

This page finds an employee

From your browser, enter the following URL to display the output:

http://localhost/~phphol/ajax_id.html

Click OK to dismiss the alert window.

Note: if you use Internet Explorer, you will need to edit ajax_id.html and change the XMLHttpRequest() call to ActiveXObject("Msxml2.XMLHTTP") or ActiveXObject("Microsoft.XMLHTTP").

When the HTML page is loaded, the makeRequest() javascript function is called. It prepares an XMLHttpRequest request to call ajax_id.php. A callback function onreadystatechange is set. Finally the request is sent to the webserver asynchronously.

When the callback function is notified that the web server request has returned, the output from ajax_id.php is displayed by the alert() function. In web application, the Java script could be invoked by various
events and could be made to alter the content of the current page.

Edit ajax_id.html and change 185 to 186.

Reload it in the browser. The new value is displayed. Click OK to dismiss the alert window.

Note: You may also need to flush the browser cache to see the changed value.

Binds PHP array to Oracle PL/SQL array by name

Binds the PHP variable to the Oracle placeholder

Cancels reading from cursor

Closes Oracle connection

Commits outstanding statements

Establishes a connection to the Oracle server

Uses a PHP variable for the define-step during a SELECT

Returns the last error found

Executes a statement

Fetches all rows of result data into an array

Returns the next row from the result data as an associative or numeric array, or both

Returns the next row from the result data as an associative array

Returns the next row from the result data as an object

Returns the next row from the result data as a numeric array

Fetches the next row into result-buffer

Checks if the field is NULL

Returns the name of a field from the statement

Tell the precision of a field

Tell the scale of the field

Returns the size of the field

Tell the raw Oracle data type of the field

Returns data type of the field

Frees all resources associated with statement or cursor

Enables or disables internal debug output

Allocates new collection object

Establishes a new connection to the Oracle server

Allocates and returns a new cursor (statement handle)

Initializes a new empty LOB or FILE descriptor

Returns the number of result columns in a statement

Returns number of rows affected during statement execution

Prepares Oracle statement for execution

Changes password of Oracle's user

Connect to an Oracle database using a persistent connection

Returns a field's value from a fetched row

Rolls back outstanding transaction

Returns server version

Sets number of rows to be prefetched

Returns the type of an OCI statement