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

Answers


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)


Need Your Help

datatables with rowGrouping plugin aoColumns definitions fail

php jquery html css datatable

Ok so I am working on a table. And I am using rowGrouping on this particular table. To date I have had no serious problems with datatables until now.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.