Problems with SqlFunction, PostgreSQL, and "like"

[Peter Hancox]

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]

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]

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]

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]

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]

Hi Peter, maybe something is missing there, will have a look this afternoon

Cheers

[Konstantinos Kougios]

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]

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]

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