How to use sqlite3 UPDATE command correctly in ruby program

Ruby
1

Hi all,

I’m using sqlite3 for storage in my ruby program. I want to be able to
edit information in a single cell for a row that already exists. Here is
my current code:

def loan_book
puts “Which book is being loaned out?”
title_of_book = gets.chomp
book_to_loan = $db.execute(“SELECT * FROM books WHERE title = ?”,
title_of_book).first

if “#{book_to_loan[‘loanedTo’]}” == ‘’
puts “This book is not loaned out to anyone.”
sleep(1)
puts “Who are you loaning this book to?”
person_borrowing_book = gets.chomp
$db.execute(“UPDATE books SET loanedTo=? WHERE title = ?”,
person_borrowing_book, book_to_loan)
sleep(1)
puts “Alright! The book is now loaned out to
#{person_borrowing_book}”
sleep(1)
else
puts “This books is currently loaned out to
#{book_to_loan[‘loanedTo’]}”
end
end

When I run this program, and try the ‘Loan a book to someone’ choice, I
get an error when:

$db.execute(“UPDATE books SET loanedTo=? WHERE title = ?”,
person_borrowing_book, book_to_loan)

tries to run. Any thoughts as to why this is? I’ve included my code in
this post.

2

On Tue, Dec 17, 2013 at 7:19 PM, Mike V. [email protected]
wrote:

When I run this program, and try the ‘Loan a book to someone’ choice, I
get an error when:

$db.execute(“UPDATE books SET loanedTo=? WHERE title = ?”,
person_borrowing_book, book_to_loan)

tries to run. Any thoughts as to why this is? I’ve included my code in
this post.

What is the error you get, please?

3

Subject: How to use sqlite3 UPDATE command correctly in ruby program
Date: mer 18 dic 13 02:19:25 +0100

Quoting Mike V. ([email protected]):

When I run this program, and try the ‘Loan a book to someone’ choice, I
get an error when:

$db.execute(“UPDATE books SET loanedTo=? WHERE title = ?”,
person_borrowing_book, book_to_loan)

tries to run. Any thoughts as to why this is? I’ve included my code in
this post.

Rather than attaching the scripts, you should have copy-and-pasted the
exact error message that you get.

There are some database access libraries that expect that you pass
multiple parameters to SQL commands as an array:

$db.execute(“UPDATE books SET loanedTo=? WHERE title = ?”,
[person_borrowing_book,book_to_loan])

This might be your problem.

Carlo

4

Subject: Re: How to use sqlite3 UPDATE command correctly in ruby program
Date: mer 18 dic 13 12:27:40 +0100

Quoting Mike V. ([email protected]):

Here is the error I got:

/lib/sqlite3/statement.rb:39:in `bind_param’: no such bind parameter
(SQLite3::Exception)

What sort of an object is book_to_loan? Check it. Add a printout,
something as

p book_to_loan

When you pass bindable parameters, they should be exactly the same
number as the placeholders in your SQL statement.

It seems that you are passing two parameters, but you are apparently
passing to your query a variable that can be converted into an
array. The Sqlite lib (which joins all parameters and then flattens
the resulting array) finds more parameters than placeholders. This is
why you get that error.

Carlo

5

Sorry for the delay. Night time here in Massachusetts.

Here is the error I got:

/lib/sqlite3/statement.rb:39:in `bind_param’: no such bind parameter
(SQLite3::Exception)

I googled this error and surprisingly little came up.

Also, I changed my code to the following:

$db.prepare(“UPDATE books SET loanedTo=? WHERE title = ?”)
$db.bind_params(person_borrowing_book, book_to_loan)

and got the following error:

undefined method `bind_params’ for #SQLite3::Database:XXXXXXXXXXXXX
(NoMethodError)