I have a form with a complex onBeforeUpdateEvent that does a lot of lookups, then performs a quadratic equation on retrieved and form values to set one field prior to the database update.
It appears that, in ScriptCase 9.3 at least, that stringing complex SQL statements together causes ScriptCase to parse event code incorrectly. For example:
...
$sql = "SELECT date1, date2 FROM table ORDER BY date1 DESC LIMIT 1";
sc_lookup(res_set, $sql);
if ( count({res_set}) != 0) {
$date1 = {res_set[0][0]};
$date2 = {res_set[0][1]};
}
$sql = "SELECT TIMESTAMPDIFF(SECOND, '".$date1;
$sql .= "','".$date2."') / 86400.0";
sc_lookup(res_set, $sql);
...
There is nothing wrong with this code from a PHP perspective, and it will work in an event handler a few times, but eventually, if you have a lot of lookups like this, the application will hang with an error message: call to undefined function sc_lookup() at line 50billion.
In the above example, the point of failure might be the first call or the second, and to make matters much MUCH worse, the problem will move around as you wrap debugging code around the calls, and I haven’t yet discovered what causes it or makes it move, but it’s always a macro call that causes it to break. Logging everything the code does - the actual values of the composed strings before the call to sc_lookup(), yields absolutely no clue as to why this happens.
Right now, it happens at that second call. The composite string value before the call is:
SELECT TIMESTAMPDIFF(SECOND, '2019-04-01 10:43:29', '2019-05-01 13:24:00') / 86400.0
Clearly, my event handler has broken free of the scope established by ScriptCase between the first call and the second, but I can’t figure out how or why.
Heeeeelp!