<table>mark, fetch, change confusion

[Don Lipke]

Is there something simple that I don't understand about fetching, marking, or changinging redords? The following code gives predictable traced record number order for a small sample of six records when the asterisked ('****) line is commented out.
When the ('****) line is enabled, the trace order is tangled...more than ten passes through the WHILE loop with erratic record number order. Several times there is no apparent record pointer move for the AdTable.fetch_prev() and the AdTable.fetch_next() statements in the second half of the IF...END IF construct at the bottom of the WHILE loop.

CODE:
' This code eliminates duplicate Adinfo but updates old phone, Status
' and firstDate info from the original (oldest) records before deleting.

tblchoice = ui_get_radio("Select Table to Index in Ad order",1,"chrontemp","chronads","chrontest")
IF tblchoice = "" 'If user presses Cancel...
END ' end the script.
END IF
AdTable = table.open(tblchoice)
Table.index_create_begin(tblchoice,"Substr(AdInfo,1,100)+AdDate")
Adx = AdTable.index_create_end()
AdTable.Fetch_First()
trace.write(" First "+str(AdTable.recno()))
while .not. AdTable.fetch_eof()
vAdInfo = Substr(AdTable.AdInfo,1,100)
' vSource = AdTable.Source 'older Source not wanted
vAdDate = AdTable.AdDate
vFirstDate = AdTable.FirstDate
vStatus = AdTable.Status
vPhone1 = AdTable.Phone1
vPhone2 = AdTable.Phone2
AdTable.fetch_next()
trace.writeln(" Compare To "+str(AdTable.recno()))
if AdTable.fetch_eof()
exit while
end if
if Substr(AdTable.AdInfo,1,100) == vAdInfo 'duplicate Ad
trace.write(" Dupe")
AdTable.change_begin(.t.) 'save desired older field data
' AdTable.Source = vSource 'keep newer Source as is
' AdTable.Phone1 = vPhone1 'keep older phones in case they were...
' AdTable.Phone2 = vPhone2 ' changed from AdInfo in older data
' AdTable.FirstDate = vFirstDate
' if AdTable.FirstDate

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Don,

Since the table is being stepped through in a specific index sequence (not in record number order) it's unlikely the previous and next "record numbers" would be consecutive, right? when you create an index the table records are not physically re-arranged in the table. The index list is simply created.

Still, it's possible the change isn't being finished by the time the next record is fetched. I'd change

AdTable.change_end()
AdTable.fetch_next()

to

AdTable.change_end()
Xbasic_wait_for_idle()
AdTable.fetch_next()

-- tom

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Don,

This may be obvious, but following up on my previous, a better way to trace the accuracy of your script would be to write the index keys to the trace window instead of the record numbers.

-- tom

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

Thanks for reply Tom,

I ran tests with Xbasic_wait_for_idle() statements after each AdTable.change_... and AdTable_fetch... and still get the erratic result.
I understand that the indexed table record number order isn't monotonic. And I'm intuitively suspicious of the index pointer causing fits. I'm considering a work-around by copying records in index order to a new table for processing and then running the script.
What concerns me is that there is an Alpha bug or there is a problem with my installation of Alpha5V5.

Don

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

Thanks for that tip...index key tracing. I'll use that in future cause I had to manually predict the index order from my sample data.

The problem still is that it runs entirely different order and nearly twice as many passes through the main while loop when marking records as when not marking.

Don

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

I'll be glad to test it against my installation here if you wish. Post a working model of the database here as an attachment to a reply message. If privacy is an issue email it to me. Please point us to the right form, button, or script, too, ok?

-- tom

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Don,

Try building the desired index first, then running the script without the lines which build (rebuild) the index.

-- tom

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

TRACE DATA FOR SAME AdTable DATA:

Below are traces for a "GOOD" run order without marking and then for a "BAD" order with marking statement enabled:

The indexed record number order is 1,6,3,5,2,4 with 1,6,3 and 2,4 groups having duplicate AdData.

***GOOD
First 1 Compare To 6
Dupe Prev 1 Mark 1 Next 6 Compare To 3
Dupe Prev 6 Mark 6 Next 3 Compare To 5
Compare To 2
Compare To 4
Dupe Prev 2 Mark 2 Next 4 Compare To 4
EOF - First While Loop
____________
***BAD
First 1 Compare To 6
Dupe Prev 1 Mark 1 Next 6 Compare To 3
Dupe Prev 6 Mark 6 Next 6 Compare To 3
Dupe Prev 6 Mark 6 Next 3 Compare To 5
Compare To 2
Compare To 4
Dupe Prev 2 Mark 2 Next 6 Compare To 3
Dupe Prev 6 Mark 6 Next 3 Compare To 5
Compare To 2
Compare To 4
Dupe Prev 2 Mark 2 Next 4 Compare To 4
EOF - First While Loop
__________

[JohnZaleski]

RE: &lt;table&gt;mark, fetch, change confusion

What is contained in the field AdInfo - especially the first 100 bytes of the field,what is the expression.

I have a feeling there are much better ways to accomplish what you are attempting - if you gave a little explanation of what you are trying to accomplish it might make it easier to assist you.

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

Attached zip file is the single table, single script database which exhibits the problem. When running the script, be sure to select the third radio button table name as that is all that I've included.
After a run with AdTable.mark() enabled, I just open default browse and unmark the marked records.
For this simple table the correct records are marked even though the trace produced indicates "funny" data. For a larger table the results don't make any sense.

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Don,

Check the indextag expression. Is AdDate a date type field? If so, you cannot concatenate it with a string without converting it first.

Is AdInfo a memo field?

We're throwing darts with our blindfolds on because you haven't posted a working model for us to work with... hint, hint.

-- tom

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

I'm keeping newspaper ads (that I download from the newspaper website as text files) in a table. I want to eliminate duplicate ads and just keep the earliest and latest dates that they were published.

Except for a little field manipulation I could just eliminate duplicates with a single command...but I want the dates the ad ran and I might manually add a second phone that wasn't in the ad.

My main problem is the apparent inconsistency of the fetch-prev and fetch-next lines depending upon whether the record was modified.

Thanks for your interest.

[Peter.Wayne]

RE: &lt;table&gt;mark, fetch, change confusion

Among other things, i think your syntax for building an index is all wrong. unfortunately there are a lot of mistakes in the documentation, inserting "table" instead of .

instead of
AdTable = table.open(tblchoice)
Table.index_create_begin(tblchoice,"Substr(AdInfo,1,100)+AdDate")
Adx = AdTable.index_create_end()

AdTable= table.open(tblchoice)
AdTable.index_create_begin("AdInfoIndex","substr(AdInfo,1,100)+cdate(AdDate))"
' note I assume that AdDate is a date field

then you have to set the new index to the primary index:
ix=AdTable.index_create_end()
Adtable.index_primary_put(ix)

then you can walk through your while..end while loop.
then as for the logic of your script, it isn't clear to me what you hope to accomplish. i don't think your script can find duplicates, if that's what you want it to do.

incidentally, if you only need a one-time index, you're better off using a query:
AdTable= table.open(tblchoice)
query.filter=""
query.order="substr(AdInfo,1,100)+cdate(AdDate))"
query.options=""
qry=AdTable.query_create()
AdTable.fetch_first()
while .not. AdTable.fetch_eof()
' do something
AdTable.fetch_next()
end while

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

AdDate is a date field...I think I read in the Xbasic Reference that it would handle that?? I'll try converting with CTOD.

I finally got the test database posted a few minutes ago...

Thanks for the hints...

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

Peter,
I know I'm talking to the "guru" who's approaching a googal hours with this stuff...(I am approaching 100.)...

...but I thought that when an index is named the same as the table it would be the production index and by default be the primary?

I understand the potential problem without the date converted to character format and will change that.

Why can't the script find duplicates in the memo field? I started with AdTable.delete() in place of AdTable.mark() but then wondered how the index would keep up with deleted records so made a second pass loop to delete marked records after closing the index.


I have posted a single table, single script, database to this site.

I really appreciate your time and help.

Don