Good job Carlos and thanks for sharing.
I imagine there is a lot of effort invested there. I wanted to provide some constructive criticism as feedback.
Your Class is involved in a sensitive area in any Clarion application, which is datacentric, requiring a lot of care to avoid unforeseen events, and the complexity of the issue makes it very difficult to foresee everything, or that a Clarion version change does not end up affecting something that worked, like for example one that was mentioned lately
Possible BIND bug in clarion 13630
something like that probably won’t affect the code it’s just for example. To facilitate maintainability I believe that where it can be used what Clarion provides should be used, for example I believe that:
SELF.Tables.IsSql = CHOOSE(INLIST(UPPER(pFile{PROP:Driver}),‘MSSQL’,‘ODBC’,‘SCALABLE’,‘SQLANYWHERE’,‘SQLITE’)<>0) it could just be:
SELF.Tables.IsSql = pFile{PROP:SQLDriver}
or was the intention to limit those 5 exclusively? I don’t know if this example could be extended to the CASE used to determine the FieldType and the NUMERIC ISSTRING ISGROUP functions
I understand the initial objective of migrating code from DOS without substantially modifying them, but each case would have to be analyzed, such as the one mentioned
LOOP UNTIL tm.NEXT (Orders)
…
LOOP UNTIL tm.NEXT (Detail)
Depending on the amount of Orders and Details to be processed, the speed improvement of a Clarion VIEW that internally does the JOIN may justify the substantial modification of the code, mainly in SQL but also not SQL. On the other hand, maybe the Class could be adapted to accept tm.NEXT(OrdersAndDetailsView)? but I understand that it would be complex and laborious and may require a design modification of the class since some reflections could go back to the FILE instead of the VIEW, and the effort may not be justified, you have probably already analyzed it.
In case of using the two nested loops and SQL databases, perhaps it could be optimized if the inner LOOP sent the filter in PROP:WHERE to the server with parameters instead of the fixed but different value each time, so that the query is always the same regardless of the value of the parameter, I understand that the server could have the query precompiled, where some mention that it is more optimal, although in some cases a new plan with new statistics is better, but I guess not for this child file. The problem is that this could only be done with the collaboration of Softvelocity to send parameters, although I honestly did not test if myFile{PROP:WHERE} = ‘myField = someVariable’ or ‘myField = parentField’ if Clarion sent the value as a parameter when being binded SQL and not as a fixed value.
Finally, something that may not be related to the Class but should be monitored in the migration, the use of PROP:WHERE and / or SET(KEY,KEY) in the same thread as a browse or FileDropDowns or FileDropCombos based on te same table was causing
Error Code 78 - Illegal Number of parameters. The last thing I remember was commented on in the Clarion11 group on 2020-11-02 in the topic ‘Re: Size not equal for key’ by Bjørn Øyvind Holm, also on the 6th in the topic ‘PTSS 43119’. You can view the PTSS log for more information and testing programs. They solved using ALIAS there.
Regards,
Federico