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

Resolved Bit datatype and MSOLEDBSQL driver

  1. Advice
0
Votes
Undo
  1. Pam Koch
    Pam Koch
  2. PowerBuilder
  3. Tuesday, 1 June 2021 19:59 PM UTC

We are upgrading to R3 and are having issues with the SQL server bit data type and the MSOLEDBSQL driver.  These issues were not occurring with the SNC driver.

For example, we have a column in a datawindow declared as 'number', with an Edit style of checkbox and Data Value for On as -1.   That column corresponds to a column defined as a bit datatype in SQL server.  Using the SNC driver, the column is correctly checked when the SQL server value is 1, and correctly unchecked when the data is 0.

However, after switching to the MSOLEDBSQL driver, the column is unchecked when the SQL server value is 1.  And after changing the datawindow to a 'long' (there is not an integer option), the column is checked.

What is the correct way to handle the bit type with the MSOLEDBSQL driver?

Thanks in advance.

How to update Git from local PBL
EXAMPLE OF RESTCLIENT 2019 FOR POST JSON
Responses (3)
Kim Berghall
Kim Berghall Accepted Answer Pending Moderation
  1. Tuesday, 1 June 2021 22:47 PM UTC
  2. PowerBuilder
  3. # 1

We see the same issue with SNC as well on 2019 R3. We have made it a habit of always casting a bit as tinyint like this:

 ColumnNameInd = cast(t.ColumnNameInd as tinyint),

 t.OtherColumnName,

etc...

That way the column in the dw becomes a long and works with all DB drivers.

Comment
Pam Koch
  1. Pam Koch
  2. Wednesday, 2 June 2021 13:29 PM UTC
Thanks for your response. We have cast bits as tinyint in many, but not all, cases. I think it is a habit we should take as well.
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 2 June 2021 05:22 AM UTC
  2. PowerBuilder
  3. # 2

Hi Pam,

 

The problem you described, strictly speaking, is a defect of SNC Driver, which MSOLEDBSQL Driver doesn’t have.

 

For a bit type column in SQL Server, when its value in the database is 1, the DW retrieve data's values with OLE DB, ODBC, ADO.Net, MSOLEDBSQL Driver are also 1, however, with SNC driver, the DW retrieve data's value is -1.

 

So in SNC Driver, the checkbox of DW should be set as Data value for on (-1), Data value for off (0).

And if using OLE DB, ODBC, ADO.Net, MSOLEDBSQL Driver, the checkbox of DW should be set as Data value for on (1), Data value for off (0), which makes a bit more sense.

 

Therefore, I suggest that when you are using MSOLEDBSQL, change the checkbox Data value for on to 1.

 

Regards,

Ken

Comment
Pam Koch
  1. Pam Koch
  2. Wednesday, 2 June 2021 13:31 PM UTC
Thank you. That is the answer I was expecting, and will unfortunately make our upgrade a little more challenging.
  1. Helpful
There are no comments made yet.
Pam Koch
Pam Koch Accepted Answer Pending Moderation
  1. Wednesday, 2 June 2021 13:55 PM UTC
  2. PowerBuilder
  3. # 3

I have a similar question - what about the reverse, passing a value from Powerbuilder to a stored procedure with a bit type column.   It seems as if passing a 1 or 0, regardless of the Powerbuilder defined data type, works fine.  So Powerbuilder variables defined as integer, long, and even boolean are updated correctly in SQL server.

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.