Re: [Sqlrelay-discussion] php 5, oracle 9 and CLOBS

[Ionut C. <ion...@av...>]

It happened to us too. We were pretty happy at first that we could fetch=20
clobs without writing sepcial code for it.

Then we noticed exactly what's been happening to you, so now we fetch clo=
bs=20
the "right" oci way, in separate result sets with output binding.

The buffer size is ment for something else: fetching the results in separ=
ate=20
row chunks, one chunk after the other. It takes some extra-programming, b=
ut=20
it's a nice feature as it's a real memory-saver when dealing with large=20
result sets.
We also found another nifty usage for it with oracle: emulating limit and=
=20
offset in queries.

Ionut

----- Original Message -----=20
From: "Emmanuel Job" <emm...@gl...>
To: <sql...@li...>
Sent: Wednesday, May 18, 2005 1:18 AM
Subject: [Sqlrelay-discussion] php 5, oracle 9 and CLOBS


Im having problems when I retreive CLOBS from oracle and I was wondering =
if=20
anyone else noticed this. Im not using any output bindings and I find tha=
t=20
with small result sets everything works as I expect it to. However when I=
=20
increase the size of the data returned I sometimes dont get the full resu=
lt=20
set back and the application crashes. By changing the order of the column=
s=20
in my sql statement I sometimes get things working again however I can't=20
seem to find a consistent solution to retrieving large result sets that=20
contain CLOBS. I've tried messing with the result set buffer size which o=
nly=20
confuses me more because it returns only the number of rows that are aske=
d=20
for in the buffer.

Below is a code sample to show that Im not trying to do anything=20
complicated. The script usually crashes at about the 170th record in the=20
first for loop. If I change the buffer size to 50 it'll usually crash on =
the=20
51st record. Again, its all very weird because its very inconsistent.

$connection =3D sqlrcon_alloc("localhost", "9000",
    "",
    "test", "test", 0,1);

$cursor =3D sqlrcur_alloc($connection);
sqlrcur_dontGetColumnInfo($cursor);
sqlrcur_setResultSetBufferSize($cursor, 200);


// col1
$sql =3D "select id, col1, col2, col3, col4, col5  from test";

if (!sqlrcur_sendQuery($cursor, $sql)) {
    echo "Error: " . sqlrcur_errorMessage($cursor);
}

$results =3D array();
for ($i =3D 0; $i < 500; $i++) {
    echo "$i<br/>";flush();
    $results[] =3D sqlrcur_getRow($cursor, $i);
}



$count =3D 0;
foreach ($results as $result) {
    echo "ID $count: " . $result[0] . " <br/>";
    $count++;
}

echo "DONE";



I should also that I haven't used output binding because I cant seem to g=
et=20
CLOBs and other column types in the same result set.



-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
http://ads.osdn.com/?ad_idt12&alloc_id=16344&op=3Dick
_______________________________________________
Sqlrelay-discussion mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion=20