Candidate key problem

[MarkButler]

I have a problem where I insert a number of records into a table using an updateable view. Once I issue the tableupdate(.t.) I get an error stating that I have a non-unique candidate key. Following is the basic code.

---------------------------
<code>SELECT 0
USE parameterizedview NODATA
CURSORSETPROP('Buffering',5)
*
SELECT 0
CREATE CURSOR textrecs (primarykey C(10), candidatekey C(10))
APPEND FROM textfile.txt TYPE DELIMITED
SCAN
SCATTER MEMVAR
SELECT parameterizedview
m.primarykey=textrecs.primarykey && parameter for view
=REQUERY()
IF RECCOUNT()=0
INSERT INTO parameterizedview FROM MEMVAR
ELSE
GATHER MEMVAR
ENDIF
SELECT textrecs
ENDSCAN
*
SELECT parameterizedview
=TABLEUPDATE(.T.) <b>&& this is where I get the error</b>
*
CLOSE ALL
RETURN</code>
--------------------------

I look at the input and there is no violation, actually I remove the candidate key, run the code and then make the key a candidate and all is well. In reality the parameterized view and underlying DBF are empty, so in essence this is a load of the data.

Anyone got any ideas? TIA

Mark

 

[craigsboyd]

I'm at a bit of a disadvantage here since I can't see the contents of the textrecs cursor after you append to it from that text file nor can I see the parameterized view's code/structure...but I am wondering what is with the line?

m.primarykey=textrecs.primarykey && parameter for view

I don't see how this is necessary given that you have Scatter Memvar two lines above it. I would think that m.primarykey would already have the value you are explicitly assigning it here.

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[MarkButler]

Thanx for the response Craig. You are indeed correct. In the real parameterized view the name is different. As you pointed out, the scatter would have populated the memory variable with the same thing as my statement.

The input cursor has 200k records in it. After it is built I have issued the following select to insure that the candidate key is unique.

----------
select candidatekey, count(*) as cnt ;
from textrecs group by 1 having cnt > 1
----------

The above returns no records. I have done a number of things with bufferring and issuing intermediate tableupdates without luck. It actually bombs around 25 records if I issue "llOK=TABLEUPDATE(.T.)" every 100 records or so.

 

[craigsboyd]

Can you isolate what the value is being inserted at the time it bombs on you? Populate an array with the values or something and look at the value where it bombs and at all the previous values and tell me what you see.

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[MarkButler]

Found the problem to be something wrong with the view I was using. Did nothing but delete the view and rebuild it and the problem disappeared. I had looked at the view previously in the view editor and it looked OK. Having just a hunch and running out of hair to pull I just deleted and rebuilt the view.

Thanx

 

[jimoo]

Mark,

What may have happen was the data in the original view might have contained a blank key value, or one that was deleted.

The view would work okay at this point.

Let us assume the view has a blank key, and then you realize that and deleted the data in the source table. The blank key would still exist in the view. If you try to add to the view, via append blank, it would violate the candidate key. A requery() may have resolved that - if the blank record was no longer in the source table. Rebuilding the view probably did an inherent Requery().

Anyway, as Craig stated - it is tough to tell without looking at it, but I suspect it is something silly like this.


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com