Oracle Next Id

[eluser]Druidor[/eluser]
Hello,

I'm using CI with Oracle and ActiveRecord.

I've got a TODO Table with ID and NAME as fields.

I created a sequence and a trigger to fill the ID :

Code:

CREATE SEQUENCE SEQ_TODO_ID
  START WITH 1
  MAXVALUE 999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

CREATE TRIGGER TRIGGER_TODO_ID
   BEFORE INSERT
   ON TODO
   FOR EACH ROW
BEGIN
   SELECT SEQ_TODO_ID.NEXTVAL
     INTO :NEW.ID
     FROM DUAL;
END;

I'm trying to modify the function insert_id of drivers/oci8/oci8_driver.php :

here's what I've done :

/**
* Insert ID
*
* @access public
* @return integer
*/
function insert_id($sequence)
{

if ($sequence == '')
return '0';

$query = $this->query("SELECT MAX(ID) AS NEXTID FROM TODO");

if ($query == FALSE)
{
return 0;
}

$row = $query->result_array();

return $row[0]['NEXTID'];
}

But I got this error :

Code:

<h4>A PHP Error was encountered</h4>

<p>Severity: Warning</p>

<p>Message:  ocifetchinto() [<a href='function.ocifetchinto'>function.ocifetchinto</a>]: ORA-24374: define

not done before fetch or execute and fetch</p>

<p>Filename: oci8/oci8_result.php</p>

<p>Line Number: 160</p>

I also tried the SQL query "SELECT SEQ_TODO_ID.CURRVAL FROM DUAL" but I got the same error. Any toughts ?

[eluser]xwero[/eluser]
Have you checked if the oracle driver in SVN has changes?

[eluser]Druidor[/eluser]
Here's the code. It returns 0 all the time. How do I get the id that has been affected to the object ?

Code:

/**
     * Insert ID
     *
     * @access  public
     * @return  integer
     */
    function insert_id()
    {
        // not supported in oracle
        return 0;
    }

[eluser]xwero[/eluser]
I guess there is no other way then a select query using max(id) right after you insert a row.

[eluser]Druidor[/eluser]
Actually, this is what I'm doing but I got the same error :

Code:

function create ($args)
  {

    if ($this->db->insert($this->_table, $args))
    {

      $return = new $this->_class_name();

      foreach ($args as $key => $value)
      {
        $return->$key = "$value";
      }

     $return->id = $this->db->insert_id($this->_sequence); // call the max(id) query but gives me an error.

      return $return;
    }
    else
    {
      log_message('error', $this->db->last_query());
    }
  }

[eluser]xwero[/eluser]
i think you will have to do it in your model and not in the driver because the query is specific for that table. If you got it working in your model maybe you could try to add it to the driver code.

[eluser]Druidor[/eluser]
it doesn't work either in the model.

[eluser]Druidor[/eluser]
Here's a print_r of $this->db :

Code:

CI_DB_oci8_driver Object
(
    [_commit] => 32
    [stmt_id] => Resource id #33
    [curs_id] =>
    [limit_used] =>
    [ar_select] => Array
        (
        )
    [ar_distinct] =>
    [ar_from] => Array
        (
        )
    [ar_join] => Array
        (
        )
    [ar_where] => Array
        (
        )
    [ar_like] => Array
        (
        )
    [ar_groupby] => Array
        (
        )
    [ar_having] => Array
        (
        )
    [ar_limit] =>
    [ar_offset] =>
    [ar_order] =>
    [ar_orderby] => Array
        (
        )
    [ar_set] => Array
        (
        )
    [username] => Tata
    [password] => Tata
    [hostname] => (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = toto)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Tata)
      (INSTANCE_NAME = Tata)
    )
  )
    [database] => Tata
    [dbdriver] => oci8
    [dbprefix] =>
    [port] =>
    [pconnect] => 1
    [conn_id] => Resource id #28
    [result_id] => 1
    [db_debug] => 1
    [benchmark] => 0.10153293609619
    [query_count] => 2
    [bind_marker] => ?
    [queries] => Array
        (
            [0] => INSERT INTO todo (name) VALUES ('ludovic6')
            [1] => SELECT MAX(ID) AS NEXTID FROM TODO
        )
    [data_cache] => Array
        (
        )
    [trans_enabled] => 1
    [_trans_depth] => 0
    [_trans_status] => 1
    [cache_on] =>
    [cachedir] =>
    [cache_autodel] =>
    [CACHE] =>
)

and the error

Code:

<p>Message:  ocifetchinto() [<a href='function.ocifetchinto'>function.ocifetchinto</a>]: ORA-24374: define

not done before fetch or execute and fetch</p>

<p>Filename: oci8/oci8_result.php</p>

[eluser]xwero[/eluser]
I think you should add it to the bug list

[eluser]Derek Allard[/eluser]
Sorry Druidor, I missed this thread link in my bug response. I've change the behaviour of this function to clearly indicate that it isn't supported. This is something we'd very much like to implement in CI, however I'm unable to find an elegant way to implement this using the oci8 driver. If you can come up with a solution, I'd be very happy to roll it into the core.