4D v16

Working with Oracle Date types

Accueil

 
4D v16
Working with Oracle Date types

Working with Oracle Date types  


 

 

Unlike the Oracle Object Date Time (ODT) data type, in 4D dates and times are stored separately. If you encounter difficulties returning values from DATETIME or TIMESTAMP type Oracle columns with 4D for OCI, you should try using the one of the following specific functions:

Since Oracle date fields store the time as well, you must use the SQLT_ODT type and pass two pointers instead of just one: one for the date and a second for the time, even if you do not need to use it.

The functions listed above work the same way as the Oracle commands they are based on (i.e.: OCIDefineByPos, OCIBindByName and OCIBindByPos), except that we have added a second pointer parameter to adapt them for working with Oracle Date fields.

Exemple  

Using OCIDefineDateByPos with a SELECT request:

 ARRAY DATE(date_results;3) //date array for first pointer
 ARRAY LONGINT(time_results;3) //time array for second pointer
 ARRAY LONGINT($arrNull;1)
 ARRAY LONGINT($arrSizes;1)
 ARRAY LONGINT($arrCodes;1)
 
 $arrSizes{1}:=255
 
 $UserName:="xxx"
 $Password:="xxx"
 $OraServ:="xxx"
 
 $sql:="SELECT inv_date FROM invoices"
 
 $Status:=OCIEnvCreate($DescrEnv;OCI_DEFAULT)
 $Status:=OCIHandleAlloc($DescrEnv;$DescrErr;OCI_HTYPE_ERROR)
 $Status:=OCIHandleAlloc($DescrEnv;$DescrService;OCI_HTYPE_SVCCTX)
 $Status:=OCIHandleAlloc($DescrEnv;$DescrAuth;OCI_HTYPE_SESSION)
 $Status:=OCIHandleAlloc($DescrEnv;$DescrServer;OCI_HTYPE_SERVER)
 
 $Status:=OCIServerAttach($DescrServer;$DescrErr;$OraServ)
 $Status:=OCIAttrSetVal($DescrService;$DescrServer;OCI_ATTR_SERVER;$DescrErr)
 $Status:=OCIAttrSetText($DescrAuth;$UserName;OCI_ATTR_USERNAME;$DescrErr)
 $Status:=OCIAttrSetText($DescrAuth;$Password;OCI_ATTR_PASSWORD;$DescrErr)
 $Status:=OCISessionBegin($DescrService;$DescrErr;$DescrAuth;OCI_CRED_RDBMS;OCI_DEFAULT)
 $Status:=OCIAttrSetVal($DescrService;$DescrAuth;OCI_ATTR_SESSION;$DescrErr)
 
 $Status:=OCIHandleAlloc($DescrEnv;$DescrStmt;OCI_HTYPE_STMT)
 $Status:=OCIStmtPrepare($DescrStmt;$DescrErr;$sql;Length($sql))
 
 $sqlt:=SQLT_ODT
 $Status:=OCIDefineDateByPos($DescrStmt;$DescrDefine;$DescrErr;1;->date_results;
 ->time_results;$sqlt;->$arrNull;->$arrSizes;->$arrCodes;OCI_DEFAULT)
 
 $Status:=OCIStmtExecute($DescrService;$DescrStmt;$DescrErr;0;0;0;0;OCI_DEFAULT)
 
 $Status:=OCIStmtFetch($DescrStmt;$DescrErr;10)
 
 $text:=String(date_results{1})+Char(13)
 For($i;2;Size of array(date_results))
    $text:=$text+String(date_results{$i})+Char(13)
 End for
 
 ALERT("ARRAY:"+Char(13)+$text)
 
 $Status:=OCISessionEnd($DescrService;$DescrErr;$DescrAuth)
 $Status:=OCIServerDetach($DescrServer;$DescrErr)
 $Status:=OCIHandleFree($DescrService)
 $Status:=OCIHandleFree($DescrServer)
 $Status:=OCIHandleFree($DescrErr)
 $Status:=OCIHandleFree($DescrEnv)
 
 ARRAY DATE(date_results;0)
 ARRAY DATE(time_results;0)

In addition, here is a short SQL script to create the invoices table:


drop table invoices;
create table invoices (inv_ID varchar(3), date_fact date);
insert into invoices (inv_ID,inv_date) values ('F01','10/05/2014');
insert into invoices (inv_ID,inv_date) values ('F02','11/05/2014');
insert into invoices (inv_ID,inv_date) values ('F03','12/05/2014');
select * from invoices;
commit;

 
PROPRIÉTÉS 

Produit : 4D
Thème : Examples of use
Nom intl. : Working with Oracle Date types

 
HISTORIQUE 

 
UTILISATION DE L'ARTICLE

4D for OCI ( 4D v16)