ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:oracle_tutorial

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
Next revisionBoth sides next revision
userguide:oracle_tutorial [2015/08/30 18:01] – created mnewnhamuserguide:oracle_tutorial [2015/12/26 18:18] – mnewnham
Line 1: Line 1:
 +<WRAP todo>
 +This needs fixing
 +</WRAP>
 +<WRAP right box>
 +== Author Credit ==
 +(c) 2004-2014 John Lim
 +</WRAP>
 +====== ADOdb & Oracle ======
  
 +===== Description ====
 Using ADOdb with PHP and Oracle: an advanced tutorial Using ADOdb with PHP and Oracle: an advanced tutorial
  
 +===== Introduction =====
  
-(c) 2004-2014 John Lim. All rights reserved.+Oracle is the most popular commercial database used with PHP. There are many ways of accessing Oracle databases in PHP. These include:
  
-1. Introduction +  * The oracle extension 
- +  * The oci8 extension 
-Oracle is the most popular commercial database used with PHP. There are many ways of accessing Oracle databases in PHP. These include:+  * PEAR DB library 
 +  * ADOdb library
  
-The oracle extension 
-The oci8 extension 
-PEAR DB library 
-ADOdb library 
 The wide range of choices is confusing to someone just starting with Oracle and PHP. I will briefly summarize the differences, and show you the advantages of using ADOdb. The wide range of choices is confusing to someone just starting with Oracle and PHP. I will briefly summarize the differences, and show you the advantages of using ADOdb.
  
Line 53: Line 60:
 PEAR DB is good enough for simple web apps. But if you need more power, you can see ADOdb offers more sophisticated functionality. The rest of this article will concentrate on using ADOdb with Oracle. You can find out more about connecting to Oracle later in this guide. PEAR DB is good enough for simple web apps. But if you need more power, you can see ADOdb offers more sophisticated functionality. The rest of this article will concentrate on using ADOdb with Oracle. You can find out more about connecting to Oracle later in this guide.
  
-ADOdb Example+===== ADOdb Example =====
  
 In ADOdb, the above oci8 example querying the emp table could be written as: In ADOdb, the above oci8 example querying the emp table could be written as:
  
 +<code php>
 include "/path/to/adodb.inc.php"; include "/path/to/adodb.inc.php";
 +
 $db = NewADOConnection("oci8"); $db = NewADOConnection("oci8");
 $db->Connect($tnsName, "scott", "tiger"); $db->Connect($tnsName, "scott", "tiger");
Line 63: Line 72:
 $rs = $db->Execute("select * from emp where empno>:emp order by empno", $rs = $db->Execute("select * from emp where empno>:emp order by empno",
                     array('emp' => 7900));                     array('emp' => 7900));
-while ($arr = $rs->FetchRow()) {+while ($arr = $rs->FetchRow()) 
 +{
     print_r($arr);     print_r($arr);
- echo "<hr>";+    echo "<hr>";
 } }
 +</code>
 +
 The Execute( ) function returns a recordset object, and you can retrieve the rows returned using $recordset->FetchRow( ). The Execute( ) function returns a recordset object, and you can retrieve the rows returned using $recordset->FetchRow( ).
  
 If we ignore the initial connection preamble, we can see the ADOdb version is much easier and simpler: If we ignore the initial connection preamble, we can see the ADOdb version is much easier and simpler:
  
 +<code php>
 Oci8 ADOdb Oci8 ADOdb
 $stmt = OCIParse($conn, $stmt = OCIParse($conn,
Line 89: Line 102:
  echo "<hr>";  echo "<hr>";
 } }
- +</code> 
  
