+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Important: CI can't handel sql statment to select from Oracle view (/showthread.php?tid=20518)
Important: CI can't handel sql statment to select from Oracle view - El Forum - 07-13-2009
[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
Important: CI can't handel sql statment to select from Oracle view - El Forum - 07-13-2009
[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.
Important: CI can't handel sql statment to select from Oracle view - El Forum - 07-13-2009
[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: 152i follow your codes, and now i get this error.
n the oracle error still the same.