Database using SQLite3 (Ruby) is not inserting

Question

I am following a tutorial trying to learn the basics of connecting Ruby to a database. Currently, I have a program that receives an sms using twilio, and sends back a message based on what you said. I am trying to store the phone number and many other parts of the message to a database using SQLite3, but there are no entries to the database whenever I run the code. Here it is.

require 'twilio-ruby'
require 'google_places'
require 'sinatra'
require 'dotenv'
require 'sqlite3'

begin
    db = SQLite3::Database.open "test.db"
    db.execute "CREATE TABLE IF NOT EXISTS Entries(Id INTEGER PRIMARY KEY AUTOINCREMENT, Searched TEXT, Place TEXT, Number BLOB)"
    begin
db.execute "CREATE TABLE IF NOT EXISTS Entries(Id INTEGER PRIMARY KEY AUTOINCREMENT, Searched TEXT, Place TEXT, Number BLOB)"
db.execute("INSERT INTO Entries (Searched, Place, Number) VALUES(?,?,?)", @incoming, @best_place_fmt, @phonenumber)
rescue SQLite3::Exception => e 
    puts "Exception occurred"
    puts e
#ensure
#    stm.close if stm
#   db.close if db
end

All of the twilio functions work and the message is received and a message is sent, but none of the database commands are actually editing the file. Thanks for the help!

Answer 1

When I execute your DB-related code I get no error and the database is filled.

If I add a select like in this code:

require 'sqlite3'

begin
    db = SQLite3::Database.open "test.db"
    db.execute "CREATE TABLE IF NOT EXISTS Entries(Id INTEGER PRIMARY KEY AUTOINCREMENT, Searched TEXT, Place TEXT, Number BLOB)"
    db.execute "INSERT INTO Entries(Searched) VALUES ('Is')"
    db.execute "INSERT INTO Entries(Place) VALUES ('This')"
    db.execute "INSERT INTO Entries(Number) VALUES ('Working')"
    (db.execute "Select * from Entries").each{|dataset|
      p dataset
    }
rescue SQLite3::Exception => e 
    puts "Exception occurred"
    puts e
#ensure
#    stm.close if stm
#   db.close if db
end

then I get:

[1, "Is", nil, nil]
[2, nil, "This", nil]
[3, nil, nil, "Working"]

How did you see, that there is no entry in the DB?

Are you sure you check the right test.db? You can check the actual directory with p Dir.pwd.

---

Example after the modification of the question:

If I take your insert-command works, when the variables have a value

require 'sqlite3'
File.delete('test.db')  #Delete previous result and start with initial example.
@incoming = 'in'
@best_place_fmt = 'fm'
@phonenumber = 123456789
begin
    db = SQLite3::Database.open "test.db"
    db.execute "CREATE TABLE IF NOT EXISTS Entries(Id INTEGER PRIMARY KEY AUTOINCREMENT, Searched TEXT, Place TEXT, Number BLOB)"
    db.execute("INSERT INTO Entries (Searched, Place, Number) VALUES(?,?,?)", @incoming, @best_place_fmt, @phonenumber)
    (db.execute "Select * from Entries").each{|dataset|
      p dataset
    }
rescue SQLite3::Exception => e 
    puts "Exception occurred"
    puts e
#ensure
#    stm.close if stm
#   db.close if db

end

My result:

 [1, "in", "fm", 123456789]

---

I asked in a comment to the question if you ever thougt about the usage of an ORM. Just to give you an impression below an example with sequel:

@incoming = 'in'
@best_place_fmt = 'fm'
@phonenumber = 123456789

require 'sequel'
db = Sequel.sqlite("test.db") #Here you could use also another DB
db.tables.each{|table| db.drop_table(table)}
db.create_table :entries2 do
  primary_key :id
  field :searched, :type => :text
  field :place, :type => :text
  field :number, :type => :blob
end
db[:entries2].insert(nil,@incoming, @best_place_fmt, @phonenumber)
puts db[:entries2].all #{:id=>1, :searched=>"in", :place=>"fm", :number=>"123456789"}