-2. ADOdb Query Semantics+===== ADOdb Query Semantics =====
  
 You can also query the database using the standard Microsoft ADO MoveNext( ) metaphor. The data array for the current row is stored in the fields property of the recordset object, $rs. MoveNext( ) offers the highest performance among all the techniques for iterating through a recordset: You can also query the database using the standard Microsoft ADO MoveNext( ) metaphor. The data array for the current row is stored in the fields property of the recordset object, $rs. MoveNext( ) offers the highest performance among all the techniques for iterating through a recordset:
  
 +<code php>
 $rs = $db->Execute("select * from emp where empno>:emp", array('emp' => 7900)); $rs = $db->Execute("select * from emp where empno>:emp", array('emp' => 7900));
 while (!$rs->EOF) { while (!$rs->EOF) {
Line 100: Line 114:
  $rs->MoveNext();  $rs->MoveNext();
 } }
 +
 +</coe>
 +
 And if you are interested in having the data returned in a 2-dimensional array, you can use: And if you are interested in having the data returned in a 2-dimensional array, you can use:
  
Line 115: Line 132:
 The $offset parameter is optional. The $offset parameter is optional.
  
-Array Fetch Mode+===== Array Fetch Mode =====
  
 When data is being returned in an array, you can choose the type of array the data is returned in. When data is being returned in an array, you can choose the type of array the data is returned in.
Line 124: Line 141:
 The default is ADODB_FETCH_BOTH for Oracle. The default is ADODB_FETCH_BOTH for Oracle.
  
-Caching+===== Caching =====
  
 You can define a database cache directory using $ADODB_CACHE_DIR, and cache the results of frequently used queries that rarely change. This is particularly useful for SQL with complex where clauses and group-by's and order-by's. It is also good for relieving heavily-loaded database servers. You can define a database cache directory using $ADODB_CACHE_DIR, and cache the results of frequently used queries that rarely change. This is particularly useful for SQL with complex where clauses and group-by's and order-by's. It is also good for relieving heavily-loaded database servers.
Line 134: Line 151:
 There are analogous CacheGetArray( ), CacheGetRow( ), CacheGetOne( ) and CacheSelectLimit( ) functions. The first parameter is the number of seconds to cache. You can also pass a bind array as a 3rd parameter (not shown above). There are analogous CacheGetArray( ), CacheGetRow( ), CacheGetOne( ) and CacheSelectLimit( ) functions. The first parameter is the number of seconds to cache. You can also pass a bind array as a 3rd parameter (not shown above).
 There is an alternative syntax for the caching functions. The first parameter is omitted, and you set the cacheSecs property of the connection object: There is an alternative syntax for the caching functions. The first parameter is omitted, and you set the cacheSecs property of the connection object:
 +
 +<code php>
  
 $ADODB_CACHE_DIR = '/var/adodb/tmp'; $ADODB_CACHE_DIR = '/var/adodb/tmp';
Line 139: Line 158:
 $rs = $connection->CacheExecute($sql, array('id' => 1)); $rs = $connection->CacheExecute($sql, array('id' => 1));
    
 +</code>
 +
 +===== Prepare =====
  
