Avoiding crashes caused by SELECT SQL

Tech-Archive recommends: Fix windows errors by optimizing your registry

A little while ago I started experiencing crashes in VFP9 and could not
figure out what caused them. A post here and my local VFP guru pointed me in
the right direction and I now believe I have resolved the problem (thanks
Bill and Roger!).

The following may assist other developers experiencing crashes.

A cut down version of the code that was crashing is:-
LPARAMETERS tn_id
IF EMPTY(tn_id)
* error message
RETURN
ENDIF

SELECT * from names ;
WHERE names.id_key=tn_id ;
INTO CURSOR cur_temp

The first step was to eliminate the line breaks:-
LOCAL lc_sql
lc_sql='SELECT * from names'
lc_sql=lc_sql+' WHERE names.id_key=tn_id' && Starts with a space
lc_sql=lc_sql+' INTO CURSOR cur_temp'
=EXECSCRIPT(lc_sql)

Instead of crashing, I now got an error saying that tn_id could not be
found - even though it had been tested for empty.

The second setp was to replace the variable with a field:-
CREATE CURSOR cur_filter (n_id i)
m.n_id=tn_id
INSERT INTO cur_filter FROM MEMVAR

then change the comparison:-
lc_sql=lc_sql+' WHERE names.id_key=cur_filter.n_id'

This now runs without crashing and returns the correct results.

As I have lots of SQL statements in a project that is nearing completion I
wrote a utility that converts the first format into the second format.

This saved a lot of time and I would be happy to post to an appropriate web
site if anyone is interested.

John Pugh
Adelaide, South Australia


.