How can I select a value from a database using Ruby and SQLite3?

Question

I am having trouble returning a single value from a database using SQLite3 and Ruby.

A bit of context: I have a table of people and I would like to be able to query the database and get back the first name of someone in that table. I am using Rubymine with the SQLite3 gem.

When I run this code:

name = @db.execute <<-SQL
    SELECT firstname FROM tbl_people WHERE email_address = '#{email_address}';
  SQL
  puts name

I get the following ouput:

{"firstname"=>"james", 0=>"james"}

When really the output I am expecting is:

james

If anyone can even point me in the right direction here it would be very much appreciated.

Cheers, James

Ian · Accepted Answer · 2017-02-14 11:58:01Z

4

It sounds like you want to return the first value of the first row of a result set, and discard all other values and rows. The Database#get_first_value is what you want.

Also, for security you shouldn't inject strings into your SQL; allow the library to handle that for you.

query = "SELECT firstname FROM tbl_people WHERE email_address = ?;"
name = @db.get_first_value query, email_address
puts name

answered Feb 14, 2017 at 11:58

Ian

11.9k3 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shadow · Accepted Answer · 2016-03-02 02:32:52Z

3

The execute() method creates a resultset, not just a single scalar value. You need to process the resultset to get the single value out of it.

name.each do |row|
    puts row['firstname']
end

answered Mar 2, 2016 at 2:32

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

1 Comment

James Webb Over a year ago

THANK YOU!! This helps so much, I thought it would be something simple like this but didn't really understand what I was doing, this explained it brilliantly! I would have up voted your answer but I don't have enough reputation :(

Collectives™ on Stack Overflow

How can I select a value from a database using Ruby and SQLite3?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related