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

Resolved Issue in Dynamic creation of DW

  1. Issue
0
Votes
Undo
  1. Sivaprakash BKR
    Sivaprakash BKR
  2. PowerBuilder
  3. Friday, 30 April 2021 06:01 AM UTC

Hello,

Using PB 2017R3, PostgreSQL 11

With the following query, we create a datawindow at runtime and issue few modify statements to format the datawindow.  

Since the query is from multi-tables, we expect the table name(s) to get prefixed with the column name.  eg b.accountnamesubbranchpk will be gl_account_name_sub_branch_accountnamesubbranchpk ...

THIS IS NOT THE CASE for few databases...    We tested with 5 databases, all with the same structure, created at different point of time [ all PostgreSQL only].    When we run the below query, in few database we get the field name prefixed with table name and NOT for few database(s).  In those database the following modify command fails.

Can anyone shed some light on why and on what conditions the 'table prefix ' won't be there ?

Happiness Always
BKR Sivaprakash

// Query to Pass
ls_sql = " Select '' as hand, a.accountnamesubbranchpk, b.accountnumber, b.accountsubname, a.billbranchname "
ls_sql += " From gl_account_name_sub_branch a "
ls_sql += " Join gl_account_name_sub b "
ls_sql += " On b.accountnamesubpk = a.accountnamesubfk "
ls_sql += " Where b.accountsubtype in ('S', 'U', 'P') "
ls_sql += " Order by b.accountsubname, a.billbranchname "

// Column to hide
ls_hidden_colname[1] = 'gl_account_name_sub_branch_accountnamesubbranchpk'
ls_hidden_colname[2] = 'selection'

// Pass parameters
tab_1.tabpage_1.uo_account.of_set_dwsql(ls_sql, ls_hidden_colname)

// Retrieve data
tab_1.tabpage_1.uo_account.of_set_postretrieve_process('') // Should be set before retrieve
tab_1.tabpage_1.uo_account.of_retrieve(1)

// Column heading and width settings
tab_1.tabpage_1.uo_account.of_set_dwmodify('gl_account_name_sub_branch_accountnamesubbranchpk.Width = 0')
tab_1.tabpage_1.uo_account.of_set_dwmodify("gl_account_name_sub_accountnumber_t.Text = '" + 'Sup. No.' + "'")
tab_1.tabpage_1.uo_account.of_set_dwmodify("gl_account_name_sub_accountsubname_t.Text = '" + 'Supplier Name' + "'")
tab_1.tabpage_1.uo_account.of_set_dwmodify("gl_account_name_sub_branch_billbranchname_t.Text = '" + 'Branch' + "'")

tab_1.tabpage_1.uo_account.of_set_dwmodify("hand_t.Height = '" + '200' + "'")
tab_1.tabpage_1.uo_account.of_set_dwmodify("gl_account_name_sub_accountnumber_t.Height = '" + '200' + "'")
tab_1.tabpage_1.uo_account.of_set_dwmodify("gl_account_name_sub_branch_billbranchname_t.Height = '" + '200' + "'")
tab_1.tabpage_1.uo_account.of_set_dwmodify("gl_account_name_sub_accountsubname_t.Height = '" + '200' + "'")
tab_1.tabpage_1.uo_account.of_set_dwmodify("datawindow.header.Height = '" + '230' + "'")

tab_1.tabpage_1.uo_account.of_set_dwmodify('gl_account_name_sub_accountnumber.Width = 800')
tab_1.tabpage_1.uo_account.of_set_dwmodify('gl_account_name_sub_accountsubname.Width = 3000')
tab_1.tabpage_1.uo_account.of_set_dwmodify('gl_account_name_sub_branch_billbranchname.Width = 1750')

 

  1. DataWindow
  2. PowerBuilder (Appeon)
JSONParser and Nulls
PowerClient IIS Server housekeeping
Accepted Answer
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 6 May 2021 05:04 AM UTC
  2. PowerBuilder
  3. # Permalink

By Dan Cooperstock

If you don't need the Datastore to be updatable, you could use the trick of giving your own column names by putting the actual column names in parentheses, so that PB sees them as an expression, and doesn't assume what table they are in. So your SQL shown above would start like:

Select '' as hand, (a.accountnamesubbranchpk) as accountnamesubbranchpk, (b.accountnumber) as accountnumber, ...

 

***************

Thanks Dan Cooperstock.   

Comment
There are no comments made yet.
Responses (3)
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 30 April 2021 14:52 PM UTC
  2. PowerBuilder
  3. # 1

SyntaxFromSQL has never been enhanced.  It does not work the same as creating the datawindow in the painter.  fwiw, i have some of the issues documented in a bug report:

https://www.appeon.com/standardsupport/search/view?id=3860

 

I suggest you submit something similar 

 

Comment
There are no comments made yet.
Benjamin Gaesslein
Benjamin Gaesslein Accepted Answer Pending Moderation
  1. Friday, 30 April 2021 08:12 AM UTC
  2. PowerBuilder
  3. # 2

As a workaround, you could use the column number instead, this should work:

"#2.width=0"

Of course this doesn't really help with the header text.

Comment
Load more comments
Daniel Vivier
  1. Daniel Vivier
  2. Sunday, 2 May 2021 21:48 PM UTC
If you don't need the Datastore to be updatable, you could use the trick of giving your own column names by putting the actual column names in parentheses, so that PB sees them as an expression, and doesn't assume what table they are in. So your SQL shown above would start like:

Select '' as hand, (a.accountnamesubbranchpk) as accountnamesubbranchpk, (b.accountnumber) as accountnumber, ...
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 3 May 2021 05:55 AM UTC
Matt Balent,

That we checked by copying the database from client's computer to our computer, that doesn't work in our computer. We copied only the database structure and data and not any of the (servers') parameters.

If parameters were the issue, mike would not have created a bug report.
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Thursday, 6 May 2021 05:03 AM UTC
Dan Cooperstock,,

Yes, putting parentheses around the actual column names did the trick. We tried it in a couple of queries and it's working. Thanks Dan Cooperstock.

  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.