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

Resolved Increase in column size of datawindow

  1. Issue
0
Votes
Undo
  1. Sivaprakash BKR
    Sivaprakash BKR
  2. PowerBuilder
  3. Sunday, 15 December 2019 02:30 AM UTC

Hello,

Using PB 2017 R3, Postgresql 11.6

By using the query

Select a.itemcode as code, a.value as value, a.itemname as name, a.version as version
From  tablex a

Union All

Select '' as codem, 0 as value, b.name as name, '001' as version
From  tabley b

a.itemcode - Column type varchar(10)
a.value - column type numeric(17,2)
a.itemname & b.name - Column type varchar(40)
a.version - Column type varchar(3)

By using the above query, the column sizes in datawindow changes to [ viewed through View - Column specifications ]
itemcode - char(255)
value - decimal(6)
name - Char(40)
version - char(255)

For columns itemcode, value & version the length & decimal precision increases.  We need to correct it using source editor, every time we make changes in the query of these datawindows.  Column name's length doesn't change, as we select a column from db, in both queries.  

This issue is only when we use empty or null or 0 or 0.00 values instead of db columns in the query.  

We didn't face this issue in Sql server & PB 11.5 combo.

Why this change happens ? Is there any way to correct this ?  

Happiness Always
BKR Sivaprakash

 

  1. PostgreSQL
  2. PowerBuilder (Appeon)
2 Separate PB apps talking to each other
Do I have to replace my SOAP Connection webservice...
Accepted Answer
Markus Eckert
Markus Eckert Accepted Answer Pending Moderation
  1. Monday, 16 December 2019 10:58 AM UTC
  2. PowerBuilder
  3. # Permalink

Try forcing the datatypes of the second part of the union, instead of just giving constants and relying on the database to determine the right datatype of the columns.

Select CAST('' as varchar(10)) as codem, 0 as value, b.name as name, cast('001' as varchar(3)) as version

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 16 December 2019 12:40 PM UTC
Thanks Markus Eckert,

I'll try this cast and let u know.
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 16 December 2019 14:26 PM UTC
Cast works perfectly. Didn't thought of it, as I haven't faced this issue when we were using Sql Server. I thought, it might be an issue with Postgres setup.. like that.

All data types needs casting.. DONE.

Thanks Markus Eckert.

  1. Helpful
There are no comments made yet.
Responses (4)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 16 December 2019 12:28 PM UTC
  2. PowerBuilder
  3. # 1

Hi,
Just like Markus I often use CAST of either constant value or NULL to specific datatype.

// No control over datatypes
SELECT NULL          as "ArtworkID"
     , 'Jim Sanborn' as "Artist"
     , '03-Nov-1990' as "UnveiledDate"
     , '38 57 6.5 N' as "Latitude"
     , '77 8 44 W'   as "Longitude"
     , NULL          as "Content"

// Explicit control of every datatype
SELECT cast(NULL          as nvarchar(255)) as "ArtworkID"
     , cast('Jim Sanborn' as nvarchar(255)) as "Artist"
     , cast('03-Nov-1990' as date)          as "UnveiledDate"
     , cast('38 57 6.5 N' as varchar(40))   as "Latitude"
     , cast('77 8 44 W'   as varchar(40))   as "Longitude"
     , cast(NULL          as nvarchar(max)) as "Content"

Your SQL dialect may differ but same principle applies.

HTH /Michael

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 16 December 2019 14:27 PM UTC
Yes Michael, DONE with cast command. Works perfectly. Thanks,
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Tuesday, 17 December 2019 12:02 PM UTC
Hey Siva, I use CAST for every column where I do calculation to ensure DataWindow knows the "precision" needed.

Could be an integer value divided by 100.0 but expressed as monetary value with exactly 4 decimals.

Could be concatenation of of first, middle, and last name into full name of max 80 characters.

Could be datetime value where only the date part is used but database uses datetime because column created before database supported Date.
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Thursday, 19 December 2019 12:13 PM UTC
Yes, which I missed to carry out for a long time. In SQL Server we didn't face this issue. We learn daily a new thing.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Sunday, 15 December 2019 19:55 PM UTC
  2. PowerBuilder
  3. # 2

instead of doing

 

Select '' as codem,

try doing : 

Select RPAD('', 10, ' ') as code,

 

See if that makes a difference.

What I don't understand is that you'd get char(255) for "version".

Maybe try with :

... LPAD('1', 3, '0') as version

 

 NOTE: you've written "codeM" and "code" for the first column. I don't know if it confuses powerbuilder to use 2 different names for the same column of the UNION?

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 16 December 2019 12:40 PM UTC
Thanks Miguel Leeuwe,

CodeM is a typo error. I'll try both LPAD, Cast and let u know.

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 16 December 2019 15:08 PM UTC
Well, you've already tried the "cast", so if that works this one will probably work. "Cast" seems a cleaner solution to me anyway, so I'd use that solution.

regards
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Tuesday, 17 December 2019 00:43 AM UTC
Yes Miguel Leeuwe, cast works and it looks cleaner, known one to all here.
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 15 December 2019 03:17 AM UTC
  2. PowerBuilder
  3. # 3

Different DB clients and DB servers deliver different metadata when PowerBuilder IDE or your app investigates a SELECT statement.

You can simulate what the IDE does this way:

  1. Put your DataWindow SELECT statement including retrieval arguments into a string variable.
  2. Investigate that string using DYNAMIC SQL Format 4
    1. PREPARE USING SQLSA
    2. DESCRIBE USING SQLDA
  3. Look at all the "description" metadata available via SQLDA

That metadata is what the DataWindow engine of PowerBuilder uses to build its DataWindow buffer.

 

You will see that your PostgreSQL delivers different metadata than SQL Server.

HTH /Michael

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