Oracle Nextval issue

[eluser]dignityandshame[/eluser]
So this morning I was playing around and somehow it started incrementing by 4 instead of 3. It's back to 3 now and I'm not sure if I did anything to cause it to jump to 4, but...kinda creepy.

Here's the test code I've been using:

Code:

echo "Before!<br>";
$sql = 'select testlog.schedule_seq.nextval from dual';
echo $sql."<br>";
$query = $this->db->query($sql);
echo "After!<br>";
$row = $query->row();
echo "nextval: ".$row->NEXTVAL."<br>";

And the output:

Code:

Before!
select testlog.schedule_seq.nextval from dual
After!
nextval: 169

And then a refresh of the page:

Code:

Before!
select testlog.schedule_seq.nextval from dual
After!
nextval: 172

Here's the php/oci8 code I used:

Code:

$conn = oci_connect('xxxxx', 'xxxxx', '//xxx.xxx.xxx.xxx/xxx');
$query = 'select testlog.schedule_seq.nextval from dual';

$stid = oci_parse($conn, $query);
$r = oci_execute($stid, OCI_DEFAULT);

while ($row = oci_fetch_row($stid)) {
  foreach($row as $item) {
      echo $item."<br>";
  }
}

oci_close($conn);

which returns this:

Code:

177

and after a refresh, this:

Code:

178

[eluser]Ben Galaviz[/eluser]
I'm new to CodeIgniter and I am testing it out. At my work, we only use Oracle for DB backend. I too had this issue with oracle sequences, so I modified the insert statement in the oci8_driver.php file to handle nextval. (As far as I know, only the insert statement would have the need to access a sequence.) %-P

Code:

/**
     * Insert statement
     *
     * Generates a platform-specific insert string from the supplied data
     *
     * @access  public
     * @param   string  the table name
     * @param   array   the insert keys
     * @param   array   the insert values
     * @return  string
     */
    function _insert($table, $keys, $values)
    {
        //Messy fix for inserting sequences
        $seq = FALSE;
        foreach($values as $insertKey)
        {
            if (substr_count(strtoupper($insertKey), "NEXTVAL") > 0)
            {
                $seq = TRUE;
            }
        }
        
        if ($seq)
        {
            $sqlnextval="";
            foreach($values as $insertKey)
            {
                if (strlen($insertKey) > 8)
                {
                    if (strtoupper(substr($insertKey,-8,7))=="NEXTVAL")
                    {
                        $nextval = str_replace("'", "",$insertKey);
                        $sqlnextval="$sqlnextval,$nextval";
                    }else{
                        $sqlnextval="$sqlnextval,$insertKey";
                    }
                }
            }
            $sqlnextval = substr($sqlnextval,1,strlen($sqlnextval));
            return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES ($sqlnextval)";
        }else{
            return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
        }
    }

I'm new to php so I thought to post this code to see if it can be cleaned up. I originally come from a Java/WebLogic/Oracle background.

[eluser]Unknown[/eluser]
I was having the same problem -- calling something like:

Code:

$q = $this->db->query('SELECT somesequence.NEXTVAL FROM dual');
$result = $q->row();          // Increments three times
$result = $q->result();       // Increments three times
$result = $q->row_array();    // Increments two times
$result = $q->result_array(); // Increments two times

Looking deeper, I found that the function num_rows() in CI_DB_oci8_result (oci8_result.php) is calling ociexecute. This seems to be what's causing the extra DB queries that leads to the sequence getting incremented more than it should.

To remedy this, I altered the function to read as:

Code:

function num_rows()
{
    if ( $this->num_rows )      return $this->num_rows;
    if ( $this->result_array )  return count($this->result_array);
        
    return count($this->result_array());
    ...
}

In addition to that, I had to overwrite CI_DB_result's result_object() and row_object() functions for the oci8 driver.

This probably won't cover all the use cases, but it seems to be working for my application.

[eluser]Unknown[/eluser]
Sorry to bump a 6 month old thread, but I am experiencing the same thing, increments by 3.

I did not see anything in the bug tracker for this.
Does anyone have a complete fix for this? derp alteration of the function didnt work for me, and didnt list what else he did to fix it.

[eluser]Unknown[/eluser]
I’m new to CodeIgniter. I try add this line to file oci8_result.php.

Code:

if(!array_key_exists('NEXTVAL',$this->result_array()[0]))

in function num_rows()

Code:

public function num_rows()
{
  if ($this->num_rows === 0 && count($this->result_array()) > 0)
  {
   $this->num_rows = count($this->result_array());

   if(!array_key_exists('NEXTVAL',$this->result_array()[0]))
   @oci_execute($this->stmt_id);

   if ($this->curs_id)
   {
    @oci_execute($this->curs_id);
   }
  }

  return $this->num_rows;
}

I hope this will help.
I use CI 2.1.3 and this work for me.