SQL TYPEs

Manipulating SQL data that involves date, time, or binary data can be complicated using traditional COBOL host variables, and traditional techniques for handling variable-length character data can also be problematic. To make working with this data easier, we provide the SQL TYPE declaration to make it easier to specify host variables that more closely reflect the natural data types of relational data stores. This allows more applications to be built using static rather than dynamic SQL syntax, and also can help to optimize code execution.

Note: For a complete listing of available SQL TYPEs, see the SQL TYPEs reference topic.

Example: Defining date, time, and timestamp fields as SQL TYPEs

This example program shows date, time and timestamp escape sequences being used, and how to redefine them as SQL TYPEs. It applies to both OpenESQL and DB2 ECM:

 working-storage section.
 EXEC SQL INCLUDE SQLCA END-EXEC

 01  date-field1      pic x(29).
 01  date-field2      pic x(29).
 01  date-field3      pic x(29).

 procedure division.
     EXEC SQL
        CONNECT TO 'Net Express 4.0 Sample 1' USER 'admin'
     END-EXEC
* If the Table is there drop it.
     EXEC SQL
        DROP TABLE DT
     END-EXEC

* Create a table with columns for DATE, TIME, and DATE/TIME
* NOTE:  Access uses DATETIME column for all three.
*        Some databases will have dedicated column types.
* If you are creating DATE/TIME columns on another data 
* source, refer to your database documentation to see how to
* define the columns.

     EXEC SQL
        CREATE TABLE DT ( id  INT,
                        myDate DATE NULL,
                        myTime TIME NULL,
                        myTimestamp TIMESTAMP NULL)
     END-EXEC

* INSERT into the table using the ODBC Escape sequences

     EXEC SQL
        INSERT into DT values (1 ,
            {d '1961-10-08'},  *> Set just the date part
            {t '12:21:54'  },  *> Set just the time part
            {ts '1966-01-24 08:21:56' } *> Set both parts
                              )
     END-EXEC

* Retrieve the values we just inserted
   
     EXEC SQL
        SELECT myDate
              ,myTime
              ,myTimestamp
         INTO  :date-field1
              ,:date-field2
              ,:date-field3
         FROM DT
         where id = 1
     END-EXEC

* Display the results.

     display 'where the date part has been set :' 
             date-field1
     display 'where the time part has been set :' 
             date-field2
     display 'NOTE, most data sources will set a default '
             'for the date part '
     display 'where both parts has been set :' 
             date-field3

* Remove the table.
   
     EXEC SQL
        DROP TABLE DT
     END-EXEC

* Disconnect from the data source

     EXEC SQL
        DISCONNECT CURRENT
     END-EXEC

     stop run.

Alternatively you can use host variables defined with SQL TYPEs for date/time variables. Define the following host variables:

01  my-id          pic s9(08) COMP-5.
01  my-date        sql type is date.
01  my-time        sql type is time.
01  my-timestamp   sql type is timestamp.

and replace the INSERT statement with the following code:

*> INSERT into the table using SQL TYPE HOST VARS
    move 1                           to  MY-ID
    move "1961-10-08"                to  MY-DATE
    move "12:21:54"                  to  MY-TIME
    move "1966-01-24 08:21:56"       to  MY-TIMESTAMP

     EXEC SQL
        INSERT into DT value (
          :MY-ID
         ,:MY-DATE
         ,:MY-TIME
         ,:MY-TIMESTAMP  )
     END-EXEC