PostgreSQL with NHibernate problem with LIKE and parameter

Question

I have a problem with PostgeSQL and NHibernate. I have tried HQL and Criteria and select returns nothing. I used log4net to get generated query, replaced parameters with value and query returns result, why is that? Is there a special configuration for Postgre or NHibernate? I even tried with lowering (lower(column) like lower(:param)) and no result.

HQL : return sess.CreateQuery("from VersFile e where e.ConfigId = :confId and lower(e.FolderPath) like lower(:path) and e.Type = :type and e.Statuss = :statuss and e.Tester != '' order by e.Sequence asc").SetString("path",path).SetInt32("type", fileType).SetInt32("confId", configId).SetInt32("statuss", recordStatuss).List<VersFile>();

FolderPath is character varying column

OR Criteria

return sess.CreateCriteria(typeof(VersFile)).Add(Expression.Eq("Type", fileType)).Add(Expression.Eq("Statuss", recordStatuss)).Add(Expression.Eq("ConfigId", configId)).Add(Expression.Like("FolderPath", path, MatchMode.Start)).Add(Expression.Not(Expression.Eq("Tester", ""))).Add(Expression.Not(Expression.Eq("Statuss", (int)RecordStatuss.Merged))).AddOrder(Order.Asc("Sequence")).List<VersFile>();

Generated query looks like

select .... from versfile versfile0_ where ( versfile0_.statuss!=99) and versfile0_.confId=:p0 and (lower(versfile0_.fpath) like lower(:p1)) order by versfile0_.sequen asc;:p0 = 1, :p1 = '\%MainVss\%WinGui\%'

I've removed useless parameters from query string

consider editing your post and formatting it as a code, unless you expect everyone to be cryptanalyst. — Michael Krelin - hacker
– Michael Krelin - hacker, Commented Sep 21, 2009 at 8:39

Michael Krelin - hacker · Accepted Answer · 2009-09-21 09:05:20Z

1

Actually, the '\%MainVss\%WinGui\%' isn't quite valid in modern postgreses. It's either E'\%MainVss\%WinGui\%' or '%MainVss%WinGui%'. Which is equivalent to '%MainVss%WinGui%', which is not necessarily what you want it to be.

answered Sep 21, 2009 at 9:05

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PavelsM Over a year ago

Actually it have to be \MainVss\WinGui\% , but when I perform select using pgAdmin, the only correct value that returns result is \%MainVss\%WinGui\%, why? i don't know.

Michael Krelin - hacker Over a year ago

What you want in actual query is E'\\MainVss\\WinGui\\%'. You can, probably, omit E prefix, but that should lead to the warning. The way you got it to work in in pgadmin means basically '<anything>*MainVss*<anything>*WinGui*<anything>'. And backslashes match this anything, but what you really wanted to do is just double your backslashes. Plus you may need addition escaping in your source code which I lack knowledge of.

PavelsM Over a year ago

Yes, i'm searching for E'\\MainVss\\WinGui\\%', but i'm using NHibernate, all the data is inserted in tables by NHibernate. I don't have that problem on test db where i use SQLite.

Michael Krelin - hacker Over a year ago

Well, the thing is - in the code you posted there's a mention of weirdly formatted string, so clearly this is where the problem lies. Unfortunately, I can't help you further than this point.

Collectives™ on Stack Overflow

PostgreSQL with NHibernate problem with LIKE and parameter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related