I'd like to find out how other people are formatting their SQL statements before submitting them to the SQLExec() function for execution. I'm looking for a better way. I've done the bit where I enclose the various parts of the command in quotes and then added my variables such as:
m.pcSQLCommand = 'SELECT OrderNo, CustNo, CustName, ShipCity ' + ;
' FROM OrderHdr ' + ;
' INTO CURSOR curOrderHdr ' + ;
' WHERE OrderNo = "' + m.pcOrderNo + '"'
Starts to make it unreadable, doesn't it? Imagine a much more complex SQL command with INNER or OUTER JOINS and multiple join conditions, etc. So, I then hit on the idea of loading the SQL statement in with a TEXT...ENDTEXT command such as:
TEXT TO m.pcSQLCommand
SELECT OrderNo, CustNo, CustName, ShipCity
FROM OrderHdr
WHERE OrderNo = 'm.pcOrderNo'
ENDTEXT
This is more readable but it has some drawbacks such as resolution of the VFP memory variable. I tried doing a macro substitution of the m.pcOrderNo variable but it would not do the substitution within the TEXT...ENDTEXT command. It also would not resolve it when the m.pcSQLCommand variable was passed to the SQLExec()function. I had to add a SUBSTR() function after the TEXT...ENDTEXT command to substitute the string m.pcOrderNo with the actual value of the variable.
So, my question is... Is there a better way? Is there a more acceptable method of doing this?
Time is a wonderful teacher. Unfortunately it kills all of its students.