Part of the EllisLab Network
CodeIgniter  >   Forum Home  >  CodeIgniter Development Forums  >  CodeIgniter Discussion  >  Thread
   
 
Oracle INSERTs failing, but no errors
 
jabbett
Posted: 03 December 2007 08:44 PM   [ Ignore ]  
Summer Student
Avatar
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 08:52 PM   [ Ignore ]   [ # 1 ]  
Research Assistant
RankRankRank
Total Posts:  483
Joined  07-04-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 09:33 PM   [ Ignore ]   [ # 2 ]  
Summer Student
Avatar
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 09:35 PM   [ Ignore ]   [ # 3 ]  
Research Assistant
RankRankRank
Total Posts:  483
Joined  07-04-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 09:37 PM   [ Ignore ]   [ # 4 ]  
Summer Student
Avatar
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 09:59 PM   [ Ignore ]   [ # 5 ]  
Research Assistant
RankRankRank
Total Posts:  483
Joined  07-04-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 10:13 PM   [ Ignore ]   [ # 6 ]  
Summer Student
Avatar
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 10:37 PM   [ Ignore ]   [ # 7 ]  
Research Assistant
RankRankRank
Total Posts:  483
Joined  07-04-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: 03 December 2007 11:04 PM   [ Ignore ]   [ # 8 ]  
Summer Student
Avatar
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: 03 December 2007 11:14 PM   [ Ignore ]   [ # 9 ]  
Summer Student
Avatar
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: 03 December 2007 11:34 PM   [ Ignore ]   [ # 10 ]  
Administrator
Avatar
RankRankRankRankRankRank
Total Posts:  6905
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
BambooInvoice - Open Source, CodeIgniter powered invoicing.

Profile
MSG
 
 
jabbett
Posted: 03 December 2007 11:51 PM   [ Ignore ]   [ # 11 ]  
Summer Student
Avatar
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 12:12 AM   [ Ignore ]   [ # 12 ]  
Administrator
Avatar
RankRankRankRankRankRank
Total Posts:  6905
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
BambooInvoice - Open Source, CodeIgniter powered invoicing.

Profile
MSG
 
 
jabbett
Posted: 04 December 2007 12:32 AM   [ Ignore ]   [ # 13 ]  
Summer Student
Avatar
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 12:34 AM   [ Ignore ]   [ # 14 ]  
Administrator
Avatar
RankRankRankRankRankRank
Total Posts:  6905
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
BambooInvoice - Open Source, CodeIgniter powered invoicing.

Profile
MSG
 
 
   
 
 
‹‹ SQL Query Help      EE Simple Commerce Module with CI ››
Post Marker Legend
New Topic New posts Hot Topic Hot Topic with new posts New Poll New Poll Moved Topic Moved Topic Sticky Topic Sticky topic
Old Topic No new posts Hot Old Topic Hot Topic with no new posts Old Poll Old Poll Closed Topic Closed Topic Announcement Announcements
Theme
Change Theme
Visitor Statistics
The most visitors ever was 719, on June 06, 2008 10:16 AM
Total Registered Members: 77529 Total Logged-in Users: 21
Total Topics: 101537 Total Anonymous Users: 4
Total Replies: 544308 Total Guests: 272
Total Posts: 645845    
Members ( View Memberlist )
Newest Members:  top10review722,  AlanK3818,  gemmortgages,  cdlabel,  ttapan,  gradozero,  sagaasu44,  bovinecolostrum,  nvacclogic583329,  aghajee38420
Active Members:    balord,  Caelis,  Crucial,  ezsolt,  garymardell,  Gaz,  JasonS,  jayrulez,  jedd,  john lanz,  klassev,  MrEnirO,  Pascal Kriete,  polyvoid,  silenz,  slowgary,  spmckee,  steven2,  Tom Glover,  Wades,  _Michael_
Powered By ExpressionEngine
ExpressionEngine Discussion Forum - Version 2.1.1 (20081028)
Script Executed in 0.5579 seconds
Atom Feed   RSS 2.0