ilike operator

[Rob Love]

If someone can shed some light on this. I'm querying a PostgreSQL db
and I need to user the ilike operator. I have searched around google
and have yet to find an example.
I get an error when I do this: login = entityload("logins",{username
ilike "fooO"});

Thanks in advance,
Rob

[Adam Tuttle]

I've never used postgres or ilike, but I can tell you right away that your code won't work because it's not valid CFML. the {} bit is creating a CF structure, and that will throw an error because ilike is not a CF assignment operator. My best guess is that the only way you'll be able to do this is using HQL: ormExecuteQuery("from blah where username ilike 'fooO'");

[Rob Love]

Thanks Adam.

your example works using an = but when I try the ilike I get the
following error unexpected token: ilike .

Ideas?

[Tony Nelson]

What if you tried something like:

ormExecuteQuery("from blah where lower(username) like :username",
{username = "%#lcase('fooO')#%" });

[John Whish]

Hibernate's HQL doesn't support ilike, so go with Tony's advice.

Supported expressions listed here:
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-expressions

> --
> You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
> To post to this group, send email to cf-or...@googlegroups.com.
> To unsubscribe from this group, send email to cf-orm-dev+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cf-orm-dev?hl=en.
>
>

[Rob Love]

Thanks guys for the input.

Tonys solution will work for small tables but when its a large table I
wonder about performance. I did find an explanation of how to create a
custom dialect for hibernate and it seems simple but I do not know how
to make it happen since it is baked in to CF.

Here is qoute from site.

"create a custom dialect of org.hibernate.dialect.PostgreSQLDialect,
adding an "ilike" function into it. This is actually a lot easier
than it sounds, here's some (untested) code, which I think might do
the job:

public class CustomPostgreSQLDialect extends
org.hibernate.dialect.PostgreSQLDialect {
public CustomPostgreSQLDialect() {
super();
registerFunction("ilike", new
VarArgsSQLFunction(Hibernate.BOOLEAN, ""," ilike ", ""));
}
}
"

So now, who knows how to add this to the hibernate in CF?

On Aug 2, 4:37 pm, John Whish <john.wh...@googlemail.com> wrote:
> Hibernate's HQL doesn't support ilike, so go with Tony's advice.
>

> Supported expressions listed here:http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.h...

[John Whish]

Haven't tried, but I think you would need to create your custom class
and then wrap it up in the jar and then reference it using:

this.ormsettings.dialect="org.hibernate.dialect.MyCustomPostGresDialect"