Re: [PHP] can't retrieve more than 1 record at the time still
Thank you so much for sending me in the right direction. I've re-written the first part of the script using PDO statements and it works great. Catherine Richard Quadling wrote: 2009/12/14 Catherine Madsen : Hi Again, Following the suggestions I received from a earlier post, I've closed the statement while ($stmt->fetch(PDO::FETCH_BOUND)) before $stmt = NULL; I don't get an error anymore, but still retrieve only one record. To check that the array was being filled correctly, I inserted in several places $ncand = count($_SESSION['numberCand']); $ndocid = count($_SESSION['DOCIDs']); and the count is always 0 for $ndocid and 1 for $ncand. Now I believe that the array is not being filled, and that's why only the last record gets processed. Can anybody see what I'm doing wrong in the for each statement? The updated code is below. Thank you for your help. Catherine Earlier message: 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! getMessage()); exit; } if (empty($_SESSION['docIDs'])) { $_SESSION['DOCIDs'] = array(); $_SESSION['msgs'] = array(); $sql = "SELECT COUNT(*) all_rec FROM myschema.mytable 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.mytable 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; } } $ncand = count($_SESSION['numberCand']); $ndocid = count($_SESSION['DOCIDs']); print "Cand Number: " . $ncand . "\n"; print "DOCid Number: " . $ndocid . "\n"; } } if (!empty($_SESSION['DOC_IDS']) and $_SESSION['numberCand'] < count($_SESSION['DOC_IDS'])) { $ncand = count($_SESSION['numberCand']); $ndocid = count($_SESSION['DOCIDs']); print "Cand Number: " . $ncand . "\n"; print "DOCid Number: " . $ndocid . "\n"; /* 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 PERSONAL_HIST_PDF_CONTENT from otherdb.other_table where DOC_ID = :id'); $stmt->bindParam(':id', $cand[
Re: [PHP] can't retrieve more than 1 record at the time still
2009/12/14 Catherine Madsen : > Hi Again, > > Following the suggestions I received from a earlier post, I've closed the > statement > while ($stmt->fetch(PDO::FETCH_BOUND)) > before $stmt = NULL; > I don't get an error anymore, but still retrieve only one record. > > To check that the array was being filled correctly, I inserted in several > places $ncand = count($_SESSION['numberCand']); > $ndocid = count($_SESSION['DOCIDs']); > and the count is always 0 for $ndocid and 1 for $ncand. > Now I believe that the array is not being filled, and that's why only the > last record gets processed. Can anybody see what I'm doing wrong in the for > each statement? The updated code is below. > Thank you for your help. > Catherine > > > Earlier message: > 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.mytable > 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.mytable > 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; > > } > } > $ncand = count($_SESSION['numberCand']); > $ndocid = count($_SESSION['DOCIDs']); > print "Cand Number: " . $ncand . "\n"; > print "DOCid Number: " . $ndocid . "\n"; > } > } > > if (!empty($_SESSION['DOC_IDS']) and $_SESSION['numberCand'] < > count($_SESSION['DOC_IDS'])) > { > $ncand = count($_SESSION['numberCand']); > $ndocid = count($_SESSION['DOCIDs']); > print "Cand Number: " . $ncand . "\n"; > print "DOCid Number: " . $ndocid . "\n"; > > /* 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) > { >
[PHP] can't retrieve more than 1 record at the time still
Hi Again, Following the suggestions I received from a earlier post, I've closed the statement while ($stmt->fetch(PDO::FETCH_BOUND)) before $stmt = NULL; I don't get an error anymore, but still retrieve only one record. To check that the array was being filled correctly, I inserted in several places $ncand = count($_SESSION['numberCand']); $ndocid = count($_SESSION['DOCIDs']); and the count is always 0 for $ndocid and 1 for $ncand. Now I believe that the array is not being filled, and that's why only the last record gets processed. Can anybody see what I'm doing wrong in the for each statement? The updated code is below. Thank you for your help. Catherine Earlier message: 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! getMessage()); exit; } if (empty($_SESSION['docIDs'])) { $_SESSION['DOCIDs'] = array(); $_SESSION['msgs'] = array(); $sql = "SELECT COUNT(*) all_rec FROM myschema.mytable 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.mytable 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; } } $ncand = count($_SESSION['numberCand']); $ndocid = count($_SESSION['DOCIDs']); print "Cand Number: " . $ncand . "\n"; print "DOCid Number: " . $ndocid . "\n"; } } if (!empty($_SESSION['DOC_IDS']) and $_SESSION['numberCand'] < count($_SESSION['DOC_IDS'])) { $ncand = count($_SESSION['numberCand']); $ndocid = count($_SESSION['DOCIDs']); print "Cand Number: " . $ncand . "\n"; print "DOCid Number: " . $ndocid . "\n"; /* 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 PERSONAL_HIST_PDF_CONTENT 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();