Anorm says no results were returned for select query preceded by inserts (PSQLException: No results were returned by the query)

I'm writing a Play 2 application with a Postgres backend.

My code has a sql string which is a concatenation of two inserts and a select. Then when I try to execute my query, anorm blows up and says me no results have been returned. I could break it up into two separate queries but then I'd be making an unnecessary trip to the database?

The sql string is pretty simple.

val sql = """
        insert into gameconstants 
            ...
        values 
            ...;

        insert into gamevariables
            ...
        values
            ...;

        select lastval() as gameid;
  """  

Then the code that throws the exception just tries to get the gameid from query. This should work right?

DB.withTransaction { implicit connection =>
  val gameid = SQL(sql).on(
    ... )()
    .map(row => row[Long]("gameid")).head
}  

Here is the error [PSQLException: No results were returned by the query.]

Answers


As the other comments have said, this is caused by issuing a composite statement to JDBC. You have a couple options.

  1. You could do a JDBC batch. As Craig notes above this just sends each query separately in a batch.

  2. You could write a user defined function/stored procedure with this logic and call in a single query. This has the advantage of encapsulating your db logic behind an API. The disadvantage is that without a lot of attention it becomes hard to maintain contracts between stored procedures and the application. This is an area I am doing some work on in Perl, but not yet getting there in Java.

  3. You could collapse the query into two (your code shows three). The simple solution is:

    val query1 = """
        insert into gameconstants 
            ...
        values 
            ...;"""
    
    
    val query2 = """
        insert into gamevariables
            ...
        values
            ...
        RETURNING * 
     """  
    

This would reduce your round trips to some extent.


Need Your Help

Using Merge statement inside a cursor

sql oracle stored-procedures plsql oracle10g

We have a requirement to populate a master table which consists of columns from a set of 20 different tables.

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.