Contents

@ Bind an Array to IN-condition

Bind an Array to IN-condition
=============================

Binding an arbitrary-length array to IN-condition is not simple.
You need to create an SQL statement containing a comma-separated
list whose length is same with the input data.

Example:

    ids = [ ... ] # an arbitrary-length array containing user IDs.
    
    place_holder_string = Array.new(ids.length) {|index| ":id_#{index}"}.join(', ')
    # place_holder_string is:
    #   ":id_0" if ids.length == 1
    #   ":id_0, :id_1" if ids.length == 2
    #     ...
    cursor = conn.parse("select * from users where id in (#{place_holder_string})")
    ids.each_with_index do |id, index|
      cursor.bind_param("id#{index}", id) # bind each element
    end
    cursor.exec()

However this is awkward. So {OCI8.in_cond} was added in ruby-oci8 2.2.2.
The above code is rewritten as follows:

    ids = [ ... ] # an arbitrary-length array containing user IDs.
    
    in_cond = OCI8::in_cond(:id, ids)]
    cursor = conn.exec("select * from users where id in (#{in_cond.names})", *in_cond.values)

or

    ids = [ ... ] # an arbitrary-length array containing user IDs.
    
    in_cond = OCI8::in_cond(:id, ids, Integer) # set the data type explicitly
    cursor = conn.exec("select * from users where id in (#{in_cond.names})", *in_cond.values)

Version data entries

6 entries across 6 versions & 1 rubygems

Version	Path
ruby-oci8-2.2.3	docs/bind-array-to-in_cond.md
ruby-oci8-2.2.3-x86-mingw32	docs/bind-array-to-in_cond.md
ruby-oci8-2.2.3-x64-mingw32	docs/bind-array-to-in_cond.md
ruby-oci8-2.2.2-x64-mingw32	docs/bind-array-to-in_cond.md
ruby-oci8-2.2.2-x86-mingw32	docs/bind-array-to-in_cond.md
ruby-oci8-2.2.2	docs/bind-array-to-in_cond.md