INLIST Function not EXACT by default

[vernpace]

After some bug hunting, I discovered that string comparisions with the INLIST function are not exact by default. For some reason, I always assumed it was - bad assumption. Easy to fix by SET EXACT ON, but easy to forget to do it if needed. Now I gottah go through a bunch of code and make changes...

 

[Mike Lewis]

Vernpace, I've never thought about this before, but I just did some quick tests, and of course you are right.

It seems that the comparison works like the equals operator, in that the first parameter behaves like the term to the left of the equal sign, and the others like the one to the right - which I suppose is what you would expect.

The reason I have never thought about it is that I always have SET EXACT ON at the start of my main program, and I leave it ON the whole time. I know that that sort of global setting is not best practice, but I've been doing it since dBASE II, and it's hard to change.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vernpace]

Mike, the benefits of a global SET EXACT ON probably outweigh potential headaches of it being set OFF. We have SET ANSI ON as a global, but not the other - dunno why and may be too risky to change.

 

[Mike Lewis]

I agree. It would be interesting to know what other developers do in this regard.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I think of SET EXACT ON as useless, because of how the help topic "String and Value Comparison in Visual FoxPro" describes the matching behavior:

What's wrong?

SET EXACT ON
? 'ab ' = 'ab'  && .T.
? 'ab' = 'ab '  && .T.

* how I'd expect exact comparison:
? 'ab ' == 'ab' && .F.
? 'ab' == 'ab ' && .F.

I think it's due to char fields that ignoring spaces was considered as okay. SET ANSI also was mentioned, INLIST will behave by EXACT setting in normal code and according to the ANSI setting in SQL.

You should take a look at the ASCAN options. Since arrays are not limited to just 25 values, you can do more with them and can have the comparison as you like it without setting the global EXACT or ANSI settings. And ALINE helps to cenvert a CSV string like 'alice,bob,charlie' into an array in the first place. Or use the IN clause in SQL, or an INNER join with the list of values in a cursor.

Chriss

 

[Mike Lewis]

Well, since this seems to have become a discussion on the merits or otherwise of using INLIST(), I agree with Chriss that there are several better options. In particular, searching an array is more flexible, not least because it allows you to add values to the search list without altering your code.

That said, I still find INLIST() useful when there is a small number of fixed values to check. For example:

IF INLIST(lnErrorCode, 124, 1643, 1644, 1958)
  * printer-related error
ENDIF

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

That's true, inlist() does not have problems comparing other data types like numbers or dates.
In SQL I'd rather use the IN clause as that's SQL standard.


Chriss

 

[GriffMG]

Personally, I usually work with EXACT OFF - and allow for the length differences.
I don't seem to ever explicitly set it OFF, I just accept it as a default.

As far as I can tell, I have never used INLIST(), I tend to build a string, using a delimiter, and search using $
so something like :

 if m.String+";"$"ABC ;DEF ;HIJK;LM  ;"

Both sides of the $ might be built from tables...

Where I am working with a web interface and want to select a lot of records based on their unique IDs I
tend to use a parameter to do the same thing - because the VFPOLEDB is pretty quick at extracting the records

SELECT * from tbkXYZ where UID$? order by Field1,Field2

I suspect this is dBaseII behaviour carried through to the extreme!



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

inlist() does not have problems comparing other data types like numbers or dates

True, but you need to be careful with dates and datetimes:

d1 = DATE(2020, 2, 16)
d2 = DATE(2020, 2, 17)
d3 = DATETIME(2020, 2, 16, 15, 0, 0)
? INLIST(d1, d2, d3)

Since d1 and d3 contain the same date, you might expect INLIST() to return .T. In fact, it returns .F. because it coerces d1 to a datetime, and the time elements of d1 and d3 do not match. That probably sounds obvious, and it's unlikely to be an issue as long as you only have two or three parameters. But if you pass a dozen or more date parameters, and the odd datetime creeps in, it could catch you out.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Well, DateTime extends Date, but the same is true about prolonged strings, isn't it? It's a matter of your expectations. If you think of a date as the interval of the whole day and consider a datetime within it as equal to the date because it's part of it, then you're also stretching the meaning of exact match, don't you?

We could extend the topic to the BETWEEN() function and the SQL BETWEEN clause.

Chriss

 

[TamarGranor]

The behavior of SET EXACT makes more sense when you realize that FoxPro started life as an interactive, command-line product. SET EXACT OFF made it possible to type something like:

SEEK 'SM'

to position the record point on the first 'SM' record.

That history is also why the GOTO keyword is optional. Type a number in the Command Window and the record pointer moves to that record #.

Tamar

 

[GriffMG]

Tamar

That is news to me.

I will try that, no good reason, just nice to know

Thank you

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

Go to heck

that works still

not massively useful, but very interesting B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[vernpace]

Trying to come up with cases where SET EXACT ON | OFF makes sense. Griff, your technique of building a string and using the $ operator is a good alternative to INLIST, so what's left? I cannot come up with anything. We don't use it in our framework, nor have we ever used it in a project that I remember (except for a few times using INLIST).

 

[Mike Lewis]

not massively useful, but very interesting

Griff, are you referring to the fact that the keyword GOTO is optional in the GOTO command?

I used to use this in a quiz on my VFP courses. I would ask the class to write the smallest amount of code possible that would move the record pointer to the first physical record in the currently selected table, and with no assumptions about indexes, filters or deleted records.

The answer of course was:

1

Very few of the people in the class got it right.

By the way, there is also an optional RECORD keyword.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads