Our software does a bit of on-the-fly script building to do bulk updates, inserts and merges. What I would like to do is be able to build something that in effect ignores constraint errors and proceeds to the end. Suggestions?

Peter


Check out EXCEPTION in the OLH. Does pretty much what the Delphi try..except block does.

Roy Lambert [Team Elevate]

Peter,

<< Our software does a bit of on-the-fly script building to do bulk updates,
inserts and merges. What I would like to do is be able to build something
that in effect ignores constraint errors and proceeds to the end.
Suggestions? >>

As Roy indicated, the only way to do so right now is to wrap each execution
that you want to trap with a BEGIN..EXCEPTION block:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=EXCEPTION

and just don't do anything in the EXCEPTION portion (you could log the
message using SET LOG MESSAGE TO, if you wanted).

--
Tim Young
Elevate Software
www.elevatesoft.com

Peter,

As an alternative, you could also use "select * from information.constraints" to obtain information about the constraints. Drop them, do your import, and recreate them afterwards.

Kick

<<As an alternative, you could also use "select * from information.constraints" to obtain information about the constraints. Drop them, do your import, and recreate them afterwards.>>

A quick experiment shows this would be a bad idea.

ALTER TABLE "UserStats"
ADD CONSTRAINT "Rubbish" CHECK (_fkStaff <> 'XX')

Won't add the constraint because its violated. So if Peter removes the constraints, process his data adding information that violates the constraints he has a problem. Much better to sort it out as you go.

Roy Lambert [Team Elevate]

<<Won't add the constraint because its violated. So if Peter removes the constraints, process his data adding information that violates the constraints he has a problem. Much better to sort it out as you go.>>

True! But in some scenarios, you know the data you import IS valid, but multiple tables have references to each other. This makes it impossible to import the whole tables at once, and you would have to import record by record respecting the referential integrity. I have such a project where the data is imported from textfiles exported out of another DB that has referential integrity (so I know the data I am importing (in the the end) will be valid).

situation:
Table1 has references to table2 and table2 has references to table1. You would not be able to import Table1 until table2 is imported, but you cannot import table2 until table1 is imported.
You would have to import record by record and sort out the order in which to import.

In these situations it's much easier to drop the constraints, import table1 and table2 then recreate the constraints. But of course thats only if you know the data you import IS valid. A somewhat saver approuch is to make (empty) copies of the destination tables, import the data into these tables, create the constraints on these tables to see if the imported data is valid, and then copy these new tables in the destination ones.

Kick

>Table1 has references to table2 and table2 has references to table1. You would not be able to import Table1 until table2 is imported, but you cannot import table2 until table1 is imported.

In that circumstance your approach of dropping the constraint isn't only the best way its almost mandatory.

Out of interest what is the data involved? I'm having difficulty envisaging what data would be involved in such a scenario.

Roy Lambert [Team Elevate]

<<Out of interest what is the data involved? I'm having difficulty envisaging what data would be involved in such a scenario.>>

We import productinformation in our software. It's pretty static data that's imported in a batch every week. It's supplied by a central organisation that gathers this info from different suppliers and producers. Among others this data contains 2 major tables. The products table (that holds info from the producer about the product) and the articles table (that holds info from the suppliers about the price and shipping). The articles table references the products table. But producers can sell the product themselfs as well therefore the products table also has a reference to the articles table.

I's probably not the right way to model your data. You could have had a field in the articles table that specifies that it is a producersarticle or something.

Kick

>We import productinformation in our software. It's pretty static data that's imported in a batch every week. It's supplied by a central organisation that gathers this info from different suppliers and producers. Among others this data contains 2 major tables. The products table (that holds info from the producer about the product) and the articles table (that holds info from the suppliers about the price and shipping). The articles table references the products table. But producers can sell the product themselfs as well therefore the products table also has a reference to the articles table.
>
>I's probably not the right way to model your data. You could have had a field in the articles table that specifies that it is a producersarticle or something.

Probably not

Roy Lambert