Wiki Home

SQL Select Traps


Namespace: SoftwareEng
Nice article by Mike Lewis: Three SQL SELECT traps: SQL SELECT is one of the most versatile command available to Visual FoxPro programmers. But watch out for the pitfalls. http://www.ml-consult.co.uk/foxst-09.htm

Here's a quick summary (in case the link ever is broken):

1) If using a cursor from an Select Sql statement in a second Select Sql statement, put the NoFilter clause on the first statement.

2) When using IIF(l,r1,r2) as a calculated field value, make sure r1 and r2 are the same length or else the longer one might get truncated.

In VFP9: select cast( IIF(l,r1,r2) as char(100) )

3) Never change the data environment in a UDF being used in a Select Sql statement. Even if the UDF sets the select area, index order, current record position, etc, all back the way it found them, it can still cause the Select Sql to return incorrect results.
Does this hold true if the UDF is in a different datasession? Ie: a Select Sql calls a method of a business object that is in a different datasession than the code doing the Select Sql. Is this safe at all? Is it also safe for that "protected" UDF in its own private datasession to change the data environment?
Category Tips And Tricks Category Learning VFP Category VFP Commands
( Topic last updated: 2007.10.09 12:48:35 PM )