• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Decimal values incorrectly passed to SQL Server as varchar

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

Resolved Decimal values incorrectly passed to SQL Server as varchar

  1. Issue
0
Votes
Undo
  1. Richard Donagrandi
    Richard Donagrandi
  2. PowerBuilder
  3. Wednesday, 10 April 2024 22:59 PM UTC

Using SQL Server and Powerbuilder 2022 R2 build 2819, I found another bug:

If I have the following:

decimal ldec_value
long ll_id

ll_id = 1

ldec_value = 7.99

UPDATE my_table
     SET my_old_price = CASE WHEN :ldec_value <> my_old_price then :ldec_value else my_old_price END 
     WHERE  my_id = :ll_id;

..... You will get a SQL Server error: Conversion failed when converting the varchar value '7.990                                                                           ' to data type int. 

WORKAROUND:

Hard cast the decimal values to numeric:

 UPDATE my_table
     SET my_old_price = CONVERT(numeric, (CASE WHEN CONVERT(numeric :ldec_value) <> my_old_price then CONVERT(numeric, :ldec_value) else my_old_price END)) 
     WHERE  my_id = :ll_id;

... I shouldn't have to encapsulate the datatypes. Powerbuilder should know that a decimal value is a numeric, not a character string!

  1. Embedded SQL
  2. SQL Server
HttpClient Object
SilentInstall in Docker Container Won't Compile
Responses (2)
Bernhard Giesriegl
Bernhard Giesriegl Accepted Answer Pending Moderation
  1. Wednesday, 8 May 2024 21:11 PM UTC
  2. PowerBuilder
  3. # 1

Hi Richard,

first, and off subject, you could change your SQL statement to:

UPDATE my_table
     SET my_old_price = :ldec_value
     WHERE  my_id = :ll_id AND my_old_price <> :ldec_value;

 

second, have you tried to change

decimal ldec_value

to

double ldec_value

?

 

You can also try

CAST(:ldec_value AS DECIMAL(10,3))

if you need a certain number of decimal places

 

third,

Your SQL Server error: 'Conversion failed when converting the varchar value '7.990' to data type int.`

looks strange from where comes to convert to int? (which can't hold decimal places)

 

 

HTH, regards

Bernhard

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 11 April 2024 15:14 PM UTC
  2. PowerBuilder
  3. # 2

Hi Richard;

  FYI: I just did a quick SP test on my SS 2022 DBMS server using PB 2022 R3 build 3256 (latest MR) and the Decimal data type was handled correctly. If you have a chance, please try the latest PB R3 build. Also, I was using the "MSOLEDBSQL" DB Client v19 that is now what Microsoft is recommending for interfacing with SS2019 & higher releases. Note that PB 2022 R3 supports that newer DB driver via the setting: SQLCA.DBMS = "MSO".    HTH

Regards .. Chris

Comment
Richard Donagrandi
  1. Richard Donagrandi
  2. Thursday, 11 April 2024 18:19 PM UTC
No, this did not solve the issue. Although I did not change my dbms to MSOLEDBSQL -- I am sticking to ODBC. So it could be something with Powerbuilder's ODBC interface. FYI, Microsoft is doing a 360-degree turn and is now fully supporting ODBC, so there's no performance or feature disparity between ODBC and MSOLEDBSQL. For SS 2022, you must use the ODBC version 18 driver, though.
  1. Helpful 1
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 11 April 2024 18:48 PM UTC
Hi Robert.

It could also be the way I have my W10/W11 test environments set-up or I am just not mirroring your exact issue.

If this is easily reproducible at your end, I would suggest that you create a simple PB App test case and then open a Support Ticket for this issue. Them ZIP & attach your test case to that ticket.

Regards .. Chris
  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.