Executing an SQL DELETE request

4D v19

The method in the example below executes an SQL DELETE request in order to remove records added previously in the Oracle "emp" table.

We delete the records that were inserted and updated previously. The code of this method is similar to that of the UPDATE request in that we have also done a bind (association of a 4D variable with an Oracle column) by name.

Exemple

Source code of the OCI_DELETE project method:

 C_TEXT($sql_request) //label of SQL request

 

 C_LONGINT($status) //return code of OCI commands

 C_LONGINT($errhp) //error handle

 C_LONGINT($stmthp) //request handle

 C_LONGINT($bind) //bind handle

 

 C_POINTER(pnull_ind1;pnull_ind2;pnull_ind3) //indicator variables (see the OCI_SELECT method)

 

 C_LONGINT($nb_emp) //number of employees to delete from the Oracle database

 ARRAY LONGINT(tld_empno;3) //declaration of data to search for deletion

 

  //numbers of employee records to delete from Oracle database

 tld_empno{1}:=1111

 tld_empno{2}:=2222

 tld_empno{3}:=3333

 

  //deletion from Oracle emp table of employees whose numbers are 1111, 2222, and 3333

  //these employees were added using the OCI_INSERT method

 $sql_request:="DELETE FROM emp WHERE empno=:numemp"

 

  //allocation of request handle

 $status:=OCIHandleAlloc(envhp;$stmthp;OCI_HTYPE_STMT)

 

  //allocation of error handle

 $status:=OCIHandleAlloc(envhp;$errhp;OCI_HTYPE_ERROR)

 

  //assigning of SQL request label to request handle

 $status:=OCIStmtPrepare($stmthp;$errhp;$sql_request;OCI_DEFAULT)

 

  //we do a bind by name

  //note that the $bind bind handle did not need to be allocated explicitly

 $status:=OCIBindByName($stmthp;$bind;$errhp;":numemp";->

 tld_empno;SQLT_INT;pnull_ind1;pnull_ind2;pnull_ind3;OCI_DEFAULT;BIND_IN)

 

  //execution of SQL request

 $nb_emp:=3

 $status:=OCIStmtExecute(svchp;$stmthp;$errhp;$nb_emp;0;0;0;OCI_DEFAULT)

 

  //confirmation of deletion by immediate validation of the transaction

  //otherwise, the deletion is done when the session is closed

 $status:=OCITransCommit(svchp;$errhp;0)

 

  //release of request handle

 $status:=OCIHandleFree($stmthp)

 

  //release of error handle

 $status:=OCIHandleFree($errhp)