Tweet
We have a situation where the same program accessing the same data provides results that differ. The problem occurs when the underlying data changes. It seems that the process fails to recognize the change in data, and reverts back to a previous results set. The hierarchy of programs is:
a. an RPG ILE program using CGIDEV2 to display a web page
b. calls an SQL UDF
c. which in turn calls an ILE function called GetCutExpectedDate
The GetCutExpectedDate is included in a results set that is generated by the SQL UDF. The SQL UDF passes off it's results to the RPG ILE pgm.
We know the GetCutExpectedDate returns the right date because
a. we observed this with debug
b. we also invoke the GetCutExpectedDate function directly in the RPG ILE program (for other purposes) and it returns the correct date
We also debugged inside the SQL UDF, and confirmed that it too is returning the right date as output. But when we debugged what was being returned to the RPG ILE, we see that the SQL UDF is not returning the same data that we saw when we debugged within the SQL UDF. The date is returned as a column to the RPG ILE, and it is also used as an "order by" in order to sort the data being returned
We have temporarily fixed the problem by eliminating the use of the SQL UDF within the SQL that is found in the RPG ILE pgm. Instead, we replicate the process that takes place within the ILE function within the SQL statement that is inside the RPG ILE program (essentially duplicating the logic so that we now have 2 source codes to maintain if we want to make a change)
Another interesting point is that if I copy and paste the URL for the web page from one browser to another, and sign on as the same user, I get the same bad results. But if I sign on to the second browser session as a different user, it provides refreshed/accurate data. Where si the data being cached?
Note that the SQL UDF was originally defined as DETERMINISTIC, but we changed this to NON_DETERMINISTIC (thinking we had solved the problem). But after all, this was not the case. Or perhaps it is simply that cached somewhere is a version of the SQL UDF that does have the correction for the change to NON_DETERMINISTIC?
a. an RPG ILE program using CGIDEV2 to display a web page
b. calls an SQL UDF
c. which in turn calls an ILE function called GetCutExpectedDate
The GetCutExpectedDate is included in a results set that is generated by the SQL UDF. The SQL UDF passes off it's results to the RPG ILE pgm.
We know the GetCutExpectedDate returns the right date because
a. we observed this with debug
b. we also invoke the GetCutExpectedDate function directly in the RPG ILE program (for other purposes) and it returns the correct date
We also debugged inside the SQL UDF, and confirmed that it too is returning the right date as output. But when we debugged what was being returned to the RPG ILE, we see that the SQL UDF is not returning the same data that we saw when we debugged within the SQL UDF. The date is returned as a column to the RPG ILE, and it is also used as an "order by" in order to sort the data being returned
We have temporarily fixed the problem by eliminating the use of the SQL UDF within the SQL that is found in the RPG ILE pgm. Instead, we replicate the process that takes place within the ILE function within the SQL statement that is inside the RPG ILE program (essentially duplicating the logic so that we now have 2 source codes to maintain if we want to make a change)
Another interesting point is that if I copy and paste the URL for the web page from one browser to another, and sign on as the same user, I get the same bad results. But if I sign on to the second browser session as a different user, it provides refreshed/accurate data. Where si the data being cached?
Note that the SQL UDF was originally defined as DETERMINISTIC, but we changed this to NON_DETERMINISTIC (thinking we had solved the problem). But after all, this was not the case. Or perhaps it is simply that cached somewhere is a version of the SQL UDF that does have the correction for the change to NON_DETERMINISTIC?
Comment