PDO and Oracle query problem

Question

PDO and Oracle query problem

Navigation

#1 by (1 votes)

1

I have a problem when making the query, it does not return anything. The query is correct since I have executed it in the sqldeveloper and it does not give me an error in the connection either. This is my code:

test.php

<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>MUNICIPIOS</title>
</head>
<body>
    <?php
    include_once('php/clases.php');
    $bd = new ConexionBD();
    $consulta = 'SELECT * FROM MUNICIPIOS';
    $res = $bd->consulta($consulta);

    foreach($res as $fila){
        print_r($fila);
    }
    ?>
</body>
</html>

clases.php

<?php

class ConexionBD{
    private $conexion;
    private $host = "oci:dbname=localhost/XE";
    private $usuario = "BD";
    private $pass = "1234";

    function __construct(){
        try{
            $this->conexion = new PDO($this->host, $this->usuario, $this->pass);
            $this->conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "Conexion realizada con exito!<br>";
        }catch(PDOException $e){
            $this->conexion = null;
            $codigo = $e->getCode();
            $mensaje = $e->getMessage();
            $fichero = $e->getFile();
            $linea = $e->getLine();
            echo "Error en la conexion! <br>
            Mensaje de error: $mensaje<br>
            Codigo: $codigo<br>
            Fichero: $fichero<br>
            Linea: $linea";
        }
    }

    function consulta($query){
        try {
            $stmt= $this->conexion->query($query);
            echo "Consulta realizada con exito!<br>";
        }catch( PDOException $e ){
            $stmt = null;
            $codigo = $e->getCode();
            $mensaje = $e->getMessage();
            $fichero = $e->getFile();
            $linea = $e->getLine();
            echo "Error en la consulta! <br>
                Mensaje de error: $mensaje<br>
                Codigo: $codigo<br>
                Fichero: $fichero<br>
                Linea: $linea";
        }
        return $stmt;
    }

    function ejecuta($query){
        try {
            $stmt= $this->conexion->exec($query);
        }catch( PDOException $e ){
            $stmt = null;
            $codigo = $e->getCode();
            $mensaje = $e->getMessage();
            $fichero = $e->getFile();
            $linea = $e->getLine();
            echo "Error en la ejecucion! <br>
                Mensaje de error: $mensaje<br>
                Codigo: $codigo<br>
                Fichero: $fichero<br>
                Linea: $linea";
        }
        return $stmt;
    }

    function consultaPaginada($consulta, $page_num, $page_size){
    try{
        $first = ($page_num - 1) * $page_size + 1;
        $last = $page_num * $page_size;
        $paged_query = "SELECT * FROM  ( SELECT ROWNUM RNUM, AUX.*  FROM( $consulta) AUX WHERE ROWNUM <= :last) WHERE  RNUM >= :first";
        $stmt = $this->conexion->prepare($paged_query);
        $stmt->bindParam( ':first', $first );
        $stmt->bindParam( ':last', $last );
        $stmt->execute();
        return $stmt;
    }catch( PDOException $e ) {
        $stmt = null;
        $codigo = $e->getCode();
        $mensaje = $e->getMessage();
        $fichero = $e->getFile();
        $linea = $e->getLine();
        echo "Error en la consulta! <br>
            Mensaje de error: $mensaje<br>
            Codigo: $codigo<br>
            Fichero: $fichero<br>
            Linea: $linea";
    }
    }


}


?>

php oracle pdo

asked by Jos Manuel Gonzlez Gutirrez jm 07.04.2018 в 17:40

source

1 answer

Write in a text file the result of a command in Ruby Is it better to use the using within the namespace?

score 1 · Answer 1

In your function consulta you are not returning a representation of the data, which is obtained by applying some method fetch to $stmt that has resulted from your query. If you modify this function in the following way it should work:

function consulta($query){
        $stmt= $this->conexion->query($query);
        //echo "Consulta realizada con exito!<br>";
    if ($stmt){
        $arrResultado =$stmt->fetchAll(PDO::FETCH_ASSOC);
    }else{
        $errInfo=$this->conexion->errorInfo();
        $arrResultado=array("Error"=>"Error en la consulta! <br>
            Mensaje de error: $errInfo[2]<br>
            Codigo: $errInfo[0]");
    }
    return $arrResultado;
}

What I've done has basically been to build an array with valid data, in case the query gets results, or build an array with an error message in the case of failure. Returning at the end that array and not the $stmt , which is a pointer, on the results, which must be read in some way. In this case I used fetchAll , but you can use any other method that suits you.

In the edition, I used the method errorInfo of PDO, to obtain the error messages, as indicated by the PHP Manual .