1. Kalpana Snehi
  2. PowerBuilder
  3. Friday, 13 December 2019 11:02 AM UTC

One of the column of my datawindow is a number and the value coming from SQL query column is a BIT with 0 and 1 value. For all 1 values its displayed -1 on datawindow. Have anyone faced same issue? Kindly share the solution for this issue?

Accepted Answer
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 20:21 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Kalpana ;

   Please let me know if this works for you ...   

   If the value of the StaticBind DBParm is 1 (default), the SS SNC interface does not describe the columns and thus, does not do any data conversion. As a result, a bit column with the value 1 appears as -1 in a DW. To work around this issue, set the StaticBind in the SQLCA's DBParm field to 0. This will enable the SNC DB interface to convert the data type correctly.

Example:  SQLCA.DBParm="StaticBind=0"

HTH

Regards  ... Chris

Comment
  1. Kalpana Snehi
  2. Saturday, 14 December 2019 07:07 AM UTC
Thanks Chris! This solution worked for me.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Saturday, 14 December 2019 17:05 PM UTC
Hi Kalpana ... That is excellent news! Thanks for the feedback. :-)
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 20:05 PM UTC
  2. PowerBuilder
  3. # 1

Hi, Kalpana -

What database vendor & version? What PB database interface (SQLCA.DBMS property)?

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 20:20 PM UTC
  2. PowerBuilder
  3. # 2

I tried the following SELECT statement in SQL Server 2017 as the source for a DataWindow in PB 2017 R2:

SELECT cast(1 as bit) as BinaryOne,
       cast(0 as bit) as BinaryZero,
       23 as PrimeNumber
  FROM (SELECT a=1) temp

and sure enough, the value -1 is shown in the BinaryOne column.

However, by altering the SELECT statement slightly to cast the bit columns to tinyint's:

SELECT cast(cast(1 as bit) as tinyint) as BinaryOne,
       cast(cast(0 as bit) as tinyint) as BinaryZero,
       23 as PrimeNumber
  FROM (SELECT a=1) temp

The value 1 appears in the BinaryOne column. I would venture a guess that a leading (even if it's the only) 1-bit in a number column is interpreted as a negative value.

HTH,
John

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.