1. Marc Wietscher
  2. PowerBuilder
  3. Tuesday, 3 January 2023 13:50 PM UTC

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.

Accepted Answer
Andreas Mykonios Accepted Answer Pending Moderation
  1. Tuesday, 3 January 2023 14:04 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi. Happy New Year.

As this is an enhancement request, you should submit it to Search Bugs | Appeon. In the description you can also mention that it's an enhancement request. Normally someone from appeon's staff will answer.

Andreas.

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 3 January 2023 16:48 PM UTC
Hi Marc;

FYI: https://www.appeon.com/developers/roadmap

=> Provide better support for SQLPreview event (such as adding support for PreviewUpdate, PreviewInsert, and PreviewDelete) in PowerServer projects.

Regards ... Chris
  1. Helpful
  1. Roland Smith
  2. Tuesday, 3 January 2023 19:09 PM UTC
Chris, that is for PowerServer. We should have SQLPreview for the desktop Transaction object similar to DataWindow/DataStore with a way to override like the SetSQLPreview function.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 3 January 2023 20:17 PM UTC
Thanks Roland ...

FYI: ticket #9538 now reflects this as an enhancement request for SQLCA as well.

Regards ... Chris
  1. Helpful
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 3 January 2023 14:24 PM UTC
  2. PowerBuilder
  3. # 1

Hi Marc,

Have you tried the SetSQLPreview function to change the SQL in SQLPreview event of a datawindow?

https://docs.appeon.com/pb2021/datawindow_reference/dwmeth_SetSQLPreview.html

But it seems no such function for transaction object.

Regards,

René

Comment
  1. Marc Wietscher
  2. Tuesday, 3 January 2023 15:32 PM UTC
Hi René,



thank you! This is exactly what we would need for the transaction class.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.