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!

Hi, and welcome to SO - it seems like the twilio stuff is essentially irrelevant to the problem you're facing, would that be right? If so, you can make life easier for answerers (and therefore make it more likely you'll get a useful response) by trimming your answer down to just the DB bit of the code - check out [MCVE] for more info :) — Jeff, Oct 03 '16 at 03:32
When are you expecting the SQLite code to execute? What are you expecting the current directory to be when it does? Is `test.db` created? What are the permissions on `test.db`? — mu is too short, Oct 03 '16 at 04:07
Does the SQL work if you paste it in to the SQLite CLI?https://www.sqlite.org/cli.html — Kris, Oct 03 '16 at 09:52
Beside your actual problem: Did you ever think about the usage of an ORM like [Sequel](http://sequel.jeremyevans.net) (my favorite) or [Active Record](http://guides.rubyonrails.org/active_record_basics.html) (You don't need rails to use it, the ORM are independant and can be used without rails.) Advantage: You may change the DB later. — knut, Oct 04 '16 at 12:09

knut · Accepted Answer · 2016-10-04T17:08:52.383

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"}

Yes it turns out I was not opening up the database when using the sqlite3 CLI. I have updated my code to what is now there and it creates the table, but none of the variables are being put into the database. Is there some special way to put variables into the database entry? — Parker Harris, Oct 04 '16 at 01:53

Database using SQLite3 (Ruby) is not inserting

1 Answers1