CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlactiverecordrails-activerecord
Jeremy Fox
Jeremy Fox

Reputation: 2668

Why is ActiveRecord/PostgreSQL not finding my data in the database?

I'm sure this is something simple I'm overlooking but since I've been dealing with this strange issue for a few days now I'm asking for help.

Here is my apps setup and the issue:

A Rails 3.2.13 app with the pg gem and the following db scheme:

create_table "clients", :force => true do |t|
  t.string   "uuid"
  t.string   "private_key"
  t.datetime "created_at",  :null => false
  t.datetime "updated_at",  :null => false
end

add_index "clients", ["private_key"], :name => "index_clients_on_private_key"
add_index "clients", ["uuid"], :name => "index_clients_on_uuid"

I currently have two rows in my "clients" database. They pictured below:

enter image description here

The issue

When I perform a simple "SELECT * FROM clients;" using the RubyMine database console I get the rows pictured in the screen shot above. When I perform a "SELECT * FROM clients WHERE id = 11;" I get that row, all is well there. However, the issue is that when I try to perform a "SELECT * FROM clients WHERE private_key = "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF";" it fails and shows the following in the console output...

sql> SELECT * FROM clients WHERE private_key = "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF"
[2013-04-17 20:09:51] [42703] ERROR: column "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF" does not exist
  Position: 43

I've also tried pulling this row out of the db by utilizing the active record helper methods...

Client.exists?(:private_key => token)
Client.find_by_private_key(token)

However, nothing works, it never finds the record that does exist in the db. Does anyone have any idea what is going on here? All I need to be able to do is utilize the active record helpers, specifically the "Client.exists?(:private_key => token)" to be able to check and see if the supplied token exists in the db.

Thanks for your help!

Update 4/18/13 @ 9:30am

I just tried Client.find(12) just to see if it would find the record by id, and this works. This doesn't help me understand why I still can't use Client.exists?(:private_key => token) or Client.find_by_private_key(token).

Update 4/19/13 @ 9:15am

The answer to this problem was that the code generating my private_key value was adding a return/white space to the end of the value. So when trying to query the db for row based on a private_key it was always failing to find anything.

To fix the problem I added a gsub to the end of the code that generates the private_key, like this...

private_key = SecurityHelper.encrypt_private_key(client_uuid).gsub(/\s+/, "")

This strips all white space from the generated private key and solved the problem.

Upvotes: 2

Views: 289

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324295

You're using the wrong quoting style. ANSI SQL quoting as used in PostgreSQL is:

 "SELECT * FROM clients WHERE private_key = 'MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF';"

Note the single quotes.

It isn't clear how the first statement even got parsed by Ruby, since the inner double quotes would've ended the quoted string, leading to:

"SELECT * FROM clients WHERE private_key = "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF";"
                                           ^^^^
                                           should be Ruby syntax error here

I don't really do Rails so I can't speak for the correct Rails syntax to do this check via ActiveRecord, though.

Upvotes: 1

Fred
Fred

Reputation: 8602

Have you tried 

Client.exists?(:private_key => "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF")

Upvotes: 0

Related Questions