[2.0] Anorm support for prepared statements with "in"

Showing 1-12 of 12 messages
[2.0] Anorm support for prepared statements with "in" David Ross 3/27/12 10:28 PM
Hello,

I was wondering adding support for "in" is a reasonable feature for anorm. Something like this: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives

Specifically, if the api could generalize the annoyance of dynamically creating the multiple ?'s. Possible syntax would be:

SQL("SELECT * FROM my_table WHERE my_id IN {ids}").onList('ids -> List(1, 2, 3))

Behind the scenes, this would be the same as:

SQL("SELECT * FROM my_table WHERE my_id IN ({ids1}, {ids2}, {ids3}").on('id1 -> 1, 'id2 -> 2, 'id3 -> 3)

But would be flexible on changing the number of items in 'ids


Thoughts?


Thanks,

David
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" Guillaume Bort 3/28/12 3:33 AM
What would be the common way to set this value using standard JDBC?

> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/play-framework/-/Tsg3AiJAGHsJ.
> 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

Re: [play-framework] [2.0] Anorm support for prepared statements with "in" David Ross 3/28/12 10:00 AM
There is no common way in JDBC. It seems from the stackoverflow link above that people tend to do one of two things:
  1. Eschew prepared statements altogether and just build the string with the raw values.
  2. Have a helper method on top of the prepared statement to dynamically create a (?, ?, ?) and fill as needed.
I propose that anorm provide a common way to do (2).
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" David Ross 3/28/12 3:48 PM
Here is some sample code that does it:

   def inHelper(sql: String, key: String, values: List[String]) = {
        val toBeReplaced = "{%s}" format key
        val length = values.size
        val newKeys = (1 to length) map ("%s%d" format (key, _))
        val newKeysWithBraces = newKeys map ("{%s}" format _)
        val replacement = "(%s)" format (newKeysWithBraces mkString ",")
        val newSql = sql replace (toBeReplaced, replacement)
        val parameterValues: List[ParameterValue[String]] = values map (toParameterValue(_))
        val pairs = newKeys zip parameterValues 
        SQL(newSql) on (pairs: _*)
    }


It would be great if this was built into anorm though.
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" David Ross 4/2/12 11:18 AM
Would a patch to anorm for this be accepted? Or is this not a desired-enough feature?
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" Guillaume Bort 4/2/12 11:21 AM
Well I'm not sure to understand exactly what would the API look like
from the user point of view. Can you show an example first?

--
Guillaume Bort

Re: [play-framework] [2.0] Anorm support for prepared statements with "in" David Ross 4/3/12 12:31 AM
Sure.

Without api support:

SQL("SELECT * FROM users WHERE user_id in (" + List(1, 2, 3) + ")") // bad
SQL("SELECT * FROM users WHERE user_id in ({id1}, {id2}, {id3})").on('id1 -> 1, 'id2 -> 2, 'id3 ->3) // impossible to generalize if you don't know the number of ids


With the function I wrote above:

inHelper("SELECT * FROM users WHERE user_id in {ids}", 'ids -> List(1, 2, 3))


With support in anorm:

SQL("SELECT * FROM users WHERE user_id in {ids}").onList('ids -> List(1, 2, 3))


Thoughts?

Dave
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" Guillaume Bort 4/3/12 3:00 AM
Well it would be great, but I'm not sure how to implement this.
SQL(...) compute a prepared statement and it is too late to modify the
SQL statement after that.
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" David Ross 4/3/12 9:57 AM
Ah, I see. How about:

ExpandableSQL("SELECT * FROM users WHERE user_id in {ids}").expand('ids -> List(1, 2, 3))

or:

SQL("SELECT * FROM users WHERE user_ids in {ids}".expand('ids -> 3)).onList('ids -> List(1, 2, 3))

Now these aren't as nice as what I proposed above, and not a whole lot better than just having a helper function. I do think, however, it would be nice to have a common way to solve this problem, as it seems to be a popular issue (see stackoverflow, for example).
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" kheraud 5/29/12 9:16 AM
+1

The first one with ExpandableSQL would be great.

To do so, I chose the "+ daList.mkString(...) +" stuff but as said, it's bad...

Thanks for the helper function.
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" Jun Yamog 5/29/12 12:36 PM
Thanks David for looking at this.  I currently just mkString my seq,
which is not ideal.  Would be good to have as part of anorm.

I would go for your 2nd option.

SQL(...).onList(...)

Does that mean this would be possible?

SQL(...).onList(...).on(...).

Makes the syntax more part of SQL.  However if that proves to be
difficult then I guess ExpandableSQL should be great.

Maybe we should just accept a Seq a much more generic collection
rather than a List.  We are only concerned about ordering right?

.onSeq where Seq can be of type String, Integer, Byte, etc. (any of
the supported jdbc types)
> https://groups.google.com/d/msg/play-framework/-/UBkCn9Z_0yAJ.
Re: [play-framework] [2.0] Anorm support for prepared statements with "in" scott clasen 7/18/12 5:40 PM
FYI https://github.com/twitter/querulous/ supports this, might look there for inspiration

Here's their usage  

    val users = queryEvaluator.select("SELECT * FROM users WHERE id IN (?) OR name = ?", List(1,2,3), "Jacques") { row =>
  new User(row.getInt("id"), row.getString("name"))
}