Re: syntax error at or near "RETURNING"

[Adam Tistler]

I "fixed" this issue my modifying Async.scala:

798         def getFilledStatement(connection:java.sql.Connection) = {

799             val s =

800                 if ( sql.query.startsWith("select") ) connection.prepareStatement(sql.query)

801                 else connection.prepareStatement(sql.query,java.sql.Statement.RETURN_GENERATED_KEYS)

However now I get the following:

RuntimeException occured : ColumnNotFound(nodes.node_id)

@ 14: val node = Node.find("node_id={node_id}").on("node_id" -> 3).first()         

play.exceptions.JavaExecutionException: ColumnNotFound(nodes.node_id)

at play.mvc.ActionInvoker.invoke(ActionInvoker.java:227)

at Invocation.HTTP Request(Play!)

Caused by: java.lang.RuntimeException: ColumnNotFound(nodes.node_id)

at scala.Predef$.error(Predef.scala:58)

at play.db.anorm.Sql$.parse(Anorm.scala:924)

at play.db.anorm.Sql$class.parse(Anorm.scala:853)

at play.db.anorm.SimpleSql.parse(Anorm.scala:784)

at play.db.anorm.SimpleSql.first(Anorm.scala:796)

at controllers.Application$.listNodes(app/controllers.scala:14)

at play.mvc.ActionInvoker.invokeWithContinuation(ActionInvoker.java:540)

at play.mvc.ActionInvoker.invoke(ActionInvoker.java:498)

at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:492)

at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:469)

at play.mvc.ActionInvoker.invoke(ActionInvoker.java:157)

Source is below:

package models

import play.db.anorm._

import play.db.anorm.defaults._

case class Node(

  node_id: Pk[Int],

  node_type: Int,

  template_id: Int

)

object Node extends Magic[Node](Some("nodes"))

package controllers

import play._

import play.mvc._

import play.db.anorm._

import models._

import templates.Template

object Application extends Controller {

  def index = Template

  def listNodes = {

    val node = Node.find("node_id={node_id}").on("node_id" -> 3).first()

    Text(node)

  }

}

On Apr 28, 2011, at 11:44 PM, gobagoo wrote:

For some reason the JDBC postgres driver is adding: RETURNING * to the
end off certain select statements.
Does anyone have any idea why?

I think it has something to do with (Anorm.scala):

798         def getFilledStatement(connection:java.sql.Connection) = {
799             val s
=connection.prepareStatement(sql.query,java.sql.Statement.RETURN_GENERATED_KEYS)

PSQLException occured : ERROR: syntax error at or near "RETURNING"


package controllers

import play._
import play.mvc._
import play.db.anorm._
import models._
import templates.Template

object Application extends Controller {

 def index = Template

 def listNodes = {
   val node = Node.find("id={id}").on("id" -> 3).first()
   Text(node)
 }
}


package models

import play.db.anorm._
import play.db.anorm.defaults._

case class Node(
 node_id: Pk[Int],
 node_type: Int,
 template_id: Int
)

object Node extends Magic[Node](Some("nodes"))


                                       Table "public.nodes"
   Column    |           Type           |
Modifiers
--------------+--------------------------
+-----------------------------------------------------------
node_id      | integer                  | not null default
nextval(('node_id_seq'::text)::regclass)
node_type_id | integer                  | not null
template_id  | integer                  | not null
timestamp    | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
Indexes:
   "nodes_pkey" PRIMARY KEY, btree (node_id)
   "n_node_id_index" btree (node_id)
   "n_node_type_id_index" btree (node_type_id)
   "n_template_id_index" btree (template_id)


Execution exception (In /app/controllers.scala around line 14)
PSQLException occured : ERROR: syntax error at or near "RETURNING"
Position: 33

play.exceptions.JavaExecutionException: ERROR: syntax error at or near
"RETURNING"
 Position: 33
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:227)
at Invocation.HTTP Request(Play!)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at
or near "RETURNING"
 Position: 33
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:
2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:
1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:
500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:
388)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:
273)
at play.db.anorm.Sql$class.resultSet(Anorm.scala:844)
at play.db.anorm.SimpleSql.resultSet(Anorm.scala:784)
at play.db.anorm.Sql$class.parse(Anorm.scala:850)
at play.db.anorm.SimpleSql.parse(Anorm.scala:784)
at play.db.anorm.SimpleSql.first(Anorm.scala:796)
at controllers.Application$.listNodes(app/controllers.scala:14)
at play.mvc.ActionInvoker.invokeWithContinuation(ActionInvoker.java:
540)
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:498)
at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:
492)
at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:
469)
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:157)

