PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
I am running the following code and I get to the marked line and it shows that $success is "true" but when I check the data after completing the script, the data doesn't get updated.
public function updateRecord($query, $bindsArray){
$conn = $this->getConnection();
$query = $this->deanonimize($query);
$stmt = oci_parse($conn, $query);
if(!$this->bindToStatement($stmt, $bindsArray)){
$err = oci_error($stmt);
$this->setOciErrorMsg($err);
}
$success = oci_execute($stmt);
if(!$success){ // <------------ this is where I can view that $success is 'true';
$err = oci_error($stmt);
$this->setOciErrorMsg($err);
}
return $success;
}
I know the SQL is correct, because I can pull it out and run it in SQL*Plus and get the appropriate changes made.
Anybody have any ideas why I'd get a "true" result when the oci_execute() either doesn't run the SQL or doesn't commit it?
Last edited by vaughtg on Tue May 03, 2011 11:52 am, edited 2 times in total.
public function updateRecord($query, $bindsArray){
$conn = $this->getConnection();
$query = $this->deanonimize($query);
$stmt = oci_parse($conn, $query);
if(!$this->bindToStatement($stmt, $bindsArray)){
$err = oci_error($stmt);
$this->setOciErrorMsg($err);
}
$success = oci_execute($stmt, OCI_NO_AUTO_COMMIT); // <--------------- UPDATED FUNCTION CALL
if($success){ // this block has been modified to allow for the lack of auto-commit functionality
oci_commit($conn);
}else{
oci_rollback($conn);
$err = oci_error($stmt);
$this->setOciErrorMsg($err);
}
return $success;
}
Now, what I get back from the oci_execute() function is a null. The documentation on the API indicates that this is supposed to be a boolean. Is it possible that the function is dying midstream and not returning anything? That's the only option I can think of at the moment. And if so, what can be done about that?
And another update to the code. I found that the statement was not being maintained as consistent when being passed to the bindToStatement() function, so I took that out and found that there were bind issues occurring, so I had to modify the oci_bind_by_name() paramters to the following. Still getting a null returned, though, and oci_error() returns nothing.
After input and feedback from numerous sources, along with some significant troubleshooting, I've figured out that the issue is with the oci_bind_by_name() call on a number field, inserting either a string or an integer by variable (I tried both). I ended up running through and running down the cause for half-a-dozen Oracle errors and ended up with a ORA-01722: invalid number error.
I finally ended up just validating the data (server-side) as an integer (should NEVER be over 5 for this implementation) and concatenating it into the query string - no binding involved for that field. After that, it executed and committed perfectly as expected.
I hope this ends up helping someone else, otherwise this is just documentation for myself.