From: Brian Candler <b.candler@...>
Date: 2010-11-29T20:39:23+09:00
Subject: Re: SQLite3/Sinatra not returning results

Alex Gutteridge wrote in post #963848:
> On Thu, 25 Nov 2010 20:35:13 +0900, "zimbatm ..." <jonas@pfenniger.name>
> wrote:
>> $ rvm use --default ruby-1.9.2
>>   zimbatm
> Thanks. It turned out to be an String encoding issue. Sinatra was
> returning an "ASCII-8BIT" encoded string, while the SQLite3 db is
> "US-ASCII" encoded. Manually setting the Sinatra string encoding to
> "US-ASCII" got the DB query to match.

Ergh. I can replicate this, with ruby 1.9.2p0 (rvm) + sqlite3-ruby-1.3.2 
(gem) under Ubuntu Lucid x86_64.

ruby-1.9.2-p0 > require 'sqlite3'
 => true
ruby-1.9.2-p0 > db = SQLite3::Database.new("foo.db")
 => #<SQLite3::Database:0x00000002631bc0>
ruby-1.9.2-p0 > db.execute("create table foo (id integer auto_increment, 
bar varchar(255))")
 => []
ruby-1.9.2-p0 > db.execute("insert into foo (bar) values ('hello')")
 => []
ruby-1.9.2-p0 > s1, s2, s3 = "hello", 
"hello".force_encoding("US-ASCII"), "hello".force_encoding("ASCII-8BIT")
 => ["hello", "hello", "hello"]
ruby-1.9.2-p0 > s1 == s2
 => true
ruby-1.9.2-p0 > s1 == s3
 => true
ruby-1.9.2-p0 > db.execute("select * from foo where bar=?", s1)
 => [[nil, "hello"]]
ruby-1.9.2-p0 > db.execute("select * from foo where bar=?", s2)
 => [[nil, "hello"]]
ruby-1.9.2-p0 > db.execute("select * from foo where bar=?", s3)
 => []

So s1 == s3, but the two queries give different results?? This is 
barking mad. Having said that, I can't find anything in the sqlite3-ruby 
documentation which says how queries may be affected by encodings, so 
the behaviour is undefined.

Sinatra's documentation also doesn't seem to mention the encoding that 
your |gene| parameter will have, so you have to find out by 
trial-and-error that it's ASCII-8BIT. You might have guessed this 
because the data comes from a socket and sockets get ASCII-8BIT by 
default - but Sinatra might have decided to parse the 
Content-Type:...encoding parameter and apply that.

In ruby 1.8 the value "hello" is just 5 bytes, with no hidden dimension 
to worry about.

-- 
Posted via http://www.ruby-forum.com/.