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 minimal reproducible example for more info :) — Jeff
– Jeff, Commented Oct 3, 2016 at 3: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
– mu is too short, Commented Oct 3, 2016 at 4:07
Does the SQL work if you paste it in to the SQLite CLI?sqlite.org/cli.html — Kris
– Kris, Commented Oct 3, 2016 at 9:52
Beside your actual problem: Did you ever think about the usage of an ORM like Sequel (my favorite) or Active Record (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
– knut, Commented Oct 4, 2016 at 12:09

knut · Accepted Answer · 2016-10-04 17:08:52Z

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?

Collectives™ on Stack Overflow

Database using SQLite3 (Ruby) is not inserting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related