1. Daniel Vivier
  2. PowerBuilder
  3. Wednesday, 5 June 2019 17:50 PM UTC

We are building our own simple filter window, adapting from the PFC version but changing it to our own desired specs.

We want to show a drop-down list of column names that can be filtered on, and have that be exactly the data column names (the names coming from the SQL).

We were originally using the list from dw.Object.Datawindow.Objects, and using the restrictions available in the PFC code for that window to restrict it to what it considered to be "columns". But that had various problems, including on a DW where there were two displayed fields showing the data from the same SQL column - both of them showed up on the list.

So we changed to getting the column names by first getting dw.Object.Datawindow.Column.Count, then getting the names via dw.Describe("#1.name") etc.

That is basically giving what we want, except for one thing really weird in that same DW mentioned above with two display fields showing data from the same SQL column. Those fields are named "amount" and "amount_1" (with the data column being named "amount") and what is showing up from the Describe call is just one name, "amount_1". 

Why would that be, and is there anything we can do about that? I really thought that sort of Describe call would give the real data column names, not display field names.

Thanks.

 

 

Michael Kramer Accepted Answer Pending Moderation
  1. Friday, 7 June 2019 13:23 PM UTC
  2. PowerBuilder
  3. # 1

Hi Dan,

This is my function - note the WARNING on how code can be tricked.
I have never seen such syntax in real life - I would doubt that it exists at all.

// Collect and return list of current buffer column names.
// Code extracts them from DataWindow syntax.
// -----------------------------------------------------------------------------------------------
// SYNTAX INTERPRETATION
// Every column definition starts on a line matching either
//
// 1) "table(column=(type=... updatewhereclause=... name= dbname..."
// - or -
// 2) "column=(type=... updatewhereclause=... name= dbname..."
//
// PROPERTY SEQUENCE
// Some properties are optional, others exist for every buffer column.
// Sequence of column properties is: (*:Mandatory)
// type* update updatewhereclause* key name* dbname* initial values validation validationmsg=
//
// Column name therefore text between " name=" and " dbname=".
// All properties preceding name in column definition are static. Hence, no end-of-line markers.
// -----------------------------------------------------------------------------------------------
// WARNING
// It is possible but hardly expected to find computed property expressions containing lines like
// (1) and (2). This code identifies such lines as columns. Complete tokenizer could fix this.
// -----------------------------------------------------------------------------------------------
constant string columnPattern$ = 'column=(type=.+ updatewhereclause=.+ name=.+ dbname=.+'

long lineStart=1, lineStop, nextLine, syntaxLength, colNo, colCount, nameStart, nameStop
string lineText, dwSyntax, empty[]

TRY
as_colName = empty

dwSyntax = this.object.datawindow.syntax
if IsNull(dwSyntax) then RETURN // No syntax => DONE

syntaxLength = Len(dwSyntax)
if syntaxLength = 0 then RETURN // No synaxt => DONE

colCount = long(this.object.datawindow.column.count)
if colCount = 0 then RETURN // No columns => DONE
CATCH (RuntimeError lex_error)
RETURN // DW object missing => DONE
END TRY

// Pre-allocate dynamic array
as_colName[colCount] = ''

// Search through syntax line-by-line
do while lineStart <= syntaxLength

// Extract next line
nextLine = Pos(dwSyntax, '~r~n', (lineStart + 1))
if nextLine = 0 then
lineStop = syntaxLength + 1
else
lineStop = nextLine
end if
lineText = Mid(dwSyntax, lineStart, (lineStop - lineStart))

// Check whether line contains column start
choose case true
case Match(lineText, '^table(' + columnPattern$), &
Match(lineText, '^' + columnPattern$)

// Extract name from line
colNo ++
nameStart = Pos(lineText, " name=") + 6
nameStop = Pos(lineText, ' dbname=', nameStart)
as_colName[colNo] = Mid(lineText, nameStart, (nameStop - nameStart))
end choose

// Move forward to next line
lineStart = nextLine + 2
loop
// DONE

 

Comment
  1. Michael Kramer
  2. Friday, 7 June 2019 14:32 PM UTC
thx, my outcome is a DW base class with yet another feature. Happy to chime in. /Michael
  1. Helpful
  1. Daniel Vivier
  2. Friday, 7 June 2019 19:32 PM UTC
