RE: Table Definitions DDS or SQL?

Rob,

I obtain the error message 'Statement contains wrong number of values.' within 
SQL when performing INSERT INTO ROB/JOEP2 VALUES('NOWISTHETI') ?

How did you manage to use SQL to insert invalid data ?

Thanks,
Sean

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of rob@xxxxxxxxx
Sent: 05 October 2006 21:43
To: RPG programming on the AS400 / iSeries
Subject: Re: Table Definitions DDS or SQL?

Like Denis, most of our files are defined using DDS.  But that's packaged 
software for you.
I define my new files using SQL.  Then again, I am probably the only one.

Here's something to chew over:

Problem with DDS created files.  DDS created files are built to allow crap 

in most fields.  Some legacy thing when people got off grunting, thumping their 
chest and writing batch programs that checked fields down to the byte level for 
data that is right.  Like the first COBOL program I wrote in a class - make 
sure a field that is supposed to be numeric, is.  DDS files are fast to write 
to, but slow to read from because they don't care what kind of data you write 
to them.  But they'll check the data quality on a read.  When you think about 
it, most of your data access is reading, right?
SQL created files check data on a write.  Not on a read.
Recently saw a classic example of this.
Compile this:
                R JOEPR
                  MYCHAR         5A
                  MYPACK         9P 2 
with CRTPF FILE(ROB/JOEP) SRCFILE(ROB/QDDSSRC) Run this command:  CRTPF 
FILE(ROB/JOEP2) RCDLEN(10) STRSQL INSERT INTO ROB/JOEP2 VALUES('NOWISTHETI')

CREATE TABLE ROB/JOEP3
(MYCHAR CHAR (5 ) NOT NULL WITH DEFAULT,  MYPACK DEC (9 , 2) NOT NULL WITH 
DEFAULT) RCDFMT JOEP3R (exit sql)

CPYF FROMFILE(JOEP2) TOFILE(JOEP) MBROPT(*ADD) FMTOPT(*NOCHK)
Notice:  Allowed crap into JOEP.  You have a packed decimal field with alpha 
characters in it.

But CPYF FROMFILE(JOEP2) TOFILE(JOEP3) MBROPT(*ADD) FMTOPT(*NOCHK) Tells me to 
bugger off with CPF5035-Data mapping error on member JOEP3.

Now if I attempt to read the corrupted DDS created file with a simple RPGLE 
cycle program
     FJOEP      IP   E             DISK
     FQSYSPRT   O    F   80        PRINTER
     OQSYSPRT   D
     O                       MYCHAR
     O                                              ' '
     O                       MYPACK 
I will get RNQ0907-Decimal-data error occurred.

Verifying that DDS files check data integrity on read.  SQL created files check 
data integrity on write.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Denis Robitaille" <denis_robitaille@xxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/05/2006 04:08 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Rép. : Table Definitions DDS or SQL?






Most of our files are defined using DDS. But all new files are defined
as SQL tables.

I have grey hair so I like DDS. But some kind of fields can not be
defined in DDS (like blob I think). If only for that, I agree with our
choice of SQL.

Mike <koldark@xxxxxxxxx> 2006-10-05 15:38 >>>

/me opens can of worms

Hey, I just thought of something. Do most people still use DDS for
file
descriptions or have you moved to SQL tables? I can see pros and cons
for
both. Thoughts?