-3. Using Prepare( ) For Frequently Used Statements+Using Prepare( ) For Frequently Used Statements
  
 Prepare( ) is for compiling frequently used SQL statement for reuse. For example, suppose we have a large array which needs to be inserted into an Oracle database. The following will result in a massive speedup in query execution (at least 20-40%), as the SQL statement only needs to be compiled once: Prepare( ) is for compiling frequently used SQL statement for reuse. For example, suppose we have a large array which needs to be inserted into an Oracle database. The following will result in a massive speedup in query execution (at least 20-40%), as the SQL statement only needs to be compiled once:
  
 +<code php>
 $stmt = $db->Prepare('insert into table (field1, field2) values (:f1, :f2)'); $stmt = $db->Prepare('insert into table (field1, field2) values (:f1, :f2)');
 foreach ($arrayToInsert as $key => $value) { foreach ($arrayToInsert as $key => $value) {
  $db->Execute($stmt, array('f1' => $key, 'f2' => $val);  $db->Execute($stmt, array('f1' => $key, 'f2' => $val);
 } }
- +</code> 
  
-4. Working With LOBs+===== Working With LOBs =====
  
 Oracle treats data which is more than 4000 bytes in length specially. These are called Large Objects, or LOBs for short. Binary LOBs are BLOBs, and character LOBs are CLOBs. In most Oracle libraries, you need to do a lot of work to process LOBs, probably because Oracle designed it to work in systems with little memory. ADOdb tries to make things easy by assuming the LOB can fit into main memory. Oracle treats data which is more than 4000 bytes in length specially. These are called Large Objects, or LOBs for short. Binary LOBs are BLOBs, and character LOBs are CLOBs. In most Oracle libraries, you need to do a lot of work to process LOBs, probably because Oracle designed it to work in systems with little memory. ADOdb tries to make things easy by assuming the LOB can fit into main memory.
Line 158: Line 181:
 For updating records with LOBs, the functions UpdateBlob( ) and UpdateClob( ) are provided. Here's a BLOB example. The parameters should be self-explanatory: For updating records with LOBs, the functions UpdateBlob( ) and UpdateClob( ) are provided. Here's a BLOB example. The parameters should be self-explanatory:
  
 +<code php>
 $ok = $db->Execute("insert into aTable (id, name, ablob) $ok = $db->Execute("insert into aTable (id, name, ablob)
                                 values (aSequence.nextVal, 'Name', null)");                                 values (aSequence.nextVal, 'Name', null)");
Line 163: Line 187:
 # params: $tableName, $blobFieldName, $blobValue, $whereClause # params: $tableName, $blobFieldName, $blobValue, $whereClause
 $db->UpdateBlob('aTable', 'ablob', $blobValue, 'id=aSequence.currVal'); $db->UpdateBlob('aTable', 'ablob', $blobValue, 'id=aSequence.currVal');
 +</code>
 +
 and the analogous CLOB example: and the analogous CLOB example:
  
 +<code php>
 $ok = $db->Execute("insert into aTable (id, name, aclob) $ok = $db->Execute("insert into aTable (id, name, aclob)
                                 values (aSequence.nextVal, 'Name', null)");                                 values (aSequence.nextVal, 'Name', null)");
 if (!$ok) return LogError($db->ErrorMsg()); if (!$ok) return LogError($db->ErrorMsg());
 $db->UpdateClob('aTable', 'aclob', $clobValue, 'id=aSequence.currVal'); $db->UpdateClob('aTable', 'aclob', $clobValue, 'id=aSequence.currVal');
 +</code>
 Note that LogError( ) is a user-defined function, and not part of ADOdb. Note that LogError( ) is a user-defined function, and not part of ADOdb.
  
 Inserting LOBs is more complicated. Since ADOdb 4.55, we allow you to do this (assuming that the photo field is a BLOB, and we want to store $blob_data into this field, and the primary key is the id field): Inserting LOBs is more complicated. Since ADOdb 4.55, we allow you to do this (assuming that the photo field is a BLOB, and we want to store $blob_data into this field, and the primary key is the id field):
 +<code php>
 +$sql = "INSERT INTO photos ( ID, photo) ".
 + "VALUES ( :id, empty_blob() )".
 + " RETURNING photo INTO :xx";
  
- $sql = "INSERT INTO photos ( ID, photo) ". +$stmt = $db->PrepareSP($sql); 
- "VALUES ( :id, empty_blob() )". +$db->InParameter($stmt, $id, 'id'); 
- " RETURNING photo INTO :xx";+$blob = $db->InParameter($stmt, $blob_data, 'xx',-1, OCI_B_BLOB); 
 +$db->StartTrans(); 
 +$ok = $db->Execute($stmt); 
 +$db->CompleteTrans(); 
 +</code>
  
-  $stmt = $db->PrepareSP($sql); +===== REF CURSORs =====
- $db->InParameter($stmt, $id, 'id'); +
- $blob = $db->InParameter($stmt, $blob_data, 'xx',-1, OCI_B_BLOB); +
- $db->StartTrans(); +
- $ok = $db->Execute($stmt); +
- $db->CompleteTrans(); +
-5. REF CURSORs+
  
 Oracle recordsets can be passed around as variables called REF Cursors. For example, in PL/SQL, we could define a function open_tab that returns a REF CURSOR in the first parameter: Oracle recordsets can be passed around as variables called REF Cursors. For example, in PL/SQL, we could define a function open_tab that returns a REF CURSOR in the first parameter:
v5/userguide/oracle_tutorial.txt · Last modified: 2019/12/29 21:36 by mnewnham