SQLite3 acting very strangely

good evening (and morning, also!) everyone. this is my first attempt at using sqlite3 for a rails app, and so far, not so good

when I try to save a very basic record from the controller, it gives me back an ActiveRecord::StatementInvalid error.

"that doesn't sound very nice"

so I switched the app to mysql and sure enough, it went through without a hitch. unsatisfied, I switched back to sqlite3 and fired up the console

  mike@lua:~/Rails/hotspots$ ./script/console   Loading development environment.   >> spot = Hotspot.new :title => "testing", :address => "222 water st"   => #<Hotspot:0xb732af48 @attributes={"title"=>"testing", "notes"=>"NULL", "user_id"=>0, "lat"=>"NULL", "address"=>"222 water st", "long"=>"NULL"}, @new_record=true>   >> spot.save   => true   >>

"???"

and here's the source, right from the controller's mouth:

  def create     if session[:user]       spot = Hotspot.new :title => "testing", :address => "222 water st"       spot.save     end   end

and here's what that gives me:

Processing ApiController#create (for 127.0.0.1 at 2007-05-09 23:30:52) [POST]   Session ID: b859a7e645f2c60ddd4747368a677b04   Parameters: {"title"=>"asdf", "action"=>"create", "notes"=>"", "controller"=>"api", "address"=>"asdf", "lat"=>"47.54501765940571", "long"=>"-52.7204704284668"}   User Load (0.000560) SELECT * FROM users WHERE (login = 'mikew' and password = 'test') LIMIT 1   SQL (0.000000) SQLite3::SQLException: SQL logic error or missing database: INSERT INTO hotspots ("title", "notes", "user_id", "address", "lat", "long") VALUES('testing', 'NULL', 0, '222 water st', 'NULL', 'NULL')

ActiveRecord::StatementInvalid (SQLite3::SQLException: SQL logic error or missing database: INSERT INTO hotspots ("title", "notes", "user_id", "address", "lat", "long") VALUES('testing', 'NULL', 0, '222 water st', 'NULL', 'NULL'))

now I can't make any sense of what's going on

Have you checked your database.yml file? I suspect it might not be set up properly.

The relevant section of database.yml should look like: adapter: sqlite3 dbfile: db/development.db

Note that SQLite uses "dbfile" rather than "database" to point to the database, and "dbfile" points to the actual file in the filesystem. There's no "username" or "password" fields, since these aren't used by SQLite.

For what it's worth, once you get these sort of issues sorted out with SQLite, I've found it's an extremely robust solution for small Rails sites.

Regards

Dave M.

the rails app was generated with `rails appname --database=sqlite3`, so it created the database and setup the database config for me. you can see that right above the troublesome query, it even loads data from the user table.

the schema is all controlled by db:migrate as well, so the MySQL and SQLite databases are identical

here is my database.yml file though:

mike@lua:~/Rails/hotspots$ cat config/database.yml # SQLite version 3.x # gem install sqlite3-ruby development:   adapter: sqlite3   database: db/development.sqlite3   timeout: 5000

# Warning: The database defined as 'test' will be erased and # re-generated from your development database when you run 'rake'. # Do not set this db to the same as development or production. test:   adapter: sqlite3   database: db/test.sqlite3   timeout: 5000

production:   adapter: sqlite3   database: db/production.sqlite3   timeout: 5000

my guess is that the 'NULL' values are the clues.

null values in the database should come back as nil in rails

ex:

u = User.find(2)

=> #<User:0xb740fd54 @attributes={"created_on"=>"2007-04-30 06:34:09", "last_login"=>nil, "name"=>"Bob Jones", "login_flag"=>"0", "updated_on"=>"2007-04-30 06:34:09", "active_flag"=>"1", "id"=>"2", "admin_flag"=>"1", "station"=>nil, "login"=>"bjones"}>

my guess is that you are storing the actual string NULL in the database somehow when you shouldn't be and mysql is being forgiving about it whereas sqlite3 is not.

look in your model or your migrations for where you might be setting default values incorrectly.

other than that, nothing else jumps out as being strange or wrong.

oh, when I do fetch the record in the console, those null values come back as nil for ruby

what I don't understand is how the same code can be used in the console and the controller and work in one and not the other

the models are blank except for the has_many/belongs_to/habtm relationships. and the migrations don't set any default values

Do you know which version of sqlite3 you're using?

This might help:

  Ruby on Rails — Using SQLite3 with Rails

d.

well, something is causing the empty attributes to be set to "NULL" instead of nil.

here's some data i have in an sqlite3 db

select * from topics where parent_id IS NULL limit 1;

id name url_name parent_id ---------- ---------- ---------- ---------- 1 Technology technology

script/console

t = Topic.find(1)

=> #<Topic:0x382ce10 @attributes={"name"=>"Technology", "id"=>"1", "url_name"=>"technology", "parent_id"=>nil}>

t = Topic.new :name => "Foo", :url_name => "foo"

=> #<Topic:0x382910c @new_record=true, @attributes={"name"=>"Foo", "parent_id"=>nil, "url_name"=>"foo"}>

another thing you might try is to go into the sqlite3 console and check out the schema for the table $>sqlite3 db/development_db

.schema <table_name>

and see what the default is

let us know what you find out

oh sorry chris, you were right. I thought I saw it returning 'nil' values, but it turns out it sets and returns NULL values

$ ./script/console Loading development environment.

spot = Hotspot.new :title => "test", :address => "2 signal hill rd"

