Re: [sqlite3-ruby] What is wrong with this primitive query?

[Aaron Patterson]

On Mon, Mar 18, 2013 at 9:44 AM, Paweł Kosiński <pskos...@gmail.com> wrote:

db.execute 'SELECT * FROM users WHERE user = ? and pass = ?', user, pass

It is returning empty array, why?

I tried also:

db.execute 'SELECT * FROM users WHERE user = ? and pass = ?', [user, pass]

Empty array too.

Query is for sure correct and works in sqlite3 console.

What is the encoding of the `user` and `pass` variables?

--
Aaron Patterson
http://tenderlovemaking.com/

[Paweł Kosiński]

Oh… yes, I found that it was "the problem" so I removed my post, I didn't see that someone answered, sorry.

Anyway I found this: http://www.ruby-forum.com/topic/476119

I tried solution: user.force_encoding('US-ASCII') (and same for pass) and query works.

Then I checked what encoding have all string, and well… I wanted to make new thread but I think that I will write about it here. :)

So… I am using sqlite3-ruby in Sinatra. Strings in my app have encoding UTF-8. Database has encoding UTF-8 too. Both "PRAGMA encoding" in sqlite3 console and encoding property of Database object from sqlite3-ruby are showing that encoding is UTF-8.

So it looks a bit strange… Is sqlite3-ruby somehow converting encoding on query's way from app to database? Or what else could be a reason?

I tried to use Sequel gem in same app and with Sequel it works well. force_encoding is not good solution in my case because I just need UTF-8 (in some queries, in example above not).

[Paweł Kosiński]

Ah, sorry, nevermind.

I didn't check one variable, the only one having encoding ASCII-8BIT. I just converted its encoding to UTF-8 and now works well…

Sorry, my fault. :)

[Paweł Kosiński]

But from other side… couldn't sqlite3-ruby check if string has same encoding as Database.encoding and if not then try to convert? Or it is not task for it?

[Aaron Patterson]

ASCII-8BIT strings won't be transcoded automatically since ASCII-8BIT effectively means "we don't know the encoding".  Strings with known encodings will be automatically transcoded.

https://github.com/luislavena/sqlite3-ruby/issues/45#issuecomment-1895378

Hope that helps!

--
You received this message because you are subscribed to the Google Groups "sqlite3-ruby" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlite3-ruby...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Paweł Kosiński]

Ok, thank you, now I understand. :)

[Aaron Patterson]

No problem!  If you're getting ASCI-8BIT strings from some library, it's likely a bug.  Do you know where these strings are coming from?