[play2 anorm] Why can't find the message by id?

[Freewind]

I'm using anorm to save and query a Message. It can be saved, but
can't be queried.

The code is:

Message.scala
============

case class Message(
id: Pk[String] = NotAssigned,
username: Option[String] = None,
accountQQNumber: Option[String] = None,
accountOther: Option[String] = None,
content: String,
messageType: String = Message.MESSAGE_TYPE_NEW_CREATED, //
imported_text, imported_html, new_created
createdAt: Date,
updatedAt: Option[Date] = None,
userId: Option[String] = None) {
}

object Message {

val MESSAGE_TYPE_NEW_CREATED = "new_created"
val MESSAGE_TYPE_IMPORTED_TEXT = "imported_text"
val MESSAGE_TYPE_IMPORTED_HTML = "imported_html"

val simple = {
get[Pk[String]]("messages.id") ~
get[Option[String]]("messages.username") ~
get[Option[String]]("messages.account_qqnumber") ~
get[Option[String]]("messages.account_other") ~
get[String]("messages.content") ~
get[String]("messages.message_type") ~
get[Date]("messages.created_at") ~
get[Option[Date]]("messages.updated_at") ~
get[Option[String]]("messages.user_id") map {
case id ~ username ~ accountQQNumber ~ accountOther ~ content
~ messageType ~ createdAt ~ updatedAt ~ userId =>
Message(id = id, content = content, createdAt = createdAt,
username = username, userId = userId, accountQQNumber =
accountQQNumber,
accountOther = accountOther, messageType = messageType,
updatedAt = updatedAt)
}
}

def findById(id: String): Option[Message] = {
DB.withConnection { implicit connection =>
return SQL("select * from messages where id = {id}").on(
'id -> id).as(Message.simple.singleOpt)
}
}

def create(message: Message): Message = {
DB.withConnection { implicit connection =>
val id = newId();
SQL(
"""
insert into messages (
id, username, account_qqnumber, account_other, content,
message_type, created_at, updated_at, user_id
) values (
{id}, {username}, {accountQQNumber}, {accountOther},
{content}, {messageType}, {createdAt}, {updatedAt}, {userId}
)
""").on(
'id -> id,
'username -> message.username,
'accountQQNumber -> message.accountQQNumber,
'accountOther -> message.accountOther,
'content -> message.content,
'messageType -> message.messageType,
'createdAt -> message.createdAt,
'updatedAt -> message.updatedAt,
'userId -> message.userId).executeUpdate()

message.copy(id = Id(id))

}
}
}

The test is used "scalatest":

BaseSuite.scala
============

@RunWith(classOf[JUnitRunner])
class BaseSuite extends FlatSpec with ShouldMatchers
with BeforeAndAfterEach with BeforeAndAfterAll {

protected def fakeApp = FakeApplication(additionalConfiguration =
Map(
("db.default.url") -> ("jdbc:postgresql://localhost:35432/
shuzu2_test")))

MessageTest.scala
===============

class MessageTest extends BaseSuite {

"Message" should "be found by id" in {
running(fakeApp) {
val message = Message.create(new Message(
content = "Hello, world!",
createdAt = new Date))

val generatedId = message.id.get
println("id: " + generatedId)

val found = Message.findById(generatedId)
found should not be (None)
found.get.content should be("Hello, world!")
}
}

But the test is always failed on this line:

found should not be (None)

The error message is:

None was equal to None

Seems it can't found that message by id, but I've checked the
database, it's there.

There is no detailed document for anorm, and I've spent several hours
on it.

Please help me, thanks!

[Guillaume Bort]

Try to run it with single instead of singleOpt to see why it is failing.

> --
> 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.
>

--
Guillaume Bort

[Freewind]

Thank you! It tells me the error:

[info] java.lang.RuntimeException: messages.id not found, available
columns :

id, username, account_qqnumber, account_other, content, message_type,
created_at
, updated_at, user_id

[info] at scala.sys.package$.error(package.scala:27)
[info] at scala.Predef$.error(Predef.scala:66)

So I removed the "messages." part from the code of following:

get[Pk[String]]("messages.id")

get[Option[String]]("messages.username")

get[Option[String]]("messages.account_qqnumber")

get[Option[String]]("messages.account_other")

...

This time the unit tests passed. Thank you again.

But I still have some questions. I check the zentasks sample, and
found the "Project" object:

val simple = {
get[Pk[Long]]("project.id") ~
get[String]("project.folder") ~
get[String]("project.name") map {
case id~folder~name => Project(id, folder, name)
}
}

It uses "project.??" and the "findById" method is:

def findById(id: Long): Option[Project] = {
DB.withConnection { implicit connection =>
SQL("select * from project where id = {id}").on(
'id -> id
).as(Project.simple.singleOpt)
}
}

Why it runs OK? Does it depend on the type of database?

[Drew Hamlett]

Postgres maybe dropping the table name. I have this same thing happen
to me on mysql when I do a query like

"select hour(time_stamp) as hour from blah"

where I'm naming the column name. So here I'll parse the row as hour
instead of blah.hour.

Obviously your not doing that but I suspect it's a similar problem.

[Guillaume Bort]

Yes if you are using postgres it's probably the cause. The postgresql
jdbc driver is broken and doesn't return table names.

[Freewind]

From this discussion, we can see that they think
"rs.getMetaData.getTableName(col)" should return the alias name in
query not real table name, which is hard to implement, so they left it
empty:

http://archives.postgresql.org/pgsql-jdbc/2009-12/msg00100.php

Also they gave a method to get the table name, use:

PGResultSetMetaData.getBaseTableName()

Here is the test code:

import java.sql.DriverManager
import org.postgresql.Driver
import org.postgresql.PGResultSetMetaData
object TestPostgres {
def main(args: Array[String]) {
DriverManager.registerDriver(new Driver)
val conn = DriverManager.getConnection("jdbc:postgresql://
localhost:5432/_test", "postgres", "postgres")
// val conn = DriverManager.getConnection("jdbc:h2:mem:play",
"sa", "")

try {
val stmt = conn.createStatement()
// create table
val ddl = """
drop table if exists x;
create table x (
id text not null primary key,
name text
);
"""
stmt.executeUpdate(ddl)

val rs = stmt.executeQuery("select * from x")

// convert it to PGResultSetMetaData
val meta = rs.getMetaData().asInstanceOf[PGResultSetMetaData]
val tableName = meta.getBaseTableName(1)
println(tableName)
} finally {
conn.close()
}
}
}

It prints:

x

Which is the correct table name.

On Feb 5, 11:43 pm, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> Yes if you are using postgres it's probably the cause. The postgresql
> jdbc driver is broken and doesn't return table names.
>
>
>
>
>
>
>
>
>
> On Sun, Feb 5, 2012 at 4:13 PM, Drew Hamlett <drewhj...@gmail.com> wrote:
> > Postgres maybe dropping the table name.  I have this same thing happen
> > to me on mysql when I do a query like
>
> > "select hour(time_stamp) as hour from blah"
>
> > where I'm naming the column name.  So here I'll parse the row as hour
> > instead of blah.hour.
>
> > Obviously your not doing that but I suspect it's a similar problem.
>