Actually we thought about it some more and decided that your way was best, Michael, because it doesn't have the potential problems we were encountering when there were two display columns showing the same data column. Here is our version of your code.



You will notice that at the very bottom of your original code, we changed "lineStart = nextLine + 2" to "lineStart = lineStop + 2", which would prevent an infinite loop if for some reason the DW syntax didn't exactly end with a ~r~n. (We believe it does always end that way, mind you, as I expect was your experience!)



We also simplified the testing for a line representing a data column, and added a test for a line representing a display column which then exits the loop, to prevent having to work through the entire syntax.



And finally, we added a sanity check with a messagebox at the end, to make sure the count of columns we found matches Datawindow.Column.Count as it should!



long lineStart=1, lineStop, nextLine, syntaxLength, colNo, colCount, nameStart, nameStop, ColumnsWentThrough

string lineText, dwSyntax, ls_column, empty[]



TRY

as_colName = empty



dwSyntax = lower(idw_requestor.object.datawindow.syntax)

if IsNull(dwSyntax) then RETURN // No syntax => DONE



syntaxLength = Len(dwSyntax)

if syntaxLength = 0 then RETURN // No syntax=> DONE



colCount = long(idw_requestor.object.datawindow.column.count)

if colCount = 0 then RETURN // No columns => DONE

CATCH (RuntimeError lex_error)

RETURN // DW object missing => DONE

END TRY



// Search through syntax line-by-line

do while lineStart 0 then exit



// Move forward to next line

lineStart = lineStop + 2

loop



ColumnsWentThrough = colNo

if ColumnsWentThrough colCount then

MessageBox("Internal Error!", "There had been extra column(s) found when building the list. Total of " + string( ColumnsWentThrough) + &

" columns were processed while there are " +string(colCount) + " columns in the datawindow." +&

"Please report this to support for assistance.")

End if

  1. Helpful
  1. Michael Kramer
  2. Friday, 7 June 2019 21:44 PM UTC
Hi Dan, Good ideas. Godspeed.



I noted following potential improvements while testing my code. However, tuning can complicate code reading. Rather have code example that developers can read for inspiration.

I'm happy to share what I learned for fellow developers to benefit.



Tuning options:

1) Avoid copy each line into lineText

Earlier version worked only inside dwSyntax.

Definitely harder to read.

I rewrote to debug easily and fix issue when .values included "~r~n".



2) Start with lineStart = Pos(dwSyntax, "~r~ntable(column=(") + 8 to by-pass all prior syntax

This makes lineStart for first line start at "column=(…" just like all the other column lines

Earlier version also included this tweak.



3) Stop search when the "column=(" section has completed.

Earlier version stopped at line prefix = "retrieve".

That failed for stored procedure DW because such DW ends column section with prefix = "procedure".

So in published version I simply keep parsing the syntax until the very end.



Debugging tip: I copy dwSyntax's text into VS Code to follow along while debugging in PB IDE to know exactly what text my code is parsing.

Reason: In Edit Source column #2 onwards include space character as initial character.

dwSyntax at runtime does not!

HTH /Michael
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 16:36 PM UTC
  2. PowerBuilder
  3. # 2

Question: Can you use Describe("#n.dbName") ?

.dbName contains

. -- or -- SQL alias if defined like that. -- AND -- .dbName holds mixed case name when database is case sensitive.

EVERY PROPERTY defined on a DW buffer column can be Described -- EXCEPT for "name". Sorry.

Hoping for miracles I also tried "#n.column.name" and "#n.colname". No luck.

HTH /Michael

 

 

Comment
  1. Daniel Vivier
  2. Thursday, 6 June 2019 17:02 PM UTC
I don't think the dbName is a good choice for our purposes, which is showing users a list of columns for filtering, because those aren't filterable names, as far as I know. And we want to be able to adjust the names the users will see for filtering to make good sense, which our DB table and column names don't necessarily - not from and end user perspective.
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 13:58 PM UTC
  2. PowerBuilder
  3. # 3

How about getting the DataWindow Syntax and parsing the table section?

Column 1 starts at 'table(column=' and each subsequent column at '~r~n column='.

 

Comment
  1. Daniel Vivier
  2. Thursday, 6 June 2019 15:39 PM UTC
