Hi all, have a happy new year!

I could not find another way to post an enhancement request for future PB versions, so I'll leave it here. If there is a better way to do so, please let me know.

We develop and maintain a quite large c/s application (100+ libraries) that was started back in the 90s with one of the first versions of PB from Powersoft. Since then it was based on SQL Anywhere (Watcom, Sybase, SAP). So - like most of you - we struggled with the times when there were no nvos and some other programming concepts/enhancements that were implemented over the decades.

Unfortunately new features, versions, enhancements are always prioritized higher than refactoring code, that still does its job. That is why we still have a lot of business logic placed inside of windows and even global functions instead of nvos and we still have tens of thousands of embedded CRUD SQLs all over our application.

Now we are planning to support other databases than SQL Anywhere, like PostgreSQL and MSSQL. Our idea to solve this was to place some logic in the SQLPreview events of datawindow/datastore and transaction classes and replace the existing SQLs on-the-fly during runtime using a C# transpiling class, that converts our existing SQLs to some other SQL language.

We already accomplished that in some test cases for the datawindow/datastore classes using either SetSQLSelect(...) or Modify("DataWindow.Table.Select='...'"), but unfortunately the SQL cannot be replaced in the SQLPreview event of the transaction class and we could not find another way to get around this.

So our enhancement request is to have either the SQLSyntax argument in the SQLPreview event of the transaction class be by reference or to have some other kind of method like in DW/DS (see above) to replace the SQL syntax at runtime.

Or has anyone accomplished this task in a different way that we did not come up with yet? Without refactoring the whole application for example by replacing all embedded SQLs with DataStores, of course.

Best regards and thanks in advance

Marc

EDIT: I am aware that it might be easy to accomplish for INSERT/UPDATE/DELETE statements (by using EXECUTE IMMEDIATE and RETURN 1 in the SQLPreview event). BUT this does not work for SELECT statements as there seems to be no way to return the retrieved data back into the host variables of the calling script.