Search Bugs

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

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

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

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

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

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

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.

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.

Hi Andreas,

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

Regards,
Mark Lee

Hi Andreas,

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

Regards,
Mark Lee

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

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

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

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

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

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

odbc-64

Created attachment 7233
odbc-64

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)

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)

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

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

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

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

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]

Created attachment 7226
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]