Welcome Guest, Not a member yet? Register   Sign In
CodeIgniter Oracle select - random issue
#1

[eluser]Unknown[/eluser]
Hello there!

I'm having a very weird problem with a quite simple select statement. Here it is:

I'm reading a csv file (that is an export of an Excel file). Field separator is ";" and each field is enclosed with "".

I use the second, third and fifth fields to create a SELECT to look for that information in the DB. In other words, I'm looking for people in a table according to their last and first names and, email. For example:

Code:
SELECT MAX(n_id_persona) AS n_id_persona
FROM (
    SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
    FROM PERSONAS p
    WHERE (NO_ACCENTS(p.d_apellidos) LIKE NO_ACCENTS('%Lastname%')
        AND NO_ACCENTS(p.d_nombres) LIKE NO_ACCENTS('%Firstname%'))
    UNION
    SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
    FROM PERSONAS p
    LEFT JOIN CORREOS c ON (p.n_id_persona = c.n_id_persona)
    WHERE ((c.c_correo LIKE '%[email protected]%')) );

The issue is that, randomly, CodeIgniter does not bring any N_ID_PERSONA but if I test the same query in Oracle SQL Developer it retrieves and ID.

I first thought it was a timeout, but is discarded. Also, reformulated the queries because I thought they were not correct but nop. The function NO_ACCENTS() just replaces all accented characters by the corresponding letter and writes everything in uppercase. I also tried without using this NO_ACCENTS() function and got the same weird result.

Here it is the code:

controllers/importador.php

Code:
public function __construct()
{
    parent::__construct();
    $this->output->enable_profiler(TRUE);
    session_start();
}

public function index()
{
    //Connection info to the DB

    $_SESSION['c'] = $connection_info;

    $this->db = $this->load->database($_SESSION['c'], TRUE, TRUE);
    $this->load->model('Importador_model');

    //Reads the csv
    $this->load->library('csvreader');

    $filePath = '/home/vcl/Desktop/CRM/File_name.csv';

    $data['csvData'] = $this->csvreader->parse_file($filePath);

    foreach($data['csvData'] as $linea){

        $data['coincidencia'] = $this->Importador_model->Buscar_contacto(str_replace('\'', '\'\'', $linea['Apellido']), str_replace('\'', '\'\'', $linea['Nombre']), $linea['Email']);  

        echo $linea['Apellido'].' '.$linea['Nombre'].''.$linea['Entrevista'].'<br>';
        $temp = $data['coincidencia']->row();
        echo $temp->N_ID_PERSONA;
        echo '<br>';
    }
}

models/importador_model.php

Code:
function Buscar_contacto($ape = NULL, $nom = NULL, $email = NULL)
{
$CI =& get_instance();

$query = $CI->db->query('SELECT MAX(n_id_persona) AS n_id_persona
             FROM (
                SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
                FROM PERSONAS p
                WHERE (NO_ACCENTS(p.d_apellidos) LIKE NO_ACCENTS(\'%'.$ape.'%\')
                    AND NO_ACCENTS(p.d_nombres) LIKE NO_ACCENTS(\'%'.$nom.'%\'))
                UNION
                SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
                FROM PERSONAS p
                LEFT JOIN CORREOS c ON (p.n_id_persona = c.n_id_persona)
                WHERE ((c.c_correo LIKE \'%'.$email.'%\')) )');

return $query;
}

Any ideas? Does this happen to anyone? Is this a bug?

Thanks a lot!

V




Theme © iAndrew 2016 - Forum software by © MyBB