PRB: SQL SELECT Statement UDF Executes Twice On First Record

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a table called SQLUDF.DBF, and add a single numeric field called nField1.
2. Place the values 1, 2, 3, 4, and 5 in the first five records.
3. Run the following code:

      * start of SQLUDF.PRG
      *
      * This program selects records from the input table and calculates a
      * running total in the output table by using a UDF.
   
      nRunningTot = 0        && initialize running total to 0
      nIterations = 0        && initialize iteration counter to 0
   
      SELECT *, Add_Em(nField1) as nRunTotal FROM SQLUDF INTO CURSOR temp
      BROWSE NOWAIT
   
      PROCEDURE Add_Em
           PARAMETERS tnFld1
              nIterations = nIterations + 1  && Increment iteration counter
   
              WAIT WINDOW "Record Number:        "+ALLTRIM(STR(RECNO())) + ;
                 CHR(13)+ "Number of Iterations: "+ALLTRIM(STR(nIterations)) ;
   
                 TIMEOUT 1
              nRunningTot=nRunningTot + tnFld1     && Calculate running total
          RETURN nRunningTot
      ENDPROCEDURE
   
      *End of SQLUDF.PRG
   						

SQLUDF.PRG Notes

• The Record Number displays 1 twice, then it displays 2 through 5.
• The Number of Iterations displays 1 through 6.
• The values placed in the RunTotal field are 2, 4, 7, 11, and 16 for records 1 through 5. The values should be 1, 3, 6, 10, and 15.

Example Workaround

   * start of SQLUDF2.PRG
   *
   * This program sets a logical flag to test for the first iteration of
   * the UDF to suppress getting a sum from the first record twice.
   *

   nRunningTot = 0                   && initialize running total to 0
   nIterations = 0                   && initialize iteration counter to 0
   lFirst_Time = .T.         && initialize 1st time through variable
   SELECT *, Add_Em(nField1) as nRunTotal FROM SQLUDF INTO CURSOR temp
   BROWSE NOWAIT

   PROCEDURE Add_Em
      PARAMETERS tnFld1
      IF lFirst_Time != .T.     && Only run if not 1st time through
         nIterations=nIterations + 1    && Increment iteration counter
         WAIT WINDOW "Record Number:        " + ;
            ALLTRIM(STR(RECNO())) + ;

            CHR(13) +"Number of Iterations: " + ALLTRIM(STR(nIterations));
            TIMEOUT 1

            nRunningTot = nRunningTot + tnFld1   && Calculate running total
       ENDIF
       lFirst_Time=.F.        && Togggle to .F. on 1st call to UDF
       RETURN nRunningTot

   ENDPROCEDURE

   *End of SQLUDF.PRG
				

Workaround Notes

• The lFirst_Time variable causes the UDF to not create a running total when called the first time.
• The Record Number displays 1 through 5.
• The Number of Iterations displays 1 through 5.
• The values placed in the RunTotal field are correct: 1, 3, 6, 10, and 15.

General Notes

• If record 1 of the source table does not satisfy a WHERE condition, the UDF is called once for the first record in spite of the workaround code.
• The overriding emphasis remains that the behavior of UDFs called from SQL SELECT statements is unpredictable. Extensive testing should be done to ensure that results are as expected. Please see the subheading <select_item> under SELECT - SQL in the FoxPro Language Reference for more information.