|
|
|
|
Bookmark this on Delicious
Share on Facebook
 Slashdot It!
Digg  ora_do
Parse, Exec, Fetch
(PHP 4, PHP 5 <= 5.0.5)
Code Examples / Notes » ora_dorussell
Remember that this function includes an implicit ora_fetch (so the first row is on the cursor, if it exists). I think this problem has probably gotten "everyone" at one time or another... at least once... :-) A fairly generic retrieval segment that, given an SQL query in $query (and with the proper Oracle credentials), will perform a database query on your database and return the results in a single variable: // Null out results array $results = array(); putenv( "ORACLE_HOME=/path/to/oracle/productdir/version" ); $ora_conn = ora_logon( "${oracle_user}@${oracle_db}", $oracle_pw ); // Parse, exec and fetch... $ora_cur = ora_do( $ora_conn, $query ); if ( $ora_cur ){ $numCols = ora_numcols( $ora_cur ); // Figure out how many columns // Get the first fetched row and put it in to our array... $row = array(); for( $i = 0; $i < $numCols; $i++ ){ // Loop through columns array_push( $row, ora_getcolumn( $ora_cur, $i ) ); } array_push( $results, $row ); // Fetch rows, one at a time, putting them in their own // array. Each row should be appended to the array of // results.. while ( ora_fetch( $ora_cur ) ){ // Get each row $row = array(); for( $i = 0; $i < $numCols; $i++ ){ // Loop through columns array_push( $row, ora_getcolumn( $ora_cur, $i ) ); } array_push( $results, $row ); } } ora_logoff( $ora_conn ); // Result set is now in an array of rows, each containing an // array of columns if ( count( $results ) ){ print_r( $results ); } else { print( "No rows returned.\n" ); } Note: for a null result set, ora_do() may also drop a warning depending on how you have error reporting configured in the script or your PHP.INI file. You might want to include: // Turn off error reporting to the user - let me handle // it myself... error_reporting( 0 ); And make sure you properly handle all errors yourself (such as failues on ora_conn() as well as ora_do()). owend
I couldn't figure out how this worked until I looked at the PHP source code. The documentation does not mention that what ora_do returns is in fact a cursor, not just an integer true/false flag. So the following snippet illustrates how I have been using it: $cur = ora_do($conn, $query); $foo = ora_getcolumn($cur,0); ... etc ... bakerst
As far as I'm concerned, this ora_do function should not be used in most cases. I was unable to catch errors when using this function, b/c it does the first fetch for you before you can even check if there's anything to fetch. Therefore, if there's a chance you won't get records back when running your query, using this function will not make it any easier for you. The best way to retrieve your data is to use this process: $strThis = ora_logon("username@sid", "password"); $my_cursor = ora_open($my_db_conn); ora_parse($my_cursor, $my_query, 0); ora_exec($my_cursor); then, so you don't get the "NO DATA FOUND" error from oracle when trying to fetch 0 records, check if it is possible to fetch the first record: if (ora_fetch($my_cursor)) { do soemthing; } else { raise an error (query returned no records); } don't forget, when you do the check, if the result is true, then you just fetched the first record. so if you try to do another fetch afterwards, that will not be the first record, it will be the second record. so use this method if it works for you. easy, huh? |
