4D v16

Executing an SQL SELECT request

ホーム

 
4D v16
Executing an SQL SELECT request

Executing an SQL SELECT request  


 

 

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.

例題  

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)

 
プロパティ 

プロダクト: 4D
テーマ: Examples of use

 
履歴 

 
ARTICLE USAGE

4D for OCI ( 4D v16)