• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Important: CI can't handel sql statment to select from Oracle view

#1
[eluser]Unknown[/eluser]
halo all,
i have some problem with sql statement that connect to oracle.
I'm using CI 1.7.1 n oracle XE 9.

i have an oracle view, just call it myView.

this is database config that i used:

$active_group = "siproltrn";
$active_record = TRUE;
$db['siproltrn']['hostname'] = "//localhost/xe";
$db['siproltrn']['username'] = "siprol2";
$db['siproltrn']['password'] = "siprol2";
$db['siproltrn']['database'] = "";
$db['siproltrn']['dbdriver'] = "oci8";
$db['siproltrn']['dbprefix'] = "";
$db['siproltrn']['pconnect'] = TRUE;
$db['siproltrn']['db_debug'] = TRUE;
$db['siproltrn']['cache_on'] = FALSE;
$db['siproltrn']['cachedir'] = "";
$db['siproltrn']['char_set'] = "utf8";
$db['siproltrn']['dbcollat'] = "utf8_general_ci";

rank controller :
private function ranking(){
$sql = "SELECT * FROM SIPROL2.myView";
$query = $this->rank_model->getRanking($sql);
}


rank_model model :
function getRanking($sql){
$rank = $this->load->database('siproltrn', true);
$query = $rank->query($sql);
return $query;
}

the return from CI:
A Database Error Occurred
Error Number:
SELECT * FROM SIPROL2.myView

but is okay if i selected data from table, but from view always error.
please help me,.. I'm from Indonesia sorry if my English is not good enough

#2
[eluser]kgill[/eluser]
First step is to figure out what's going wrong... If you're not opposed to modifying the driver a little you can make your life much easier by adding some error logging.
Code:
//oci8_driver.php around line 145 you'll see this:

    function _execute($sql)
    {
        // oracle must parse the query before it is run. All of the actions with
        // the query are based on the statement id returned by ociparse
        $this->stmt_id = FALSE;
        $this->_set_stmt_id($sql);
        ocisetprefetch($this->stmt_id, 1000);
        return @ociexecute($this->stmt_id, $this->_commit);
    }    

/*
the ociexecute is returning the true/false directly and not doing any error checking, we want to know what's going on so we need to modify this a bit so change the function like so:
*/

    function _execute($sql)
    {
        // oracle must parse the query before it is run. All of the actions with
        // the query are based on the statement id returned by ociparse
        $this->stmt_id = FALSE;
        $this->_set_stmt_id($sql);
        ocisetprefetch($this->stmt_id, 1000);
        $exec_worked = ociexecute($this->stmt_id, $this->_commit);
        if ($exec_worked === FALSE) { // if ociexecute failed, grab the oracle error message and log it
            $e = oci_error($this->stmt_id);
            log_message('error', $e['message']);
        }
        return $exec_worked;
    }

Now you'll have the actual oracle error messages showing up in your log file when you enable error logging, once you've got those you should be able to figure out what's going on by googling the ORA- number.

#3
[eluser]Unknown[/eluser]
Code:
A PHP Error was encountered

Severity: Warning

Message: ociexecute() [function.ociexecute]: ORA-01858: a non-numeric character was found where a numeric was expected

Filename: oci8/oci8_driver.php

Line Number: 152

i follow your codes, and now i get this error.
n the oracle error still the same.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.