Devart Forums

Code: Select all

select * from vbstocklvl where warehouse like :warehouse and product like :productcode and bin_number like :bincode and tracking_no = :trackingno
--> Params 4
warehouse: FixedChar = '%'
productcode: FixedChar = '%'
bincode: FixedChar = '%'
trackingno: FixedChar = '816450'

Code: Select all

create table vbstocklvl (warehouse char(2), product char(20), bin_number char(10), tracking_no char(20))
insert into vbstocklvl values ('MC','FOIL','A20','886841')

--When setting PREPARED=TRUE
--Equivalent incorrect code made by DEVART dbexpress driver that can run in SQL Server--
declare @p1 int
exec sp_prepare @p1 output,N'@P1 char(2),@P2 char(20),@P3 char(10),@P4 char(20)',N'select * from vbstocklvl where warehouse like  @P1  and product like  @P2  and bin_number like  @P3  and tracking_no =  @P4',1
select @p1
exec sp_execute @p1,'% ','%                   ','%         ','886841              '
exec sp_unprepare @p1

--To fix you have to make the chars varchar params and not pass in trailing spaces -> this is because of the like statement

--Corrected code that works--
declare @p2 int
exec sp_prepare @p2 output,N'@P1 varchar(2),@P2 varchar(20),@P3 varchar(10),@P4 varchar(20)',N'select * from vbstocklvl where warehouse like  @P1  and product like  @P2  and bin_number like  @P3  and tracking_no =  @P4',1
select @p2
exec sp_execute @p2,'%','%','%','886841'
exec sp_unprepare @p2
drop table vbstocklvl

Code: Select all

SELECT * FROM vbstocklvl WHERE warehouse LIKE rtrim(:p1)