Executing an SQL INSERT request

4D v16

The method in the example below executes an SQL INSERT request to add records to the Oracle "emp" table.

The values to be added are stored in 4D arrays. We associate the data to be added with the Oracle columns by using the ":" character in the SQL request. The association of 4D data with Oracle columns can be done either by position (OCIBindByPos) or by name (OCIBindByName).

Note that for inserting values of the Date type, we use the OCIBindDateByPos command, which expects both date and time variables to be passed. This way it is able to represent the entirety of the Oracle Object Date Time (ODT) type, which contains 2 parts: date and time. Since the time part does not concern us, we used a 4D time variable with a null value.

Ejemplo

Source code for OCI_INSERT 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 insert

 

  //declaration of data to insert

 ARRAY LONGINT(tli_empno;3) //empno column

 ARRAY TEXT(tai_ename;3) //ename column

 ARRAY TEXT(tai_job;3) //job column

 ARRAY LONGINT(tli_mgr;3) //mgr column

 ARRAY DATE(tdi_hiredate;3) //hiredate column

 C_TIME(null_time) //time type variable, in addition to the date column, to represent

  //the whole Oracle ODT ODT type, which includes the date and time

 ARRAY LONGINT(tli_sal;3) //sal column

 ARRAY LONGINT(tli_comm;3) //comm column

 ARRAY LONGINT(tli_deptno;3) //deptno column

 

  //filling in data to be inserted

  //empno column

 tli_empno{1}:=1111

 tli_empno{2}:=2222

 tli_empno{3}:=3333

  //ename column

 tai_ename{1}:="Joel"

 tai_ename{2}:="Catherine"

 tai_ename{3}:="Marianne"

  //job column

 tai_job{1}:="analyst"

 tai_job{2}:="salesperson"

 tai_job{3}:="manager"

  //mgr column

 tli_mgr{1}:=7902

 tli_mgr{2}:=7698

 tli_mgr{3}:=7788

  //hiredate column

 tdi_hiredate{1}:=!01/01/03!

 tdi_hiredate{2}:=!02/01/03!

 tdi_hiredate{3}:=!03/01/03!

 null_time:=?00:00:00?

  //sal column

 tli_sal{1}:=1915

 tli_sal{2}:=2012

 tli_sal{3}:=1713

  //comm column

 tli_comm{1}:=100

 tli_comm{2}:=200

 tli_comm{3}:=150

  //deptno column

 tli_deptno{1}:=20

 tli_deptno{2}:=30

 tli_deptno{3}:=20

 

  //SQL request for data insertion. We specify the columns of the 'emp' table which will be filled

  //Note the use of the ":" character to indicate that a variable will provide the data to insert

  //This indication will either be by name (name of the 4D variable) or by position (number of

  //position in the request).

 $sql_request:="INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)"

 $sql_request:=$sql_request+" VALUES (:tli_empno, :tai_ename, :tai_job, :tli_mgr,"

 $sql_request:=$sql_request+" :tdi_hiredate, :tli_sal,:tli_comm,:tli_deptno)"

 

  //allocation of request handle

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

 

  //allocation of error handle

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

 

  //assigning the label of the SQL request to the request handle

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

 

  //performing the bind for each column of the request. Each bind is carried out by position

  //note that the $bind bind handle does not have to be explicitly allocated

 $status:=OCIBindByPos($stmthp;$bind;$errhp;1;->

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

 $status:=OCIBindByPos($stmthp;$bind;$errhp;2;->

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

 $status:=OCIBindByPos($stmthp;$bind;$errhp;3;->

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

 $status:=OCIBindByPos($stmthp;$bind;$errhp;4;->

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

 

  //the Oracle ODT type recovers both the date and time

  //since the time value does not interest us here, it is passed as null

 $status:=OCIBindDateByPos($stmthp;$bind;$errhp;5;->null_time;->

 tdi_hiredate;SQLT_ODT;pnull_ind1;pnull_ind2;pnull_ind3;OCI_DEFAULT;BIND_IN)

 $status:=OCIBindByPos($stmthp;$bind;$errhp;6;->

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

 $status:=OCIBindByPos($stmthp;$bind;$errhp;7;->

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

 $status:=OCIBindByPos($stmthp;$bind;$errhp;8;->

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

 

  //number of employees to insert

 $nb_emp:=3

 

  //execution of SQL request

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

 

  //confirmation of insertion by immediate validation of the transaction

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