SQL Select behaves not like Seek

[mirkado227]

Say we have a table created like

CREATE TABLE Cards (KartNr C(6) NOCPTRANS)

The index is created with:

SET COLLATE TO  "machine"
INDEX ON kartnr TAG kartnr_idx

KartNr contains binary informations. With the browse command there are displayed as garbage characters. To display them, I have to use e.g. ASC(SubStr(KartNr,1,1)) etc.

Now say:
- in variable cKartNr is a valid CardNumber
- and I try to find the specific record in table Cards

SELECT * FROM Cards Where KartNr == cKartNr

returns all records in Table Cards

But

=SEEK(cKartNr, "Cards", "KartNr_Idx")

finds the specific record.

With other words: I found a solution for my problem. But I does not understand the background of this problem. Can someone enlighten me?

Thanks in advance

 

[mirkado227]

An addendum: I'm using Vfp9 under W2k

 

[audiopro]

Does '==' work with strings?

Keith

http://www.studiosoft.co.uk

 

[mirkado227]

Yes, of course!

 

[Stella740pl]

Audiopro, =='s main use is character string comparison. What else would you use it with?

mirkado227
Just in case, are those records deleted by any chance, and what's SET DELETED status? Is there only one record in the table for the search criteria?

Otherwise, take a look at this article. See if it has something to do with your problem. Looks like it might.

http://www.west-wind.com/presentations/foxunicode/foxunicode.asp

 

[audiopro]

Sorry, I was in Perl mode for a moment there, where == is a numeric operator.
Looks to me like both sides of the query are evaluating to a value of 0, therefore every record will match the criteria.
What do you get if you do a comparison list of
KartNr vs. cKartNr?

Keith

http://www.studiosoft.co.uk

 

[Stella740pl]

I was in Perl mode for a moment there

You, probably, still is.;-)

both sides of the query are evaluating to a value of 0
Zero value? In a character strings? Did you mean NULL, EMPTY, or anything else?

What do you get if you do a comparison list of
KartNr vs. cKartNr?
It's possible that KartNr vs.cKartNr is not the same as cKartNr vs. KartNr when comparing with "=", but "==" is "==" - exactly equal. A valid question, though.

 

[mirkado227]

@Stella740pl
I will read the article.

The answers of your questions are:
- The status of SET DELETED is ON
- Yes, only 1 record

 

[audiopro]

OK let mme re - phrase that then.
Both sides of the argument are evaluating to the same thing.
If they visually appear to be different (the reason for doing the visual comparison) then there is an error in the code or data type. Both sides of the argument must evaluate as 0, null or some other variant of nothing. Just what the value is is the next stage of the process.
This is known as logical problem solving.


Keith

http://www.studiosoft.co.uk

 

[Stella740pl]

mirkado227,

What I meant is that SELECT-SQL would ignore SET DELETED setting (unlike SEEK), so if many records are matching, even if they are marked for deletion, they all would be selected anyway. SEEK, on the contrary, would find only those not deleted if SET DELETED is ON.
But as you have only one matching record, it's out of the question. Take a look at the article, it might be of help to you. It elaborates on how to deal with texts in different languages represented as binary Unicode in VFP. You can also try to search this and other Tek-Tips VFP forums for those keywords; you might find some older threads to be of help.

audiopro,

Both sides of the argument must evaluate as 0, null or some other variant of nothing
Well, yes, I understand what you mean, that's probably what's happening, but the main question of this thread is, why they don't evaluate to those same things in SEEK. That's why I suggested that the article might be of help in explaining it.

 

[mirkado227]

@Stella740pl

I understand your argument with the deleted problem. But there are no duplicate records. And the SELECT statement returns all records, not only a few: all!

Thanks for the article. I also think, that there I am able to find the answer.

 

[Stella740pl]

I understand your argument with the deleted problem. But there are no duplicate records.
Yes, I got it. I already said above that "as you have only one matching record, it's out of the question".

And the SELECT statement returns all records, not only a few: all!
Yes, I've seen things like that. But usually that happens when "=" and not "==" is used for comparison of character strings and one of them is EMPTY or NULL. You know, with SET EXACT OFF, "ab"="" but ""#"ab".
(Thus is a very valid audiopro's question "What do you get if you do a comparison list of KartNr vs. cKartNr?”.) But you use “==”.
(Still, I would try SELECT * FROM Cards Where cKartNr == KartNr before doing anything else, just out of interest.)

You might want also to take a look at STRCONV( ) and try to apply it to both sides of the comparison.

 

[Olaf Doschke]

Hi mirkado,

try

SELECT * FROM Cards Where KartNr == cKartNr AND debugSQL()

function debugSQL()
   set step on
   return .T.
endfunc

Now you may be able to detect if KartNr is really the field KartNr of Cards or a variable or what else is messing up the comparison. Maybe the table Cards is open twice, once with an Alias like "G", "H" or something.

Bye, Olaf.

 

[mirkado227]

@Stella740pl

With STRCONV() everythink runs fine. E.g.

SELECT * FROM Cards Where STRCONV(KartNr,15) == STRCONV(cKartNr,15)

returns only the specific record.

Thanks for this hint! But I am still confused, why the orginal code

SELECT * FROM Cards Where KartNr == cKartNr

returns not the specific but all the records of the table Cards.

 

[Olaf Doschke]

you set the collation sequence to "machine" when indexing, but how is it set during SQL? I wonder if that makes the difference and STRCONV just converts to the range of characters, where that doesn't matter.

Bye, Olaf.

 

[audiopro]

I have been trying to follow this thread but it has not been easy. I am having trouble understanding how the data in the original CHR field has become binary.
This presumably is being compared with directly entered, card number data.

Keith

http://www.studiosoft.co.uk

 

[Olaf Doschke]

Hi audiopro,

the field is binary because of the NOCPTRANS option given in CREATE TABLE, so that field can contains binary data, as no code page conversion is done to any character. Don't confuse binary fields with BLOB or GENERAL fields.

Bye, Olaf.

 

[Stella740pl]

mirkado227,

This quote is from the article I directed you to (underlining is mine):

Using binary data is not optimal because [u]you loose the ability to treat the data like a string[/u]. String comparisons are not easy, [u]the data cannot sort according to language sorting patterns and anytime you do anything with the data it requires that you convert[/u]. But you can at least store this data. 

[I][b] Rick Strahl 
"Using Unicode in Visual FoxPro Web and Desktop Applications" [/b][/I]

That’s why I would suggest that your success with the SEEK might be unstable. Even though the article doesn’t contain examples where binary field is used as a key field for indexing and searching, so I am not 100% sure, I guess you should try to use STRCONV() in your INDEX expression and with SEEK. See if it works.

It is also possible that SEEK works differently from SELECT-SQL.

Years ago I used to work with Russian character data (and menu items, messages, all displayed text, etc.) in FoxBase, dBase III Plus and Clipper Summer ’87 under DOS, and this problem didn’t exist for me at all. You just run a Russian driver in your AUTOEXEC, and you are all set to go. Now, in VFP under Windows, I work only with English.

 

[Stella740pl]

Olaf,

Thanks for the debugSQL() function. Simple but useful, and apparently not easy to come up with .
Once in a while I am thinking of the ways to get inside the SELECT-SQL statement and see, what's happening there. Now I know how.

 

[mirkado227]

@Stella740pl

I changed all my code using now STRCONV( ..., 15) and everything works fine now. Thanks again.