ADG_REDIRECT_DML in Oracle Active Data Guard 19c

on
0

Last Insert ID with PHP & Oracle

on
This article is continue of earlier article Using PHP with Oracle database. You can expand the part of dbUtil.php with following code: 
public function executeVariable($sql, $printSql = false) {
 
   if ($this->connection) {
      // Prepare the statement
      $stid = oci_parse($this->connection, $sql);

      if($printSql) {
         echo $sql;  
      }

      if (!$stid) {
         $e = oci_error($this->connection);
         var_dump($e);
      }
 
      OCIBindByName($stid,":ID",$id);
  
      // Perform the logic of the query
      $r = oci_execute($stid);

      if (!$r) {
         $e = oci_error($stid);
         var_dump($e);
      }

      return array("result" => $r, "lastID" => $id);
   }
}

Now you can use new function executeVariable() as following:

<?php 
error_reporting(E_ALL);

require_once 'dbUtil.php';
$db = new DBUtil();

$sql = $db -> executeVariable("insert into dbtool_schema.test (id, name) values (1,'test') returning id into :id",true);
print "
".$sql["lastID"];
?>

Comments

  1. Tejas Patel17 August, 2012 13:58

    very informative blog by lucia!! thanks

    ReplyDelete
  2. José Gabriel González26 October, 2012 03:32

    Sorry Lucia but to me this script not work. I give this error: "ORA-00947: not enough values in..."

    ReplyDelete
    Replies
    1. Lucia Hustatyova26 October, 2012 10:12

      This error is very general, could you please paste your code? Or if you want, you can send me it on my e-mail: lucia.hustatyova@gmail.com

      Delete
    2. José Gabriel González26 October, 2012 17:31

      Sorry, is my fault. Missing a field's value in the Insert Statement. I solve this, but, although now I can execute de Insert Statement without problem and I can see the register in my table, the variable binding to get the returning last inserted id always it have "int 0".

      Delete
    3. Lucia Hustatyova27 October, 2012 15:47

      Great ;) Main problem is solved. For the table do you have created also sequence and trigger?

      Delete
  3. José Gabriel González26 October, 2012 16:10

    Well, I've a class named Conection where I'm abstracting all OCI8 function, but I'll try to show you a code simple equivalent:

    $conn = oci_pconnect($user,$passw,$str_conexion);

    $sql = 'INSERT INTO GOE_TECNIC_ASIG_EVENT (FE_SINCE ,NU_DOCM_IDNT ,CO_EVENT ,IN_STATUS ,USR_INIT ,DATE_INIT ) VALUES (SYSDATE, '2867', 'A', '1', SYSDATE) RETURNING ID_TECNIC_ASIG_EVENT INTO :id';

    $sent_stid = oci_parse($conn, $sql);

    // I try with this code also, but neither work
    // $maxlength = -1;
    // oci_bind_by_name($sent_stid, ':id', $id, $maxlength, OCI_B_INT);

    oci_bind_by_name($sent_stid, ':id', $id);

    $r = oci_execute($sent_stid); //Here is the error

    ReplyDelete
  4. José Gabriel González26 October, 2012 17:48

    HAHAHA Sorry again, this error too is my fault. This method is full functional, thank for your help. I was printing with var_dump function the variable before execute the statement with oci_execute. -.- I'm a stupid.

    This Binding is functional too and allow you specify the type if the data is Integer:

    ...
    oci_bind_by_name($stid, ':id_asig', $id_asig, $maxlength, OCI_B_INT);
    ...

    ReplyDelete
  5. camimaster04 May, 2014 04:16

    thank you very much, I not use your method but I modify my code to append just the relevant segment.
    XD

    ReplyDelete

Post a Comment