Bug on Fixed Char like queries with Prepared=True option

[shaunwallace]

I have queries that use the like statement and I pass in the values as parameters but there is a bug in the driver and SQL server. Tracking it via profiler I can see the problem and a solution. I don't know what your driver does so this might be going wrong in the Microsoft side. I have written the equivalent statements that you can run as queries so you can see the problem and the solution.

In driver options I set prepared=true.

The Delphi statement and parameters are as follows:

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'

To view issue in server run this as a query:

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

[AlexP]

Hello,

This problem is due to the behaviour of the MS SQL client: when preparing a query, parameters are defined as FixedChar and complemented with trailing spaces by the SQL provider. We cannot affect this behaviour. You can either change the type of the table fields or not prepare the query

[shaunwallace]

I cannot change the database as not all the tables are owned by my application. I was hoping to use Prepared to speed up database access. Is this is a bug in the Microsoft code or in the way you call the Microsoft code as other database like Informix and Oracle work with server side prepared queries fine when using the 'like' statement?

[AlexP]

Hello,

It is not a bug, it is the correct behaviour, as parameters have the FixedChar type, their values are automatically complemented by spaces to the needed length

[shaunwallace]

I partially agree. That behavior is fine for any scenario except the like statement.

The reason I disagree is that appending spaces to the remainder of the size for any wildcard based search is incorrect and results in incorrect results . Take a look at the following examples:

A search for '%' on a two character column should return any value in that column but when padding to the right with a space i.e. '% ', this limits the query to return only rows where the first character is any value and the last character is a space. So the first one would return 'AA' and 'A ' but the second one would only return 'A '.

I could pad with '%' and I have tried it but that would be wrong as it would require me detecting a like in the SQL and then also detecting if it is the MS SQL database in order to do that.

[shaunwallace]

At the end of the day I need to support multiple databases transparently. I was hoping to gain speed by using the Prepared=True option. Would you be able to comment if there is a speed difference when running parameterized queries/inserts and having that option enabled. If there is no difference or it is a small difference then I will just use the Prepared=False option. But then please make a note in your Readme.html that the Prepared=True option does not support wildcard matching on fixed width columns.

[AlexP]

Hello,

Using Prepared in most cases allows increase queries performance when they are used repeatedly. However, the like operation itself is rather slow, therefore, in this case, preparing will not affect the performance. However, if you still want to use this option in queries containing like statements, you can forcibly cut the spaces using the rtrim method.

Code: Select all

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

[shaunwallace]

That can work. I have an escaped function that translates trims for all database versions. Will give that a go.