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

Resolved How to get list of columns from a query?

  1. Issue
0
Votes
Undo
  1. Berka Frenfert
    Berka Frenfert
  2. PowerBuilder
  3. Monday, 14 January 2019 11:02 AM UTC

A PowerBuilder datawindow has some parameters and those parameters are obviously matched with columns in query of the datawindow. The query is too complex. In my script i get the query syntax in a string variable. Also i get parameters list defined in the datawindow. Now i want to get the list of columns from the query syntax where columns are being compared with retrieval arguments. I do not need those columns where column is not compared with retrieval argument.

for example query string has in its text "product_price >= :prod_price AND SupplierID is null AND store_id = :storeID"

I want to get all the columns names excluding supplierID because supplierID is not compared with any retrieval argument. The Query is too complex with many case statements and operators.

Is there any easy way to get columns list from the query string without involving complex string parsing script?

  1. DataWindow
  2. Query
SQLCA.Database v/s SQLCA.DBParm Database
of_appeondownload fails with error "File is half-b...
Responses (3)
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 14 January 2019 16:47 PM UTC
  2. PowerBuilder
  3. # 1

Theeasist way to get this in the IDE is to EDIT SOURCE on the DW.

Scroll down until you get to the "RETRIEVE" statement.

Copy that block into the text editor of your choice and look for what you want.

 

If you need to do this in RUNTIME, the you need to capture the SQL in the SQLPRIEVIEW event. Once you have the SQL in a variable, then begins the fun process of finding what you need in that string!

 

Good Luck -

Olan

Comment
Berka Frenfert
  1. Berka Frenfert
  2. Wednesday, 16 January 2019 13:51 PM UTC
There is no need to edit source because GetSQLSyntax() function returns the query.

And the fun part of the solution you suggested is in fact the real question. And i know there is no easy way to do that. I am already having fun coding in PowerBuilder. Thanks but no thanks.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 16 January 2019 17:08 PM UTC
Hi Berka;

FWIW: The GetSQLSyntax and SQLPreview event argument are functionally the same. The only benefit of the SQLPreview is that can be more accurate, as its at the point where the real SQL is being generated to be sent over to the DBMS. If your App has performed Modify ( ) commands that may alter the SQL generation, the SQLPreview event will show them. Depending on when you use the GetSQLSelect (old PB feature before the SQLPreview), the SQL may not be as accurate.

Food for thought .... Just my $0.02.

Regards ... Chris
  1. Helpful
Berka Frenfert
  1. Berka Frenfert
  2. Wednesday, 29 May 2019 08:07 AM UTC
Thank you Chirs. That is good point.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 18 January 2019 02:53 AM UTC
  2. PowerBuilder
  3. # 2

Not sure if this would help you:

What if you could make your extraction of the columnames with the parameters easier by introducing them as a comment in the dw's SQL?

Like 

/*parmfields=product_price,storeID*/
select product_price, supplierID, store_id
from table
where product_price >= :prod_price AND
SupplierID is null AND
store_id = :storeID"

 

Of course this would mean you have to manually adapt all of your datawindows to include that comment, but maybe might be of any use to you?

regards

 

Comment
Berka Frenfert
  1. Berka Frenfert
  2. Wednesday, 29 May 2019 08:03 AM UTC
Thank you Miguel but you are right there will be need to add comments in every DataWindow object and there are thousands which makes it impossible for me.
  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Saturday, 19 January 2019 11:29 AM UTC
  2. PowerBuilder
  3. # 3

Won't creating a temporary datastore, using SyntaxFrom SQL, for this purpose solve this issue ?

 
ls_present_str = "style(type=tabular)"
ls_syntax_str  = SQLCA.SyntaxFromSQL(as_query, ls_present_str, as_msg)
If ls_syntax_str = "" Then
 Return -1 
End If
ds_store.Create(ls_syntax_str, as_msg)
ds_store.SetTransObject(Sqlca)
ds_store.Retrieve()

Retrieving this datastore will give you all resultant columns.
 
Happiness Always
BKR Sivaprakash
 
Comment
Berka Frenfert
  1. Berka Frenfert
  2. Wednesday, 29 May 2019 08:00 AM UTC
Thanks for the effort to help. Datastore is no help anyway. If the query is in a string variable then next thing is the extraction of required columns from it. That is all i need to do.
  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.