RE: [U2] UniVerse SQL Tables
My test program uses OPENCHECK so the setting if this parameter should not be having any effect. However, just to stoke the fire, I have verified via uvconfig & >CONFIG DATA that we do have OPENCHK set to '1' !! So we should be seeing data constraint errors being reported by OPEN/WRITE , OPENCHECK/WRITE & ICHECK !! I have written up a summary and sent it off to the client's sys admin to pass on to their universe support. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Clifton Oliver Sent: April 29, 2008 5:56 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] UniVerse SQL Tables The parameter is OPENCHK. "OPENCHK - the modifies the behavior of operations upon files open via the BASIC OPEN statement. When set to zero, I/O to files open with the BASIC OPEN statement ignore integrity constraints. If non- zero those files will observe the constraints. This tunable does not affect files opened via the OPENCHECK statement." Regards, Clif -- W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com On Apr 29, 2008, at 1:56 PM, David Jordan wrote: > Hi Gerry > > I think there is an option in UvConfig that allows bad data to be > written to > a table despite constraints. I have not been able to write bad > data to an > SQL Table as you have done in the test program. > > Regards > > David Jordan > > Managing Consultant > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] UniVerse SQL Tables
The parameter is OPENCHK. "OPENCHK - the modifies the behavior of operations upon files open via the BASIC OPEN statement. When set to zero, I/O to files open with the BASIC OPEN statement ignore integrity constraints. If non- zero those files will observe the constraints. This tunable does not affect files opened via the OPENCHECK statement." Regards, Clif -- W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com On Apr 29, 2008, at 1:56 PM, David Jordan wrote: Hi Gerry I think there is an option in UvConfig that allows bad data to be written to a table despite constraints. I have not been able to write bad data to an SQL Table as you have done in the test program. Regards David Jordan Managing Consultant --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] UniVerse SQL Tables
Hi Gerry I think there is an option in UvConfig that allows bad data to be written to a table despite constraints. I have not been able to write bad data to an SQL Table as you have done in the test program. Regards David Jordan Managing Consultant --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] UniVerse SQL Tables
rrors
===
> 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 PAGE1
KEY1VAL1..VAL2VAL3..VAL4..
2 F1 F2F3F4
INS1 2008-1-0114:22:04 1.20 2
REC1 2008-4-2909:00:26 1.11 222
3 records listed.
===
=== SQL SELECT table to confirm bad data written to file
===
> SELECT * FROM TAB1 ORDER BY KEY1;
KEY1VAL1..VAL2VAL3..VAL4..
2 F1 F2F3F4
INS1 2008-1-0114:22:04 1.20 2
REC1 2008-4-2909: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: u2-users@listserver.u2ug.org
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
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] UniVerse SQL Tables
All I can offer is that it used to work in version 9.6. I haven't touched it since. Sounds like something to take up with IBM through your support provider. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] UniVerse SQL Tables
I modified my test to also include ICHECK - curiously enough, this does not report any errors either. It is starting to look like OPENCHECK/WRITE and ICHECK do not consider column data type as a CONSTRAINT - sort of defeats one of the main benfits of SQL tables doesn't it ? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of u2ug Sent: April 28, 2008 8:54 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] UniVerse SQL Tables Yes I know about the OPENCHECK config setting which was why I was intentionally using the OPENCHECK statement. What you are saying seems to agree with how I thought things should work, however, even when using OPENCHECK, I am able to write complete garbage to an SQL table column defined as a DATE type. The hope is that we can SQL'ize tables to allow us to setup SQL security constraints for odbc access without affecting any existing data or code. Bad data we can get around by configuring the dictionaries accordingly but having existing code bomb due to bad data is not an option. I tested using OPENCHECK because a normal write didn't cause an error but OPENCHECK should have, just to ensure that the system was working as it should - seems it isn't and that is a concern. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: April 28, 2008 3:07 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] UniVerse SQL Tables OPENCHK is also a parameter in uvconfig. It allows you to configure the system to bypass integrity checks when the file is opened with OPEN - but not when the file is opened with OPENCHK. Thus your understanding is conditionally correct, depending on the setting of OPENCHK in uvconfig. This is not new, it has been thus since SQL was added to UniVerse BASIC. This "feature" is intended for the transition period, until you have cleaned up the legacy data (!). Once you have done that, you can "switch off" the loophole via the OPENCHK configuration parameter and you will never again have data written into your table that fails any of your security or integrity checks, including data type. There is also a function called ICHECK() that allows you to perform a "trial write". It reports whether the write will or will not violate a constraint, and returns information about which one would be violated. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] UniVerse SQL Tables
Well that it the point - as I stated in my original post, I have written a test program that demonstrates that data type constraint checks are not being performed even though the OPENCHECK statement is being used. Hence the concern. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Clifton Oliver Sent: April 28, 2008 2:59 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] UniVerse SQL Tables Not unless you've written a ten line test program to prove that. On Apr 27, 2008, at 10:50 PM, u2ug wrote: > Can we safely assume that any file writes on sql tables are not > subject > to data type checks or will this pop up to bite us at some point ? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] UniVerse SQL Tables
Yes I know about the OPENCHECK config setting which was why I was intentionally using the OPENCHECK statement. What you are saying seems to agree with how I thought things should work, however, even when using OPENCHECK, I am able to write complete garbage to an SQL table column defined as a DATE type. The hope is that we can SQL'ize tables to allow us to setup SQL security constraints for odbc access without affecting any existing data or code. Bad data we can get around by configuring the dictionaries accordingly but having existing code bomb due to bad data is not an option. I tested using OPENCHECK because a normal write didn't cause an error but OPENCHECK should have, just to ensure that the system was working as it should - seems it isn't and that is a concern. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: April 28, 2008 3:07 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] UniVerse SQL Tables OPENCHK is also a parameter in uvconfig. It allows you to configure the system to bypass integrity checks when the file is opened with OPEN - but not when the file is opened with OPENCHK. Thus your understanding is conditionally correct, depending on the setting of OPENCHK in uvconfig. This is not new, it has been thus since SQL was added to UniVerse BASIC. This "feature" is intended for the transition period, until you have cleaned up the legacy data (!). Once you have done that, you can "switch off" the loophole via the OPENCHK configuration parameter and you will never again have data written into your table that fails any of your security or integrity checks, including data type. There is also a function called ICHECK() that allows you to perform a "trial write". It reports whether the write will or will not violate a constraint, and returns information about which one would be violated. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] UniVerse SQL Tables
Not unless you've written a ten line test program to prove that. On Apr 27, 2008, at 10:50 PM, u2ug wrote: Can we safely assume that any file writes on sql tables are not subject to data type checks or will this pop up to bite us at some point ? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] UniVerse SQL Tables
OPENCHK is also a parameter in uvconfig. It allows you to configure the system to bypass integrity checks when the file is opened with OPEN - but not when the file is opened with OPENCHK. Thus your understanding is conditionally correct, depending on the setting of OPENCHK in uvconfig. This is not new, it has been thus since SQL was added to UniVerse BASIC. This "feature" is intended for the transition period, until you have cleaned up the legacy data (!). Once you have done that, you can "switch off" the loophole via the OPENCHK configuration parameter and you will never again have data written into your table that fails any of your security or integrity checks, including data type. There is also a function called ICHECK() that allows you to perform a "trial write". It reports whether the write will or will not violate a constraint, and returns information about which one would be violated. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] UniVerse SQL Tables
Not unless you've written a ten line test program to prove that. On Apr 27, 2008, at 10:50 PM, u2ug wrote: Can we safely assume that any file writes on sql tables are not subject to data type checks or will this pop up to bite us at some point ? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] UniVerse SQL Tables
I am revisiting some tests we did with sql'izing universe tables. When I 1st looked at this, as I remember, it was a problem because once a file became a table, all 'file' updates ( write ) were subject to data type verification - a problem because on this legacy system data consistency was not a design concern. >From the docs it appears that since that time, the default behaviour is to not apply data type checking on file writes to a table unless the OPENCHECK statement is used instead of the OPEN statement. However, on a test table that contains a single date column I can write any garbage I want and no errors are ever generated even though I am using the OPENCHECK statement. An sql INSERT or UPDATE with bad data will generate the expected error but a WRITE won't. Can we safely assume that any file writes on sql tables are not subject to data type checks or will this pop up to bite us at some point ? Gerry --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
