[eluser]Ayo[/eluser]
I have been trying to get CI+Oracle Stored Procedures working.
The issue i currently have is the corruption of input data during oci binding:
Below is a snippet from:
MODEL
Code: function testProc($dat)
{
$params = array(
array('name'=>':p_num', 'value'=>$dat, 'type'=>SQLT_NUM, 'length'=>-1),
array('name'=>':p_out', 'value'=>&$res2, 'type'=>OCI_B_INT, 'length'=>-1)
);
$this->db->stored_procedure('SPSB_PKG','TEST',$params);
}
Code: PROCEDURE test(p_num NUMBER,
p_out OUT NUMBER)
AS
BEGIN
p_out := p_num;
END test;
When i call this function from the Controller passing in a value(eg. 4), when i check the returned value in variable $res2 i get varying values like 220189512,220189160,-0.008212.
Any help with how to bind oracle datatypes to php will be appreciated.
Thanks.
[eluser]Ayo[/eluser]
I finally figured out the problem. I had to modify the oci8_driver.php file.
Original form:
Code: 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);
}
function stored_procedure($package, $procedure, $params)
{
if ($package == '' OR $procedure == '' OR ! is_array($params))
{
if ($this->db_debug)
{
log_message('error', 'Invalid query: '.$package.'.'.$procedure);
return $this->display_error('db_invalid_query');
}
return FALSE;
}
// build the query string
$sql = "begin $package.$procedure(";
$have_cursor = FALSE;
foreach($params as $param)
{
$sql .= $param['name'] . ",";
if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
{
$have_cursor = TRUE;
}
}
$sql = trim($sql, ",") . "); end;";
$this->stmt_id = FALSE;
$this->_set_stmt_id($sql);
$this->_bind_params($params);
$this->query($sql, FALSE, $have_cursor);
}
The stored_procedure() function parses the stored procedure sql [ _set_stmt_id($sql)] and binds variables using this ociparse object [ _bind_params($params)].
A subsequent call to query($sql, FALSE, $have_cursor) internally calls _execute($sql) which nullifies the stmt_id and attempts to reparse the sql string without the necessary bind variables.
Based on other modifications to these files recommended in other posts on this forum, the modified files should look like this
oci_driver.php
Code: //bind variable declared for class
var $bind = FALSE;
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);
//begin modification
if ( ! $this->stmt_id)
{
$e = oci_error($this->stmt_id);
log_message('error', $e['message']);
return FALSE;
}
if($this->binds !== FALSE)
{
$this->_bind_params($this->binds);
}
ocisetprefetch($this->stmt_id, 1000);
//return @ociexecute($this->stmt_id, $this->_commit);
$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;
//end modification
}
function _set_stmt_id($sql)
{
//if ( ! is_resource($this->stmt_id))
//{
// $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
//}
$this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
}
function stored_procedure($package, $procedure, $params)
{
if ($package == '' OR $procedure == '' OR ! is_array($params))
{
if ($this->db_debug)
{
log_message('error', 'Invalid query: '.$package.'.'.$procedure);
return $this->display_error('db_invalid_query');
}
return FALSE;
}
// build the query string
$sql = "begin $package.$procedure(";
$have_cursor = FALSE;
foreach($params as $param)
{
$sql .= $param['name'] . ",";
if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
{
$have_cursor = TRUE;
}
}
$sql = trim($sql, ",") . "); end;";
$this->stmt_id = FALSE;
//begin modification
$this->binds = FALSE;
//end modification
$this->_set_stmt_id($sql);
//$this->_bind_params($params);
$this->query($sql, $params, $have_cursor);
}
The DB_driver.php file is also modified thus:
Code: function query($sql, $binds = FALSE, $return_object = TRUE)
{
if ($sql == '')
{
if ($this->db_debug)
{
log_message('error', 'Invalid query: '.$sql);
return $this->display_error('db_invalid_query');
}
return FALSE;
}
...
...
// Compile binds if needed
if ($binds !== FALSE)
{
//begin modification
//$sql = $this->compile_binds($sql, $binds);
if ($this->dbdriver == 'oci8')
{
$this->binds = $binds;
}
else
{
$sql = $this->compile_binds($sql, $binds);
}
//end modification
}
...
...
}
The modifications also allow the use of bind variables in oracle queries instead of the substitution of values in '?' placeholders, as shown below:
Code: function getAll()
{
$sql = 'SELECT * FROM user_info WHERE id = :P_ID and login_id = :P_LOGIN';
$params = array(
array('name'=>':P_ID', 'value'=>1, 'type'=>SQLT_CHR, 'length'=>-1),
array('name'=>':P_LOGIN', 'value'=>'ade', 'type'=>SQLT_CHR, 'length'=>-1)
);
$query = $this->db->query($sql,$params);
return $query->result_array();
}
[eluser]Bhupendra[/eluser]
Thanks for good work, I follow up your suggestion, there is no errors but still data is not inserting on table. Trying to insert records through html form on table.
I am using Oracle 11g, codeigniter 1.7.1 and PHP 5.1.2.
Thanks,
[eluser]Ayo[/eluser]
What exactly r u tryin' to do?
I have some slight modification to query function(DB_driver.php)
Code: function query($sql, $binds = FALSE, $return_object = TRUE)
{
if ($sql == '')
{
if ($this->db_debug)
{
log_message('error', 'Invalid query: '.$sql);
return $this->display_error('db_invalid_query');
}
return FALSE;
}
...
...
// Compile binds if needed
$this->binds = $binds;
if ($binds !== FALSE)
{
//begin modification
//$sql = $this->compile_binds($sql, $binds);
if ($this->dbdriver == 'oci8')
{
$this->binds = $binds;
}
else
{
$sql = $this->compile_binds($sql, $binds);
}
//end modification
}
...
...
You can PM for more help
[eluser]Jbeetle[/eluser]
I had to modify all drivers but still probem with cursors.
My code in model:
public function GetUsers(){
$cursor = $this->db->get_cursor();
$this->db->stored_procedure("jile","get_roles" ,array
(array
('name' => ':cRoles',
'value' => $cursor,
'type' => OCI_B_CURSOR,
'length' => -1)
)
);
}
displays:
ORA-24374: in file oci8/oci8_result.php, Line Number: 154
[eluser]kimo_gusatava[/eluser]
Hi,
I'm trying to learn oracle, and I'm just using a sample database. seems like selecting table and views are easy but when calling procedures I got a bit of a problem...
Is there an easier way to call an Oracle stored Procedure like using the default active record methods?
or should I really have to modify the driver?
seems like the $package is required here right this means I have to create the package? and all I got right now is a procedure...
anyway I would like to call this one:
Code: create or replace
PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
[eluser]Unknown[/eluser]
didn't help me at all, actually. i'm still stuck with this thing. i need to call not a query, but a procedure. it still outputs error on this line:
Code: $exec_worked = ociexecute($this->stmt_id, $this->_commit);
it doesn't recognize function "ociexecute".
what to do? my head's about to explode. and my boss' too.
|
