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

Resolved Translating PB select to SQL

  1. How-to
0
Votes
Undo
  1. Philippos Antonopoulos
    Philippos Antonopoulos
  2. SnapDevelop
  3. Friday, 8 December 2023 13:41 PM UTC

Hello Everybody,

We are migrating our app from a client-server app to a 3-tier api enabled one.

 

We want to avoid scaffolding all of our thousands of datawindows. So we are looking to create an api call with a dynamic approach. What we have done is created this api call with a string as the parameter. This string contains the sql select statement that is needed to populate the datawindow.

 

The issue is that most of our datawindows have been built and saved using the Graphical Query Builder, so when we use the getSQlSelectwe get a PB select. This can not be used in the api to retrieve the data.

 

Does anyone know how to translate the PB select into a normal select either in powersript without a database connection or in C#?

Is there some existing libary that has been created that we can use to do this?

 

Thank you in advance,

Philippos

 

Build issues after migrating SnapDevelop from 2021...
Obfuscate Code in Snapdevelop
Responses (3)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 12 December 2023 12:38 PM UTC
  2. SnapDevelop
  3. # 1

What's exactly the problem of needing a database connection?

regards

Comment
Philippos Antonopoulos
  1. Philippos Antonopoulos
  2. Tuesday, 12 December 2023 12:41 PM UTC
The client that will need to do this conversion will not be connected to a database since it will be communicating to that database use rest call to an api server.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 12 December 2023 12:59 PM UTC
Ok, but ... if you know the source code that your customer has, you could pre-generate maybe a text file that associates all of the dw names with a SQL (a sql that you'd have to pre-generate on you own system having a connection). You'd just have to parse this text file to get the corresponding SQL.

Sorry if I'm not understanding the full picture.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 11 December 2023 14:29 PM UTC
  2. SnapDevelop
  3. # 2

Hi Phillipos;

  This utility might help as it deals with PBSelect DBMS interactions....

https://chrispollach.blogspot.com/2017/11/sqlx.html

Regards ... Chris 

Comment
Philippos Antonopoulos
  1. Philippos Antonopoulos
  2. Tuesday, 12 December 2023 12:17 PM UTC
Hello Chris,

I had stumbled on to your program while I was doing research and from what I can see it still needs a database connection to do the conversion.

Have I understood correctly or no?

Thank you,

Philippos
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 12 December 2023 16:26 PM UTC
Correct as the DWO's "PBSelect" is *not* converted into a real SQL command unless an Active DBMS session is present. That is because the SQL generated at that instance time from a PBSelect becomes the "exact" SQL syntax of the current DBMS. That is why you should always use "Graphic" mode SQL in any DWO that will be used across different DBMS vendors at runtime. ;-)
  1. Helpful
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 8 December 2023 14:29 PM UTC
  2. SnapDevelop
  3. # 3

Hi Philippos.

You can always get an sql statement using getsqlselect method. All you have to do is to:

  • Create a datastore
  • Assign your dataobject
  • Connect to a database using settransobject

After that getsqlselect should give you back the sql select statement instead of pbselect syntax. Take a look at: GetSQLSelect - - DataWindow Reference (appeon.com). Give attention to the "Usage" part:

"If a database is connected and SetTransObject has been called for the DataWindow, then GetSQLSelect returns the SQL SELECT statement. Otherwise, GetSQLSelect returns the PBSELECT statement."

Andreas.

Comment
Philippos Antonopoulos
  1. Philippos Antonopoulos
  2. Monday, 11 December 2023 12:30 PM UTC
Hello Andreas,

Thank you for the response.

I might not have specified well in my question, but my issue is that the powerbuilder client app does not have any direct connection to the database, so GETSQLSelect would not work. Do you have any knowledge of how to do work around that?

Thank you,

Philippos
  1. Helpful
René Ullrich
  1. René Ullrich
  2. Monday, 11 December 2023 13:26 PM UTC
PB needs the database connection because the PBSelect is database independent. The translation to SQL is database dependend.
  1. Helpful 1
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.