Oracle INSERTs failing, but no errors

Posted: 03 December 2007 09:44 PM

[ Ignore ]

Summer Student

Total Posts: 28

Joined 08-08-2007

I’m trying to insert into my Oracle database. My insert command returns true (i.e. success) but nothing shows up in the database. My database admin application has no record of an INSERT every getting to the database. No CI errors are thrown or appear in the log, no PHP errors, no Oracle errors—but now rows in my table.

$result = $this->db->query('INSERT INTO logins '.
    '(userid, datein, sessionid, ipaddress) '.
    'VALUES (\'abcd\', \'123\', \'1234\', \'127.0.0.1\')');        

if (!$result) {
    die('could not insert login to database!');
} 

I’ve vardump’ed $result to confirm that it’s boolean true, and I’m successfully doing SELECTs. Here’s the gist of my database configuration, for the curious:

$active_group = "default";

$db['default']['hostname'] = "//localhost/XE";
$db['default']['username'] = "USERNAME";
$db['default']['password'] = "USERNAME";
$db['default']['database'] = "USERNAME";
$db['default']['dbdriver'] = "oci8";
$db['default']['dbprefix'] = "";
$db['default']['active_r'] = TRUE;
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = ""; 

Thanks!

Profile

tonanbarbarian

Posted: 03 December 2007 09:52 PM

[ Ignore ] [ # 1 ]

Research Assistant

Total Posts: 677

Joined 07-05-2007

try using


$result = $this->db->simple_query('...');


$this->db->query('..');

Will return a database result object where as simple_query will return a boolean result indicating the success or failure of the query

Profile

jabbett

Posted: 03 December 2007 10:33 PM

[ Ignore ] [ # 2 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

No luck with that. I’m trying to execute the query with PHP but completely outside CodeIgniter, and I’m encountering similar problems. I’ve discovered that my INSERT will work if I do a commit at the end, a la:

$c = OCILogon('USERNAME', 'PASSWORD', '//localhost/XE');
$q = 'INSERT INTO logins (userid, datein, sessionid, ipaddress) VALUES (\'abcd\', \'123\', \'1234\', \'127.0.0.1\')';

$stid = OCIParse($c, $q);
$r = oci_execute($stid, OCI_DEFAULT);

OCICommit($c);

OCILogoff($c); 

I’m now hunting for a way to automatically commit with each execute.

Profile

tonanbarbarian

Posted: 03 December 2007 10:35 PM

[ Ignore ] [ # 3 ]

Research Assistant

Total Posts: 677

Joined 07-05-2007

been several years since i did much oracle, but i think there is a command you can run to tell it to autocommit

Profile

jabbett

Posted: 03 December 2007 10:37 PM

[ Ignore ] [ # 4 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

Okay, I shouldn’t have used OCI_DEFAULT there… should be OCI_COMMIT_ON_SUCCESS to commit automatically if the execute is successful. Now to find out why CI isn’t autocommiting…

Profile

tonanbarbarian

Posted: 03 December 2007 10:59 PM

[ Ignore ] [ # 5 ]

Research Assistant

Total Posts: 677

Joined 07-05-2007

Looks like the CI OCI8 driver is setting its commit to OCI_COMMIT_ON_SUCCESS
anyway trying doing the following

$this->db->_commit = OCI_COMMIT_ON_SUCCESS;
$result = $this->db->simple_query('INSERT INTO logins '.
    '(userid, datein, sessionid, ipaddress) '.
    'VALUES (\'abcd\', \'123\', \'1234\', \'127.0.0.1\')');        

if (!$result) {
    die('could not insert login to database!');
} 

Profile

jabbett

Posted: 03 December 2007 11:13 PM

[ Ignore ] [ # 6 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

That didn’t work either.

Here’s a bit more information: in my application’s normal operation, I first do a SELECT, then I do the INSERT (and the INSERT doesn’t get committed). When the INSERT is the first query that hits the DB, it gets committed and I see it in the table.

Profile

tonanbarbarian

Posted: 03 December 2007 11:37 PM

[ Ignore ] [ # 7 ]

Research Assistant

Total Posts: 677

Joined 07-05-2007

Record locking perhaps?
I oracle does do row and even field level locking. Maybe your table is being locked by the select so that the insert does not work.

Profile

jabbett

Posted: 04 December 2007 12:04 AM

[ Ignore ] [ # 8 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

I think I’ve found the culprit. If my analysis is correct, the _set_stmt_id() function in oci8_driver.php will only ever run once.

function _set_stmt_id($sql)
{
    if ( ! is_resource($this->stmt_id))
    {
        $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
    }
} 

When the first query comes through, stmt_id is null, and the body of the if statement gets run, which prepares the SQL for execution. The second time a query comes through, since stmt_id is now a valid resource, the query won’t get parsed and then won’t be executed.

If I take out the “if” line (and its related curly braces), and let every query get parsed, everything seems to work fine.

(Of course, I’m new to this Oracle stuff, and the fact that I’m responsible for debugging a PHP Oracle adapter is unsettling, to say the least.)

Profile

jabbett

Posted: 04 December 2007 12:14 AM

[ Ignore ] [ # 9 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

...and frustrating, given that this same fix has already been recommended…

http://codeigniter.com/forums/viewthread/52849/

...and not yet integrated into the code base! Hmph.

Profile

Derek Allard

Posted: 04 December 2007 12:34 AM

[ Ignore ] [ # 10 ]

Sr. Research Associate

Total Posts: 4777

Joined 03-23-2006

If you have something that you are confident is a bug, please make an entry in the bug tracker.

Signature

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

Profile

MSG

jabbett

Posted: 04 December 2007 12:51 AM

[ Ignore ] [ # 11 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

Already in there… since June.
http://codeigniter.com/bug_tracker/bug/1892/

Profile

Derek Allard

Posted: 04 December 2007 01:12 AM

[ Ignore ] [ # 12 ]

Sr. Research Associate

Total Posts: 4777

Joined 03-23-2006

OK, thanks. I don’t have an oracle db - can you and or the others on this thread speak to the proposed solution in the bug report?

Signature

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

Profile

MSG

jabbett

Posted: 04 December 2007 01:32 AM

[ Ignore ] [ # 13 ]

Summer Student

Total Posts: 28

Joined 08-08-2007

I’ll try to collect the info from all the related forum threads and post a comprehensive fix in the report.

Profile

Derek Allard

Posted: 04 December 2007 01:34 AM

[ Ignore ] [ # 14 ]

Sr. Research Associate

Total Posts: 4777

Joined 03-23-2006

That would be awesome jonathan, and a big help. Thanks. Feel free to contact me personally (derek.allard at ellislab.com) afterwards and I’ll move on it as I can.

Signature

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

Profile

MSG

Rufus09

Posted: 21 April 2011 03:54 PM

[ Ignore ] [ # 15 ]

Summer Student

Total Posts: 6

Joined 04-19-2011

tonanbarbarian - 04 December 2007 02:52 AM
try using
$result = $this->db->simple_query('...');

$this->db->query('..');

Will return a database result object where as simple_query will return a boolean result indicating the success or failure of the query

This worked for me. I really appreciate you posting this solution since I have been trying to figure this out for a while now. I tried just about everything I could think of, but I didn’t think of this. I am pretty relieved to have finally found an answer to my problem. Thank you very much.

Profile

‹‹ SQL Query Help EE Simple Commerce Module with CI ››

Username		Remember Me?
Password		forgot password?