The prepare and execute methods are used when the SQL commandneeds to be executed multiple times or when PL/SQL procedures need to be executed. These methods are also needed when the script has to deal with complex data types such as LOB types or dynamic cursors. The syntax is shown as follows:

This syntax is simple enough, but it does not explain what the prepare method is really needed for.  Another example is shown as follows:

$SQL=”insert into TBL values (:C1,:C2,:C3)”;
$data=array( array(“a1”,”a2”,”a3”),
                      array(“b1”,”b2”,”b3”),
                      array(“c1”,”c2”,”c3”),
                      array(“d1”,”d2”,”d3”));
$stmt=$db->Prepare($SQL);
foreach($data as $row) {
     $db->Execute($stmt,array(“C1”=>$row[0],
                                                “C2”=>$row[1],
                                                “C3”=>$row[2]));
}

In this example, the expensive “parse” call is executed only once as a result of the prepare method.  The statement is parsed once, but executed multiple times; once for each element of the array, $data.  Normally, for statements executed only once, Prepare() is not needed.  Preparing SQL statements is done for two reasons: to eliminate the unnecessary and expensive parse calls and to use complex binds.

The next situation in which the prepare and execute methods are needed is when executing a stored procedure with output parameters.  To show how this is accomplished, a PL/SQL procedure with an output parameter is needed. The procedure below is one such procedure:

Example 28

create or replace
procedure cleanup(job_desc in varchar2, rows_cleaned out number)
as
begin
delete from emp where job like job_desc;
rows_cleaned:=SQL%ROWCOUNT;
end;
/

The procedure takes two arguments: one input, the job to remove from the EMP table; and one output, the number of rows that have been deleted. The PHP5 script that uses the procedure above is Example 26, slightly modified. The script also introduces several methods for transaction handling:

Example 28 (Cont.)

#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
require('adodb/adodb-exceptions.inc.php');
$DSN="oci8://scott:tiger@local"; 

$SQL="begin
         cleanup(:job,:numrec);
      end;";
$job="C%";
$numrec=-1; 

print "Cleanup deleted $numrec records.\n";
$db->CommitTrans();
?>

$db->InParameter($statement,$variable,$placeholder,$max_length,$type);
$db->OutParameter($statement,$variable,$placeholder,$max_length,$type);

If the maximum length is not specified, it defaults to 4000, the maximum size of the Oracle VARCHAR2 type. The “$type” variable is database type, inherited from the OCI8 module (ADOdb uses OCI8 the same way PEAR DB does) and is normally specified for LOB types only. These two methods are based on the oci_bind_by_name()function discussed in Chapter 3.

In the script above, the statement

binds the variable $numrec with the placeholder  :numrec. When this script is executed, the result is completely predictable:

SQL>  select count(*) from emp;
  COUNT(*)
----------
        14 

SQL>  !example28.php
Cleanup deleted 4 records. 

SQL>  select count(*) from emp;
  COUNT(*)
----------
        10 

SQL>

There is another method available called Parameter(), which is deprecated according to the documentation, and a low level method called “bind” which is, more or less identical to the oci_bind_by_name() method.

The two remaining methods, CommitTrans() and RollbackTrans()are self explanatory. They terminate a transaction by issuing a commit or rollback, respectively.

At the beginning of this section, it was noted that prepare and execute are used for avoiding parse calls when executing SQL multiple times, for calling stored procedures with OUT parameters and for dealing with special data types like LOB data and REF cursors. ADOdb libray has specific methods for dealing with these situations. The methods are PrepareSP() and ExecuteCursor(). These methods will be demonstrated in our next example.

Chapter 3 presented Example 17 dealing with REF cursors by using the oci_bind_by_name() function from the OCI8 bundled module. Example 17 calls the PL/SQL procedure PHP_DEMO1.DEMO1, which took two arguments, the first one was an input argument called “title”, and the second one was the output parameter storing a REF cursor. In the PHP script, the procedure was executed, returned a REF cursor, which was then executed and the results retrieved and printed out. The source for the PHP_DEMO1.DEMO1 package is available in Chapter 3.  Example 17 is rewritten below using the ADOdb library, using the methods mentioned above.

Example 29

#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
require('adodb/adodb-exceptions.inc.php'); 

$DSN="oci8://scott:tiger@local";
$title="CLERK";
$SQL="begin
         php_demo1.demo1(:title,:csr);
      end;"; 

This script is a few lines shorter and much cleaner than the OCI8 version, and yet it does exactly the same thing. There are a few new methods which are Oracle-specific.  The PrepareSP() method prepares a call to stored procedure (thus the letters ”SP” at the end) and is supported only for Oracle and MySQL databases. The method invoked to execute SQL is not Execute(), but ExecuteCursor(). The syntax for the PrepareSP() method is extremely simple:

This method is meant only for parsing calls to PL/SQL procedures with the REF cursor or LOB arguments. The ExecuteCursor() method was created specially to support REF cursor  handling. The syntax is, as in the case with PrepareSP() method, extremely simple:

