Page 1 of 1

[SOLVED] oci_execute($stmt) returns a false "true" or null

Posted: Mon May 02, 2011 3:51 pm
by vaughtg
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.

Code: Select all

  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?

Re: oci_execute($stmt) returns a "false" true

Posted: Tue May 03, 2011 9:14 am
by vaughtg
Okay, I've made the change denoted below:

Code: Select all

  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?

Re: oci_execute($stmt) returns a "false" true

Posted: Tue May 03, 2011 9:49 am
by vaughtg
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.

Code: Select all

  public function updateRecord($query, $bindsArray){
    $conn = $this->getConnection();
    $query = $this->deanonimize($query);
    $stmt = oci_parse($conn, $query);
    $count = 0;
    foreach($bindsArray as $var){
      $placeholder = ":ph$count";
      if(!oci_bind_by_name($stmt, $placeholder, $var)){
        $this->setErrorMsg("- Error binding values in getResultSet()");
        $this->setOciErrorMsg(oci_error($stmt));
        $stmt = false;
        break;
      }
      $count++;
    }
    if(!$stmt){
      $err = oci_error($stmt);
      $this->setOciErrorMsg($err);
    }
    $success = oci_execute($stmt);
    if(!$success){
      $err = oci_error($stmt);
      $this->setOciErrorMsg($err);
    }
    $this->closeConnection();
    return $success;
  }

Re: oci_execute($stmt) returns a false "true" or null

Posted: Tue May 03, 2011 11:51 am
by vaughtg
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.