1. Dan Cooperstock
  2. PowerBuilder
  3. Wednesday, 05 June 2019

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
0
Votes
Undo

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
thx, my outcome is a DW base class with yet another feature. Happy to chime in. /Michael
  1. Michael Kramer
  2. Friday, 7 June 2019
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. Dan Cooperstock
  2. Friday, 7 June 2019
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. Michael Kramer
  2. Friday, 7 June 2019
There are no comments made yet.
  1. Friday, 7 June 2019
  2. PowerBuilder
  3. # 1
Michael Kramer Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Dan Cooperstock
  2. Thursday, 6 June 2019
There are no comments made yet.
  1. Thursday, 6 June 2019
  2. PowerBuilder
  3. # 2
Roland Smith Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Dan Cooperstock
  2. Thursday, 6 June 2019
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. Michael Kramer
  2. Thursday, 6 June 2019
There are no comments made yet.
  1. Thursday, 6 June 2019
  2. PowerBuilder
  3. # 3
Chris Pollach Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Thursday, 6 June 2019
  2. PowerBuilder
  3. # 4
Miguel Leeuwe Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Miguel Leeuwe
  2. Thursday, 6 June 2019
There are no comments made yet.
  1. Thursday, 6 June 2019
  2. PowerBuilder
  3. # 5
René Ullrich Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Thursday, 6 June 2019
  2. PowerBuilder
  3. # 6
Miguel Leeuwe Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Dan Cooperstock
  2. Thursday, 6 June 2019
There are no comments made yet.
  1. Thursday, 6 June 2019
  2. PowerBuilder
  3. # 7
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
User Center Memory PBDOM Window Algorithm Proxy AutoScript Chrome VM File Access OAuth AEM Response MySQL OLEDB Event Database Painter Automated Testing DATETIME Entity Framework 32-bit Validation Offline IPA Expression Uninstall Collection UNIX OS Port Mobile Memory Leak COM Zoom POST Type DataStore .EXE SQL Event Handler Azure Design Compression App Store (Apple) JSON Visual Studio Code IIS Enhancement Request URL Exception Speech Recognition Font Patch Bug HTML/5 Computed Field Data SOAP ERP JSONGenerator Retrieve Other Syntax Database Provider ODBC Namespace Filter iPhone JBoss PB Classic TIME SQLite Automation Authentication WinForms AutoCompiler MAPI Error Workspace Dockable OpenSSL ASE Controls PBJVM PUSH Button Service Cordova Deployment TLS/SSL PBX PBD Roadmap Popup Upload Logging 64-bit Outlook Localization Kestrel IWA Firefox PowerServer LONGLONG Tablet Debugging HTTP/S RPC Storage Embedded SQL macOS InfoMaker Entity DataStore (C#) Source Code Calendar ADO.NET Camera Editor User Object PowerBuilder (Appeon) NativePDF API Regression Graph Multi Threading HTTPClient Format Cookies Library Elevate Conference PayPal Single Sign-on PBVM Sort Trial Upgrade Internet Explorer Encoding Function Debug Amazon AWS Web Extensions JOIN WCF .NET Assembly Query C# Model generator DataWindow Runtime Packager Cursor Print Garbage Collection Export PFC Inheritance C# OpenAPI Integer SMTP Client Crosstab WebForms Client/Server PBNI Log Out DataStore (PS) VARCHAR UltraLite Excel Output iOS Appeon Workspace Cross-Platform TRY-CATCH Certificate Swagger Size DB2 SaaS UI JDBC MIME TYPE Encryption JSONParser Picture Barcode PowerBuilder PowerScript IDE Parameter JavaScript Pipeline .NET Core Framework Event Handling Voice DATE Compiler Wizard Renew Branch & Merge Database MDI Interface MessageBox GeoLocation Firewall WinAPI Subscription Download Identity Class PBC TortoiseSVN Online C# Editor Email Git Trigger J# Messagging PBORCA TX Control Maps OLE PowerServer Toolkit PowerBuilder (SAP) OrcaScript PBG Visual Studio Transaction Appeon Xcelerator Plug-in Merge SqlBuilder Unit Testing Java Edge TFS .NET DataStore PB.NET n-Tier Properties SQL Server Oracle WebSphere SDI Android Stored Procedure SVN Permission Crash Warning PB Native Migration ModelStore Debugger Cluster Text ODATA Documentation Menu Update Security WildFly Timer Server F# Plugin Header APK Configuration Model Publish Foreground Open Source DLL Architecture SnapDevelop (PB Edition) Session DOUBLE IntelliSense Load Balancing Office Apache DO-WHILE PULL PostgreSQL DataSource LONG Build Method SQL Anywhere Command Line Windows OS XHTML App Store Browser REST PBL Log In NVO MVC IMAPI Icon Database Driver PowerServer Web Large File Play Store (Google) BigData ModelStore (C#) IDE Transparency Unit Test CSV UWP External Functions WebLogic .NET Std Framework Base64 WPF Target DragDrop TE Control Internet TortoiseGit DataObject Versioning Linux OS Emulator TabPage LINQ Database Profile Tomcat Package PowerScript (PS) Field ULONG SDK XCODE iPad License Lambda Installation Release Label Hash XML SqlModelMapper SqlExecutor Performance Workgroup Resize Testing BLOB Script Localized PBVM Cloud CSS Edit Style File Citrix PowerServer Mobile DataType Import FOR-NEXT LDAP PDF Batch Variable Array
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. Learn More