Undefined Index with Oracle connection

Asked

Viewed 170 times

0

For some reason, my code is resulting in error

Notice: Undefined Index IRRAD

when trying to perform a query in the Oracle database. The PHP code I am using:

set_time_limit( 600 );
date_default_timezone_set('America/Sao_Paulo');    
$query2 = 'SELECT \'0 - 300\' as intervalo, ROUND(SUM('.$irradInc.')/3600000, 2) as irrad FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 0 AND 300 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'300 - 700\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 300 AND 700 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'700 - 1000\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 700 AND 1000 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'1000 - 1200\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 1000 AND 1200 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'> 1200\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' > 1200 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')';
    //echo $query2;
    $conn = oci_connect('****', '***', '****');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    $stid = oci_parse($conn, $query2);
    oci_execute($stid);
    $array = array();
    while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
        array_push(
                $array,
                array(
                'value' => $row['irrad'],
                'label' => $row['INTERVALO']
                 )
             );


        unset($row);  
    }

The query I’m running is:

SELECT \'0 - 300\' as intervalo, ROUND(SUM('.$irradInc.')/3600000, 2) as irrad FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 0 AND 300 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')

Using the query directly in Oracle’s SQL Developer, the result comes correctly, with column names as in PHP code. The index Range works correctly, however, the index IRRAD it accuses the cited error.

Is there a rule to be followed to read the headers or is there something wrong with the code?

php oracle
  • 1

    Tries to recover the name in low box, $row['irrad']

  • continues with Undefined Index. Other queries in the same template are running smoothly. I’ve also never had problems with Mysql query. I’ll post the rest of the code to see if there’s any interference

  • that, might help.

  • 1

    When you do print_r($row); appears what? can put in the comments even the result.

  • Array ( [RANGE] => 0 - 300 )

  • Remove the alias of irrad and run the print_r again, I hope something different will come.

  • Continue only with index RANGE :/

  • I think I get it. For some reason I was returning NULL to the IRRAD column and when the query returns NULL, the $row does not receive the column.

  • I do not know if the oracle has any particularity but I believe that the column should be defined with null and not simply exist.

  • I added a checker to evaluate whether null returns and is working correctly. Add an answer with the functional code or edit the question? Thank you very much!

  • If you solved the problem, create an answer :).

Show 6 more comments

2 answers

1

In addition to the solution already presented, there are also these:

Force the return of columns with NULL values by stating this in $mode of any oci_fetch_*

oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS+OCI_RETURN_NULLS)

Avoid returning null to any field in the query with the command COALESCE 

$query2 = 'SELECT \'0 - 300\' as intervalo, COALESCE(ROUND(SUM('.$irradInc.')/3600000, 2), 0) as irrad FROM(...)

  • I don’t know oracle, in which case when a column returns null, it simply doesn’t add it on the query return?

  • I think this is the behavior of Client Oracle (usually Instant Client) and not SGDB itself. Must be some specification that the OCI and PDO_OCI extensions follow.

1

The problem was that when the query returned value NULL, the variable $row did not receive the index from this column. Therefore, when you were going to read the $row['IRRAD'], was resulting in Undefined Index.

To fix, I added a check line:

while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
    //print_r($row);
    if(!oci_field_is_null( $stid , 'IRRAD' )){
        array_push(
            $array,
            array(
            'value' => round(($row['IRRAD']/$irradTotal)*100, 2),
            'label' => $row['INTERVALO']
             )
         );
    }   


    unset($row);  
}

The command oci_field_is_null returns true if the parsed field is NULL and false if the field contains some value.

Browser other questions tagged php oracle

You are not signed in. Login or sign up in order to post.