On Friday, June 17, 2016 at 11:15:19 PM UTC-7, Douglas Teoh wrote:
>
> Sequel doesn't seem to work with IN equality for prepared statements.
>
> The code:
>
> ps = DB[:foo].where([[:id, [:$id]]]).prepare(:select, :select_bulk)
> ps.call(id: [1, 2, 3])
>
> With logging switched on, here is what happens:
>
> I, [2016-06-18T15:09:18.071668 #98572] INFO -- : (0.000409s) SET
> standard_conforming_strings = ON
> I, [2016-06-18T15:09:18.072000 #98572] INFO -- : (0.000196s) SET
> client_min_messages = 'WARNING'
> I, [2016-06-18T15:09:18.072179 #98572] INFO -- : (0.000129s) SET
> DateStyle = 'ISO'
> I, [2016-06-18T15:09:18.073650 #98572] INFO -- : (0.001131s) PREPARE
> select_bulk AS SELECT * FROM "validation" WHERE ("id" IN ($1))
> E, [2016-06-18T15:09:18.073924 #98572] ERROR -- :
> PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer:
> "[1, 2, 3]": EXECUTE select_bulk; [[1, 2, 3]]
> /Users/dteoh/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/adapters/postgres.rb:531:in
>
> `exec_prepared': PG::InvalidTextRepresentation: ERROR: invalid input
> syntax for integer: "[1, 2, 3]" (Sequel::DatabaseError)
>
>
This is expected, as PostgreSQL doesn't support prepared statements in such
a manner. This is because IN doesn't take an value, but a set of values,
and you can't provide a set of values as a bound variable. However "id IN
(1, 2, 3)" is the same as "id = ANY('{1,2,3}'::integer[])", so the
following should work:
DB.extension :pg_array
ps = DB[:foo].where{{id => ANY(:$id)}}.prepare(:select, :select_bulk)
ps.call(:id=>Sequel.pg_array([1,2,3], :integer))
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.
