Bug 6094

Issue with decimal retreival arguments bigger than 2^32 22 June, 2022

Andreas Dellen
17 February, 2021
Product: PowerBuilder Category: DataWindow
Version: 2019 R2 Build: 2353
Classification: Publishing: Public
Priority: P2
Status: Verifying Reason:
Communication Status: Waiting for Customer
Mark Lee @Appeon 22 June, 2022
#13
Hi Andreas,

I'm just touching base with you about this ticket.  
Did we provide you with enough information to resolve this issue?. 

Please let us know if there is something else to support you with.  
Remember that you can always open a new ticket if need it. 

Regards,
Mark Lee
Mark Lee @Appeon 07 June, 2022
#12
Hi Andreas,

I would like to check with you if the response provided has been helpful enough to resolve your case. If so, can we proceed to close the ticket?. Otherwise, please let us know if we can provide you with any further assistance, remember that you can always open another ticket any time you need it. 

Regards,
Mark Lee
Mark Lee @Appeon 24 May, 2022
#11
Hi Andreas,

Based on our in-depth analysis, it is believed that this problem is a limit on the length of numeric type of the ODBC 32bit driver.
According to our test verification, if you use the same code and switch to SNC SQL Native Client or MSOLEDBSQL SQL Server, both will work.

In addition, if you want to use MSOLEDBSQL SQL Server Driver, it is recommended that you upgrade PB to the minimum version of PB 2019 R3 which officially supports Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). This driver is also officially recommended by Microsoft to connect to SQL Server databases.

Regards,
Mark Lee
Andreas Dellen 23 May, 2022
#10
Using the 64 bit odbc driver is no option for our productive delivery currently
As far as I see it, using he 64 bit odbc drivers requires all other parts of the delivered software to be 64 bit
As this is on our road map, it is  not possible to do now.
Mark Lee @Appeon 17 May, 2022
#9
Hi Andreas,

Are you still facing this matter? 
Is there anything else we can do to keep helping?

Regards,
Mark Lee
Baron Zhang @Appeon 11 July, 2021
#8
Hi Andreas,

I would like to check with you if the response provided has been helpful enough to resolve your case. If so, can we proceed to close the ticket?
Otherwise, do let us know if we can provide you with any further assistance, and keep in mind that you are always welcome to open another ticket in case you need it. 


Regards,
Baron
Baron Zhang @Appeon 15 March, 2021
#7
Hi Andreas,

We have been trying to keep in touch with you to find out about the resolution of this issue. We would like to continue with our support but without more information, we are in the need to close it according to our internal help support policy but please remember you can always open a new ticket.   

Thanks for you feedback and have a nice day!

Regards,
Baron
Baron Zhang @Appeon 19 February, 2021
#6
Hi Andreas,

Please use 64-bit ODBC to connect to the SQLServer database, as shown in the attachment.
After the connection is complete, re-create the DataWindow, and set the query parameter to decimal type, and then execute the program to verify whether the problem is solved.

I think the problem may be related to the size limitation of the bigint type under 32-bit ODBC.

Regards,
Baron
Baron Zhang @Appeon 19 February, 2021
#5
odbc-64
Andreas Dellen 18 February, 2021
#4
The table defintion is contained int the orginal request below " remarks:"

I had filled the datawindow successfully with following code

longlong ll_i,ll_num,ll_new
ll_num = 1
for ll_i = 1 to 63
	ll_num = ll_num * 2
	if ll_i = 63 then 
		ll_num = 9223372036854775807
	end if
	ll_new = dw_bigint.insertrow(0)
	dw_bigint.setitem(ll_new,"along",ll_num)
	dw_bigint.setitem(ll_new,"aname",string(ll_i))
next


Everything is working ok (save into db, retrieval, delete, find, filter)

Only following command fail in Msql (Odbc connected)

decimal ll_num

ll_num =  9223372036854775807.0
ll_return = dw_bigint.retrieve(ll_num)

or 

ll_num = 1
for ll_i = 1 to 50
	ll_num = ll_num * 2
next
ll_return = dw_bigint.retrieve(ll_num)
Ken Guo @Appeon 18 February, 2021
#3
Hi Andreas,

We tested according to your description:
• Use sql server database;
• Connect via odbc;
• Insert 9223372036854775807 into the data table and use it in a query.
The test result is normal and the data can be returned normally.

We analyze that the problem may be related to the table used in the DataWIndow. Please let us know the creation syntax of the table. Also, please query and let us know the maximum value of the data in the bigint column in the table so that we can reproduce the problem.

Regards,
Ken
Chris Pollach @Appeon 17 February, 2021
#2
Hi Andreas;
  
  I will now transfer this ticket over to the main Support / Engineering Team for their review and feedback on this issue.

  In the mean-time, I would suggest trying another DB Client (ie: OLE-DB, ADO.Net, SNC, etc) to connect to SS vs ODBC. These other connectivity options might work properly vs ODBC

Regards ... Chris
Andreas Dellen 17 February, 2021
#1
d_biginttext.srd (14KB)

*Phenomenon:

Using a decimal retrieval argument do a datawindow with a bigint column results in an error using big values ( > 2^32)

ll_num =  9223372036854775807.0
ll_return = dw_bigint.retrieve(ll_num)

results in:
Select Error: SQLSTATE = 22003
[Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range

whereas inline sql is working as expected

ll_num =  9223372036854775807
select count(*) into :ll_count from biginttext where along = :ll_Num;
//returns correct result

The retrieval is working when connecting to an Oracle database with OCI


*Reproduce Steps:

dw_bigint.dataobject = "d_biginttext"
dw_bigint.settransobject(sqlca)
ll_num =  9223372036854775807.0
ll_return = dw_bigint.retrieve(ll_num)


Remarks:

tablle definition used
CREATE TABLE [dbo].[biginttext](
	[along] [bigint] NOT NULL,
	[aname] [nvarchar](100) NULL,
 CONSTRAINT [PK_biginttext] PRIMARY KEY CLUSTERED 
(
	[along] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
OS:
Windows 10
Platform:
32-bit
Database Type:
Microsoft SQL Server
Database Version:
12.04522