I am preparing for a Powerbuilder upgrade from 8.0 to 2019. I will be moving embedded sql to stored procedures and db functions. Is the sql in datawindows considered embedded sql and is it a good practice to move it to stored procedures and have the datawindows call them or to leave it as is?
I agree with Mike. I think stored procedures are abused, and also locks you down to a database.
If I was the decision maker for your project, I would personally approach like this (of course I haven't seen your source code so take my suggestions with a grain of salt).
1. First, upgrade to PowerBuilder 2019 R3. Compared to PowerBuilder 2019 R2, this is a long-term support version and has some really nice features. Again, I think Mike is totally right you do the PB upgrade first get on supported version then mess around with architectural changes.
2. Second, I would look at removing obsolete features. Obsolete features are ticking time bombs! This is not the complete list since you are on such an old PB version, but I would definitely recommend at least dealing with this short list.
3. Third, consider enabling some new features that will modernize your app with very little to no effort, for example UI Themes, NativePDF, PowerClient, switch out icons with the new ones provided, and if you are using SQL Server switch to the new OLE DB driver.
4. And finally, if you need to make architectural change, I would look at moving PowerBuilder business logic (e.g. embedded SQL, non-visual PowerScript, DataStores, etc.) into REST APIs. The PowerScript Migrator feature of PowerBuilder CloudPro Edition will highly-automate such conversion. You can easily call REST APIs from PowerBuilder using the RESTClient object.
Basically, simply put I would approach architectural changes last.
this is off topic, but i came to that conclusion too and went 100% in the other direction. I eliminated most stored procedures in my application. There are less than a half dozen reports that require stored procedures (very complex reports that won't work otherwise.), nothing else uses a stored procedure. This allowed a huge amount of flexibility especially at runtime. My application generally gets faster with every release - and it runs both as client/server as well as powerserver web.
- mike S
- Friday, 25 September 2020 17:44 PM UTC
- Armeen Mazda @Appeon
- Friday, 25 September 2020 20:39 PM UTC
In my experience I have found that I want EVERYTHING under my control, thank you VERY much! I have no desire to wait for DBA approvel to start updating my SPs, or to depend on anyone else to synchronize the movement of these updated processes into production environments. While they do have a place, they should be used as sparingly as possible at all times. Use the flexibility and power of PowerBuilder whenever possible.
- Olan Knight
- Saturday, 26 September 2020 03:51 AM UTC
FYI: Since you are coming from an ANSI version of PB to a Unicode version of PB (Unicode since PB 10.0 and higher), the SQL challenge may be in the old PB vs ANSI join if you are also going to use a newer DBMS version and thus DB Client. You may have to change the way your SQL join operator is used. The join types can now be controlled in your App's SQLCA DBParm field as follows:
- OJSyntax='PB' // Very Old way vs the new ...
- OJSyntax='ANSI' // or alternatively ...
I would suggest that you check this feature / setting out in the PB Help and also with your DBA Team to see if this might impact your PB App(s) after the basic migration.
Regards .... Chris
"I will be moving embedded sql to stored procedures and db functions"
how does that relate to upgrading your powerbuilder version?
You are just adding complexity to an upgrade. IMO, you should upgrade your PB version first, then if you want to move your sql to stored procedures do that as a phase 2.
You do lose flexibility by using stored procedures for everything. they do have their place, but they are not a magic bullet.
The SQL that retrieves data for a DW is not considered to be EMBEDDED SQL by PowerBuilder.
- Miguel Leeuwe
- Friday, 25 September 2020 15:13 PM UTC
The SQL based DWO's can be refactored to be SP based DWO's (DW's with no SQL that just call a SP instead). It's up to you if you have the time or convert the App "as is" and make the SQL=>SP DWO conversion something to do little by little over time.
Regards ... Chris
- Page :
However, you are not allowed to reply to this question.