RE: Problem with view that uses an SQL function created from a procedure within a service program

Rob,

Thanks for the explanation. Very clear and concise. I just was not aware
that in the original message that the "can only happen" referred to when
data integrity is checked (Write -vs- Read).

I remembered that after your prompting. Your explanation as to Why DDS
allows invalid data sort of makes sense. That is, your explanation was
clear; the "sort of" refers to the decision to provide that ability (no
check on write). The SQL check-on-write is, I assume, part of the SQL
consortium rules.

Again, thanks.

Jerry C. Adams
I've come to the conclusion that I'm a reasonably intelligent person who
does moderately stupid things on a semi-regular basis.
IBM i Programmer/Analyst
--
NMM&D
615-832-2730

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
Rob Berendt
Sent: Tuesday, November 03, 2020 8:57 AM
To: Midrange Systems Technical Discussion
Subject: RE: Problem with view that uses an SQL function created from a
procedure within a service program

Jerry,
Create two files.

One with DDS
R PFTEST1R
MYDEC 15P 0

One with DDL
CREATE TABLE ROB.PFTEST2 (MYDEC DEC (15 , 0) NOT NULL WITH DEFAULT)
RCDFMT PFTEST2R

DSPFD shows them both with
Maximum record length . . . . . . . . . . . : 8

So create a third file
CRTPF FILE(ROB/PFTEST3) RCDLEN(8)
Put some data into it
INSERT INTO ROB.PFTEST3 VALUES('AAAAAAAA')

Now copy that over PFTEST1
CPYF FROMFILE(ROB/PFTEST3) TOFILE(ROB/PFTEST1)
MBROPT(*REPLACE) FMTOPT(*NOCHK)
CPC2955 - 1 records copied from member PFTEST3.

Check for invalid data
RUNQRY QRYFILE((ROB/PFTEST1))
QRY1161 - Data mapping error 1 running query.

Try to corrupt the DDL file
CPYF FROMFILE(ROB/PFTEST3) TOFILE(ROB/PFTEST2)
MBROPT(*REPLACE) FMTOPT(*NOCHK)
It won't do it. Hard stop.

Why? Because DDS check the data at read, not write. The benefit to that is
if you have an application with a column for Turkistan currency conversion
factor and you do not do business with Turkistan you can overlay that
numeric field with a data structure in RPG and store character data in it.
Thus repurposing that column for something else you want without having to
recompile all your programs. Ancient technique which used to be used.
You cannot do that with a DDL file.


Rob Berendt