Call to a member function prepare() on a non-object in PDO using PHP

Question

I ma using PHP's to communicate with an Oracle 11g Database. This code is intended for a user to upload an .xls or .csv file. If .xls, then convert to .csv. If .csv, continue. Afterward, it takes the csv and updates an oracle database. In the past, I have used sql injection without realizing the security disadvantages associated with it. So, I began to learn to use PDO. However, I am very new. The code I have designed (below) gets this error:

Fatal error: Call to a member function prepare() on a non-object in /opt/apache/servers/planninganddesign/htdocs/JointUse/update_handler.php on line 89 

Line 89 is marked, below.

Code:

<?php
require_once('Classes/PHPExcel/IOFactory.php'); 
require_once("mcl_Oci.php");
?>

<br>

<?php

    $filename = $_FILES['uploaded']['name'];
    $file = $_FILES['uploaded']['tmp_name'];
    $ext = pathinfo($filename, PATHINFO_EXTENSION);
    if($ext='xls') {



        $inputFileType = 'Excel5';
        $inputFileName = $file;

        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcelReader = $objReader->load($inputFileName);

        $loadedSheetNames = $objPHPExcelReader->getSheetNames();

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcelReader, 'CSV');

        foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
                $objWriter->setSheetIndex($sheetIndex);
                $objWriter->save('abc.csv');}
        $files = fopen('abc.csv', 'r'); 
            if($files) {
                    while (($line = fgetcsv($files)) !== FALSE) {
                    $csv_array[] = array_combine(range(1, count($line)), array_values($line));
            }           echo 'Your .xls file was uploaded successfully. Have a nice day.';
        }
        elseif ($ext='csv'){
            while (($line = fgetcsv($files)) !== FALSE) {
                    $csv_array[] = array_combine(range(1, count($line)), array_values($line));
            }   echo 'Your .csv file was uploaded successfully. Have a nice day.';
        }
        else {
            echo "This is not an accepted file type. Please save as either '*.csv' or '*.xls' and re-upload.";
        }
    }


    $ArrayNumber = 0;   

    while ($csv_array["$ArrayNumber"] != NULL) {

    $ArrayNumber = $ArrayNumber + 1;
    $RECEIVED=$csv_array["$ArrayNumber"]["1"];
    $REGION=$csv_array["$ArrayNumber"]["2"];
    $PERMIT=$csv_array["$ArrayNumber"]["3"];
    $CUSTOMER=$csv_array["$ArrayNumber"]["4"];
    $TO_BUSINESS=$csv_array["$ArrayNumber"]["5"];
    $TO_PERMIT=$csv_array["$ArrayNumber"]["6"];
    $POWER_ADDRESS=$csv_array["$ArrayNumber"]["7"];
    $COMPANY=$csv_array["$ArrayNumber"]["8"];
    $MAP_SECTION = $csv_array["$ArrayNumber"]["9"];
    $POLES = $csv_array["$ArrayNumber"]["10"];
    $FEE = $csv_array["$ArrayNumber"]["11"];
    $INVOICE = $csv_array["$ArrayNumber"]["12"];
    $DATE_LARRY = $csv_array["$ArrayNumber"]["13"];
    $DAYS_LARRY = $csv_array["$ArrayNumber"]["14"];
    $PI_PON = $csv_array["$ArrayNumber"]["15"];
    $DATE_PD = $csv_array["$ArrayNumber"]["16"];
    $DATE_LARRY_PD = $csv_array["$ArrayNumber"]["17"];
    $RETURNED = $csv_array["$ArrayNumber"]["18"];
    $DAYS_PD = $csv_array["$ArrayNumber"]["19"];
    $ERRORS_PD = $csv_array["$ArrayNumber"]["20"];
    $ERRORS_MULTIPLE = $csv_array["$ArrayNumber"]["21"];
    $DATE_MAKEREADY_JU = $csv_array["$ArrayNumber"]["22"];
    $DATE_MAKEREADY_COMPLETE = $csv_array["$ArrayNumber"]["23"];
    $DAYS_MAKEREADY = $csv_array["$ArrayNumber"]["24"];
    $DAYS_JU_PROCES = $csv_array["$ArrayNumber"]["25"];
    $APPROVAL = $csv_array["$ArrayNumber"]["26"];
    $DAYS_PERMIT_IN_JU = $csv_array["$ArrayNumber"]["27"];
    $DATE_PLANNING = $csv_array["$ArrayNumber"]["28"];
    $DAYS_OPEN = $csv_array["$ArrayNumber"]["29"];
    $DATE_ROUTED = $csv_array["$ArrayNumber"]["30"];
    $DAYS_TOTAL = $csv_array["$ArrayNumber"]["31"];
    $COMMENTS = $csv_array["$ArrayNumber"]["32"];

    $conn = oci_connect("user", "pass", "(description=(address=(protocol=tcp)(host=host)(port=1533))(connect_data=(service_name=name)))");
    $sql = 'INSERT INTO INTOXDM.JOINT_USE (RECEIVED, REGION, PERMIT, CUSTOMER, TO_BUSINESS, TO_PERMIT, POWER_ADDRESS, COMPANY, MAPSECTION, POLES, FEE, INVOICE, DATE_LARRY, DAYS_LARRY, PI_PON, DATE_PD, DAYS_LARRY_PD, RETURNED, DAYS_PD, ERRORS_PD, ERRORS_MULTIPLE, DATE_MAKEREADY_JU, DATE_MAKEREADY_COMPLETE, DAYS_MAKEREADY, DAYS_JU_PROCES, APPROVAL, DAYS_PERMIT_IN_JU, DATE_PLANNING, DAYS_OPEN, DATE_ROUTED, DAYS_TOTAL, COMMENTS)
         VALUES (:RECEIVED, :REGION, :PERMIT, :CUSTOMER, :TO_BUSIENSS, :TO_PERMIT, :POWER_ADDRESS, :COMPANY, :MAP_SECTION, :POLES, :FEE, :INVOICE, :DATE_LARRY, :DAYS_LARRY, :PI_PON, :DATE_PD, :DATE_LARRY_PD, :RETURNED, :DAYS_PD, :ERRORS_PD, :ERRORS_MULTIPLE, 
        :DATE_MAKEREADY_JU, :DATE_MAKEREADY_COMPLETE, :DAYS_MAKEREADY, :DAYS_JU_PROCES, :APPROVAL, :DAYS_PERMIT_IN_JU, :DATE_PLANNING, :DAYS_OPEN, :DATE_ROUTED, :DAYS_TOTAL, :COMMENTS)';
    *****$stmt = $conn->prepare($sql);***** line 89
    $sql->bindValue(':RECEIVED',$RECEIVED);
    $sql->bindValue('REGION',$REGION);
    $sql->bindValue(':PERMIT',$PERMIT);
    $sql->bindValue(':TO_BUSINESS',$TO_BUSINESS);
    $sql->bindValue(':TO_PERMIT',$TO_PERMIT);
    $sql->bindValue(':POWER_ADDRESS',$POWER_ADDRESS);
    $sql->bindValue(':MAP_SECTION',$MAP_SECTION);
    $sql->bindValue(':POLES',$POLES);
    $sql->bindValue(':FEE',$FEE);
    $sql->bindValue(':INVOICE',$INVOCE);
    $sql->bindValue(':$DATE_LARRY',$DATE_LARRY);
    $sql->bindValue(':$DAYS_LARRY',$DAYS_LARRY);
    $sql->bindValue(':$PI_PON',$PI_PON);
    $sql->bindValue(':$DATE_PD',$DATE_PD);
    $sql->bindValue(':$DATE_LARRY_PD',$DATE_LARRY_PD);
    $sql->bindValue(':RETURNED',$RETURNED);
    $sql->bindValue(':DAYS_PD',$DAYS_PD);
    $sql->bindValue(':ERRORS_PD',$ERRORS_PD);
    $sql->bindValue(':ERRORS_MULTIPLE',$ERRORS_MULTIPLE);
    $sql->bindValue(':DATE_MAKEREADY_JU',$DATE_MAKEREADY_JU);
    $sql->bindValue(':DATE_MAKEREADY_COMPLETE',$DATE_MAKEREADY_COMPLETE);
    $sql->bindValue(':DAYS_MAKEREADY',$DAYS_MAKEREADY);
    $sql->bindValue(':DAYS_JU_PROCES',$DAYS_JU_PROCES);
    $sql->bindValue(':DATE_PERMIT_IN_JU',$DATE_PERMIT_IN_JU);
    $sql->bindValue(':DATE_PLANNING',$DATE_PLANNING);
    $sql->bindValue(':DAYS_OPEN',$DAYS_OPEN);
    $sql->bindValue(':DATE_ROUTED',$DATE_ROUTED);
    $sql->bindValue(':DAYS_TOTAL',$DAYS_TOTAL);
    $sql->bindValue(':COMMENTS',$COMMENTS);
    $stmt->execute();

    $exec = execute();

    }