=> #<Hotspot:0xb73ebea0 @attributes={"created_on"=>nil, "title"=>"test", "notes"=>"NULL", "user_id"=>0, "lat"=>"NULL", "address"=>"2 signal hill rd", "long"=>"NULL"}, @new_record=true>

spot.save

=> true

Hotspot.find :first

=> #<Hotspot:0xb7364e28 @attributes={"created_on"=>"2007-05-11 11:43:13", "title"=>"test", "id"=>"1", "notes"=>"NULL", "user_id"=>"0", "address"=>"2 signal hill rd", "lat"=>"NULL", "long"=>"NULL"}>

here's the schema:

$ sqlite3 db/development.sqlite3 SQLite version 3.3.13 Enter ".help" for instructions

.schema hotspots

CREATE TABLE hotspots ("id" INTEGER PRIMARY KEY NOT NULL, "title" varchar(255) DEFAULT NULL, "address" varchar(255) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "user_id" integer DEFAULT NULL, "lat" varchar(255) DEFAULT NULL, "long" varchar(255) DEFAULT NULL, "created_on" datetime DEFAULT NULL);

as you can see I'm using sqlite 3.3.13. that's no biggie though, I'll try manually compiling 3.3.7 and the qslite3-ruby gem and see if that works out

after installing sqlite3 3.3.7 and reinstalling sqlite3-ruby there's still no change. I still get that big error in the controller and NULL values in the console

also, this is interesting. here is the migration used to create the table:

$ cat db/migrate/001_create_hotspots.rb class CreateHotspots < ActiveRecord::Migration   def self.up     create_table :hotspots do |t|         t.column :title, :string         t.column :address, :string         t.column :notes, :string         t.column :user_id, :integer         t.column :lat, :string         t.column :long, :string         t.column :created_on, :datetime     end   end

  def self.down     drop_table :hotspots   end end

yet here's the schema.rb in the end

$ cat db/schema.rb # This file is autogenerated. Instead of editing this file, please use the # migrations feature of ActiveRecord to incrementally modify your database, and # then regenerate this schema definition.

ActiveRecord::Schema.define(:version => 4) do   ...   create_table "hotspots", :force => true do |t|     t.column "title", :string, :default => "NULL"     t.column "address", :string, :default => "NULL"     t.column "notes", :string, :default => "NULL"     t.column "user_id", :integer, :default => 0     t.column "lat", :string, :default => "NULL"     t.column "long", :string, :default => "NULL"     t.column "created_on", :datetime   end   ... end

okay, I've cleaned up those NULL values. the problem was I had ubuntu's libsqlite3-ruby1.8 package installed. once I uninstalled that the values were being stored properly as nil. but I still get the StatementInvalid error from the controller

Processing ApiController#create (for 127.0.0.1 at 2007-05-11 12:35:15) [POST]   Session ID: 74e977db220146ab742e2eb0811854d4   Parameters: {"title"=>"asdf", "action"=>"create", "notes"=>"", "controller"=>"api", "address"=>"asdf", "lat"=>"47.55857307874545", "long"=>"-52.71583557128906"}   User Load (0.000549) SELECT * FROM users WHERE (login = 'mikew' and password = 'test') LIMIT 1   SQL (0.000000) SQLite3::SQLException: SQL logic error or missing database: INSERT INTO hotspots ("created_on", "title", "notes", "user_id", "address", "lat", "long") VALUES('2007-05-11 12:35:15', 'testing', NULL, NULL, '222 water st', NULL, NULL)

ActiveRecord::StatementInvalid (SQLite3::SQLException: SQL logic error or missing database: INSERT INTO hotspots ("created_on", "title", "notes", "user_id", "address", "lat", "long") VALUES('2007-05-11 12:35:15', 'testing', NULL, NULL, '222 water st', NULL, NULL))

at the sqlite3 command prompt run

.schema hotspots

and make sure the columns that are being inserted with NULL values can take NULL.

also, cut/paste that query from the error into the prompt and see what happens

the only field that won't accept null is the `id` field

CREATE TABLE hotspots ("id" INTEGER PRIMARY KEY NOT NULL, "title" varchar(255) DEFAULT NULL, "address" varchar(255) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "user_id" integer DEFAULT NULL, "lat" varchar(255) DEFAULT NULL, "long" varchar(255) DEFAULT NULL, "created_on" datetime DEFAULT NULL);

I cut and pasted the failing code from the error message into the sqlite prompt and it went through without a hitch

$ sqlite3 db/development.sqlite3 SQLite version 3.3.7 Enter ".help" for instructions

SELECT * FROM hotspots; INSERT INTO hotspots ("created_on", "title", "notes",

"user_id", "address", "lat", "long") VALUES('2007-05-11 18:42:52', 'testing', NULL, NULL, '222 water st', NULL, NULL);

SELECT * FROM hotspots;

1|testing|222 water st|||||2007-05-11 18:42:52

Mike,

It still looks to me as though the database.yml is wrong. I think

development:   adapter: sqlite3   database: db/development.sqlite3   timeout: 5000

should be

development:   adapter: sqlite3   dbfile: db/development.sqlite3   timeout: 5000

and similarly for test and production.

Regards,

Andrew

actually either works, however :database is the preferred option due to consistency.

http://dev.rubyonrails.org/ticket/2644

I posted on the Rails Trac, but that doesn't seem to be going anywhere either <http://dev.rubyonrails.org/ticket/8360&gt;

so for now, the app has switched to MySQL