PHP :: Request #27485 :: OCI clob column objects inconsistent in result set

[2004-03-03 16:58 UTC] jseverson at myersinternet dot com

Description:
------------
When handling clob columns in Oracle, PHP is inconsistent in the way that it returns the result set of your query containing that clob column when the clob is null. In one case, the result set contains an Object, and in another case, the result set doesn't contain any Object, even though in both cases, the VALUE of the object after load() is "empty" (column is null).

We believe what causes these two different cases is that in the case of an Object being returned, the table being queried had multiple (3) clob columns. The other case when no Object was returned, the table being queried had only one (1) clob column.

This makes it nearly impossible to handle your result set data since you can't call the load() function if the object doesn't exist without getting an error, but on the other hand, you can't check whether or not the clob is empty because an Object is present even when the Object has no value.

It seems like the correct behavior would be to always return an Object, so that you don't have to first check whether or not the Object is empty or not, before called the load() OCI function.

Reproduce code:
---------------
//table_a has one clob and clob is null
$sql = "select * from test_schema.table_a where primary_key=1";
$stmt = OCIParse($conn_ora, $sql);
OCIExecute($stmt, OCI_DEFAULT);
OCIFetchInto($stmt, $table_a_row, OCI_ASSOC);
OCIFreeStatement($stmt);

echo "<pre>";
var_dump($table_a_row);

//table_b has multiple clobs (3) and all clobs are null
$sql = "select * from test_schema.table_b where primary_key=1";
$stmt = OCIParse($conn_ora, $sql);
OCIExecute($stmt, OCI_DEFAULT);
OCIFetchInto($stmt, $table_b_row, OCI_ASSOC);
OCIFreeStatement($stmt);

echo "<pre>";
var_dump($table_b_row);

Expected result:
----------------
array(1) {
  ["CLOB_1"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(9) of type (oci8 descriptor)
  }
}
array(3) {
  ["CLOB_1"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(10) of type (oci8 descriptor)
  }
  ["CLOB_2"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(11) of type (oci8 descriptor)
  }
  ["CLOB_3"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(12) of type (oci8 descriptor)
  }
}

------------------  OR  -----------------

array(0) {
}
array(0) {
}


Actual result:
--------------
array(0) {
}
array(3) {
  ["CLOB_1"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(9) of type (oci8 descriptor)
  }
  ["CLOB_2"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(10) of type (oci8 descriptor)
  }
  ["CLOB_3"]=>
  object(OCI-Lob)(1) {
    ["descriptor"]=>
    resource(11) of type (oci8 descriptor)
  }
}

[2004-03-03 18:58 UTC] cjbj at hotmail dot com

What does using
  OCIFetchInto($stmt, $table_a_row, OCI_ASSOC+OCI_RETURN_NULLS);
give?

[2004-03-03 19:27 UTC] jseverson at myersinternet dot com

That does make it so that the var_dump at least shows the null clob column being captured in my result set, but the value of that column is "Null" rather than an Object:

array(1) {
  ["CLOB_1"]=>
  NULL
}

[2004-03-04 01:59 UTC] tony2001@php.net

I can't get your results with this code.
In both cases I get empty arrays, if OCI_RETURN_NULLS wasn't used or array of NULLs, if it was.
Tested with PHP5-cvs, PHP4-cvs, PHP4.3.3.

[2004-03-04 12:22 UTC] jseverson at myersinternet dot com

Can you please try PHP version 4.3.4? We looked at the CVS log for the OCI changes done and it looks like a lot of work was done in December, 2003 dealing with LOBs, which wouldn't be present in 4.3.3.

Thanks

[2004-03-05 02:42 UTC] tony2001@php.net

The same results(i.e. allright) with 4.3.4.
By the way, PHP 4.3.4 was released before all these changes you're talking about, in the early november, 2003.

[2004-03-05 14:36 UTC] jseverson at myersinternet dot com

After several more hours of investigating, we have determined that our first hypothesis was not true. I am not quite sure this is still a PHP bug or not, as it seems like is more a case of a behavior of Oracle. Here is a script I've setup to demonstrate the behavior:

http://test1.myersinternet.com/php_test/test_clobs.html

To briefly summarize, a clob can exist in two states, both of which APPEAR to be "null" when viewing the clob in TOAD, TORA, or SQLPLUS. In one state, the clob is actually "null", which is the case where the column is ommitted completely when doing an insert. The second case, the clob is an "empty locator", which is the case when specifying the column in the insert, using the empty_clob() function, but then not performing a save on the oci descriptor. My script demonstrates both of these cases and their output.

Oracle Documentation explaining this behavior:

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76940/adl02bs5.htm#117091

"You can set an internal LOB -- that is, a LOB column in a table, or a LOB attribute in an object type defined by you-- to be NULL or empty:

Setting an Internal LOB to NULL: A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other datatypes.

Setting an Internal LOB to Empty: By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, you get back a locator which you can use to populate the LOB with data via one of the six programmatic environments, such as OCI or PL/SQL(DBMS_LOB)."

It would be NICE if PHP had a method of saving null into your descriptor so that you can keep your clobs consistent, since the presence of empty locators behave differently when selecting from them as compared with null clobs.

[2004-03-05 18:43 UTC] derick@php.net

I would classify this as a feature request then, assinging to the the maintainer, Antony.

[2004-04-19 09:05 UTC] tony2001@php.net

>It would be NICE if PHP had a method of saving null into your descriptor
>so that you can keep your clobs consistent, since the presence of empty
>locators behave differently when selecting from them as compared with null clobs.
there is no need in such method, NULL clobs/blobs/anything alse can be saved using SQL:
INSERT INTO clobs_table (...., clob_field) VALUES(....,NULL);

Patches

Pull Requests

History

	php.net \| support \| documentation \| report a bug \| advanced search \| search howto \| statistics \| random bug \| login
go to bug id or search bugs for


Copyright © 2001-2024 The PHP Group All rights reserved.	Last updated: Sun May 05 13:01:30 2024 UTC