CODEWITHSUNDEEP
sqlpostgresqlscalaplayframework-2.0
Marouane Lakhal
Marouane Lakhal

Reputation: 784

Playframework SQL query

Im trying to make a query that help me get a user by its username. What i want to do is to check if the given username is unique. so what i thought about is to uppercase the username when i do my select request and compare it with an uppercase of the given username. This is what i tried:

SQL("select * from utilisateur where upper(pseudo) = {pseudo}").on(
        'pseudo -> pseudo.toUpperCase
        ).as(Utilisateur.utilisateur.singleOpt)

but it get this error: 

[RuntimeException: SqlMappingError(too many rows when expecting a single one)]

I tried this too:

SQL("select * from utilisateur where ucase(pseudo) = {pseudo}").on(
        'pseudo -> pseudo.toUpperCase
        ).as(Utilisateur.utilisateur.singleOpt)

and i got this error:

[PSQLException: ERROR: function ucase(character varying) does not exist Indice : No function matches the given name and argument types. You might need to add explicit type casts. Position : 33]

What should i do ?

PS:Im using PostgreSQL 9.1

Upvotes: 0

Views: 656

Answers (1)

Andrew Conner
Andrew Conner

Reputation: 1436

First, in Play!, only use singleOpt if you're expecting exactly one row back. Otherwise, it will throw an exception. Since you do want just one row, add a LIMIT 1 to the end of your query.

The PostgreSQL function you want is upper(). Here's an example:

SQL("select * from utilisateur where upper(pseudo) = {pseudo} limit 1").on(
        'pseudo -> pseudo.toUpperCase
        ).as(Utilisateur.utilisateur.singleOpt)

(You can play with it here: http://sqlfiddle.com/#!1/6226c/5)

Upvotes: 2

Related Questions