Company logo

FOXSTUFF

Advice, tips, techniques and downloads for Visual Foxpro developers.

Home | Contact

Three SQL SELECT traps

SQL SELECT is one of the most versatile command available to Visual FoxPro programmers. But watch out for the pitfalls.

Cursor or Filter?

If you need to run an unusually complex SELECT, you might be tempted to break it into two separate statements. The first SELECT would produce an intermediate cursor, which you would then use as input to the second SELECT. For example:

SELECT Cust_id, Company, Country, Maxordamt FROM Customer ;
 INTO CURSOR C1
 
SELECT C1.company, Orders.order_id, Orders.order_amt;
 FROM C1 INNER JOIN Orders ;
 ON C1.cust_id = Orders.cust_id;
 WHERE Orders.order_amt > .25 * C1.maxordamt

Although there is normally no problem with this, the second SELECT might go wrong in certain circumstances. In particular, if you are using VFP 6.0 or later, the second SELECT might produce the following error message:

Figure 1: Error message in VFP 6.0 or later

In earlier versions, you won't see this message, but the second SELECT might either produce incorrect results or no results at all.

The problem arises when the first SELECT meets these three criteria: (i) it is based on a single table; (ii) it contains no calculated fields; and (iii) it is fully Rushmore optimisable. When all of those conditions are true, VFP does not really create the cursor. Rather, it simulates it internally by applying a filter to the input table.

This normally causes no problem. A filtered table will behave just like a cursor - except where it is being used as an input to another SELECT. In that case, VFP (6.0 or later) will generate a run-time error and display a message like the one shown above (in earlier verions, the SELECT will simply go wrong).

Fortunately, there is an easy workaround: just add the keyword NOFILTER to the INTO CURSOR clause in the first SELECT. So, referring to the above example, the first statement will look like this:

SELECT Cust_id, Company, Country, Maxordamt FROM Customer ;
 INTO CURSOR C1 NOFILTER

If you use the Query Designer to generate SELECT statements, you can specify NOFILTER by opening the Query Destination dialogue (from the Query menu). Click on the Cursor button. You'll see a NOFILTER checkbox in the expanded dialogue. But this option is only present in VFP 8.0 and above. In earlier versions, you will have to add NOFILTER by hand-modifying the generated code.

Adding NOFILTER will solve the problem. But note that there is a performance penalty in doing this, so don't do it unless you really need to.

Watch your field widths

Consider the following SELECT statement:

SELECT product_id, ;
 IIF(products.discontinu,"DISC",products.prod_name) ;
 FROM Products

You would expect this to produce a result set with two columns. The first column will contain the product ID. The second column will contain the product's name, unless the product has been discontinued (that it, the discontinu field is true), in which case it will contain the string DISC.

When you run this SELECT, it might work perfectly. Alternatively, you might find that the product name has been truncated, so that only the first four characters are visible. This is because SELECT determines the widths of the columns in the result set from the widths of the fields in the first row. If the first product happens to be discontinued, the entire column will only be four characters wide, which is not what you want.

It's easy to work round the problem, provided you are aware of it. In this case, you would simply pad out the DISC string with an appropriate number of spaces.

Are UDFs safe?

According to the Visual FoxPro documentation, a column in a SELECT's result set can be based on any legal VFP expression. In other words, you can include any expression you like in the list immediately following the SELECT keyword. The result of the expression will be used to populate the corresponding column in the results.

In theory, that means that you can call user-defined functions (UDFs) in a SELECT. For example, the following code would be valid:

SELECT product_id, MyFunc() FROM products

where MyFunc() is a UDF.

However, as many VFP programmers have discovered, a SELECT can go seriously wrong when it includes UDFs. In fact, we have seen several books and articles which stridently advise programmers never to use UDFs in a SELECT statement.

In our own experience, UDFs are safe to use in SELECTs, provided they do not affect the data environment in any way. In particular, they must not switch work area, move the record pointer, change the controlling index, update the current record, or do anything else that is in any way connected with tables or data. This is true even if the function carefully restores every aspect of the environment to its original state before it exits. If this rule is violated, your SELECT will almost certainly produce incorrect results.

However, where a UDF only performs an operation on memory variables, it should be perfectly safe to use it in a SELECT. But do be careful. This advice is based on our own experience, which might not apply to your particular code. So be sure to test your SELECTs especially carefully whenever there are UDFs involved.

Mike Lewis Consultants Ltd. December 1999.  Revised January 2006.

More Visual FoxPro articles | Crystal Reports articles | Recommended books | Visual FoxPro consultancy | Contact us

FoxStuff is maintained by Mike Lewis Consultants Ltd. as a service to the VFP community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).

The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.

© Copyright Mike Lewis Consultants Ltd.