Problems with SqlFunction, PostgreSQL, and "like"

Skip to first unread message

Peter Hancox

May 27, 2013, 9:20:48 AM5/27/13
Having problems with SqlFunction and "like" criteria in PostgreSQL.

Defining a "lower" function as follows:
val lower = SqlFunction.with1Arg[String, String]("lower")
Executing a query with "===":
val persons =  query(select from p where (lower(p.surname) === "hancox"))
Generates the following SQL which works as expected.
from Person pe1 where lower(pe1.surname)='hancox'
Executing a query with "like":
val persons =  query(select from p where (lower(p.surname) like "hancox%"))
Generates the following SQL which fails:
from Person pe1 where lower(pe1.surname)like'hancox%'

Throws error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "like$1"
I thought this might have been due to PostgreSQL requiring spaces around the "like".
However, leaving out the spaces works OK from the PSQL command line.

Any ideas???

BTW, does mapperdao include a list of standard SQL functions.  Couldn't find any but
seems like a good idea for functions that are part of the SQL standard.

Peter Hancox

May 27, 2013, 10:13:09 AM5/27/13
Appears to work with "H2" database, just PostgreSQL getting upset.

Some more error messages (different test case so query is against different tables)  JUnit test rather than from the application.

    Cause: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select,,su1.postcode,su1.state,
from Suburb su1 
where lower(
]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "like$1"

Kostas Kougios

May 27, 2013, 11:21:59 AM5/27/13
Hi Peter, seems like a bug, will have a look.

Also currently there isnt a library of std sql functions but sounds like a good idea to add one.

Sent from my self, sorry for any typos
You received this message because you are subscribed to the Google Groups "mapperdao" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
For more options, visit

Konstantinos Kougios

May 27, 2013, 11:48:21 AM5/27/13
ok, rc23-SNAPSHOT ready with a fix for this. Also there is a scaffold for an initial lib of std functions, just added the "lower" function to kick it off. StdSqlFunctions object.


Peter Hancox

May 28, 2013, 12:17:52 AM5/28/13
Thanks for the quick turnaround.  That appears to work.

Now how can I achieve the following?  I know it's a contrived example but you can see that it would be a common requirement.  Doesn't appear to like database functions on the right-hand side?

select from p where (lower(p.surname) like lower(p.firstName))

results in the following exception

ERROR c.d.s.internet.PersonSearchPanel - scala.MatchError: SqlFunctionValue(lower,List(ColumnInfo(Column(PersonEntity(Person,com.dtc.deltasoft.entity.Person),first_name,class java.lang.String),<function1>,class java.lang.String))) (of class com.googlecode.mapperdao.sqlfunction.SqlFunctionValue)

Konstantinos Kougios

May 28, 2013, 5:18:08 AM5/28/13
Hi Peter, maybe something is missing there, will have a look this afternoon


Konstantinos Kougios

May 28, 2013, 3:59:08 PM5/28/13
yes, there was an issue with right side sql functions which I've fixed and uploaded a new snapshot of rc23. Should be ok now.


On 28/05/13 05:17, Peter Hancox wrote:

Peter Hancox

May 28, 2013, 11:06:02 PM5/28/13
Thanks that fixed it.

A side question...  To grab the latest versions I've been fighting with SBT, eclipse, IvyDE, and the sonatype snapshots repository.  
SBT can download from sonatype snapshots and will grab the source jar as well with "update-classifiers", however, IvyDE won't attach that
source jar to the binary jar.  IvyDE will attach the source only if it does the download but it can't handle timestamps in the snapshot repository.

So in order to have source code for mapperdao available while I'm testing, I've decided the best approach is to clone the GIT
repository and depend on the eclipse project.  This also gives me the flexibility of hacking the mapperdao code if necessary to 
figure out what I should be doing in my code.

I have one remaining problem however.  Eclipse won't allow my code to work with the mapperdao dependent project if it contains
errors.  Because mapperdao has a dependency on time_2.9.1, eclipse doesn't like the error from the scala compiler about it
being compiled with a different version of scala.  So even though it is only a dependency for testing, I can't use the mapperdao
eclipse project as a dependency for my project.  Turning off the compiler check has allowed me to get everything going but I
would be interested in alternative approaches?  Is there a reason that mapperdao has the scala 2.9.1 version of time as a 
dependency rather than 2.10.1?

Forgive me if this is a stupid question but I'm still at the bottom of the learning curve with scala and dependency management.


May 29, 2013, 1:18:32 PM5/29/13
Hi, there isnt a scala time 2.10 version. Maybe exclude the dep of scalatime to scala2.9.1 if possible. 

I am using intellij and it all works. Scala291 shouldnt be taken into account

Sent from my self
Reply all
Reply to author
0 new messages