CODEWITHSUNDEEP
rubysqliterubygems
artze
artze

Reputation: 581

Return Values of #execute and #query methods of sqlite3 gem

I am working with sqlite3 database in ruby with the help of sqlite3 gem (I'm trying to gain a better understanding of using sqlite3 gem without the use of ActiveRecords). I have an sql query below:

SELECT id FROM contacts WHERE first_name = 'Charles'

From which I'm expecting it to return the value '5'

execute

$db.execute('SELECT id FROM contacts WHERE first_name = ?', 'Charles') do |result|
  puts result
end

Above will print '5', but will return an sqlite3 object. I couldn't seem to get the execute method to return the value '5'.

query

$db.query('SELECT id FROM contacts WHERE first_name = ?', 'Charles') do |result|
  result.first.first
end

The closest I could get is to use the query method to return the value '5' (above), but this requires .first.first which seems convoluted.

Is there a better way for me to get the value I needed? i.e. perhaps through accessing the sqlite3 object?

Upvotes: 1

Views: 2958

Answers (2)

spickermann
spickermann

Reputation: 106882

I would use to_i:

$db.execute('SELECT id FROM contacts WHERE first_name = ?', 'Charles') do |result|
  puts result.to_i
end

The reason why you need to call to_i is, because the sqlite gem doesn't know the data type of the returned value. To identify the datatype it would need to make another query for the table definition for example. This is something that the sqlite gem doesn't do for you like an ORM like ActiveRecord would do.

Btw. you find the reason why puts returns the value too in the same files, because there is a to_s method defined that is called by puts on result when printing.

Upvotes: 1

Ursus
Ursus

Reputation: 30056

Try this one

$db.results_as_hash = true
$db.execute('SELECT id FROM contacts WHERE first_name = ?', 'Charles') do |row|
  puts row['id']
end

You want a method. Make something like

def find_id_by_first_name(first_name)
  $db.results_as_hash = true
  $db.execute('SELECT id FROM contacts WHERE first_name = ?', first_name) do |row|
    return row['id']
  end
end

But anyway, you could have a problem with your query. If first_name has not a unique index, this query could return more than one row. So this method doesn't make so much sense. This just returns the first matching result.

Upvotes: 1

Related Questions