That would definitely work, but be a bit tricky to code. Now that we know what is happening and how to work around issues I'm satisfied with just using Describe("#n.name"). It would be nice if there was a retrievable property just showing the name value (not the dbname, which IS available) from the table section!
  1. Helpful
  1. Michael Kramer
  2. Thursday, 6 June 2019 17:13 PM UTC
Hi Dan, I actually wrote such piece of code. fairly complex because validation, validationmsg, and values in the column definition may contain the sequence ~r~n, so just the task of finding start and end of each column definition turned out to be hairy code. I will try to clean up my code and then post it. Maybe not until this weekend. My code is a function = of_GetColumnNames( REF string as_colName[ ]) wrapping the logic into a public function on my DataWindow base class.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 13:28 PM UTC
  2. PowerBuilder
  3. # 4

Hi Miguel;

  An interesting DW quirk! The problem all starts with the statement ...
ls_objects = dw_1.Object.DataWindow.Objects

  I think that your assumption is correct ... the DW Painter would normally keep the column names in the correct order. In fact, if I edit the "weird" DWO in the DW Painter in normal graphic's mode for any aspect - the DW Painter resets the column order upon a save. Then the weird behaviour goes away.

  The workaround for now is to not edit DWO's like this in syntax mode. Always let the DW Painter perform the changes.

Regards ... Chris

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 06:22 AM UTC
  2. PowerBuilder
  3. # 5

I've made a small sample application to illustrate the problem and the solution. See attached zip file.

This one has been made with 2017 R3

Attachments (1)
Comment
  1. Miguel Leeuwe
  2. Thursday, 6 June 2019 06:37 AM UTC
To illustrate your problem, I edited the dw syntax manually and pasted the line with ..id=2 from the amount_1 column above the amount column. (d_amount-weird-behaviour)

It starts to repeat the amount column in the exported list of objects in ls_objects variable. I'll report this as a bug if I get some time later on. Happens on both 2017 and 2019.
  1. Helpful
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 06:08 AM UTC
  2. PowerBuilder
  3. # 6

Hi,

many many years ago the feature of having more than one column objects for one column was added. But it seems that Describe have not adapted to this.

The column name is the name of the column that you have added or renamed at least. (The name is in the synatx in the table() section so the order of the column object doesn't matter.)

As Miguel already wrote column.count counts the number of columns from the table() section (the defined columns by SQL).

Describe("Datawindow.Objects") should return all objects. If you have more than one column object for a column you should find both in it.

If you try to get information about a columns object by ID you will always get the information from first object in the dw syntax. You have to access the different columns objects by name.

HTH,

René

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 05:03 AM UTC
  2. PowerBuilder
  3. # 7

Hi,

My theory (no time to test now, but let me now if you need help):

- I think, the column.count will probably give you only the amount of columns in the Select of the dw, but could be wrong and maybe it's simply a bug.

- If I'm right then if a same column has been placed more than once, then there'll be one missing in the column.count, so whatever loop you construct around the obtained columns from the describe will miss out on one.

"amount" and "amount_1" will have the same "id=" in your exported dw code. So I guess it simply finds the first description of the first column it finds when dealing with the amount field, in your case "amount_1". Try editing the source code and move the "column ....(id=..) of the amount before the amount_1 and see if you still get "amount_1"?

Anyway a simple fix (if it's not an n-up dw), would be to NOT place the same column twice on your datawindow. Instead just add it as an extra column to you select statement:

select amount as amount, amount as amount_1 from myTable

Of course that might be too much work if you have lots of datawindows.

 

I'm not sure about the describe syntax to get all columns from the list where they have "...id=..." but I'm convinced that should be possible, in worst case scenario, you could export the whole datawindow and then search for all the "...id=.." to get all the columns.

HIH

regards,

 

 

Comment
  1. Daniel Vivier
  2. Thursday, 6 June 2019 10:58 AM UTC
This seems to be basically the correct analysis based on the testing I just did after reading all of these comments. The name you get from Describe("#n.name") is the name of the first column() display object in the source view with the ID of the object in the table() section. Manually re-arranging those column() display objects allows me to get the desired one picked in the list I'm building for our filter window.

Another note is that if there is no column() display object with the given ID value, the name from the column in the table() section is used for the value of Describe("#n.name").

We do NOT want all of the names of the display columns in the filter list, only the names of the data columns, so adding a 2nd instance of the column in the original SQL would be the wrong solution for us.
  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.
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.