On Tuesday, February 26, 2019 at 1:28:07 PM UTC-8, Ho Gi Hung wrote:
>
> Howdy. I've been asked to optimize how our app does DB calls to use bound
> variables. For things that only need a single string this have been farily
> easy/straight forward. However I'm having issues when trying to convert
> the
> DB queries to prepared statements that will take a list of values.
>
>
> # Works
> some_id = DB[:some_table].where(name:
> my_array_variable).select(:some_id).first
> -> above knows my_array_variable is an array and creates SQL ~ ... where
> name IN (xxx,yyy,zzz)
>
>
> # Trying to get the following to work
> ## MODEL (loaded when app starts)
> DB["select some_id from some_table where name in (?)",
> :$name].prepare(:select, :select_some_id_by_value)
> -OR-
> DB["select some_id from some_table where name in ?",
> :$name].prepare(:select, :select_some_id_by_value)
>
> ## MAIN CODE (called when needed)
> some_id = DB.call(:select_some_id_by_value, name: my_array_variable).first
>
>
> NOTE: my_array_variable is a ruby array, eg. ["value1", "value2", "value3"]
>
>
> I've also tried:
> some_id = DB.call(:select_some_id_by_value, name:
> my_array_variable.join(",").first
>
> along with countless variations of trying to use Sequel.lit,
> DB(x).literal, etc.
>
> I'm sure there is something easy I'm missing but I'm lost.
>
> Any pointers would be appreciated. Thanks,
>
> HoGi...
>
> P.s. this is Jruby 1.7.26 with Sequel 5.12.0
>
You can't use arrays of values as bound variable (not supported by pretty
much any database). If you are using PostgreSQL, you could refactor your
code to use a PostgreSQL array (see the PostgreSQL documentation, the
Sequel pg_array/pg_array_ops extensions, and the Sequel postgres adapter
tests), which would allow you to use the Sequel::Postgres::PGArray instance
as a bound variable (though the syntax would be different). Otherwise
you'll have to skip using a prepared statement for these queries.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
