Executing an SQL UPDATE request

4D v16

The method in the example below executes an SQL UPDATE request to update records that were added previously to the Oracle "emp" table. In this method, we update 3 records that were added by the INSERT request. We are only modifying the names (ename column).

Note that here we have done a bind (association of a 4D variable with an Oracle column) by name. In the previous INSERT request, we did the bind by position.

Exemple

Source code for OCI_UPDATE 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 update

 

  //declaration of data to modify

 ARRAY LONGINT(tlu_empno;3) //empno column

 ARRAY TEXT(tau_ename;3) //ename column

 

  //search criteria in SQL request for the update

 tlu_empno{1}:=1111

 tlu_empno{2}:=2222

 tlu_empno{3}:=3333

  //modified data

 tau_ename{1}:="JJ"

 tau_ename{2}:="CC"

 tau_ename{3}:="MM"

 

  //update of employee names in the Oracle "emp" table whose numbers are 1111, 2222, and 3333

  //reminder: it is the data that was inserted that is modified

 $sql_request:="UPDATE emp SET ename=:the_names WHERE empno=:the_numbers"

 

  //allocation of request handle

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

 

  //allocation of error handle

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

 

  //assignment SQL request label to request handle

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

 

  //we do a bind by name. On the INSERT, we did it by position

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

 tau_ename;SQLT_STR;pnull_ind1;pnull_ind2;pnull_ind3;OCI_DEFAULT;BIND_IN)

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

 tlu_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 modification by immediate validation of the transaction

  //otherwise, the update 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)