× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.