Stored Procedures in Oracle

#1
[eluser]ELRafael[/eluser]
Hello everybody!

I have some issues here, in specially run stored_procedure() method of oci8_driver.php

What's going on:

We have a website that connects to MySQL to view all the content. It's a CMS website. This is ok Smile
Inside that, we connect to an ERP, with Oracle database to show the products in e-commerce.

The second connection is just fine. Works good and so. But one the client rule is about discounts on the products. If I login with X user, I'll see some prices. If I do with Y user, I'll see other prices for the same product. This rule is inside a stored procedure.

I tried with this
Code:
function exec_sp2($params)
{
    //"Externo" is a group of database.php config file. It's valid
    $rs = $this->load->database('externo', TRUE);

    $binds = array(
        array('name'=>':emp',        'value'=> $params['emp'],        'type'=>SQLT_NUM,    'length' => 30),
        array('name'=>':cliente',    'value'=> $params['cliente'],     'type'=>SQLT_NUM,    'length' => 50),
        array('name'=>':artigo',    'value'=> $params['artigo'],     'type'=>SQLT_CHR,    'length' => 20),
        array('name'=>':qtd',        'value'=> $params['qtd'],        'type'=>SQLT_NUM,    'length' => 20),
        array('name'=>':venda',        'value'=> &$venda,                'type'=>SQLT_NUM,    'length' => 20),
        array('name'=>':desc1',        'value'=> &$desc1,                'type'=>SQLT_NUM,    'length' => 20),
        array('name'=>':desc2',        'value'=> &$desc2,                'type'=>SQLT_NUM,    'length' => 20),
        array('name'=>':desc3',        'value'=> &$desc3,                'type'=>SQLT_NUM,    'length' => 20),
        array('name'=>':texto',        'value'=> &$texto,                'type'=>SQLT_CHR,    'length' => 200)
                        );
    $rs->stored_procedure('WEBTERGOM', 'p_desconto', $binds);
    $retorno['venda'] = $venda;
    $retorno['desc1'] = $desc1;
    $retorno['desc2'] = $desc3;
    $retorno['desc3'] = $desc2;
    $retorno['texto'] = $texto;

    return (object) $retorno;
}

Well, this method is inside my model. I don't know why, but always returns a message from database "client not found".

But if I wrote this code
Code:
function exec_sp($params)
{
    $conn = ocilogon('xxxx', 'xxxxx', 'xxxxxx/XE', 'utf8');
    $sql = 'BEGIN WEBTERGOM.p_desconto(:emp, :cliente, :artigo, :qtd, :venda, :desc1, :desc2, :desc3, :texto); END;';
    $stmt = oci_parse($conn, $sql);
    //Here I'm using PHP5 functions, but ocibindbyname(....) [PHP4] the result is the same
    oci_bind_by_name($stmt, ':emp',     $params['emp'],        30);
    oci_bind_by_name($stmt, ':cliente',    $params['cliente'],    50);
    oci_bind_by_name($stmt, ':artigo',    $params['artigo'],     20);
    oci_bind_by_name($stmt, ':qtd',     $params['qtd'],        20);
    oci_bind_by_name($stmt, ':venda',    $venda,        20);
    oci_bind_by_name($stmt, ':desc1',     $desc1,        20);
    oci_bind_by_name($stmt, ':desc2',     $desc2,        20);
    oci_bind_by_name($stmt, ':desc3',     $desc3,        20);
    oci_bind_by_name($stmt, ':texto',     $texto,        20);
    $r2 = @oci_execute($stmt);
    $retorno['venda'] = $venda;
    $retorno['desc1'] = $desc1;
    $retorno['desc2'] = $desc2;
    $retorno['desc3'] = $desc3;
    $retorno['texto'] = $texto;
    
    return (object) $retorno;
}

The result is satisfatory.

In theory, both functions should return the same result, right?

I wish to know what's wrong with my method. Something I forgot?
I really really don't wanna use that second method, although I know that works, but there is no CI pattern there. Confusedhut:

Thanks for any help,
Rafael


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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