How to get list of columns from a query?

Resolved How to get list of columns from a query?

Issue

Votes

Undo

Berka Frenfert
PowerBuilder
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?

Responses (3)

Sivaprakash BKR Accepted Answer Pending Moderation

Saturday, 19 January 2019 11:29 AM UTC
PowerBuilder
# 1

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

Helpful 0

There are no comments made yet.

Miguel Leeuwe Accepted Answer Pending Moderation

Friday, 18 January 2019 02:53 AM UTC
PowerBuilder
# 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?

/*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
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.

Helpful 0

There are no comments made yet.

Olan Knight Accepted Answer Pending Moderation

Monday, 14 January 2019 16:47 PM UTC
PowerBuilder
# 3

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

Helpful 0

Chris Pollach @Appeon
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

Helpful 0

Berka Frenfert
Wednesday, 29 May 2019 08:07 AM UTC

Thank you Chirs. That is good point.

Helpful 0

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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

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.