Ah, then I feel better - it's not me being a nuckle head. Thanks a bunch for the quick response!
Hogi... On Tue, Feb 26, 2019, 5:38 PM Jeremy Evans <[email protected]> wrote: > 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. > -- 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.
