Re: [HELP]: Problem using views to drive cursors

Neil Greene <neil_at_kynug.org> writes:

>Consider this a vehicle licensing application where you have motorists
>with fname, lname, birthdate, social security and a primary key generated
>by the system. Someone now submits a data file with vehicle registration
>information along with the applicant information. You want to create a
>new master record if the person is not found in your database and you want
>to add the registration information to the correct applicant in your
>system finds the applicant.
 

>I have a view which I use to drive a cursor. This view merges our
>applicants table with a submission table to see if the applicant being
>submitted is already in our database. An outer join is performed in the
>view to revel the primary key of the applicant in our database and of
>course this field is NULL if the applicant is not found in our database.
 

>PROBLEM: The problem I am having is when multiple licenses are submitted
>on the same applicant, creating duplicate master records.
 

>The first submission sees the primary key field as NULL and the applicant
>record is added and committed to our database. However, the cursor
>accessing the view does not recognize this and creates another master
>record if more then one license is submitted on the same applicant.
 

>I have tried issuing a commit right after inserting the applicant record,
>thinking my view would now see the update. But no good. Is my problem in
>the fact that the cursor has already fetched the records and began
>processing. So that although the driving tables have been updated and the
>views are actually working correctly, the cursor is not?
 

>Suggestions?
>--
>Neil Greene
>benchMark Developments, Inc. [NeXT VAR]
>2040 Regency Road, Suite C Lexington, KY 40503
>Phone: 606-231-6599 / Fax: 606-254-4864
 

What I didn't understand is how the entire or part of the primary key can be NULL?
If the PK is defined as NOT NULL, may be the problem will be solved.

Though I didn't work in Oracle, I did work in DB2. How DB2 handles cursors is like this:

If the cursor is NOT read-only (queries with no GROUP BY, DISTINCT, ORDER BY clauses), then DB2 doesn't create any temp. table to process the query/cursor. While this type of cursor is OPEN, any update ops. we do (INSERT/UPDATE/DELETE) might or might not be "visible" to the cursor i.e., it's undefined.

If the cursor is read-only, then DB2 might or might not create a temp. table. If the query has an ORDER BY clause and there exists an index with the same columns, then DB2 doesn't create a temp. table. However, there is no index on the ORDER BY clause column(s), then DB2 has to sort all the rows in the base table & stores them in a new temp. table. In any of these two cases, update ops (INS/UPD/DEL) we do on the base table while the cursor is OPEN have no effect on the cursor i.e, they are definitely "invisible" to the cursor because the cursor is reading off the temp table & the update ops act against the base table.

Hope I gave u some useful info here, instead of confusing you.

Good luck! Received on Thu Jul 21 1994 - 04:21:56 CEST