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