sqlite3 + ruby: using prepare and "in"

[Robert Citek]

How can I quote a list of items to be used in a SQL where ... in
clause to be used in a prepare method?

I would very much like to to create an arbitrary-length list and then
send the list to the prepared sql statement in an execute method.
However, what I have tried is not working. Here's a section from an
irb session:

irb(main):061:0> sql_set = <<-eof
irb(main):062:0" select *
irb(main):063:0" from foobar
irb(main):064:0" where bar in (?)
irb(main):065:0" eof
=> " select *\n from foobar\n where bar in (?)\n"
irb(main):066:0> prep=db.prepare(sql_set)
=> #<SQLite3::Statement:0xb7cb52d4
@driver=#<SQLite3::Driver::Native::Driver:0xb7b4a624 @trace={},
@busy_handler={}, @authorizer={}, @callback_data={}>, @results=nil,
@closed=false, @handle=#<SWIG::TYPE_p_sqlite3_stmt:0xb7cb5284>,
@db=#<SQLite3::Database:0xb7b539b8
@driver=#<SQLite3::Driver::Native::Driver:0xb7b4a624 @trace={},
@busy_handler={}, @authorizer={}, @callback_data={}>,
@transaction_active=false, @closed=false,
@handle=#<SWIG::TYPE_p_sqlite3:0xb7b4a598>, @translator=nil,
@statement_factory=SQLite3::Statement, @type_translation=false,
@results_as_hash=false>, @remainder="", @columns=nil>
irb(main):067:0> foo=[1,2]
=> [1, 2]
irb(main):068:0> puts foo.join(",")
1,2
=> nil
irb(main):069:0> prep.execute(foo.join(",")).each do |row|
irb(main):070:1* puts row.join("\t")
irb(main):071:1> end
=> nil

Notice that I merely get 'nil' instead of an error or even one of the
possible result rows, which makes me suspect that everything is
syntactically correct and that it is the formatting of the list that
is not correct. The other possibility is that prepare is doing
something differently than what I think its doing.

In any case, I'm curious to know if what I am trying to do can be done
or if I should use one of the other ways. The full code is below.

Thanks in advance.

Regards,
- Robert

#!/usr/bin/env ruby
require 'sqlite3'
db = SQLite3::Database.new( "test.db" )

# create the database
sql = <<-eof
CREATE TABLE foobar (foo text, bar int);
INSERT INTO "foobar" VALUES('hello',1);
INSERT INTO "foobar" VALUES('world',2)
eof
db.execute_batch(sql)

# explicit : works
sql_set = <<-eof
select *
from foobar
where bar in (1,2)
eof
db.execute(sql_set) do |row|
puts row.join("\t")
end

# list - interpolated : works
foo=[1,2]
sql_set = <<-eof
select *
from foobar
where bar in (#{foo.join(",")})
eof
db.execute(sql_set) do |row|
puts row.join("\t")
end

# list - individual prepared : works
sql_set = <<-eof
select *
from foobar
where bar in (?,?)
eof
prep=db.prepare(sql_set)
foo=[1,2]
prep.execute(foo).each do |row|
puts row.join("\t")
end

# list - single prepared : does not work
sql_set = <<-eof
select *
from foobar
where bar in (?)
eof
prep=db.prepare(sql_set)
foo=[1,2]
puts foo.join(",")
prep.execute(foo.join(",")).each do |row|
puts row.join("\t")
end

[Ed Howland]

Robert,

I don't think it can be done. In your example you are really passing a
single string which gets bound to the first parameter ? in the where
clause. In essence you are doing this:

stmt = db.prepare("select * from foobar where bar in (?)")
stmt.bind("1,2")

Then when the query executes it is searching for where bar = "1,2".
Since they are ints, they don't match.

I tried this:
irb(main):042:0> stmt.bind_params(1,2)
=> [1, 2]
irb(main):043:0> stmt.execute!
=> [["hello", "1"]]

There are two variables passed to bind, but only one placeholder, so
only the first (1) matches.

stmt = db.prepare("select * from foobar where bar in (?,?)")
=> #<SQLite3::Statement:0x0000010125e028
@db=#<SQLite3::Database:0x000001010bf4d0
@driver=#<SQLite3::Driver::Native::Driver:0x000001010bdd68
@callback_data={}, @authorizer={}, @busy_handler={}, @trace={}>,
@statement_factory=SQLite3::Statement,
@handle=#<SWIG::TYPE_p_sqlite3:0x000001010bdc18>, @closed=false,
@results_as_hash=false, @type_translation=false, @translator=nil,
@transaction_active=false>,
@driver=#<SQLite3::Driver::Native::Driver:0x000001010bdd68
@callback_data={}, @authorizer={}, @busy_handler={}, @trace={}>,
@closed=false, @columns=nil, @results=nil,
@handle=#<SWIG::TYPE_p_sqlite3_stmt:0x0000010125dfb8>, @remainder="">
irb(main):050:0> stmt.bind_params(*foo)
=> [1, 2]
irb(main):051:0> stmt.execute!
=> [["hello", "1"], ["world", "2"]]

As you note in your full example.

Conclusion: You will have to construct your sql statement on the fly,
and ignore binding variables. I;ve often done this:

db.query! "select * from foobar where bar in (Z)".gsub(/Z/,
foo.join(",")) do |row|
irb(main):024:1* puts row.join("\t")
irb(main):025:1> end
hello 1
world 2
=> nil

Note: loaded qbang!.rb[1] and selected the db first.

As nothed in your previous thread.

Ed
**** Standard extreme caveat applies: I know nothing at all about
sqlite3-ruby. ****

--
Ed Howland
http://greenprogrammer.blogspot.com
http://twitter.com/ed_howland

[Robert Citek]

On Mon, Nov 9, 2009 at 10:07 PM, Ed Howland <ed.ho...@gmail.com> wrote:
> I don't think it can be done. In your example you are really passing a
> single string which gets bound to the first parameter ? in the where
> clause.

That's what I afraid of, but was hoping there might be chance to make
it work. Oh, well. Fortunately, creating a new query each iteration
isn't too bad.

Thanks for looking at this as well as your examples of how you probed
ruby to figure out what it is doing.

Regards,
- Robert

[Craig Buchek]

I've run into the same problem with PHP PDO. It sucks, because you
don't get the automatic escaping that prepared statements give you.
And trying to do escaping by hand can be very error prone. I guess
you'd just escape each item in the list, then join them. For SQLite3,
it looks like Database::quote can be used to escape strings; not sure
if it'd work for integers or other types.

Craig