can't retrieve more than 1 record at the time

[Catherine Madsen <cmadsen@xxxxxxxxxxxx>]

Hi,

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!

<?php

/* 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