Hello all. I'm new to LLBLGen Pro and am quite impressed so far. This is a very elegant approach to O/R mapping. I'm having a problem though:

protected new void tcIndex_Click(object sender, System.EventArgs e) { // this._accounts is a Typed List this._accounts.Clear() ; this._predicate = PredicateFactory.Like(Proximity.References.DAL.AccountFieldIndex.AccKey, "B%") ; this._filter.Add(this._predicate) ; this._sorter = new SortExpression(SortClauseFactory.Create(Proximity.Preferences.DAL.AccountFieldIndex.AccKey, SortOperator.Ascending)) ; this._accounts.Fill(0, _sorter, true, _predicate) ; }

Is this example, the search-like pattern is "B%". This query should produce a result with all AcountFieldIndex.AccKeys that start with B, but it doesn't. It produces no records. However, when I change the pattern to "B%%%", it WILL list results such as "Best" and "Bags", but not "Baltimore" or "Ballgame". This is not the way the "B%" pattern should work.

Is this a bug, or am I doing something wrong?

Thanks - - Jeff

Hmm. The pattern you specify is passed as a value to the parameter which is added in the LIKE predicate, thus your query would be something like:

SELECT * FROM Accounts 
WHERE AccKey LIKE param

and param's value then is 'B%'

I think you use Oracle, not SqlServer, is this correct? (because the amount of '%' you specify is not important for SqlServer, don't know if there is an option in Oracle where you can turn that on/of

No, I'm using SQL Server ver. 8.0. Transact-SQL calls for the use of the "%" character to do a wildcard search. See link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp

... which talks about the wildcard character: "%".

Have I identified a bug? Is there a work-around that you can recommend?

Thanks - - Jeff

I really don't know what causes this... Can you check with the sql profiler which query is executed? (you can also find that out by stepping into the code, into the Fill method, then into the TypedListDAO and then until you find the line where an IRetrievalQuery is created. Step over it, open command window in visual studio and type:

?((RetrievalQuery)selectQuery).ToString()

you'll see the query and also the parameters. You can check the values for the parameters in the Parameters collection of teh Command object in the selectQuery. This can be done via the 'locals' debug window or a watch.

When you have the query, can you please execute it in Query Analyzer on your database and see if it works there? I'll try to reproduce it here with some testcode, but I'm pretty sure it will work here as it is part of my tests for the templatecode.

perhaps it's a bug in sqlserver or a setting, please check if you've applied at least Service pack 3 (the 3a version probably) on your sqlserver installation. I'll check if this is a bug or setting in sqlserver

It seems related to a CHAR/ NCHAR field. These values are padded with spaces, parameter values are too. I'll look into a solution.

Well... without doing that other stuff you suggested (I'm new to LLBLGen Pro AND C# AND VS.Net) and your suggestions are a bit confusing to me at this time... I DID run the query in SQL Query Analyzer as follows:

select * from account where accKey like 'B%'

And, yes, I got the proper result. I'm sure that there's a bug here.

Jeff

Thanks. I await your solution.

Jeff

This works:

DECLARE @pattern varchar(5)
SET @pattern='C%'
SELECT * FROM Customers WHERE CustomerID LIKE @pattern

This doesn't:

DECLARE @pattern nchar(5)
SET @pattern='C%'
SELECT * FROM Customers WHERE CustomerID LIKE @pattern

The reason for this is that the (n)char parameters get padded with spaces. I give a LIKE parameter the same type as the field. This is obviously not correct, it should be nvarchar() in this case.

I'll try to fix this with the current lib and will send you a fixed version. The fix is preliminairy and will be official when the next library version is released, approx within 2 weeks.

Btw, as a workaround, you can specify as much '%' as the CHAR field is long, so if the field is 20 chars long, you specify 'B%%%%%%%%%%%%%%%%%%%'.

Sounds silly, but it should work (because no space padding is done in that case)

That'll work since this problem only affects nchar and not nvarchar. In fact, when I changed the code to...

this._predicate = PredicateFactory.Like(Proximity.References.DAL.AccountFieldIndex.AccName, _lookupChar + "%") ...

it worked just fine because AccName is varchar(255), as opposed to AccKey which is char(6). But, since wildcard lookups on nchar fields are prevasive throughout my code, I'll wait for your bug-repair since I'd rather not hard-code _lookupChar + "%%%%%%%%%%%%" throughout my application.

Thanks for your QUICK response. I'm appreciative and impressed.

Jeff

Ok.

I'll try to fix the problem tomorrow and will mail you the fixed runtime libraries.

Fix mailed. because the runtime library is currently in development, the official release of this fix is scheduled with the release of the new library. If another person requires this fix also, mail to: support@llblgen.com

A fix for this problem is now available as a separate download (runtime libraries). If you need this fix, download the latest runtime libraries archive from the customer area.