From: Alex Gutteridge <alexg@...>
Date: 2010-11-18T20:18:42+09:00
Subject: SQLite3/Sinatra not returning results

Hi,

This has been doing my head in all morning - I wonder if anyone can help
me work out where my problem is:

I have the following code within a Sinatra app:

$probe_db_file = "data/lumiHumanIDMapping.sqlite"
$probe_table = "HumanHT12_V4_0_R2_15002873_B"
$data_db_file  = "data/rpediff.db"

$probe_db = SQLite3::Database.new($probe_db_file)
$data_db  = SQLite3::Database.new($data_db_file)

get '/:gene' do |gene|
    
  STDOUT.puts gene.inspect
  STDOUT.puts gene.class
  STDOUT.puts gene == "PAX6"
      
  probes = $probe_db.execute("SELECT Probe_ID,Symbol from #{$probe_table}
WHERE Symbol = ?",gene).map{|row| row[0]}
  
  STDOUT.puts "#{Time.now} - Gene: #{gene}"
  STDOUT.puts "#{Time.now} - Probes: #{probes.inspect}"

  gene = "PAX6"
    
  probes = $probe_db.execute("SELECT Probe_ID,Symbol from #{$probe_table}
WHERE Symbol = ?",gene).map{|row| row[0]}
  
  STDOUT.puts "#{Time.now} - Gene: #{gene}"
  STDOUT.puts "#{Time.now} - Probes: #{probes.inspect}"

end

Running on my development machine (OSX, Ruby 1.8.7, Sinatra 1.1,
sqlite3-ruby 1.3.2) this logs (as expected):

"PAX6"
String
true
Thu Nov 18 11:12:09 +0000 2010 - Gene: PAX6
Thu Nov 18 11:12:09 +0000 2010 - Probes: ["ILMN_1789905", "ILMN_2314140"]
Thu Nov 18 11:12:09 +0000 2010 - Gene: PAX6
Thu Nov 18 11:12:09 +0000 2010 - Probes: ["ILMN_1789905", "ILMN_2314140"]

Running on my production machine (Linux, Ruby 1.9.2, Sinatra 1.1,
sqlite3-ruby 1.3.2) the same query logs:

"PAX6"
String
true
2010-11-18 11:08:48 +0000 - Gene: PAX6
2010-11-18 11:08:48 +0000 - Probes: []
2010-11-18 11:08:48 +0000 - Gene: PAX6
2010-11-18 11:08:48 +0000 - Probes: ["ILMN_1789905", "ILMN_2314140"]

So on this machine the first query returns an empty result for some
reason, even though the rows it should match *are* in $probe_table (as you
can see I can find them if I set the 'gene' parameter 'manually'). All I
can think of is that somewhere in Sinatra/sqlite3-ruby the 'gene' variable
is not quite the simple String it claims to be, but the fact that this only
seems to happen on Ruby 1.9.2 has got me baffled! Both Probe_ID and Symbol
are TEXT columns in Sqlite3 BTW.

-- 
Alex Gutteridge