Problem with SEEK() 3

[mmerlinn]

I have a table (SCF2St) which contains two columns, one of which contains only Zip Codes (Pscf) in string format and is exactly 5 characters wide.

'zcurrval' contains an ALLTRIMmed 5-digit zip code in string format.

Why won't the following code work?

[tt]
= TORDER('SCF2St', 'Pscf') && SELECTs & ORDERs table
znear = SET('NEAR')
zexact = SET('EXACT')
SET EXACT OFF
SET NEAR ON
&& Table is correctly SELECTed here
&& Table is correctly ORDERed here
SEEK zcurrval
&& ALWAYS sets EOF() to .T.
&& and ALWAYS moves record poiner to EOF
SET NEAR &znear
SET EXACT &zexact[/tt]


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[TamarGranor]

Show us some actual data.

Tamar

 

[mmerlinn]

zcurrval ='95695'

Pscf values include '00000', '11296', '90000', '95800', '95814', '95900', '96001', & '99999'.

zcurrval can be ANY zip code, but the result is ALWAYS the same, EOF.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[severach]

It's working here.

clos data
erase ("test.dbf")
create table test (zip c(5))

zips='00000,11296,90000,95800,95814,95900,96001,99999'
do while len(zips)>0
if ","$zips then
zw=left(zips,at(',',zips)-1)
zips=substr(zips,at(',',zips)+1)
else
zw=zips
zips=""
endif
append blank
repl zip with zw
enddo
index on zip tag zp
set order to zp
zcurrval ='95695'
set near on
set exact off
seek zcurrval
?recno(),zip
seek '11296'
?recno(),zip
dele tag all
clos data
erase ("test.dbf")

Output:
4 95800
2 11296

 

[mmerlinn]

severach:

Thanks for verifying my code - a star is coming your way. You have confirmed that the problem does NOT lie in the code. So the only other place the problem could be is in the DBF itself.

I previously checked the DBF several times for problems and could not see any. The most obvious problem could be the letter 'O' instead of the digit "0" in the entries themselves. However, that is the first thing I checked and found nothing wrong there, every '0' is indeed a zero and not a letter 'O'.

The other obvious place to check is the incoming string (zcurrval). It comes from a text box that is set to accept numerals only, so that can not be the problem either.

I guess the only thing left to do is to rebuild the table from scratch and throw away the one that is giving me fits. Hopefully that will solve the problem.

I also copied your code to tinker with it, but when I tried to run it I got the 'Unrecognized command verb' error message in this line: 'if "," $ zips then'.

So I made the following changes to your code using two of my favorite UDFs (faq184-5975), and the results I get match the results you posted.

[tt]
clos data
erase ("test.dbf")
create table test (zip c(5))
zips='00000,11296,90000,95800,95814,95900,96001,99999'

[COLOR=black yellow]DO WHILE NOT EMPTY(zips)
APPEND BLANK
REPLACE zip WITH ATXLEFT(zips, ',')
zips = ATXRIGHT(zips, ',')
ENDDO[/color]

index on zip tag zp
set order to zp
zcurrval ='95695'
set near on
set exact off
seek zcurrval
?recno(),zip
seek '11296'
?recno(),zip
dele tag all
clos data
erase ("test.dbf")[/tt]


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

This is NUTS!!!!

Built completely new table (SCF2St) with a character field (Pscf) exactly 5 characters wide.

Populated it with a list of zip codes.

Ran an ASCII check to validate that every entry was exactly 5 characters long and that every character in every entry contained only ASCII codes 48 thru 57 (0-9).

Verified that every entry in the table was a character type {TYPE('Pscf') = 'C'}.

Verified that incoming character string (zcurrval) was exactly 5 characters long and contained only ASCII codes 48 thru 57.

Verified that SCF2St was the correct active table immediately before calling SEEK.

Verified that the active table was correctly ordered on 'Pscf' immediately before the SEEK.

Built and ran my app several times changing ONLY the line with SEEK in it.

Ran using SEEK LEFT(zcurrval, 1) - WORKS PERFECTLY.
Ran using SEEK LEFT(zcurrval, 2) - WORKS PERFECTLY.
Ran using SEEK LEFT(zcurrval, 3) - WORKS PERFECTLY.

