Oninit cdi / Archive
Talk to an expert →

Help! FILTERING WITH ERROR and START VIOLATIONS aren't working

Posted in 2000

Topics: Stored Procedures & SPL, Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration, Security, Permissions & Auditing, Data Types & Schema Design, Transactions, Locking & Isolation, Clustering, Grid & MACH11, Third-Party Tools & Monitoring

JD — — source: Informix-list mailing list archive (1991-1998)

Hi! I've been working on this for a very long time and need your help. Thank you in advance for ANY thoughts. According to Version 7.2 SQL Syntax Vol 2, the FILTERING WITH ERROR and START VIOLATIONS are supposed to make it so an update, delete or insert which violates a unique key (in this case zip_code and city_name) is logged to the _vio violations table and diagnostics (_dia) table and DO NOT halt loading data. I have followed all the directions and when I try to load data (through a 4gl application) it fails (it errors out and halts loading data - error 971 - Integrity violations detected) on the first record which violates the unique zip_code and city_name index. I've tried changing the city_name to varchar, rebuilding the table, running the debugger etc. create table "sysdba".zip_info_new ( zi_zip integer, zi_zip_code char(6) not null filtering with error , zi_classification char(1), zi_city_name char(28) not null filtering with error , zi_last_city varchar(28,1), zi_state char(2) not null filtering with error , zi_county_num smallint, zi_latitude smallfloat, zi_longitude smallfloat, zi_area_code varchar(15,3), zi_time_zone char(2) ); revoke all on "sysdba".zip_info_new from "public";create unique index "sysdba".ix124_2 on "sysdba".zip_info_new (zi_zip_code,zi_city_name) filtering with error ; create index "sysdba".ix124_1 on "sysdba".zip_info_new (zi_zip); create index "sysdba".zi_lat_idx on "sysdba".zip_info_new (zi_state, zi_latitude,zi_longitude); create index "sysdba".zi_long_idx on "sysdba".zip_info_new (zi_longitude, zi_latitude); start violations table for "sysdba".zip_info_new using zip_info_new_vio, zip_info_new_dia; revoke all on "sysdba".zip_info_new_vio from "public"; revoke all on "sysdba".zip_info_new_dia from "public"; grant all on "sysdba".zip_info_new from "public"; grant all on "sysdba".zip_info_new_vio to "public"; grant all on "sysdba".zip_info_new_dia to "public"; Index name Owner Type Cluster Columns ix124_2 sysdba unique No zi_zip_code zi_city_name ix124_1 sysdba dupls No zi_zip zi_lat_idx sysdba dupls No zi_state zi_latitude zi_longitude zi_long_idx sysdba dupls No zi_longitude zi_latitude Column name Type Nulls zi_zip integer yes zi_zip_code char(6) no zi_classification char(1) yes zi_city_name char(28) yes zi_last_city varchar(28,1) yes zi_state char(2) no zi_county_num smallint yes zi_latitude smallfloat yes zi_longitude smallfloat yes zi_area_code varchar(15,3) yes zi_time_zone char(2) yes Here is the essential 4gl app code. It fails at line 60 of the data file D00603V19710 RAMEY UNV17137AGUADILLA YY 420360PR005AGUADILLA 18.4408 67.1507787 right at the insert statement. The reason is the city_name and zip_code are both the same as a previous record: CREATE TEMP TABLE zip_from ( zip_record CHAR(232) ) WITH NO LOGPROMPT "Enter the full filename (zipusa.Sdf) " FOR file_name LET fileNpath = "/tapes/general/images/",file_name CLIPPED DISPLAY fileNpath LOAD FROM fileNpath INSERT INTO zip_from DECLARE zip_curs CURSOR WITH HOLD FOR SELECT zip_record INTO zip_rec FROM zip_from DISPLAY "FORMATTING" BEGIN WORK LOCK TABLE zip_info_new IN EXCLUSIVE MODE FOREACH zip_curs LET zip = zip_rec[2,6] LET zip_code = zip_rec[2,6] LET classification = zip_rec[13,13] LET city_name = zip_rec[14,41] LET last_city = zip_rec[63,90] LET state = zip_rec[100,101] LET county_num = zip_rec[102,104] LET latitude = zip_rec[130,136] LET longitude = zip_rec[137,144] LET area_code = zip_rec[145,159] LET time_zone = zip_rec[160,161] LET counter = counter + 1 DISPLAY counter INSERT INTO zip_info_new VALUES (zip,zip_code, classification, city_name, last_city,sta e, county_num, latitude, longitude, area_code, time_zone) END FOREACH COMMIT WORK END MAIN

↪ replying to JD
Rudy Fernandes — — source: Informix-list mailing list archive (1991-1998)

Your problem is related to "FILTERING WITH ERROR". This object mode will cause the offending row to be logged in the violation & diagnostic table AND return an error. Your 4GL does not seem to be handling this error. One simple way to resolve your problem would be to change the mode of your unique index (and, possibly, your not-null constraints) to "FILTERING WITHOUT ERROR" (the default). Alternatively, trap the 971 error in your 4gl code. Rudy JD wrote: > Hi! > > I've been working on this for a very long time and need your help. Thank you > in advance for ANY thoughts. > > ... > create unique index "sysdba".ix124_2 on "sysdba".zip_info_new > (zi_zip_code,zi_city_name) filtering with error ; > ...

↪ replying to Rudy Fernandes
Jamie Delton — — source: Informix-list mailing list archive (1991-1998)

Rudy Fernandes, That worked! Thanks a million! Jamie jdelton@iqmktg.com

↪ replying to Rudy Fernandes
Joben — — source: Informix-list mailing list archive (1991-1998)

Reading through your 4GL, you could try a WHENEVER ERROR CONTINUE|CALL error_handler 4GL maybe Informix but it doesn't mean what's true for the database (IDS) is true for the tool. =) Just my experience (or lack of it too.) Joben In article <39207939.9E58B4A0@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: >Your problem is related to "FILTERING WITH ERROR". This object mode will cause >JD wrote: > >> Hi! >> >> I've been working on this for a very long time and need your help. Thank you * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

↪ replying to JD
Steve Pearson — — source: Informix-list mailing list archive (1991-1998)

> According to Version 7.2 SQL Syntax Vol 2, the > FILTERING WITH ERROR and START VIOLATIONS are supposed to make it so an > update, delete or insert which violates a unique key (in this case zip_code > and city_name) is logged to the _vio violations table and diagnostics (_dia) > table and DO NOT halt loading data. > I have followed all the directions and when I try to load data (through a > 4gl application) it fails (it errors out and halts loading data - error > 971 - Integrity violations detected) on the first record which violates the > [...] According to my reading, WITH ERROR means an error should be returned to the user. Perhaps you would do better using "WITHOUT ERROR" (the default) which says no error is returned to the user. -steve p.

Glossary