|
Oracle Nextval issue
|
|
|
| Posted: 02 April 2009 06:42 PM |
[ Ignore ]
|
|
|
Joined: 2009-04-02
8 posts
|
I’ve got an interesting issue going on here when grabbing the nextval from a sequence in Oracle. The problem is that the nextval increments by 3 each time, not 1. The increment parameter is set to 1 in Oracle, and if I do the same select from the command line, it returns the nextval incremented by only 1. So, my question is, why and/or how is this being incremented by 3 when I run the query through CI?
$sql = "select testlog.schedule_seq.nextval from dual";
$query = $this->db->query($sql);
$row = $query->row();
echo $row->NEXTVAL;
|
|
|
|
|
|
| Posted: 02 April 2009 07:22 PM |
[ Ignore ]
[ # 1 ]
[ Rating: 0 ]
|
|
|
Joined: 2008-06-04
2101 posts
|
Howdi, and welcome to the CI forums.
I know next to nothing about Oracle, but I’m assuming that nextval is meant to return one row?
It might be worth doing a num_rows() call in there, before you access the row, to make sure that you did in fact only get one row. $query->row() of course will work fine with multi-row results, it’ll just blissfully return the first row only. |
|
|
|
|
|
| Posted: 02 April 2009 07:31 PM |
[ Ignore ]
[ # 2 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-04-02
8 posts
|
It’s only returning one row. That nextval query in Oracle is the equivalent of an auto_increment field in MySQL (since Oracle doesn’t have an auto_increment feature  ). Somehow this query is being run 3 times. It’s not inside any kind of loop or anything either. |
|
|
|
|
|
| Posted: 02 April 2009 07:42 PM |
[ Ignore ]
[ # 3 ]
[ Rating: 0 ]
|
|
|
Joined: 2008-06-04
2101 posts
|
Okay, the next thing I’d try (because it’s easy) is to turn on the Profiling Library - which will report, on your browser, the exact database calls being made. This should give you a pointer as to whether it’s a weirdness with the Oracle/CI interaction (we don’t get many Oracle users around these ‘ere parts) or at the other end. I’m guessing you can crank up some Oracle profiling tools to see if it’s getting the same request three times? |
|
|
|
|
|
| Posted: 02 April 2009 08:44 PM |
[ Ignore ]
[ # 4 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-04-02
8 posts
|
Does it need to be placed anywhere special within the controller? I’m not getting any output…
function index() {
$this->output->enable_profiler(TRUE);
$data['title'] = "Scheduler";
$data['query'] = $this->approve_model->getDetails();
$data['table'] = $this->approve_model->getPendingTable($data['query']);
$this->load->view('approve_view',$data);
}
|
|
|
|
|
|
| Posted: 02 April 2009 08:56 PM |
[ Ignore ]
[ # 5 ]
[ Rating: 0 ]
|
|
|
Joined: 2008-06-04
2101 posts
|
No, anywhere should be okay. I normally put it in my constructor, just so I can easily comment / de-comment it during development.
Do you mean you get no output at all or just no profiler output - should be trailing at the end of your web page - there’s quite a bit of it, so it’s hard to miss I guess. |
|
|
|
|
|
| Posted: 02 April 2009 09:27 PM |
[ Ignore ]
[ # 6 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-04-02
8 posts
|
Sorry, I should’ve been a little more specific. No profiler output. There is output on the page though.
I’ll see if I can fire up something from the Oracle side tomorrow when I’m back at work to see how many queries are coming in.
I’m intrigued by this profiler though so I’d like to get it displaying properly if possible. |
|
|
|
|
|
| Posted: 02 April 2009 09:37 PM |
[ Ignore ]
[ # 7 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-04-02
8 posts
|
Disregard. I got the profiler working. It’s only running one “nextval” query so I’ll check things out from the Oracle side tomorrow. |
|
|
|
|
|
| Posted: 03 April 2009 01:27 AM |
[ Ignore ]
[ # 8 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-04-02
8 posts
|
Ok, I’d say this is definitely some sort of bug within CI. On the Oracle side, the query shows up like this:
select testlog.schedule_seq.nextval from dualselect testlog.schedule_seq.nextval from dualselect testlog.schedule_seq.nextval from dual
I wrote my own connection/query using the base oci php functions and the number only increments by 1 each time. |
|
|
|
|
|
| Posted: 03 April 2009 07:41 AM |
[ Ignore ]
[ # 9 ]
[ Rating: 0 ]
|
|
|
Joined: 2008-06-04
2101 posts
|
Well .. it’s time to file that bug report. As I mentioned, we don’t see a lot of Oracle users, and I guess the same goes for the EL guys.
You could try running the query twice - it’d be fascinating it you got a result of 4 incs rather than 6.  (I’m having a Joseph Heller flashback now.)
And you have stuck an echo “BOB SAYS HI” or similar in there, to really be sure that that code block only gets hit the once, right? |
|
|
|
|
|
| Posted: 03 April 2009 11:32 AM |
[ Ignore ]
[ # 10 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-04-02
8 posts
|
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:
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:
Before!
select testlog.schedule_seq.nextval from dual
After!
nextval: 169
And then a refresh of the page:
Before!
select testlog.schedule_seq.nextval from dual
After!
nextval: 172
Here’s the php/oci8 code I used:
$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:
177
and after a refresh, this:
178
|
|
|
|
|
|
| Posted: 15 October 2009 12:52 PM |
[ Ignore ]
[ # 11 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-10-15
3 posts
|
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.) 
/**
* 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. |
|
|
|
|
|
| Posted: 20 October 2009 07:02 PM |
[ Ignore ]
[ # 12 ]
[ Rating: 0 ]
|
|
|
Joined: 2009-10-20
2 posts
|
I was having the same problem—calling something like:
$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:
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. |
|
|
|
|
|
| Posted: 07 March 2010 08:20 PM |
[ Ignore ]
[ # 13 ]
[ Rating: 0 ]
|
|
|
Joined: 2010-03-05
1 posts
|
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. |
|
|
|
|
|
| Posted: 30 October 2011 08:07 PM |
[ Ignore ]
[ # 14 ]
[ Rating: 0 ]
|
|
|
Joined: 2011-10-30
1 posts
|
I think its must be bug , we should need to throw it in dustbin, that’s just waste of time ! |
|
|
|
|
|
| Posted: 13 May 2013 07:43 AM |
[ Ignore ]
[ # 15 ]
[ Rating: 0 ]
|
|
|
Joined: 2013-03-05
1 posts
|
I’m new to CodeIgniter. I try add this line to file oci8_result.php.
if(!array_key_exists('NEXTVAL',$this->result_array()[0]))
in function num_rows()
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. |
|
|
|
|
