Oracle INSERTs failing, but no errors

[eluser]jabbett[/eluser]
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.

Code:

$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:

Code:

$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!

[eluser]tonanbarbarian[/eluser]
try using

Code:

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

Code:

$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

[eluser]jabbett[/eluser]
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:

Code:

$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.

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

[eluser]jabbett[/eluser]
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...

[eluser]tonanbarbarian[/eluser]
Looks like the CI OCI8 driver is setting its commit to OCI_COMMIT_ON_SUCCESS
anyway trying doing the following

Code:

$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!');
}

[eluser]jabbett[/eluser]
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.

[eluser]tonanbarbarian[/eluser]
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.

[eluser]jabbett[/eluser]
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.

Code:

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.)

[eluser]jabbett[/eluser]
...and frustrating, given that this same fix has already been recommended...

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

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