Roger Spears wrote:
I'm using PHP/PEAR to execute my queries on an oracle table. I'm trying
to store very long pieces of text into a CLOB field. Currently I'm
getting a string too long error.
I've tried:
OCIBindByName($sql,:fieldName,$myVariable, -1);
But I'm not sure I'm using it correctly with PEAR. In my code, I have
my sql statement, then I do the OCIBindByName() call, then I do my insert.
But, this gets me:
ocibindbyname(): supplied argument is not a valid OCI8-Statement resource
Roger,
PEAR DB is not particularly LOB friendly.
Below is a quick CLOB example using PHP's OCI8 API.
If you need to use PEAR DB because of existing code commitments, there
is a hack that might work. In PEAR DB, the PHP OCI8 connection can be
retrieved and used to call PHP OCI8 functions directly. Accessing
PEAR's internals is not something to be done lightly. It may cause
short term problems and not be portable in the long term. I do not
recommend it.
Using this method, my sample script would be coded something like:
require_once(DB.php);
$db = DB::connect(oci8://scott:[EMAIL PROTECTED]);
// Delete any existing CLOB
$stid = $db-prepare('DELETE FROM MYCLOBTAB');
$db-execute($stid);
// Now upload lob
$conn = $db-connection; // Bad!!
$lob = OCINewDescriptor($conn, OCI_D_LOB);
... Code similar to that below ...
// Now query the uploaded CLOB and display it
... More PEAR DB calls using $db-whatever()
An alternative is to use a package like ADOdb. See
http://phplens.com/lens/adodb/docs-adodb.htm
Chris
-
?php
//
// Sample form to upload and insert data into an ORACLE CLOB column
// using PHP's Oracle 8 API.
//
// Based on http://www.php.net/manual/en/function.ocinewdescriptor.php
// modified to work on CLOBs and using register_globals = Off.
//
// Before running this script, execute these statements in SQL*Plus:
// drop table myclobtab;
// create table myclobtab (c1 number, c2 clob);
//
// Make sure php.ini's value for upload_max_filesize is large enough
// for the largest lob to be uploaded.
//
// Tested with PHP 4.3.3 against Oracle 9.2.0.4
//
if (!isset($_FILES['lob_upload'])) {
?
form action=?php echo $_SERVER['PHP_SELF']; ? method=POST
enctype=multipart/form-data
Upload file: input type=file name=lob_upload
input type=submit value=Upload
/form
?php
}
else {
$myid = 1; // should really be a unique id e.g. a sequence number
$conn = OCILogon('scott', 'tiger', 'mydb');
// Delete any existing CLOB so the query at the bottom
// displays the new data
$query = 'DELETE FROM MYCLOBTAB';
$stmt = OCIParse($conn, $query);
OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
OCIFreeStatement($stmt);
// Insert the CLOB from PHP's tempory upload area
$lob = OCINewDescriptor($conn, OCI_D_LOB);
$stmt = OCIParse($conn, 'INSERT INTO MYCLOBTAB (C1, C2) VALUES('.
$myid . ', EMPTY_CLOB()) RETURNING C2 INTO :C2');
OCIBindByName($stmt, ':C2', $lob, -1, OCI_B_CLOB);
OCIExecute($stmt, OCI_DEFAULT);
// The function $lob-savefile(...) reads from the uploaded file.
// If the data was already in a PHP variable $myv, the
// $lob-save($myv) function could be used instead.
if ($lob-savefile($_FILES['lob_upload']['tmp_name'])) {
OCICommit($conn);
echo Clob successfully uploaded\n;
}
else {
echo Couldn't upload Clob\n;
}
$lob-free();
OCIFreeStatement($stmt);
// Now query the uploaded CLOB and display it
$query = 'SELECT C2 FROM MYCLOBTAB WHERE C1 = '.$myid;
$stmt = OCIParse ($conn, $query);
OCIExecute($stmt, OCI_DEFAULT);
OciFetchInto($stmt, $arr, OCI_ASSOC);
$result = $arr['C2']-load();
echo 'pre';
echo $result;
echo '/pre';
OCIFreeStatement($stmt);
OCILogoff($conn);
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