The returning value is a result set which contains the result of the executed cursors, bound to the placeholder which is passed to the ExecuteCursor() method as the second argument.  This result set can be handled the same way queries are normally handled with ADOdb.  The last thing to notice in this example is the use of

to retrieve only the array indexed by numbers and not the column names. This is the same thing that was accomplished by setting the fetch mode to OCI_NUM in Example 17.

The next case which requires the use of the PrepareSP() and InParameter()or OutParameter()methods is handling LOB columns. As was said before, ADOdb is based on the existing PHP5 functions used to support various databases. For Oracle RDBMS, there is the OCI8 module.  This means that for handling LOB columns the InParameter() or OutParameter() methods would be used like this:

$db->InParameter($stmt,$clob,'clob',-1,OCI_B_CLOB);
                                       - or -
$db->OutParameter($stmt,$clob,'clob',-1,OCI_B_CLOB);

So far this section has been demonstrating the handling of LOB columns by showing the HTML form to upload a text file into SCOTT.POETRY table, created in Chapter 3. In Chapter 4, the login form was rewritten to use PEAR DB. There is no need to rewrite it again, because PEAR DB and ADOdb use the same form of DSN. In other words, the database connection can be shared between a script that uses PEAR DB and a script that uses ADOdb.

The only line in Example23.php that needs changing is the one that reads:

Predictably, the line will be changed to:

The file “pear_file.html” is a HTML form with two HTML fields and the file “pear_file.php” in the “action” part of the <FORM> tag. If a copy is made of “pear_file.html” and then called “adodb_file.html”, it can be changed in such a way to invoke “adodb_file.php” instead of “pear_file.php”. So, in order to change Example 23 into Example 30, the only thing to rewrite is the file “pear_file.php”. Here it is:

Example 30

<html>
<head>
  <title>Upload File (ADOdb)</title>
</head> 

<body>
  <?php
  require_once('adodb/adodb.inc.php');
  require_once('adodb/adodb-exceptions.inc.php');
  session_start();
  $db=NewADOConnection("oci8");
  $DSN=$_SESSION['DSN'];
 

  $INS="insert into poetry(file_desc,file_data)
        values (:pdesc,:cont)";
  $clob=file_get_contents($_FILES['file']['tmp_name']); 

  <center>
    <h2>File <?=$_FILES['file']['name']?>
     uploaded successfully!</h2>
  </center>
</body>
</html>
 

There are only three things worth noting in this script:

Binding of a CLOB column to the placeholder. This is done almost exactly the same way as in the oci_bind_by_name() function from the OCI8 module. The OCI_B_CLOB flag is defined in the OCI8 module.

Using the PrepareSP method instead of Prepare(). When binding LOB variables or using OUT parameters, the “SP” variety of the prepare method must be used.

Using the getTraceAsString() method from the Exception class  in the “die” function instead of the getMessage() method. Why is that?  There are several database operations in the “try” block”; one connect, two binds and an execute. If an Oracle error occurs, it is be nice to know the exact line in the script where the error appeared, and the circumstances of the error.  Below is the output of the getTraceAsString() method. The error was caused by deliberately changing the name of the “pdesc” placeholder into “desc” which is short for “describe”, a reserved word.

The above result reveals that the Oracle error ORA-1745 occurred at line 26 of the script “adodb_file.php”.  This error is shown below: 

$ oerr ora 1745
01745, 00000, "invalid host/bind variable name"
// *Cause:
// *Action:

So, this method reveals information about the nature and location of the error, which is very useful for debugging.  The “oerr” command is available on most of  Oracle installations (it is not available on MS Windows). For details about the “oerr” command, please see the Oracle Concepts manual.

Sometimes, when the LOB value is large, the same method used to insert a LOB value in Chapter 3 has to be used with ADOdb. The LOB column was first initialized by inserting an empty LOB by using the empty_clob() PL/SQL method and then updating it by using a special bind. This method is also supported by ADOdb.

ADOdb has updateBLOB(), updateCLOB() and updateBLOBFile() methods to perform updates for various types of  LOB columns.

Semantics of those methods is shown as follows:

$db->updateCLOB($table_name,
                               $LOB_column_name,
                               $LOB_column_value,
                               $where condition);

In case of the POETRY table, the whole thing would look like this:

insert into POETRY(FILE_DESC,FILE_DATA
                 values(:PDESC,empty_clob());
 

$db->updateCLOB('POETRY',
                                'FILE_DATA',
                                $clob,
                                'FILE_ID=POEM_ID_SEQ.CURRVAL');

 To understand this example, it is important to remember the schema structure laid out in Chapter 3. The FILE_ID column is populated by a “BEFORE INSERT trigger which uses the sequence POEM_ID_SEQ to generate the next number.

The other two ADODdb methods to update LOB values behave the same way as updateCLOB() and have the same syntax.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_2005_2_php_oracle.htm