v5:userguide:oracle_tutorial
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
userguide:oracle_tutorial [2015/08/30 18:01] – created mnewnham | userguide:oracle_tutorial [2015/12/27 15:20] – mnewnham | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <WRAP todo> | ||
+ | This needs fixing | ||
+ | </ | ||
+ | <WRAP right box> | ||
+ | == Author Credit == | ||
+ | (c) 2004-2014 John Lim | ||
+ | </ | ||
+ | ====== 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. | + | |
- | + | ||
- | 1. Introduction | + | |
Oracle is the most popular commercial database used with PHP. There are many ways of accessing Oracle databases in PHP. These include: | Oracle is the most popular commercial database used with PHP. There are many ways of accessing Oracle databases in PHP. These include: | ||
- | The oracle extension | + | * 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, | The wide range of choices is confusing to someone just starting with Oracle and PHP. I will briefly summarize the differences, | ||
First we have the C extensions which provide low-level access to Oracle functionality. These C extensions are precompiled into PHP, or linked in dynamically when the web server starts up. Just in case you need it, here's a guide to installing Oracle and PHP on Linux. | First we have the C extensions which provide low-level access to Oracle functionality. These C extensions are precompiled into PHP, or linked in dynamically when the web server starts up. Just in case you need it, here's a guide to installing Oracle and PHP on Linux. | ||
- | Oracle extension Designed for Oracle 7 or earlier. This is obsolete. | + | ===== Oracle extension |
- | Oci8 extension Despite it's name, which implies it is only for Oracle 8i, this is the standard method for accessing databases running Oracle 8i, 9i or 10g (and later). | + | Designed for Oracle 7 or earlier. This is obsolete. |
+ | ===== Oci8 extension | ||
+ | |||
+ | Despite it's name, which implies it is only for Oracle 8i, this is the standard method for accessing databases running Oracle 8i, 9i or 10g (and later). | ||
Here is an example of using the oci8 extension to query the emp table of the scott schema with bind parameters: | Here is an example of using the oci8 extension to query the emp table of the scott schema with bind parameters: | ||
+ | <code php> | ||
$conn = OCILogon(" | $conn = OCILogon(" | ||
Line 31: | Line 43: | ||
echo "< | echo "< | ||
} | } | ||
+ | </ | ||
+ | |||
This generates the following output: | This generates the following output: | ||
- | Array ( [0] => 7902 [1] => FORD [2] => ANALYST [3] => 7566 [4] => 03/DEC/81 [5] => 3000 [7] => 20 ) | + | |
- | Array ( [0] => 7934 [1] => MILLER [2] => CLERK [3] => 7782 [4] => 23/JAN/82 [5] => 1300 [7] => 10 ) | + | Array ( [0] => 7934 [1] => MILLER [2] => CLERK [3] => 7782 [4] => 23/JAN/82 [5] => 1300 [7] => 10 ) |
+ | | ||
We also have many higher level PHP libraries that allow you to simplify the above code. The most popular are PEAR DB and ADOdb. Here are some of the differences between these libraries: | We also have many higher level PHP libraries that allow you to simplify the above code. The most popular are PEAR DB and ADOdb. Here are some of the differences between these libraries: | ||
- | Feature PEAR DB 1.6 ADOdb | + | ^Feature^PEAR DB 1.6^ADOdb| |
- | General Style Simple, | + | |General Style|Simple, easy to use. Lacks Oracle specific functionality.|Has multi-tier design. Simple high-level design for beginners, and also lower-level advanced Oracle functionality.| |
- | Support for Prepare Yes, | + | |Support for Prepare|Yes, but only on one statement, as the last prepare overwrites previous prepares.|Yes (multiple simultaneous prepare' |
- | Support for LOBs No Yes, | + | |Support for LOBs|No|Yes, using update semantics| |
- | Support for REF Cursors No Yes | + | |Support for REF Cursors|No|Yes| |
- | Support for IN Parameters Yes Yes | + | |Support for IN Parameters|Yes|Yes| |
- | Support for OUT Parameters No Yes | + | |Support for OUT Parameters|No|Yes| |
- | Schema creation using XML No Yes, including ability to define tablespaces and constraints | + | |Schema creation using XML|No|Yes, including ability to define tablespaces and constraints| |
- | Provides database portability features No Yes, | + | |Provides database portability features|No|Yes, has some ability to abstract features that differ between databases such as dates, bind parameters, and data types.| |
- | Performance monitoring and tracing No Yes. SQL can be traced and linked to web page it was executed on. Explain plan support included. | + | |Performance monitoring and tracing|No|Yes. SQL can be traced and linked to web page it was executed on. Explain plan support included.| |
- | Recordset caching for frequently used queries No Yes. Provides great speedups for SQL involving complex where, group-by and order-by clauses. | + | |Recordset caching for frequently used queries|No|Yes. Provides great speedups for SQL involving complex where, group-by and order-by clauses.| |
- | Popularity Yes, | + | |Speed|Medium speed.|Very high speed. Fastest database abstraction library available for PHP. Benchmarks are available.| |
- | Speed Medium speed. Very high speed. Fastest database abstraction library available for PHP. Benchmarks are available. | + | |High Speed Extension available| No|Yes. You can install the optional ADOdb extension, which reimplements the most frequently used parts of ADOdb as fast C code. Note that the source code version of ADOdb runs just fine without this extension, and only makes use of the extension if detected.| |
- | High Speed Extension available No Yes. You can install the optional ADOdb extension, which reimplements the most frequently used parts of ADOdb as fast C code. Note that the source code version of ADOdb runs just fine without this extension, and only makes use of the extension if detected. | + | |
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 "/ | include "/ | ||
+ | |||
$db = NewADOConnection(" | $db = NewADOConnection(" | ||
$db-> | $db-> | ||
Line 63: | Line 80: | ||
$rs = $db-> | $rs = $db-> | ||
array(' | array(' | ||
- | while ($arr = $rs-> | + | while ($arr = $rs-> |
+ | { | ||
print_r($arr); | print_r($arr); | ||
- | echo "< | + | |
} | } | ||
+ | </ | ||
+ | |||
The Execute( ) function returns a recordset object, and you can retrieve the rows returned using $recordset-> | The Execute( ) function returns a recordset object, and you can retrieve the rows returned using $recordset-> | ||
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 110: | ||
echo "< | echo "< | ||
} | } | ||
- | + | </ | |
- | 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-> | $rs = $db-> | ||
while (!$rs-> | while (!$rs-> | ||
Line 100: | Line 122: | ||
$rs-> | $rs-> | ||
} | } | ||
+ | |||
+ | </ | ||
+ | |||
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: | ||
- | $arr = $db-> | + | |
Now to obtain only the first row as an array: | Now to obtain only the first row as an array: | ||
- | $arr = $db-> | + | |
Or to retrieve only the first field of the first row: | Or to retrieve only the first field of the first row: | ||
- | $arr = $db-> | + | |
For easy pagination support, we provide the SelectLimit function. The following will perform a select query, limiting it to 100 rows, starting from row 201 (row 1 being the 1st row): | For easy pagination support, we provide the SelectLimit function. The following will perform a select query, limiting it to 100 rows, starting from row 201 (row 1 being the 1st row): | ||
- | $offset = 200; $limitrows = 100; | + | |
- | $rs = $db-> | + | $rs = $db-> |
The $offset parameter is optional. | The $offset parameter is optional. | ||
- | Array Fetch Mode | + | ===== Prepare ===== |
- | When data is being returned in an array, you can choose the type of array the data is returned in. | + | Using Prepare() For Frequently Used Statements |
- | Numeric indexes - use $connection-> | + | 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: |
- | Associative indexes - the keys of the array are the names of the fields (in upper-case). Use $connection-> | + | |
- | Both numeric and associative indexes - use $connection-> | + | |
- | The default is ADODB_FETCH_BOTH for Oracle. | + | |
- | + | ||
- | Caching | + | |
- | + | ||
- | You can define a database cache directory using $ADODB_CACHE_DIR, | + | |
- | + | ||
- | This example will cache the following select statement for 3600 seconds (1 hour): | + | |
- | + | ||
- | $ADODB_CACHE_DIR = '/ | + | |
- | $rs = $db-> | + | |
- | 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: | + | |
- | + | ||
- | $ADODB_CACHE_DIR = '/ | + | |
- | $connection-> | + | |
- | $rs = $connection-> | + | |
- | + | ||
- | + | ||
- | 3. 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: | + | |
+ | <code php> | ||
$stmt = $db-> | $stmt = $db-> | ||
foreach ($arrayToInsert as $key => $value) { | foreach ($arrayToInsert as $key => $value) { | ||
$db-> | $db-> | ||
} | } | ||
- | + | </ | |
- | 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 162: | ||
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-> | $ok = $db-> | ||
values (aSequence.nextVal, | values (aSequence.nextVal, | ||
Line 163: | Line 168: | ||
# params: $tableName, $blobFieldName, | # params: $tableName, $blobFieldName, | ||
$db-> | $db-> | ||
+ | </ | ||
+ | |||
and the analogous CLOB example: | and the analogous CLOB example: | ||
+ | <code php> | ||
$ok = $db-> | $ok = $db-> | ||
values (aSequence.nextVal, | values (aSequence.nextVal, | ||
if (!$ok) return LogError($db-> | if (!$ok) return LogError($db-> | ||
$db-> | $db-> | ||
+ | </ | ||
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. | + | Inserting LOBs is more complicated. |
- | $sql = " | + | <code php> |
- | " | + | $sql = " |
- | " RETURNING photo INTO :xx"; | + | " |
+ | " RETURNING photo INTO :xx"; | ||
- | $stmt = $db-> | + | $stmt = $db-> |
- | $db-> | + | $db-> |
- | $blob = $db-> | + | $blob = $db-> |
- | $db-> | + | $db-> |
- | $ok = $db-> | + | $ok = $db-> |
- | $db-> | + | $db-> |
- | 5. REF CURSORs | + | </ |
+ | |||
+ | ===== 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: | ||
- | TYPE TabType IS REF CURSOR RETURN TAB%ROWTYPE; | + | |
- | + | PROCEDURE open_tab (tabcursor IN OUT TabType, | |
- | PROCEDURE open_tab (tabcursor IN OUT TabType, | + | BEGIN |
- | BEGIN | + | OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames; |
- | OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames; | + | END open_tab; |
- | END open_tab; | + | |
In ADOdb, we could access this REF Cursor using the ExecuteCursor() function. The following will find all table names that begin with ' | In ADOdb, we could access this REF Cursor using the ExecuteCursor() function. The following will find all table names that begin with ' | ||
+ | <code php> | ||
+ | $rs = $db-> | ||
+ | while ($arr = $rs-> | ||
+ | print_r($arr); | ||
+ | </ | ||
- | $rs = $db-> | ||
- | while ($arr = $rs-> | ||
The first parameter is the PL/SQL statement, and the second parameter is the name of the REF Cursor. | The first parameter is the PL/SQL statement, and the second parameter is the name of the REF Cursor. | ||
- | + | ===== In and Out Parameters | |
- | 6. In and Out Parameters | + | |
The following PL/SQL stored procedure requires an input variable, and returns a result into an output variable: | The following PL/SQL stored procedure requires an input variable, and returns a result into an output variable: | ||
- | PROCEDURE data_out(input IN VARCHAR, output OUT VARCHAR) IS | + | |
- | BEGIN | + | BEGIN |
- | output := 'I love ' | + | output := 'I love ' |
- | END; | + | END; |
+ | | ||
The following ADOdb code allows you to call the stored procedure: | The following ADOdb code allows you to call the stored procedure: | ||
+ | <code php> | ||
$stmt = $db-> | $stmt = $db-> | ||
$input = ' | $input = ' | ||
Line 217: | Line 232: | ||
$ok = $db-> | $ok = $db-> | ||
if ($ok) echo ($output == 'I love Sophia Loren' | if ($ok) echo ($output == 'I love Sophia Loren' | ||
- | PrepareSP( ) is a special function that knows about bind parameters. The main limitation currently is that IN OUT parameters do not work. | + | </ |
+ | |||
+ | [[reference: | ||
Bind Parameters and REF CURSORs | Bind Parameters and REF CURSORs | ||
- | We could also rewrite the REF CURSOR example to use InParameter | + | We could also rewrite the REF CURSOR example to use InParameter: |
+ | <code php> | ||
$stmt = $db-> | $stmt = $db-> | ||
$input = ' | $input = ' | ||
Line 228: | Line 246: | ||
$rs = $db-> | $rs = $db-> | ||
while ($arr = $rs-> | while ($arr = $rs-> | ||
- | Bind Parameters and LOBs | + | </ |
- | You can also operate on LOBs. In this example, we have IN and OUT parameters using CLOBs. | + | ===== Bind Parameters |
- | $text = 'test test test'; | + | You can also operate on LOBs. In this example, we have IN and OUT parameters using CLOBs. |
- | $sql = " | + | <code php> |
- | $stmt = $conn -> PrepareSP($sql); | + | $text = 'test test test'; |
- | $conn -> InParameter($stmt, | + | $sql = " |
- | $rs = ''; | + | $stmt = $conn -> PrepareSP($sql); |
- | $conn -> OutParameter($stmt, | + | $conn -> InParameter($stmt, |
- | $conn -> Execute($stmt); | + | $rs = ''; |
- | echo " | + | $conn -> OutParameter($stmt, |
+ | $conn -> Execute($stmt); | ||
+ | echo " | ||
+ | </ | ||
Similarly, you can use the constant OCI_B_BLOB to indicate that you are using BLOBs. | Similarly, you can use the constant OCI_B_BLOB to indicate that you are using BLOBs. | ||
- | Reusing Bind Parameters with CURSOR_SHARING=FORCE | + | ====Reusing Bind Parameters with CURSOR_SHARING=FORCE |
Many web programmers do not care to use bind parameters, and prefer to enter the SQL directly. So instead of: | Many web programmers do not care to use bind parameters, and prefer to enter the SQL directly. So instead of: | ||
- | $arr = $db-> | + | |
They prefer entering the values inside the SQL: | They prefer entering the values inside the SQL: | ||
- | $arr = $db-> | + | |
This reduces Oracle performance because Oracle will reuse compiled SQL which is identical to previously compiled SQL. The above example with the values inside the SQL is unlikely to be reused. As an optimization, | This reduces Oracle performance because Oracle will reuse compiled SQL which is identical to previously compiled SQL. The above example with the values inside the SQL is unlikely to be reused. As an optimization, | ||
- | ALTER SESSION SET CURSOR_SHARING=FORCE | + | |
This will force Oracle to convert all such variables (eg. the 7900 value) into constant bind parameters, improving SQL reuse. | This will force Oracle to convert all such variables (eg. the 7900 value) into constant bind parameters, improving SQL reuse. | ||
- | More speedup tips. | + | ===== More speedup tips ===== |
- | + | ||
- | + | ||
- | 7. Dates and Datetime in ADOdb | + | ==== Dates and Datetime in ADOdb ==== |
There are two things you need to know about dates in ADOdb. | There are two things you need to know about dates in ADOdb. | ||
Line 267: | Line 289: | ||
Secondly, since Oracle treats dates and datetime as the same data type, we decided not to display the time in the default date format. So on login, ADOdb will set the NLS_DATE_FORMAT to ' | Secondly, since Oracle treats dates and datetime as the same data type, we decided not to display the time in the default date format. So on login, ADOdb will set the NLS_DATE_FORMAT to ' | ||
+ | <code php> | ||
$db = NewADOConnection(' | $db = NewADOConnection(' | ||
$db-> | $db-> | ||
$db-> | $db-> | ||
+ | </ | ||
+ | |||
Or execute: | Or execute: | ||
- | $sql = quot;ALTER SESSION SET NLS_DATE_FORMAT = ' | + | <code php> |
+ | $sql = "ALTER SESSION SET NLS_DATE_FORMAT = ' | ||
$db-> | $db-> | ||
+ | </ | ||
+ | |||
If you are not concerned about date portability and do not use ADOdb' | If you are not concerned about date portability and do not use ADOdb' | ||
- | 8. Database Portability Layer | + | ===== Database Portability Layer ===== |
ADOdb provides the following functions for portably generating SQL functions as strings to be merged into your SQL statements: | ADOdb provides the following functions for portably generating SQL functions as strings to be merged into your SQL statements: | ||
- | Function Description | + | ^Function^Description^ |
- | DBDate($date) Pass in a UNIX timestamp or ISO date and it will convert it to a date string formatted for INSERT/ | + | |DBDate($date)|Pass in a UNIX timestamp or ISO date and it will convert it to a date string formatted for INSERT/ |
- | DBTimeStamp($date) Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp string formatted for INSERT/ | + | |DBTimeStamp($date)|Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp string formatted for INSERT/ |
- | SQLDate($date, | + | |SQLDate($date, |
- | OffsetDate($date, | + | |OffsetDate($date, |
- | Concat($s1, $s2, ...) Portably concatenate strings. Alternatively, | + | |Concat($s1, $s2, ...)|Portably concatenate strings. Alternatively, |
- | IfNull($fld, | + | |IfNull($fld, |
- | Param($name) Generates bind placeholders, | + | |Param($name)|Generates bind placeholders, |
- | $db-> | + | |$db-> |
- | $db-> | + | |$db-> |
- | $db-> | + | |$db-> |
- | $db-> | + | |$db-> |
- | $db-> | + | |$db-> |
- | $db-> | + | |$db-> |
- | $db-> | + | |$db-> |
ADOdb also provides multiple oracle oci8 drivers for different scenarios: | ADOdb also provides multiple oracle oci8 drivers for different scenarios: | ||
Line 301: | Line 330: | ||
oci8 The default high performance driver. The keys of associative arrays returned in a recordset are upper-case. | oci8 The default high performance driver. The keys of associative arrays returned in a recordset are upper-case. | ||
oci8po The portable Oracle driver. Slightly slower than oci8. This driver uses ? instead of :bindvar for binding variables, which is the standard for other databases. Also the keys of associative arrays are in lower-case like other databases. | oci8po The portable Oracle driver. Slightly slower than oci8. This driver uses ? instead of :bindvar for binding variables, which is the standard for other databases. Also the keys of associative arrays are in lower-case like other databases. | ||
+ | |||
Here's an example of calling the oci8po driver. Note that the bind variables use question-mark: | Here's an example of calling the oci8po driver. Note that the bind variables use question-mark: | ||
+ | <code php> | ||
$db = NewADOConnection(' | $db = NewADOConnection(' | ||
$db-> | $db-> | ||
$db-> | $db-> | ||
- | + | </ | |
- | 9. Connecting to Oracle | + | ===== Connecting to Oracle |
Before you can use ADOdb, you need to have the Oracle client installed and setup the oci8 extension. This extension comes pre-compiled for Windows (but you still need to enable it in the php.ini file). For information on compiling the oci8 extension for PHP and Apache on Unix, there is an excellent guide at oracle.com. | Before you can use ADOdb, you need to have the Oracle client installed and setup the oci8 extension. This extension comes pre-compiled for Windows (but you still need to enable it in the php.ini file). For information on compiling the oci8 extension for PHP and Apache on Unix, there is an excellent guide at oracle.com. | ||
- | Should You Use Persistent Connections | + | ==== Should You Use Persistent Connections |
One question that is frequently asked is should you use persistent connections to Oracle. Persistent connections allow PHP to recycle existing connections, | One question that is frequently asked is should you use persistent connections to Oracle. Persistent connections allow PHP to recycle existing connections, | ||
Line 318: | Line 349: | ||
The author' | The author' | ||
- | Connection Examples | ||
- | Just in case you are having problems connecting to Oracle, here are some examples: | ||
- | a. PHP and Oracle reside on the same machine, use default SID, with non-persistent connections: | + | ===== Error Checking |
- | + | ||
- | $conn | + | |
- | $conn-> | + | |
- | b. TNS Name defined in tnsnames.ora (or ONAMES or HOSTNAMES), eg. ' | + | |
- | + | ||
- | $conn | + | |
- | $conn-> | + | |
- | or | + | |
- | + | ||
- | | + | |
- | c. Host Address and SID | + | |
- | + | ||
- | $conn-> | + | |
- | $conn-> | + | |
- | d. Host Address and Service Name | + | |
- | + | ||
- | $conn-> | + | |
- | e. Oracle connection string: | + | |
- | + | ||
- | $cstr | + | |
- | (CONNECT_DATA=(SID=$sid)))"; | + | |
- | $conn-> | + | |
- | f. ADOdb data source names (dsn): | + | |
- | + | ||
- | $dsn = ' | + | |
- | $conn = ADONewConnection($dsn); | + | |
- | + | ||
- | $dsn = ' | + | |
- | $conn = ADONewConnection($dsn); | + | |
- | + | ||
- | $dsn = ' | + | |
- | $conn = ADONewConnection($dsn); | + | |
- | With ADOdb data source names, you don't have to call Connect( ) or PConnect( ). | + | |
- | + | ||
- | + | ||
- | + | ||
- | 10. Error Checking | + | |
The examples in this article are easy to read but a bit simplistic because we ignore error-handling. Execute( ) and Connect( ) will return false on error. So a more realistic way to call Connect( ) and Execute( ) is: | The examples in this article are easy to read but a bit simplistic because we ignore error-handling. Execute( ) and Connect( ) will return false on error. So a more realistic way to call Connect( ) and Execute( ) is: | ||
+ | <code php> | ||
function InvokeErrorHandler() | function InvokeErrorHandler() | ||
{ | { | ||
Line 377: | Line 370: | ||
echo "< | echo "< | ||
} | } | ||
+ | </ | ||
+ | |||
You can retrieve the error message and error number of the last SQL statement executed from ErrorMsg( ) and ErrorNo( ). You can also define a custom error handler function. ADOdb also supports throwing exceptions in PHP5. | You can retrieve the error message and error number of the last SQL statement executed from ErrorMsg( ) and ErrorNo( ). You can also define a custom error handler function. ADOdb also supports throwing exceptions in PHP5. | ||
- | Handling Large Recordsets | + | ===== Handling Large Recordsets |
The oci8 driver does not support counting the number of records returned in a SELECT statement, so the function RecordCount() is emulated when the global variable $ADODB_COUNTRECS is set to true, which is the default. We emulate this by buffering all the records. This can take up large amounts of memory for big recordsets. Set $ADODB_COUNTRECS to false for the best performance. | The oci8 driver does not support counting the number of records returned in a SELECT statement, so the function RecordCount() is emulated when the global variable $ADODB_COUNTRECS is set to true, which is the default. We emulate this by buffering all the records. This can take up large amounts of memory for big recordsets. Set $ADODB_COUNTRECS to false for the best performance. | ||
This variable is checked every time a query is executed, so you can selectively choose which recordsets to count. | This variable is checked every time a query is executed, so you can selectively choose which recordsets to count. | ||
- | + | ===== Resources | |
- | + | ||
- | 11. Other ADOdb Features | + | |
- | + | ||
- | Schema generation. This allows you to define a schema using XML and import it into different RDBMS systems portably. | + | |
- | + | ||
- | Performance monitoring and tracing. Highlights of performance monitoring include identification of poor and suspicious SQL, with explain plan support, and identifying which web pages the SQL ran on. | + | |
- | + | ||
- | + | ||
- | + | ||
- | 12. Download | + | |
- | + | ||
- | You can download ADOdb from sourceforge. ADOdb uses a BSD style license. That means that it is free for commercial use, and redistribution without source code is allowed. | + | |
- | + | ||
- | + | ||
- | + | ||
- | 13. Resources | + | |
Oracle' | Oracle' |
v5/userguide/oracle_tutorial.txt · Last modified: 2019/12/29 21:36 by mnewnham