Cannot execute statement with IN = (?)

[mottalrd]

Hello,

I have this list of ids retrieved from my cluster

irb(main):045:0* ids
=> [e7400c90-b7a8-11e3-aaad-d1ea1cb2f3bd, e74081c0-b7a8-11e3-aaad-d1ea1cb2f3bd, e73e85f0-b7a8-11e3-aaad-d1ea1cb2f3bd, e73f2230-b7a8-11e3-aaad-d1ea1cb2f3bd, e73f9760-b7a8-11e3-aaad-d1ea1cb2f3bd]

Now I want to execute this statement

statement = client.prepare('SELECT * from playlists where id IN (?) ORDER BY song_order;')

I have tried both

rows = statement.execute(ids)
NoMethodError: undefined method `value' for #<Array:0x000000015f7340>
    from /home/student/.rbenv/versions/1.9.3-p545/lib/ruby/gems/1.9.1/gems/cql-rb-1.2.1/lib/cql/protocol/encoding.rb:30:in `write_uuid'

and

rows = statement.execute(ids.join(', '))
NoMethodError: undefined method `value' for #<String:0x000000014b7458>
    from /home/student/.rbenv/versions/1.9.3-p545/lib/ruby/gems/1.9.1/gems/cql-rb-1.2.1/lib/cql/protocol/encoding.rb:30:in `write_uuid'

I ended up building the query string manually in ruby. What I am doing wrong?

To reproduce I am attaching the keyspace definition, the table definition, and some sample data (all cql)

CREATE KEYSPACE Test WITH REPLICATION =  { 'class' : 'SimpleStrategy', 'replication_factor' : 3 };

CREATE TABLE playlists (
  id timeuuid PRIMARY KEY,
  song_order int,
  title text,
  album text,
  artist text 
);

INSERT INTO playlists 
(id, song_order, title, artist, album)
VALUES 
(now(), 5, 'Hanno ucciso l’uomo ragno', 'Hanno ucciso l’uomo ragno', '883');

INSERT INTO playlists 
(id, song_order, title, artist, album)
VALUES 
(now(), 1, 'Bocca di rosa', 'Volume I', 'Fabrizio De Andre');

INSERT INTO playlists 
(id, song_order, title, artist, album)
VALUES 
(now(), 2, 'L italiano', 'L italiano', 'Toto Cutugno');

INSERT INTO playlists 
(id, song_order, title, artist, album)
VALUES 
(now(), 3, 'Il ragazzo della via Gluck', 'Il ragazzo della via Gluck', 'Adriano Celentano');

INSERT INTO playlists 
(id, song_order, title, artist, album)
VALUES 
(now(), 4, 'Uno su mille', 'Uno su mille', 'Gianni Morandi');

[iconara]

You're using the wrong syntax. id IN (?) means the same as id = ?. You need to drop the parentheses to make it work as a bound list: id IN ?.

Prepared statements have a metadata property that can show you how the statement has been parsed by the server. With id IN (?) you can see that a VARCHAR is expected, but with id IN ? it changes to LIST<VARCHAR>:

statement = client.prepare('SELECT * from playlists where id IN (?) ORDER BY song_order')
statement.metadata.each do |column|
  p [column.column_name, column.type]
end

vs.

statement = client.prepare('SELECT * from playlists where id IN ? ORDER BY song_order')
statement.metadata.each do |column|
  p [column.column_name, column.type]
end

([:list, :varchar] is cql-rb's representation of LIST<VARCHAR>)

[mottalrd]

Ok! thanks

[ajsharp]

Thanks, this is very helpful.

[ajsharp]

Also, it seems like cql-rb expects the client to build Cql::UUID objects rather than strings that are properly-formatted stringified UUID's. It looks like the encoder looks up the column metadata when performing a query and if the column is a UUID, it assumes you've passed it a UUID and invokes the value method on it, which String does not have.

[iconara]

Yes, if a column is a UUID or TIMEUUID you need to pass a real UUID to the prepared statement. UUIDs have a non-string encoding in the protocol so string representations of UUIDs don't work.