Re: timestamp not correct

Thanks, Chuck.
I thought that I had tested this out and that the behaviors were as described, but I don't have handy access to a recent version of the IBM i and I would recommend that readers go with your explanations. I'll believe you until I can confirm it. :) You haven't disappointed yet...


On 5/24/13 1:23 PM, CRPence wrote:

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.