Problems with SqlFunction, PostgreSQL, and "like"

93 views
Skip to first unread message

Peter Hancox

unread,
May 27, 2013, 9:20:48 AM5/27/13
to mapp...@googlegroups.com
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.
select pe1.id,pe1.surname,pe1.first_name,pe1.home_address__id,pe1.work_address__id
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:
select pe1.id,pe1.surname,pe1.first_name,pe1.home_address__id,pe1.work_address__id
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

unread,
May 27, 2013, 10:13:09 AM5/27/13
to mapp...@googlegroups.com
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.id,su1.name,su1.postcode,su1.state,su1.country
from Suburb su1 
where lower(su1.name)like?
]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "like$1"

Kostas Kougios

unread,
May 27, 2013, 11:21:59 AM5/27/13
to mapp...@googlegroups.com, mapp...@googlegroups.com
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 mapperdao+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Konstantinos Kougios

unread,
May 27, 2013, 11:48:21 AM5/27/13
to mapp...@googlegroups.com
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.

Cheers

Peter Hancox

unread,
May 28, 2013, 12:17:52 AM5/28/13
to mapp...@googlegroups.com, kostas....@googlemail.com
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)
com.googlecode.mapperdao.sqlbuilder.SqlBuilder$FunctionClause.toSql(SqlBuilder.scala:178)
com.googlecode.mapperdao.sqlbuilder.SqlBuilder$WhereBuilder.toSql(SqlBuilder.scala:281)
com.googlecode.mapperdao.sqlbuilder.SqlBuilder$SqlSelectBuilder.toSql(SqlBuilder.scala:388)
com.googlecode.mapperdao.sqlbuilder.SqlBuilder$SqlSelectBuilder.result(SqlBuilder.scala:368)
com.googlecode.mapperdao.jdbc.impl.QueryDaoImpl.query(QueryDaoImpl.scala:44)
com.googlecode.mapperdao.QueryDao$class.query(QueryDao.scala:65)
com.googlecode.mapperdao.jdbc.impl.QueryDaoImpl.query(QueryDaoImpl.scala:36)
com.googlecode.mapperdao.QueryDao$class.query(QueryDao.scala:39)
com.googlecode.mapperdao.jdbc.impl.QueryDaoImpl.query(QueryDaoImpl.scala:36)
com.dtc.sportzman.internet.PersonSearchPanel$$anon$7$$anonfun$3$$anonfun$apply$1.apply$mcV$sp(PersonSearchPanel.scala:90)

Konstantinos Kougios

unread,
May 28, 2013, 5:18:08 AM5/28/13
to mapp...@googlegroups.com
Hi Peter, maybe something is missing there, will have a look this afternoon

Cheers

Konstantinos Kougios

unread,
May 28, 2013, 3:59:08 PM5/28/13
to mapp...@googlegroups.com
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.


Cheers

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

Peter Hancox

unread,
May 28, 2013, 11:06:02 PM5/28/13
to mapp...@googlegroups.com, kostas....@googlemail.com
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.

REGARDS
Peter

kostas....@googlemail.com

unread,
May 29, 2013, 1:18:32 PM5/29/13
to mapp...@googlegroups.com, mapp...@googlegroups.com
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
Forward
0 new messages