?>

What could cause this issue, and is there an easy way around it? TIA!

Answer 1

The oci extension does not have an object-oriented usage. oci_connect() returns a resource, not an object. Resources don't have methods.

The comment from @h2ooooooo is correct. You seem to be mixing oci usage and PDO usage. Pick one or the other, preferably PDO.

In other words, instead of calling oci_connect(), do this:

$dsn = 'oci:dbname=host:1533/name';
$user = 'user';
$password = 'pass';

try {
    $conn = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

If that succeeds, you should be able to use $conn as a PDO object.

But there are a couple of other mistakes in your code.

$sql->bindValue(':RECEIVED',$RECEIVED);

If you use bindValue(), it's a method of your statement object, not the $sql string. Strings don't have methods. Do this instead:

$stmt->bindValue(':RECEIVED',$RECEIVED);

And likewise for all the other calls to bindValue().

You also have a superfluous line that appears to do nothing at the end of your code:

$exec = execute();

Unless you have a function execute() in your application, I don't know what this is supposed to do.

Another mistake: your while loop tests if the current index $ArrayNumber has an entry, then you increment $ArrayNumber before you get the values. You are kind of guaranteed to cause an error, because your code will try to read past the end of the array. You should increment $ArrayNumber after you read the data in that array element.

Or else use a for loop instead of while:

$count = count($csv_array);
for ($ArrayNumber = 0; $ArrayNumber < $count; ++$ArrayNumber) {
  ...

Or even simpler:

foreach ($csv_array as $row) {
  ...

Some tips about using prepared statements. You can skip all the tedious bindValue() calls if you just pass an array to PDOStatement::execute(). And you already have your data in an array! You don't even need to create all those local variables.

Since your data is indexed with integers instead of column names, you should use ordinal parameters (with ? as the placeholder) instead of named parameters.

Also, you don't need to prepare the statement for every iteration of the loop. One of the best advantages of prepared statements is that you can prepare it once and re-use it during a loop, with different values.

Here's an example:

$sql = 'INSERT INTO INTOXDM.JOINT_USE (RECEIVED, REGION, PERMIT, CUSTOMER, TO_BUSINESS, TO_PERMIT, POWER_ADDRESS, COMPANY, MAPSECTION, POLES, FEE, INVOICE, DATE_LARRY, DAYS_LARRY, PI_PON, DATE_PD, DAYS_LARRY_PD, RETURNED, DAYS_PD, ERRORS_PD, ERRORS_MULTIPLE, DATE_MAKEREADY_JU, DATE_MAKEREADY_COMPLETE, DAYS_MAKEREADY, DAYS_JU_PROCES, APPROVAL, DAYS_PERMIT_IN_JU, DATE_PLANNING, DAYS_OPEN, DATE_ROUTED, DAYS_TOTAL, COMMENTS)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
$stmt = $conn->prepare($sql);

foreach ($csv_array as $row) {
  $stmt->execute($row);
}

One final tip: you do this after the data has been read:

echo 'Your .csv file was uploaded successfully. Have a nice day.';

But I would output that message after the data has successfully imported into the database. The positive message may not turn out to be true if there is any problem inserting into the database.