Working with Oracle Date types

4D v16

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;