Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Is datawindow sql considered embedded sql?

  1. Advice
1
Votes
Undo
  1. Raymond Longoria
    Raymond Longoria
  2. PowerBuilder
  3. Thursday, 24 September 2020 23:06 PM UTC

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?

  1. DataWindow
  2. Embedded SQL
PowerBuilder and JAWS Screen Reader, Part 2
.NET DLL Importer ( PB 2019r2 )
Accepted Answer
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Friday, 25 September 2020 16:45 PM UTC
  2. PowerBuilder
  3. # Permalink

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.

Comment
Load more comments
mike S
  1. mike S
  2. Friday, 25 September 2020 17:44 PM UTC
" stored procedures are abused,"

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.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Friday, 25 September 2020 20:39 PM UTC
Hi Raymond, Glad my answer was useful for you! I recommend you guys get PowerBuilder CloudPro edition so you have all the features including PowerScript Migrator.
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Saturday, 26 September 2020 03:51 AM UTC
I am adding my voice to support Mike's position that SPs are abused, and Armeen's statement that they lock you into one database.



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.



Just.... no.
  1. Helpful
There are no comments made yet.
Responses (5)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 24 September 2020 23:30 PM UTC
  2. PowerBuilder
  3. # 1

Hi Raymond;

   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

Comment
Raymond Longoria
  1. Raymond Longoria
  2. Friday, 25 September 2020 17:12 PM UTC
Time is definitely the issue. Thanks!
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Friday, 25 September 2020 00:10 AM UTC
  2. PowerBuilder
  3. # 2

The SQL that retrieves data for a DW is not considered to be EMBEDDED SQL by PowerBuilder.

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 25 September 2020 15:13 PM UTC
+1 for Olan, because it's NOT considered as "embedded", but .... shouldn't it be considered as embedded sql though? Because in reality it is, as it has all the properties of being fixed sql code present in our code.

regards
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 25 September 2020 15:34 PM UTC
  2. PowerBuilder
  3. # 3

"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.

 

 

 

Comment
Raymond Longoria
  1. Raymond Longoria
  2. Friday, 25 September 2020 17:11 PM UTC
I will definitely follow that order. I wasn't sure about the order of events. Thanks!
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 25 September 2020 17:30 PM UTC
  2. PowerBuilder
  3. # 4

Hi Raymond;

    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 ...
  • OJSyntax='ANSI_Escape'

  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

 

 

Comment
Raymond Longoria
  1. Raymond Longoria
  2. Friday, 25 September 2020 19:14 PM UTC
Thank you for this! I am adding this to my ToDo list.
  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.