can"t retrieve more than 1 record at the time
am 09.12.2009 20:39:55 von Catherine MadsenHi,
I'm really in need of help. I'm not a PHP programmer, but I've been
given the privilege of customizing a script written by somebody else and
can't get it to work right. I have to query 2 different tables in 2
different Oracle 10G DBs, one's mine (my_schema.my_table), the other
(otherdb.other_table) belongs to another department. In my_table, I
have the doc_id and the app_id for a record. In other_table there's the
doc_id and pdf to retrieve. The goal is to make a PDF file from each
BLOB in other_table and store them in the right directory under
hash(app_id). PDO has been installed and working, and I can connect to
both DBs without a problem. If my query limits the retrieval to one
record, the script works, but if I try to run it for all records, one
pdf file is correctly created in the right directory then I get the
following error: PHP Fatal error: Call to a member function fetch() on
a non-object in /my_location/my_script.php on line 154. It the "while
($stmt->fetch(PDO::FETCH_BOUND))" line. I've pasted my script below. I
thought my problem was that maybe I was in the wrong directory after
creation of the first pdf, but several tries changing the directory
didn't make a difference. Right now, I'm running the script at the
command line. Soon I'm going to have a few hundred records to deal with
and cannot do it one by one! Any help would be very much appreciated.
Thank you!
/* First Oracle DB connection info removed */
try
{
$dbh1 = new PDO("oci:dbname=".$tns1,$db1_username,$db1_password);
}
catch(PDOException $e)
{
echo ($e->getMessage());
exit;
}
if (empty($_SESSION['docIDs']))
{
$_SESSION['DOCIDs'] = array();
$_SESSION['msgs'] = array();
$sql = "SELECT COUNT(*) all_rec FROM myschema.my_table
where academic_year = 2010";
$_SESSION['numberCand'] = 0; /* initialize ctr for stack popping */
$_SESSION['PHPulled'] = 0; /* number new personal histories pulled */
if ($res = $dbh1->query($sql))
{
/* Check the number of rows that match the SELECT statement */
if ($res->fetchColumn() > 0)
{
/* Issue the real SELECT statement and work with the results */
$sql = "select doc_id, app_id
from myschema.my_table
where academic_year = 2010";
foreach ($dbh1->query($sql) as $row)
{
$cand = array();
$cand['DOC_ID']= $row['DOC_ID'];
$cand['APP_ID'] = $row['APP_ID'];
$_SESSION['DOC_IDS'][] = $cand;
}
}
}
}
if (!empty($_SESSION['DOC_IDS']) and $_SESSION['numberCand'] <
count($_SESSION['DOC_IDS']))
{
/* if have doc_IDs, pick next one off array */
$cand = $_SESSION['DOC_IDS'][$_SESSION['numberCand']];
if ($cand['DOC_ID'] == 0)
{ /* redirect to self to get next candidate with updated numberCand */
$_SESSION['numberCand'] = $_SESSION['numberCand'] + 1;
exit;
}
$dirname = md5($cand['APP_ID']);
$curdir = '/my_location/'.$dirname;
print "App_id/Dir: " . $row['APP_ID'] . $curdir . "\n";
if (!(is_dir($curdir)))
{
if (!mkdir($curdir,0775))
print "error: " . $curdir . "\n";
exit;
}
/* Second Oracle DB connection info removed */
try
{
$dbh2 = new PDO("oci:dbname=".$tns2,$db2_username,$db2_password);
}
catch(PDOException $e)
{
echo ($e->getMessage());
exit;
}
$stmt = $dbh2->prepare('select PDF from otherdb.other_table where
DOC_ID = :id');
$stmt->bindParam(':id', $cand['DOC_ID'], PDO::PARAM_INT);
$stmt->bindColumn(1, $PDF, PDO::PARAM_LOB);
$stmt->execute();
if (!(chdir($curdir)))
{
$_SESSION['msgs'][] = 'Could not change to '.$curdir;
continue;
}
$cnt = 0;
while ($stmt->fetch(PDO::FETCH_BOUND))
{
$filename = 'phs-'.$cnt.'.pdf';
if (!file_exists($filename))
{ /* if file not already there, write file with BLOB contents */
$fh = fopen($filename, 'w');
fwrite($fh, stream_get_contents($PDF));
fclose($fh);
/* add to $_SESSION['PHPulled'] for each new file saved */
$_SESSION['PHPulled'] = $_SESSION['PHPulled'] + 1;
}
/* increment stack counter */
$_SESSION['numberCand'] = $_SESSION['numberCand'] + 1;
$stmt = NULL; /* release the connection */
/*if not done with stack, redirect to self to get next*/
if (!empty($_SESSION['DOCIDs']) and
$_SESSION['numberCand'] < count($_SESSION['DOCIDs']))
{
exit;
}
}
}
/* once done, go back to display search page after clearing stack
if(isset($_SESSION['DOCIDs']))
unset($_SESSION['DOCIDs'] );*/
$res = null;
$dbh1 = null;
?>
--
Catherine Madsen
Programmer Analyst
College of Chemistry
Berkeley, CA 94720-1460
TEL: 510-643-1706
FAX: 510-643-6178
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php