CODEWITHSUNDEEP
sqlarraysscalaplayframework-2.0anorm
user2512057
user2512057

Reputation: 51

Inserting array data into database on play framework anorm

I need to store array data into database. But I don't find samples how to do that. I tried just treating array as a normal data type but it seems it doesn't work. Here is my code:

In the database table, I have 3 columns: 

Id integer,
uLocation integer,
Prices integer(366)

In SQL, insert, update or query the table by using: 

def InseartToDatabase(ItemPrices: ListBuffer[Int], Id: Int, uLocation: Int) = {
     val prices = ItemPrices.toArray
     DB.withConnection { implicit c =>
         SQL("insert into task (Id, uLocation, Prices) values ({Id},{uLocation}, {Prices})").on('Id ->Id, 'uLocation->uLocation, 'Prices -> Prices).executeUpdate()
     }
}

This doesn't work just silently and no exception reported. But next update just failed. If I remove the array fields, above code just works fine.

Also, I have no idea how to query it.

Upvotes: 1

Views: 1304

Answers (3)

S.Karthik
S.Karthik

Reputation: 1389

use .toSeq, instead list, this might work.

SQL("insert into task (Id, uLocation, Prices) values ({Id},{uLocation}, {Prices})").on('Id ->Id, 'uLocation->uLocation.toSeq, 'Prices -> Prices).executeUpdate()

Upvotes: 0

Zane XY
Zane XY

Reputation: 2809

If you're using H2, try to use Postgresql mode. Postgresql supports Array data type (ref) and starts from playframework 2.4.x, Anorm supports array column parser (ref), you can query it like:

import anorm.SQL
import anorm.SqlParser.{ scalar, * }

// array and element parser
import anorm.Column.{ columnToArray, stringToArray }

val res: List[Array[String]] =
SQL("SELECT str_arr FROM tbl").as(scalar[Array[String]].*)

Upvotes: 1

wwkudu
wwkudu

Reputation: 2796

You don't say which database you are using. Not all databases support array data types. I stand to be corrected but don't think anorm supports arrays in the way you are expecting. There is some discussion and a potential workaround on this google group's thread.

P.S. Allow me to make a plug for a separate table for your multi-valued column. For a relatively small inconvenience, you get standard sql processing (easier to maintain) and better cross-platform code, to name a few benefits. :)

Upvotes: 0

Related Questions