Executing an SQL SELECT request

Home

4D v16

The method in the example below executes an SQL SELECT request to retrieve values from columns in the Oracle "emp" table, which is a table of employees.

This method starts by allocating a handle for the SQL request (request handle). This type of allocation is typical for methods that work with SQL requests (INSERT, UPDATE, DELETE).

The method selects all the employees but only certain columns (fields). Here we select the employee number (empno), their name (ename), job title (job) and hire date (hiredate).
Once the label of the SQL request and the request handle have been defined and initialized, they are linked using the OCIStmtPrepare command.

For each column implicated in the SQL request, we associate an array in 4D to retrieve the values. To link the 4D array with the targeted Oracle column, we use the OCIDefineByPos command, which expects the number of the column mentioned in the SQL request.

Since Oracle's Date type is particular in that it consists of both a date and a time part, we have used the same command here as well (OCIDefineByPos command). We did this because the time component is of no interest to us here; otherwise we would have needed to use the OCIDefineDateByPos command.

Example

Source code of the OCI_SELECT 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($define) //define handle

 

  //information to retrieve from the Oracle table named 'emp'

 C_LONGINT($column_number) //number of column to retrieve after execution of the SQL request

 ARRAY LONGINT(tls_empno;20) //employee numbers ('empno' column)

 ARRAY TEXT(tas_ename;20) //employee names ('ename' column of 'emp' table)

 ARRAY TEXT(tas_job;20) //employee jobs ('job' column of 'emp' table)

 ARRAY DATE(tds_hiredate;20) //hire dates ('hiredate' column of 'emp' table)

 C_LONGINT($max_emp) //maximum number of employees to retrieve

 C_LONGINT(null_ind1;null_ind2;null_ind3) //indicator variables

 

  //selection request of list of numbers, names, jobs and hire dates of all employees

  //in the Oracle ‘emp’ table

 $requete_sql:="SELECT empno, ename, job, hiredate FROM emp"

 

  //allocation of request handle. The envhp environment handle

  //has been allocated in the OCI_CONNECT connection method

 $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;$requete_sql;OCI_DEFAULT)

 

  //indicator variables for OCIDefineByPos() command

  //The information returned by these variables is useless in our example

  //These variables can be used to find out whether there are any NULL or truncated values.

 null_ind1:=0

 null_ind2:=0

 null_ind3:=0

 

  //implementation of the Define for each column specified in the SQL request

  //Note that $define, which specifies the define handle, did not need to be explicitly allocated.

 

 $column_number:=1 //number of column pointed to, recovered by executing the (empno) request

 $status:=OCIDefineByPos($stmthp;$define;$errhp;$column_number;->tls_empno;SQLT_INT;

 ->null_ind1;->null_ind2;->null_ind3;OCI_DEFAULT)

 

 $column_number:=2 //number of column pointed to, recovered by executing the (ename) request

 $status:=OCIDefineByPos($stmthp;$define;$errhp;$column_number;->tas_ename;SQLT_STR;

 ->null_ind1;->null_ind2;->null_ind3;OCI_DEFAULT)

 

 $column_number:=3 //number of column pointed to, recovered by executing the (job) request

 $status:=OCIDefineByPos($stmthp;$define;$errhp;$column_number;->tas_job;SQLT_STR;

 ->null_ind1;->null_ind2;->null_ind3;OCI_DEFAULT)

 

 $column_number:=4 //number of column pointed to, recovered by executing the (hiredate) request

 $status:=OCIDefineByPos($stmthp;$define;$errhp;$column_number;->tds_hiredate;SQLT_ODT;

 ->null_ind1;->null_ind2;->null_ind3;OCI_DEFAULT)

 

  //retrieval of up to 20 employee records

 $max_emp:=20

 

  //execution of SQL request

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

 

  //release of request handle$status:=OCIHandleFree ($stmthp)

 

  //release of error handle

 $status:=OCIHandleFree($errhp)