create dynamic SQL with anorm

Question

i do not want to delete and reinsert every row, so I have used the following to try to use multiple rows with anorm:

SQL("""
            delete from PERSON_ROLES 
            WHERE person_id = {userId}
            and role_id not in ({rolescommastring})
            )
            """).on('userId -> userId,
                    'rolescommastring -> rolescommastring).execute()

The above yields a string that it doesn't like and i get :

c.j.b.PreparedStatementHandle - delete from PERSON_ROLES WHERE person_id = 1460 and role_id not in ( '1, 3, 8, 9' )

can i create dynamic sql with anorm?

cchantep · Accepted Answer · 2014-10-13 18:22:12Z

4

Since Anorm 2.3, multi-value parameters are supported.

SQL("""
        delete from PERSON_ROLES 
        WHERE person_id = {userId}
        and   role_id not in ({rolescommastring})
        )
        """).on('userId -> userId,
                'rolescommastring -> Seq("id1", "id2", "id3")).execute()

There are more similar examples at https://www.playframework.com/documentation/2.3.x/ScalaAnorm

answered Oct 13, 2014 at 18:22

cchantep

9,1783 gold badges32 silver badges43 bronze badges

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

Comments

mohit · Accepted Answer · 2014-10-13 15:40:37Z

0

Anorm doesn't support 'IN' clauses. It is same for most of the ORMs (like scala- slick) as prepared statements do not support IN clauses

The process that I use is from https://groups.google.com/forum/#!topic/play-framework/qls6dhhdayc/discussion

Basically something like this should work

val params = List(1, 3, 8, 9) 
val paramsList = for ( i <- 0 until params.size ) yield ("role_id" + i)

SQL("""
        delete from PERSON_ROLES 
        WHERE person_id = {userId}
        and   role_id not in ({%s})
        )
        """.format(paramsList.mkString("},{"))).on('userId -> userId ++ 
                paramsList.zip(params)).execute()

answered Oct 13, 2014 at 15:40

mohit

5,0091 gold badge25 silver badges39 bronze badges

1 Comment

Michael Zajac Over a year ago

This might be ok for integers, but it should not be used as a general solution, as it is ripe for SQL injection.

Collectives™ on Stack Overflow

create dynamic SQL with anorm

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related