|Testing Direct Database Updates||KEVIN RESCHENBERG|
While starting to plan some direct database updates
the other day, I was reminded of some of the
difficulties they can cause and a few ways of
detecting the problems.
Whenever we directly insert data into a table,
of course we need to ensure that all of the
required data relationships are preserved.
It's easy to forget to insert
corresponding rows into the required tables,
use the wrong effective dates, fail to
default certain fields, use bad data values,
and so on. These can then lead to
various problems in the application.
Only thorough testing can ensure that all required
rows have been inserted or updated. But there are
a few clues that can tip us off to problems.
After inserting the data, bring one of the inserted
rows up on the page that maintains it. Look for
any drop-downs that show "(invalid data)" and for
any lookup fields that do not display a description.
change and attempt to save. Were there any error messages?
These must be investigated.
For another test, view a different row on the page
but don't make any changes to any of the data.
Try to navigate away from the component.
If a message informs you that you have unsaved data
on the page, this probably indicates a problem with
the inserted data. What happened is that the component
processor or PeopleCode did some defaulting or other
changing of the data, which means that the data
was incomplete or incorrect in some way.
How can this error be diagnosed? One way is to
find one of the new inserted rows and export it
into Excel using Query Analyzer, Toad, SQL*Plus (gasp!),
or some other tool. Then view the record on the
page and hit the Save
button without making any changes. Then export the row again and compare
the two versions. Any changes in the data should
highlight the fields that weren't set correctly.
If you can't see these new rows on the page at all, the
problem could be related to row-level security or to
other tables that need corresponding rows. Missing
rows could lead to broken joins that cause views
to miss the rows you want. There are many possibilities;
enjoy the challenge.