Ran using SEEK LEFT(zcurrval, 4) - FAILS!!!
Ran using SEEK LEFT(zcurrval, 5) - FAILS!!!
Ran using SEEK zcurrval - FAILS!!!

When it worked perfectly, the results were always consistent with SET NEAR ON.

When it failed, the record pointer was ALWAYS set to EOF and EOF() was ALWAYS .T.

Moved the project to an earlier different machine 23 miles away. Then built and ran it there using a different incarnation of FP2.6. SAME EXACT RESULTS!!!

What can possibly be the problem here? Code? Don't see how. Table? Don't see how. FoxPro? Don't see how. Machine? Don't see how.

IS THERE SOMETHING HERE THAT I AM NOT SEEING????


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[cricket]

Have you used ALLTRIM in your indexing expression?

 

[brigmar]

could it possibly be your TORDER() function ?

 

[DSummZZZ]

Do you get funky results if you test it outside the program?

USE SCF2St ORDER whateveritis
?SEEK('956')
?SEEK('9569')
?SEEK('95695')
?SEEK('00000')

Also, did you delete and recreate the index from scratch?


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[mmerlinn]

cricket

Yes, I am using ALLTRIM when I am ordering the table, although in this case I fail to see how it would make any difference as the field width is 5 characters and every record is exactly 5 bytes long. See my last post about the verifications I did. See below where I show that I am using ALLTRIM.

brigmar

So far as I know, my TORDER UDF is not causing the problem. However, I did not replace it with straight SELECT and ORDER statements to find out. I will do that and see what happens.

Meanwhile, here is the code for TORDER and TOPEN for you to look at just in case there is a problem there that I do not see.

