Problem:
Support for CURRENT TIMESTAMP is likely to be required in migrations from Cobol AS/400 with Embedded SQL to Windows platforms using Micro Focus COBOL and OpenESQL.
This is an example of a sentence where CURRENT TIMESTAMP is used. In this case, CURRENT TIMESTAMP is substituted by the current date time at execution and used as a value for column TSCOLUMN:
EXEC SQL
INSERT INTO MYTABLE (COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
TSCOLUMN)
VALUES (:WT-COLUMN1,
:WT-COLUMN2,
:WT-COLUMN3,
:WT-COLUMN4,
CURRENT TIMESTAMP)
END-EXEC.
Resolution:
From Micro Focus Net Express 3.1, OpenESQL supports the CURRENT TIMESTAMP expression, but on top of that, your ODBC driver and DBMS must support it as well.
When OpenESQL doesn't support something, you are likely to get a compilation error, not an execution error (an SQL error).
If your DBMS doesn't support some syntax then you will be getting an SQL error on execution.
Microsoft SQL Server is not supporting this type of syntax in this example.
Searching the MSDN website, the following is found:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp
"INSERT
....
If a column is not in column_list, Microsoft® SQL Server must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. SQL Server automatically provides a value for the column if the column:
...
- Has a timestamp data type. The current timestamp value is used.
..."
Taking this into account, one solution would be simply to omit the column from your INSERT statement and Microsoft SQL Server will automatically provide the current timestamp for that column:
EXEC SQL
INSERT INTO MYTABLE (COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4)
VALUES (:WT-COLUMN1,
:WT-COLUMN2,
:WT-COLUMN3,
:WT-COLUMN4)
END-EXEC.
Alternatively, If you are using a different DBMS that doesn't support this Microsoft SQL Server feature or a similar solution you can always provide the current timestamp using the COBOL intrinsic function CURRENT-DATE:
01 cur-date.
05 yyyy pic xxxx.
05 mm pic xx.
05 dd pic xx.
05 hh pic xx.
05 mm pic xx.
05 ss pic xx.
05 ff pic xx.
05 gmt pic x(5).
01 sql-cur-date.
05 yyyy pic xxxx.
05 pic x value "-".
05 mm pic xx.
05 pic x value "-".
05 dd pic xx.
05 pic x value spaces.
05 hh pic xx.
05 pic x value ":".
05 mm pic xx.
05 pic x value ":".
05 ss pic xx.
05 pic x value ".".
05 ff pic xx.
05 pic x value "0".
05 pic x(6) value spaces.
....
MOVE FUNCTION CURRENT-DATE () TO cur-date
MOVE CORRESPONDING cur-date TO sql-cur-date
EXEC SQL
INSERT INTO MYTABLE (COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
TSCOLUMN)
VALUES (:WT-COLUMN1,
:WT-COLUMN2,
:WT-COLUMN3,
:WT-COLUMN4,
:sql-cur-date)
END-EXEC.
