Created On:  04 December 2012

Problem:

Customer is converting a native code Net Express application that uses Oracle's Pro*COBOL precompiler to access Oracle databases, to use Visual COBOL .NET managed code using the Oracle Data Provider ODP.NET driver.

OpenESQL supports dates that are in ISO format YYYY-MM-DD but the application is using the Oracle format of MM-DD-YYYY.
How can they continue to use the Oracle formats so that they do not have to change their source code?

Resolution:

Starting with Visual COBOL 2.1 Hotfix 2, there is a new SQL directive called DATE=EXTERNAL.

When this directive is set and the program is using the Oracle Data Provider for .NET (ODP.NET) then the date format used will be that set by the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT settings of Oracle or the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT environment variables if they are set.

This only works with the ODP.NET and does not work with the Microsoft Oracle ADO driver.

Here is an example program that uses this feature:

     $set sql(dbman=ado DATE=EXTERNAL behavior=mainframe)
       program-id. ADOFDate.

       data division.
       working-storage section.
      
       exec sql include sqlca end-exec.
       exec sql include sqlda end-exec.
      
       01  dte     sql type Date.
       01  ts      sql type Timestamp.
       01  ds      pic x(10).
       01  dss      pic x(10).
       01  tss     pic x(30).
       01  empno   binary-short.
       01  sal     pic s9(5)V99 comp-3.
       01  env-val pic x(30).

       procedure division.
      
           display "NLS_DATE_FORMAT" upon environment-name
           display "DD.MM.YYYY" upon environment-value
           accept env-val from environment-value
           *>display "NLS_DATE_FORMAT = " env-val
          
           exec sql
                connect to TESTORADATE
            end-exec.
           
           perform date-query
          
           move "25.12.2012" to ds
           exec sql
               update emp set hiredate = :ds where empno = 7521
           end-exec
          
           if sqlcode not = 0 or sqlerrd(3) not = 1
               display "Update failed " ds " " sqlcode " " sqlerrmc
           end-if
          
           exec sql rollback end-exec
          
           exec sql disconnect end-exec
          
           display "Test complete"

           goback.
      
       date-query section.
      
           exec sql declare c cursor for
               select empno, sal, hiredate, hiredate, to_char(hiredate)
               from emp
               where empno = 7521
           end-exec
          
           exec sql open c end-exec
          
           initialize empno sal dte ds dss
           exec sql fetch c into :empno, :sal, :dte, :ds, :dss end-exec
          
           exec sql close c end-exec
         
           *>display empno ' ' sal ' ' dte ' ' ds ' ' dss
           if empno not = 7521 or sal not = 1250.00
               or dte(3:1) not = '.' or dte(6:1) not = '.'
               or ds(3:1) not = '.' or ds(6:1) not = '.'
               or dss(3:1) not = '.' or dss(6:1) not = '.'
              
               display "Forward cursor fetch failed"
           end-if
          
           exec sql declare sc scroll cursor for
               select empno, sal, hiredate, hiredate, to_char(hiredate)
               from emp
               where empno = 7521
           end-exec              
          
           exec sql open sc end-exec
          
           initialize empno sal dte ds dss
           exec sql fetch sc into :empno, :sal, :dte, :ds, :dss end-exec
          
           exec sql close sc end-exec
         
           *>display empno ' ' sal ' ' dte ' ' ds ' ' dss
           if empno not = 7521 or sal not = 1250.00
               or dte(3:1) not = '.' or dte(6:1) not = '.'
               or ds(3:1) not = '.' or ds(6:1) not = '.'
               or dss(3:1) not = '.' or dss(6:1) not = '.'
              
               display "Scroll cursor fetch failed"
           end-if
         
           exit section.
           
       end program ADOFDate.