Inserting array data into database on play framework anorm

Question

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.

Zane XY · Accepted Answer · 2014-09-07 12:49:11Z

1

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]].*)

answered Sep 7, 2014 at 12:49

Zane XY

2,8291 gold badge24 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wwkudu · Accepted Answer · 2014-03-16 06:05:01Z

0

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

answered Mar 16, 2014 at 6:05

wwkudu

2,7963 gold badges31 silver badges42 bronze badges

2 Comments

user2512057 Over a year ago

I am using H2 in memory. I now converted the array into blob and it seems working. However, I don't know how to query it out. I am using {SQL("select Id, Prices from task").as(int("Id") ~ blob("Prices") map(flatten) *) }, but the compiler complains blob is not found.

wwkudu Over a year ago

Have you seen the anorm page? There's an image file/binary there. But what about a simpler approach, store it in a string, using mkString and split? (or a separate table ;) )

S.Karthik · Accepted Answer · 2014-09-08 07:43:16Z

0

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()

answered Sep 8, 2014 at 7:43

S.Karthik

1,3899 silver badges22 bronze badges

Collectives™ on Stack Overflow

Inserting array data into database on play framework anorm

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related