[tt]
*******************
*
* FUNCTION NAME: TORDER
*
*******************
*
* Select and order open table.
*
* SYNTAX:
* = TORDER(<table file>[, <tag name>])
*
* ENTRY:
* <table file> = alias of table to get data from
* <index tag name> = name of index tag to order table with
* EXIT:
*
* REMARKs:
* SELECTs table in open workarea - works exactly like
* SELECT(ALLTRIM(<table file>)
* *Table MUST be already open in some workarea*
* If <index tag name> defined, also SETs ORDER TO order
* specified by index tag.
* ALLTRIMmed because must be EXACT match.
*
*******************

FUNCTION TORDER
PARAMETERS qTableName, qIdxTagName

qTableName = TOPEN(qTableName)
SELECT (qTableName)

IF NOT EMPTY(qIdxTagName) AND TYPE('qIdxTagName') = "C"
SET ORDER TO TAG (ALLTRIM(qIdxTagName)) IN (ALLTRIM(qTableName))
ENDIF

RETURN
[/tt]

[tt]
*******************
*
* FUNCTION NAME: TOPEN
*
*******************
*
* Open one table.
*
* SYNTAX:
* = TOPEN(<table name>)
*
* ENTRY:
* <table name> = name of table
* MUST include table name - Path & Extension optional
* Path defaults to value of SET PATH - Extension defaults to '.DBF'
* EXIT:
*
* RETURNs:
* Alias of table
*
* REMARKs:
* Opens table in first available workarea. Table MUST already exist or
* "file <<fullpathname>> does not exist" error is returned.
*
*******************


FUNCTION TOPEN
PARAMETERS qTableName
PRIVATE ALL LIKE z*

zalias = IIF(TYPE('qTableName') = 'C', qTableName, '')

IF NOT EMPTY(zalias)
qTableName = ALLTRIM(qTableName)
zpath = JUSTPATH(qTableName)
zalias = JUSTNAME(qTableName)
zext = IIF(EMPTY(RAT('.', qTableName)), '.DBF', SUBSTR(qTableName, RAT('.', qTableName)))

zpath = IIF(EMPTY(zpath), SET('PATH'), zpath) + zalias + zext

IF NOT USED(zalias)
USE (zpath) AGAIN ALIAS (zalias) IN 0
ENDIF
ENDIF

RETURN zalias
[/tt]

DSummZZZ

Yes and no. I have tested my code against a different DBF (see above) but I have NOT checked my DBF file against different code. So I tested my DBF in the command window and got these results:

[tt]
CLOSE DATA
USE "MHDesktop FolderroDev:TPtsDBFBF:SCF2St.DBF" EXCLUSIVE
SET ORDER TO TAG Pscf OF "MHDesktop FolderroDev:TPtsDBFBF:SCF2St.CDX"
GO 20
a = Pscf
?a 28900
?type('a') C
?seek(left(a,1)) .T.
?seek(left(a,2)) .T.
?seek(left(a,3)) .T.
?seek(left(a,4)) .F.
?seek(left(a,5)) .F.
?seek(a) .F.
[/tt]

Note that I extracted a value from the table and used that value in the SEEKs. Note especially that it could NOT even find ITSELF!!!! Note also that these are the SAME EXACT results that I have been getting in my app, so somehow someway the problem MUST lie in the DBF file. Tis really screwy if you ask me.

I did not recreate the index from scratch. Will do that also since corrupted indices can produce all kinds of screwy results. Will post the results tomorrow if I can.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[cricket]

ALLTRIM in an index expression has a curious feature.
If the first record it encounters has 3 characters in the indexing field after ALLTRIMming it, all subsequent records are only indexed on the first 3 characters also.

My guess is that you have a short record of 3 characters at the top of your table, and that is why your SEEK depth is only 3.

If, as you say, all your records have 5 (non space) characters then ALLTRIM is redundant, and should be removed

Try an alternative way of indexing, and I'm sure the problem will go away.

 

[brigmar]

cricket may be on to something there...

When I run a an SQL select statement with a calculated field, the field length for ALL records is set to the field length of the first record seen..

eg.

Table 'names', column 'name' contains "Andy Brick", "Charlie Deverin", "Ella Fitzgerald"

SELECT name, CHRTRAN(name,' ','') FROM names

produces:

Andy Brick         AndyBrick
Charlie Deverin    CharlieDe
Ella Fitzgerald    EllaFitzg

It may be that when you set your index up, you had a record with only 3 characters.

Considering that some ZIP codes start with '00', the leading zeros may have been dropped if they were imported in from Excel or some other app that translated them as a number. Especially if they were ordered in that external app.

 

[DSummZZZ]

Looking at your results again, I agree with brigmar and cricket. Your SEEKS() are only valid for the first three characters of your field.
Recreate the index without doing any trimming. It's better to pad the SEEK'ed value rather than use an embedded functyion in indexing anyway, if your data has spaces.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[mmerlinn]

PROBLEM SOLVED!!!

Star to you DSummZZZ for pointing me to potential problem with CDX file. Star to you cricket for your explanation of what was probably happening.

Here is what I did. I deleted and rebuilt the CDX file from scratch. That corrected the problem.

Then I went into the old CDX file and found this:

"---151---303---455---607---759---911---999---"

Then I went into the new CDX file and found this:

"---07600---15300---23000---30700---38400---46100---53800---61500---69200---76900---84600---92300---99900---"

The dashes (---) represent variable length groups of non-printable characters in the file.

As you can see, cricket was correct. The CDX file was only 3 bytes wide as opposed to the required 5 bytes to do the job I needed done.

As to the why, I am unable to say. What I do know is that all records in the Pscf column of the DBF are exactly 5 bytes long, so the error could not have been introduced there.

Regardless, I am ECSTATIC that we found the problem and that my app works as it should.

Thanks to all of you.




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

One last comment relating to brigmar's last post.

I can easily see where importing from a spreadsheet can introduce all sorts of errors in a database, since most spreadsheets format everything that LOOKS like it MIGHT be a number as a number. That is one of the main reasons I HATE spreadsheets, they ASSUME that something is a number even if it isn't numeric. A part number that happens to be '001E-21' becomes a number 1E-21 in a spreadsheet while '001A-21' remains '001A-21'. Arghh.

However, in this particular case, these zips were not imported from anywhere. I manually entered them in one at a time since I couldn't think of any other faster way to do it, and once they are in the DBF they never change and new ones are never added. Basically this DBF is just a look-up table to find postal zones using my 972 SCF as the starting point and as such is not very long. The reason for using a DBF as opposed to embedding in the app is that as needed I can add more columns, one for each SCF, to find the postal zones between any two U.S. post offices. Conveniently, UPS, FEDEX, DHL, and the U.S. Post Office all use the same system to calculate their shipping rates.

Anyhow, this is one more that I now can add to my list of GOTCHAs.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[TamarGranor]

The key lesson here is never using any form of TRIM() in an index.

Tamar