4D v16

Executing an SQL INSERT request

Home

 
4D v16
Executing an SQL INSERT request

Executing an SQL INSERT request  


 

 

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.

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)

 
EIGENSCHAFTEN 

Produkt: 4D
Thema: Examples of use

 
GESCHICHTE 

 
ARTIKELVERWENDUNG

4D for OCI ( 4D v16)