Just so there is no question , below is the output of a simple test
program ,
Note that the INSERTs fail on bad data as they should but
OPENCHECK/WRITE & ICHECK do not.
Bad data is definitely being written into the table.
OS Info :
>.L RELLEVEL
RELLEVEL
001 X
002 10.1.8
003 NEWACC
004
005 10.1.8
>SH
$ uname -rmsv
HP-UX B.11.11 U 9000/800
COMO SQL established 09:00:26 29 APR 2008
SQLTEST
0001 crt @(0):
0002 execute 'TERM 400'
0003 execute 'COMO ON SQL'
0004
0005 execute 'CT B.BP SQLTEST -NO.PAGE'
0006
0007 crt ; crt ; crt
0008
0009 note='Drop existing table'
0010 xqt= 'DROP TABLE TAB1 ;'
0011 gosub exec
0012
0013 note='Create new table'
0014 xqt ='CREATE TABLE "TAB1"'
0015 xqt:='('
0016 xqt:=' "KEY1" CHAR(4) FORMAT "4L" ,'
0017 xqt:=' "VAL1" DATE FORMAT "10R" CONV "D4-YMD" ,'
0018 xqt:=' "VAL2" TIME FORMAT "8R" CONV "MTS" ,'
0019 xqt:=' "VAL3" DECIMAL(10,2) FORMAT "10R" CONV "MD2" ,'
0020 xqt:=' "VAL4" INT FORMAT "10R" ,'
0021 xqt:=' PRIMARY KEY ( KEY1 )'
0022 xqt:=') ;'
0023 gosub exec
0024
0025 note='List table structure'
0026 xqt='LIST.SICA TAB1 NO.PAGE'
0027 gosub exec
0028
0029 crt '======='
0030 crt '======= OPENCHECK : ':
0031 opencheck 'TAB1' to fp
0032 then crt 'ok'
0033 else abortm '** else'
0034 crt '======='
0035 crt
0036
0037 crt '======='
0038 crt '======= good data : no validation errors'
0039 crt '======='
0040 id="REC1"
0041 rec =date()
0042 rec<2>=time()
0043 rec<3>=111
0044 rec<4>=222
0045 crt
0046 crt '@ID=':id
0047 crt '@REC=':rec
0048 crt
0049 x=icheck(rec,fp,id)
0050 crt "ICHECK REC1 : ":x
0051 crt "WRITE REC1 : ":
0052 write rec on fp,id
0053 on error crt '** error'
0054 then crt 'ok'
0055 else crt '**else'
0056 crt
0057
0058 crt '======='
0059 crt '======= bad data : !! NO VALIDATION ERRORS !!'
0060 crt '======='
0061 id=2
0062 rec ="F1"
0063 rec<2>="F2"
0064 rec<3>="F3"
0065 rec<4>="F4"
0066 crt
0067 crt '@ID=':id
0068 crt '@REC=':rec
0069 crt
0070 x=icheck(rec,fp,id)
0071 crt "ICHECK REC2 : ":x
0072 crt "WRITE REC2 : ":
0073 write rec on fp,id
0074 on error crt '** error'
0075 then crt 'ok'
0076 else crt '**else'
0077 crt
0078
0079 note='======= INSERT good data : no validation errors'
0080 xqt=INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS1','2008-01-01','14:22:04',1.2,2) ; ; gosub exec
0081
0082 note='======= INSERT bad data : generates expected validation
errors'
0083 xqt=INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS2','oogity','14:22:04',1.2,2) ; ; gosub exec
0084 xqt=INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS3,'2008-01-01','boogity',1.2,2) ; ; gosub exec
0085 xqt=INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS4','2008-01-01','14:22:04','ickity',2) ; ; gosub exec
0086 xqt=INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS5','2008-01-01','14:22:04',1.2,'snickety') ; ; gosub exec
0087 xqt=INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
(1.2,'2008-01-01','14:22:04',1.2,2) ; ; gosub exec
0088
0089 note='LIST table to confirm bad data written to file'
0090 xqt='SORT TAB1'
0091 gosub exec
0092
0093 note='SQL SELECT table to confirm bad data written to file'
0094 xqt='SELECT * FROM TAB1 ORDER BY KEY1;'
0095 gosub exec
0096
0097 execute 'COMO OFF'
0098 execute 'TERM 80'
0099 stop
0100
0101 exec:
0102 crt '======='
0103 crt '======= ':note
0104 crt '======='
0105 crt
0106 crt '> ':xqt
0107 crt
0108 execute xqt
0109 crt
0110 return
0111
0112 end
=======
======= Drop existing table
=======
> DROP TABLE TAB1 ;
Dropping Table TAB1
=======
======= Create new table
=======
> CREATE TABLE "TAB1"( "KEY1" CHAR(4) FORMAT "4L" , "VAL1" DATE FORMAT
"10R" CONV "D4-YMD" , "VAL2" TIME FORMAT "8R" CONV "MTS" , "VAL3"
DECIMAL(10,2) FORMAT "10R" CONV "MD2" , "VAL4" INT FORMAT "10R" ,
PRIMARY KEY ( KEY1 )) ;
Creating Table "TAB1"
Adding Column "KEY1"
Adding Column "VAL1"
Adding Column "VAL2"
Adding Column "VAL3"
Adding Column "VAL4"
=======
======= List table structure
=======
> LIST.SICA TAB1 NO.PAGE
LIST.SICA TAB1 09:00:26am 29 Apr 2008 Page 1
==========================================
Sica Region for Table "TAB1"
Schema: SCHEMA-TEST
Revision: 4
Checksum is: 4040
Should Be: 4040
Size: 428
Creator: 431
Total Col Count: 5
Key Columns: 1
Data Columns: 4
Check Count: 0
Permission Count:0
History Count: 0
Data for Column "KEY1"
Position: 0
Key Position: 1
Multivalued: No
Not Null: constraint UVCON_0 Yes
Not Empty: No
LIST.SICA TAB1 09:00:26am 29 Apr 2008 Page 2
Unique: No
Row Unique: No
Primary Key: Yes
Default Type: None
Data Type: CHARACTER
Data Length: 4
No conversion defined
Format: 4L
No Default Value Defined
No association defined
Data for Column "VAL1"
Position: 1
Key Position: 0
Multivalued: No
Not Null: No
Not Empty: No
Unique: No
Row Unique: No
Primary Key: No
Default Type: None
Data Type: DATE
LIST.SICA TAB1 09:00:26am 29 Apr 2008 Page 3
Conversion: D4-YMD
Format: 10R
No Default Value Defined
No association defined
Data for Column "VAL2"
Position: 2
Key Position: 0
Multivalued: No
Not Null: No
Not Empty: No
Unique: No
Row Unique: No
Primary Key: No
Default Type: None
Data Type: TIME
Conversion: MTS
Format: 8R
No Default Value Defined
No association defined
Data for Column "VAL3"
LIST.SICA TAB1 09:00:26am 29 Apr 2008 Page 4
Position: 3
Key Position: 0
Multivalued: No
Not Null: No
Not Empty: No
Unique: No
Row Unique: No
Primary Key: No
Default Type: None
Data Type: DECIMAL
Data Scale: 2
Conversion: MD2
Format: 10R
No Default Value Defined
No association defined
Data for Column "VAL4"
Position: 4
Key Position: 0
Multivalued: No
Not Null: No
LIST.SICA TAB1 09:00:26am 29 Apr 2008 Page 5
Not Empty: No
Unique: No
Row Unique: No
Primary Key: No
Default Type: None
Data Type: INTEGER
Conversion: MD0
Format: 10R
No Default Value Defined
No association defined
=======
======= OPENCHECK : ok
=======
=======
======= good data : no validation errors
=======
@ID=REC1
@REC=14730|32426.7057|111|222
ICHECK REC1 : 0|0
WRITE REC1 : ok
=======
======= bad data : !! NO VALIDATION ERRORS !!
=======
@ID=2
@REC=F1|F2|F3|F4
ICHECK REC2 : 0|0
WRITE REC2 : ok
=======
======= ======= INSERT good data : no validation errors
=======
> INSERT INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS1','2008-01-01','14:22:04',1.2,2) ;
UniVerse/SQL: 1 record inserted.
=======
======= ======= INSERT bad data : generates expected validation errors
=======
> INSERT INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS2','oogity','14:22:04',1.2,2) ;
UniVerse/SQL: date and character types are incompatible in this
operation
Scanned command was INSERT INTO TAB1 ( KEY1 , VAL1 , VAL2 , VAL3 , VAL4
) VALUES ( "INS2" , "oogity" ,
UniVerse/SQL: Column "VAL1" data type does not match insert value.
=======
======= ======= INSERT bad data : generates expected validation errors
=======
> INSERT INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS3,'2008-01-01','boogity',1.2,2) ;
UniVerse/SQL: syntax error. Unexpected literal string. Token was
"2008".
Scanned command was INSERT INTO TAB1 ( KEY1 , VAL1 , VAL2 , VAL3 , VAL4
) VALUES ( "INS3," 2008
=======
======= ======= INSERT bad data : generates expected validation errors
=======
> INSERT INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS4','2008-01-01','14:22:04','ickity',2) ;
UniVerse/SQL: number and character types are incompatible in this
operation
Scanned command was INSERT INTO TAB1 ( KEY1 , VAL1 , VAL2 , VAL3 , VAL4
) VALUES ( "INS4" , "2008-01-01" , "14:22:04" , "ickity" ,
UniVerse/SQL: Column "VAL3" data type does not match insert value.
=======
======= ======= INSERT bad data : generates expected validation errors
=======
> INSERT INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
('INS5','2008-01-01','14:22:04',1.2,'snickety') ;
UniVerse/SQL: number and character types are incompatible in this
operation
Scanned command was INSERT INTO TAB1 ( KEY1 , VAL1 , VAL2 , VAL3 , VAL4
) VALUES ( "INS5" , "2008-01-01" , "14:22:04" , 1.2 , "snickety" )
UniVerse/SQL: Column "VAL4" data type does not match insert value.
=======
======= ======= INSERT bad data : generates expected validation errors
=======
> INSERT INTO TAB1 (KEY1,VAL1,VAL2,VAL3,VAL4) VALUES
(1.2,'2008-01-01','14:22:04',1.2,2) ;
UniVerse/SQL: character and number types are incompatible in this
operation
Scanned command was INSERT INTO TAB1 ( KEY1 , VAL1 , VAL2 , VAL3 , VAL4
) VALUES ( 1.2 ,
UniVerse/SQL: Column "KEY1" data type does not match insert value.
=======
======= LIST table to confirm bad data written to file
=======
> SORT TAB1
SORT TAB1 09:00:26am 29 Apr 2008 PAGE 1
KEY1 VAL1...... VAL2.... VAL3...... VAL4......
2 F1 F2 F3 F4
INS1 2008-1-01 14:22:04 1.20 2
REC1 2008-4-29 09:00:26 1.11 222
3 records listed.
=======
======= SQL SELECT table to confirm bad data written to file
=======
> SELECT * FROM TAB1 ORDER BY KEY1;
KEY1 VAL1...... VAL2.... VAL3...... VAL4......
2 F1 F2 F3 F4
INS1 2008-1-01 14:22:04 1.20 2
REC1 2008-4-29 09:00:26 1.11 222
3 records listed.
COMO completed. 09:00:26 29 APR 2008
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Jordan
Sent: April 29, 2008 2:44 AM
To: [email protected]
Subject: RE: [U2] UniVerse SQL Tables
Gerry
If you define a column as integer and that attribute is not an integer,
then
the write will lead to a fatal error if you do not have opencheck or on
error options built into your code. If you do not define a column as
multivalue, then it will have a problem with multivalues.
If you do not define the column, then there is no type checking.
The big issue to consider with SQL Tables is moving accounts around.
With
the SICA attached to the tables, it is not as easy to copy an account to
a
different area or to duplicate account for testing as you would do with
standard files. You cannot change the schema name, so when you copy
the
account you still have the same schema name in the duplicated account.
Regards
David Jordan
Managing Consultant
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
