php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #2807 LOB descriptors with Fetch/Result and FetchInto
Submitted: 1999-11-23 22:52 UTC Modified: 2006-04-03 09:22 UTC
Votes:7
Avg. Score:4.1 ± 0.8
Reproduced:4 of 4 (100.0%)
Same Version:1 (25.0%)
Same OS:0 (0.0%)
From: robert dot everett at wcom dot com Assigned: maxim (profile)
Status: Not a bug Package: Feature/Change Request
PHP Version: 4.0 OS: Solaris 2.6
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: robert dot everett at wcom dot com
New email:
PHP Version: OS:

 

 [1999-11-23 22:52 UTC] robert dot everett at wcom dot com
1. When selecting a row containing more than one LOB, the descriptors for all but the
first LOB are freed after the fetches are complete.

2. When selecting multiple rows containing a LOB, descriptors are only created for one
row and reused. Therefore, the descriptors are only valid for the last row after the fetches
are complete. In addition, if each row contains more than one LOB, the above condition also applies.

This becomes a problem when trying to create an array of the result set for later user since all but one of
the LOB descriptors are no longer valid. LOB descriptors must be used *immediately* when fetching
a row or they can't be used at all.

Unless using the OCI_RETURN_LOBS flag, LOB descriptors should be created for
each row and shouldn't be freed until explicitly freed by the user. At the very least,
a flag should be created that will allow this behavior (OCI_RETURN_ALL_LOCATORS?).

Here are the details:

PHP 3.0.12
Apache 1.3.9.2
Oracle 8.0.4
Solaris 2.6

---- Fetch/Result ----

Code:

     putenv("ORACLE_HOME=/oracle/home");
     putenv("ORACLE_SID=sid");
     
     OCIInternalDebug(1);
     
     $conn = OCINLogon("test", "pass", "sid");
     
     $sql = "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)";
     $stmt = OCIParse($conn, $sql);
     OCIExecute($stmt);
     
     $rows = 0;
     $cols = OCINumCols($stmt);
     while (OCIFetch($stmt)) {
       for ($i = 1; $i < $cols + 1; $i++) {
           $colname = OCIColumnName($stmt, $i);
           $select[$colname][$rows] = OCIResult($stmt, $i);
       }
       $rows++;
     }

     for ($i = 0; $i < $rows; $i++) {
       echo "ID: ".$select["ID"][$i]."<br>\n";
       echo "Clob: ".$select["CLOB"][$i]->load()."<br>\n"; // always value for last row; only one descriptor created
       echo "Blob: ".$select["BLOB"][$i]->load()."<br>\n"; // fails; descriptor already freed
     }
     
     OCIFreeStatement($stmt);
     OCILogoff($conn);
     
Output (debug statements/warnings prefixed with *):

     *OCIDebug: oci8_open_server new conn=2000 dname=sid
     *OCIDebug: oci8_open_user new sess=1000 user=test
     *OCIDebug: oci8_do_connect: id=1
     *OCIDebug: oci8_parse "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)" id=2 conn=1
     *OCIDebug: OCIExecute: new descriptor for CLOB
     *OCIDebug: OCIExecute: new descriptor for BLOB
     *OCIDebug: oci8_free_descr: 3c5498
     ID: 1
     *OCIDebug: OCIloaddesc: size=14
     Clob: blah blah blah
     *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx
     Blob:
     ID: 2
     *OCIDebug: OCIloaddesc: size=14
     Clob: blah blah blah
     *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx
     Blob: 
     *OCIDebug: _oci8_free_stmt: id=2 last_query="SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)"
     *OCIDebug: _oci8_close_conn: id=1
     *OCIDebug: oci8_free_descr: 3c5508
     
---- FetchInto ----

Code:

     putenv("ORACLE_HOME=/oracle/home");
     putenv("ORACLE_SID=sid");

     OCIInternalDebug(1);

     $conn = OCINLogon("test", "pass", "sid");

     $sql = "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)";
     $stmt = OCIParse($conn, $sql);
     OCIExecute($stmt);
     
     $rows = 0;
     while (OCIFetchInto($stmt, $select[$rows], OCI_ASSOC)) {
       $rows++;
     }
     
     for ($i = 0; $i < $rows; $i++) {
       echo "ID: ".$select[$i]["ID"]."<br>\n";
       echo "Clob: ".$select[$i]["CLOB"]->load()."<br>\n"; // always value for last row; only one descriptor created
       echo "Blob: ".$select[$i]["BLOB"]->load()."<br>\n"; // fails; descriptor already freed
     }
     
     OCIFreeStatement($stmt);
     OCILogoff($conn);
     
Output (debug statements/warnings prefixed with *):

     *OCIDebug: oci8_open_server new conn=2000 dname=sid
     *OCIDebug: oci8_open_user new sess=1000 user=test
     *OCIDebug: oci8_do_connect: id=1
     *OCIDebug: oci8_parse "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)" id=2 conn=1
     *OCIDebug: OCIExecute: new descriptor for CLOB
     *OCIDebug: OCIExecute: new descriptor for BLOB
     *OCIDebug: oci8_free_descr: 3e328c
     ID: 1
     *OCIDebug: OCIloaddesc: size=14
     Clob: blah blah blah
     *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx
     Blob:
     ID: 2
     *OCIDebug: OCIloaddesc: size=14
     Clob: blah blah blah
     *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx
     Blob:
     *OCIDebug: _oci8_free_stmt: id=2 last_query="SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)"
     *OCIDebug: _oci8_close_conn: id=1
     *OCIDebug: oci8_free_descr: 3e32fc

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [1999-11-24 10:31 UTC] thies at cvs dot php dot net
that's the way it's designed. 

and i'm not sure if it can be changed - if you could provide me with some docs how it's done i might have a look at it.

moving to Feature/Change request.

 [2001-02-10 13:53 UTC] jimw@php.net
refiling against 4.0.
 [2003-01-29 09:13 UTC] maxim@php.net
well, since this is not assigned to anyone in the last two years, I will mark it on myself as a reminder.
 [2006-04-03 09:22 UTC] tony2001@php.net
See also bug #30804.	
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Fri Sep 24 04:03:36 2021 UTC