Re: timestamp not correct

On 24 May 2013 09:22, aec wrote:

When you define a TIME, DATE, or TIMESTAMP column in your DDL, and
you run an INSERT statement, whether in SQL/Procedure Language or
from embedded SQL in an RPG program, the SQL will populate the above
type fields using the CURRENT_TIMESTAMP as a default value if you do
not specify the column in the list of values to populate.

The SQL INSERT will assign the DEFAULT value for the column.

Record format for file CUST:

create table cust

(Nowdate date, nowtime time, cust# dec (9,0), custname char(50) )

The statement:
INSERT INTO CUST (Cust#, Custname) VALUES
( 123, 'David Livingston')
will result in a new record with values
('2013-05-24', '12:17:00', 123, 'David Livingston').

Actually with the above DDL, the CREATE TABLE above, the unspecified columns are implicitly defined with the NULL value as the DEFAULT. The request to SELECT * FROM CUST after the noted INSERT would show:
....+....1....+....2....+....3....+....4....+....5 <<SNIP>>
NOWDATE NOWTIME CUST# CUSTNAME
- - 123 David Livingston
******** End of data ********

The assigned DEFAULT value when no value was specified for a NULL-capable column [i.e. those for which NOT NULL attribute was not established] will be the NULL value. To ensure the CURRENT register is established, explicitly specify DEFAULT CURRENT_whatever. And if the columns had been created with NOT NULL and no DEFAULT was specified, then the INSERT would be expected to fail with SQL0407 "Null values not allowed in column or variable NOWDATE."

BUT if you name them, you have to supply the value.

Or specify DEFAULT in a VALUES() to assign the default.

RPG recognizes externally defined fields as if they were
program-described fields, not defined with a current timestamp. I
assume that embedded SQL has to carry over this into the
corresponding generated RPG code for externally-described files, in
general, if the column is brought specifically into play in the RPG
or embedded RPG, even if it is only implicitly pulled in with type.

I am unsure what is intended to be implied here, but the SQL is fully aware of the description of the fields, and for /program described/ the fields are represented to the SQL as CHAR FOR BIT DATA. The database [not the SQL] assigns the default value; the requester [e.g. the SQL INSERT] effectively just builds a mask to represent which columns of the format for which actual data was provided via their write buffer.

I think if you run the above INSERT within a stored procedure it
will act the same way.

?? same as what? The INSERT is the same SQL statement regardless if it was dynamic, static embedded, or extended dynamic. The effects should be predictable regardless of interface. If implying /same way/ as described of the RPG in the prior comment... well I was not sure what was implied there.