[gobagoo]

[Sadek Drobi]

Can you try to print 

Node.find("node_id={node_id}").on("node_id" -> 3)().toList

And tell me what you get?

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.

[gobagoo]

List(Row('.node_id':3 as java.lang.Integer, '.node_type_id':1 as
java.lang.Integer, '.template_id':1 as java.lang.Integer, '.timestamp':
2004-12-22 17:42:51.641556 as java.sql.Timestamp))

On Apr 29, 5:19 am, Sadek Drobi <sadek.dr...@gmail.com> wrote:
> Can you try to print
>
> Node.find("node_id={node_id}").on("node_id" -> 3)().toList
>
> And tell me what you get?
>

> On 29 avr. 2011, at 10:31, Adam Tistler <atist...@gmail.com> wrote:
>
> I "fixed" this issue my modifying Async.scala:
>
> 798         def getFilledStatement(connection:java.sql.Connection) = {
> 799             val s =
> 800                 if ( sql.query.startsWith("select") )
> connection.prepareStatement(sql.query)
> 801                 else
> connection.prepareStatement(sql.query,java.sql.Statement.RETURN_GENERATED_KEYS)
>
> However now I get the following:
>

> *RuntimeException* occured : ColumnNotFound(nodes.node_id)

[Sadek Drobi]

Posgres Sql JDBC driver doesn't impelemnt very basic JDBC methods. In this case getTableName

 public String getTableName(int column) throws SQLException

    {

        return "";

    }

You can get it working by passing "" as a name for your table for now. We will think of a way of fixing this for the coming Play Scala release.

--
www.sadekdrobi.com
ʎdoɹʇuǝ

[gobagoo]

So this would make Anorm ( at least the combinator parser parts and
the Magic classes that rely on them ) a no-go for both postgres and
oracle as both drivers return "" for this method? I am not
complaining here, just wanted to get the facts straight. I have an
existing database (postgres obviously) that I would love to using
Anorm with. Do you have any ideas regarding a way that this might be
fixed? I have looked at the anorm.scala code and although parts of
the sql parser section confuses me, the rest does not seem to bad. I
would be happy to attempt to patch it and test if you can maybe point
me in the right direction. Thanks again for the great work on this
project.

-Adam

[Sadek Drobi]

PostgreSql driver has a method getBaseTableName that looks like it can work for Anorm. Maybe it is better to patch Postgres driver to implement more meaningfully getTableName based on getBaseTableName.

The use of table names is to resolve ambiguity when having a join with two columns with the same name.

Worth nothing, if you prefix all your columns with their table name and ask Anorm to ignore table names, then you can make it work using the Conventions mechanism.

Sadek

[oxman]

I got the same error too, but with an other case :

package models

import play.db.anorm._
import play.db.anorm.SqlParser._
import play.db.anorm.defaults._

case class Player(
id: Pk[Long],
name: String,
hfr_name: String
)

object Player extends Magic[Player] {

def all:List[(Long,Player)] =
SQL("""
SELECT * FROM players
""").as(long("id") ~< Player ^^ flatten *)

}

PSQLException occured : ERROR: syntax error at or near "RETURNING"

Position : 23
At the line : """).as(long("id") ~< Player ^^ flatten *)

> ...
>
> plus de détails »

[Sadek Drobi]

It is fixed in master. Soon we will release a new version.

Sadek

[oxman]

Nice, how can i get/build the master before the release ?

> ...
>
> plus de détails »

[tmueller]

I noticed this issue hasnt been resolved in 2.0.2-RC2

This is how I'm using it. Should I change my code to work around the RETURNING issue?

def create(user: User): User = {

    DB.withConnection { implicit connection =>

      val id: Long = SQL(

          """

          insert into account (email, password, name) values (

          {email}, {password}, {name}

          """

          ).on(

        'email -> user.email,

        'password -> user.password,

        'name -> user.name

      ).executeInsert().get

      User(user.email, user.password, user.name, new Id(id.toInt))