Query limit at 255?

[eribla]

Hi

I am trying to weed out duplicate customers in a table with thousands of records.
So I read the first phone number and query the table for duplicates,
then the next record, query again...

Strangely enough, regardless at what record I start, after 255 queries the program throws an exception.

Below is the dumbed down code, where I query the same phone number over and over. It will give 3 duplicates found 254 times and then Boom! Where can I see what the exception is? ( it only say "an error occurred" )


FOR i = 1 TO 1000 STEP 1

vPhone="2175325555"
query.filter="Phone= " + s_quote(vphone)
query.options = ""
query.order = "recno()"

qry = tbl.query_create()
nrecs = qry.records_get()

qry.drop()
nrecs =0
NEXT

[Al Buchholz]

Re: Query limit at 255?

Eriks

2 ideas to work with.
Backup backup backup

1. Run a summary operation, group on telephone number and use the count for each telephone number.

2. Use the delete duplicate utility option. Control panel - right the table - utilities - delete duplicates - there is a subsystem to help you. Works great.

[Stan Mathews]

Re: Query limit at 255?

You could also try

FOR i = 1 TO 1000 STEP 1

vPhone="2175325555"
query.filter="Phone= " + s_quote(vphone) 
query.options = ""
query.order = "recno()"

qry = tbl.query_create()
nrecs = qry.records_get()
tbl.query_detach()
[COLOR="Blue"]'or
'tbl.QUERY_DETACH_ALL()[/COLOR]
nrecs =0 
NEXT

[glenschild]

Re: Query limit at 255?

Stan's suggestion worked for me recently, had a complex nested loop that needed to conditionally update about 30k records and

tbl.QUERY_DETACH_ALL()

worked a treat. Also a great cure for insomnia! Just opened up windows explorer to the data folder and watched the .mpx files open and close... open and close ... open and close! :-)

[eribla]

Re: Query limit at 255?

Thanks guys, BUT

unfortunately it will not work (yet) for me:

The tbl.query_detach() did not do anything and the tbl.query_detach_All() throws a different error:

The precess cannot access the file because it's used by another process

c:\..\local settings\temp\$$091818.mpx

I steps through the program, and both variations actually perform the command, the "All" fails at the first try.


( Al, I can't use your suggestions - I need to merge the records, not just delete them).

[Tom Cone Jr]

Re: Query limit at 255?

In similar instances I've found it much faster to step through a sorted table. Here if the table were sorted by phone number all the numbers that would be returned by a query are already grouped together, right? Do your processing in tight loops stepping through all the records that have the same phone number. When the next fetch returns a different phone number, start over. Not one query is needed.

[Stan Mathews]

Re: Query limit at 255?

The precess cannot access the file because it's used by another process
c:\..\local settings\temp\$$091818.mpx

This error will be thrown if the tbl.query_detach_all() is encountered before the intervening code has finished executing. Can happen if you have some sort of operation converted to xbasic which hasn't completed. for example.

What steps are you taking to "Merge" the duplicates inside your loop.

[eribla]

Re: Query limit at 255?

Tom and Stan,

Tom, thanks for the tip, I will try this - especially if you think it's faster.
Stan, I am not even at the point to merge anything, since the loop finding it stalls.
Also I can put a debug(1) in front of the detach_all and wait 20 sec - and it will still fail.

[Al Buchholz]

Re: Query limit at 255?

Eriks

Have you tried option 2 of post 2?

I think you really need to consider it.