Hi,
I have migrated my application from PB 2019 to PB 2022. I am receiving below error message for datastore retrieval:
the data types varchar and text are incompatible in the greater than and equal to operator
However, the same works fine in Crystal Report retrieval and SSMS query using same connection type. This was working find for PB 2019 as well.
The datastore I am trying to retrieve is as below:
SELECT st_master.stock_id,
st_master.company_id
FROM st_master
WHERE ( st_master.company_id = :as_company_id ) AND
( st_master.stock_id >= :as_stock_id_from ) AND
st_master.stock_id <= :as_stock_id_to AND
( st_master.stock_type = 'S' ) AND
( st_master.status = 'A' )
The datatype for both columns are varchar in SQL Server.
Your advise will be greatly appreciated.
Kind regards,
Falguni
My analysis are as below:
When I created a datastore having below statement where as_company_id and as_stk_from are retrieval arguments declared as String
SELECT st_master.stock_id
FROM st_master
WHERE ( st_master.company_id = :as_company_id ) AND
( st_master.stock_id = :as_stk_from )
The SQL Server Trace is as below:
declare @p1 int
set @p1=24
exec sp_prepexec @p1 output,N'@P1 varchar(5),@P2 varchar(20)',N'SELECT st_master.stock_id FROM st_master WHERE ( st_master.company_id = @P1 ) AND ( st_master.stock_id = @P2 ) ','PIBL','B10001'
select @p1
In the same datawindow, as soon as I add >= as per below
SELECT st_master.stock_id
FROM st_master
WHERE ( st_master.company_id = :as_company_id ) AND
( st_master.stock_id >= :as_stk_from )
The SQL trace is as below:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(5),@P2 text',N'SELECT st_master.stock_id FROM st_master WHERE ( st_master.company_id = @P1 ) AND ( st_master.stock_id >= @P2 ) ','PIBL','B10001'
select @p1
As per above example, the PB Datawindow changes the SQL sp_prepexec statement when operator is changed to >= and causes this issue which is a PB datawindow bug.
Kindly note that the statement copied to SSMS query executes well and gives correct result.
Appreciate if you could address this to Appeon team to fix at earliest as my legacy application is unstable after migrating to PB2022 R2.
Since you seem to have a reproducible scenario ... please open a Support Ticket and include a reproducible test case PB App & Test DB to the ticket for Appeon Engineering to study.
Regards ... Chris