Rails - May 2007 - 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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


On 10/05/07, mikew <wyatt.mike-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On May 10, 5:22 am, "David Mitchell"
<monch1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.
>
> On 10/05/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

On 5/9/07, mikew <wyatt.mike-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.
>
> On 5/9/07, mikew <wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
>
> > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/11/07, mikew <wyatt.mike-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
Do you know which version of sqlite3 you''re using?

This might help:

  http://weblog.rubyonrails.org/2007/1/29/using-sqlite3-with-rails

d.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

sqlite> 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
sqlite> .schema <table_name>

and see what the default is

let us know what you find out

On 5/10/07, mikew <wyatt.mike-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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
>
> On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > 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.
> >
> > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> >
> >
> > > 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
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
sqlite> .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);
sqlite>

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

On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> sqlite> 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
> sqlite> .schema <table_name>
>
> and see what the default is
>
> let us know what you find out
>
> On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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
>
> > On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > 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.
>
> > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
> sqlite> .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);
> sqlite>
>
> 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
>
> On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
>
> > sqlite> 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
> > sqlite> .schema <table_name>
>
> > and see what the default is
>
> > let us know what you find out
>
> > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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
>
> > > On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > 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.
>
> > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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))

On May 11, 1:00 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
> > sqlite> .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);
> > sqlite>
>
> > 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
>
> > On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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
>
> > > sqlite> 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
> > > sqlite> .schema <table_name>
>
> > > and see what the default is
>
> > > let us know what you find out
>
> > > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > 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
>
> > > > On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > > 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.
>
> > > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On 5/11/07, mikew <wyatt.mike-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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))
>
> On May 11, 1:00 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > 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
> >
> > On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > 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
> > > sqlite> .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);
> > > sqlite>
> >
> > > 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
> >
> > > On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > 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
> >
> > > > sqlite> 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
> > > > sqlite> .schema <table_name>
> >
> > > > and see what the default is
> >
> > > > let us know what you find out
> >
> > > > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > > 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
> >
> > > > > On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > > > 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.
> >
> > > > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > > > > 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
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
sqlite> SELECT * FROM hotspots;
sqlite> 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);
sqlite> SELECT * FROM hotspots;
1|testing|222 water st|||||2007-05-11 18:42:52
sqlite>

On May 11, 3:12 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> On 5/11/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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))
>
> > On May 11, 1:00 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > 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
>
> > > On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > 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
> > > > sqlite> .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);
> > > > sqlite>
>
> > > > 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
>
> > > > On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > 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
>
> > > > > sqlite> 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
> > > > > sqlite> .schema <table_name>
>
> > > > > and see what the default is
>
> > > > > let us know what you find out
>
> > > > > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > 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
>
> > > > > > On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > > > > 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.
>
> > > > > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


On May 11, 10:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
> sqlite> SELECT * FROM hotspots;
> sqlite> 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);
> sqlite> SELECT * FROM hotspots;
> 1|testing|222 water st|||||2007-05-11 18:42:52
> sqlite>
>
> On May 11, 3:12 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
>
> > On 5/11/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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))
>
> > > On May 11, 1:00 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > 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
>
> > > > On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > 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
> > > > > sqlite> .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);
> > > > > sqlite>
>
> > > > > 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
>
> > > > > On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > 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
>
> > > > > > sqlite> 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
> > > > > > sqlite> .schema <table_name>
>
> > > > > > and see what the default is
>
> > > > > > let us know what you find out
>
> > > > > > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > 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
>
> > > > > > > On May 10, 8:53 am, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > > > > > 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.
>
> > > > > > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > > > 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
>
> ...
>
> read more »

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

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


On 5/12/07, andrew.gallear-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
<andrew.gallear-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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
>
>
> On May 11, 10:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > 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
> > sqlite> SELECT * FROM hotspots;
> > sqlite> 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);
> > sqlite> SELECT * FROM hotspots;
> > 1|testing|222 water st|||||2007-05-11 18:42:52
> > sqlite>
> >
> > On May 11, 3:12 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > 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
> >
> > > On 5/11/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > 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))
> >
> > > > On May 11, 1:00 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > > 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
> >
> > > > > On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > > > 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
> > > > > > sqlite> .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);
> > > > > > sqlite>
> >
> > > > > > 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
> >
> > > > > > On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > > > > 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
> >
> > > > > > > sqlite> 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
> > > > > > > sqlite> .schema <table_name>
> >
> > > > > > > and see what the default is
> >
> > > > > > > let us know what you find out
> >
> > > > > > > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > > > > > 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
> >
> > > > > > > > On May 10, 8:53 am, "Chris
Hall" <christopher.k.h...@gmail.com> wrote:
> > > > > > > > > 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.
> >
> > > > > > > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > > > > > > > > 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
> >
> > ...
> >
> > read more »
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

so for now, the app has switched to MySQL

On May 12, 6:02 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> actually either works, however :database is the preferred option due
> to consistency.
>
> http://dev.rubyonrails.org/ticket/2644
>
> On 5/12/07, andrew.gall...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
<andrew.gall...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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
>
> > On May 11, 10:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > 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
>
> > > $sqlite3db/development.sqlite3
> > > SQLite version 3.3.7
> > > Enter ".help" for instructions
> > > sqlite> SELECT * FROM hotspots;
> > > sqlite> 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);
> > > sqlite> SELECT * FROM hotspots;
> > > 1|testing|222 water st|||||2007-05-11 18:42:52
> > > sqlite>
>
> > > On May 11, 3:12 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > at thesqlite3command 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
>
> > > > On 5/11/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > 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))
>
> > > > > On May 11, 1:00 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > > > after installingsqlite33.3.7 and
reinstallingsqlite3-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
>
> > > > > > On May 11, 12:14 pm, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > 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:
>
> > > > > > > $sqlite3db/development.sqlite3
> > > > > > > SQLite version 3.3.13
> > > > > > > Enter ".help" for instructions
> > > > > > > sqlite> .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);
> > > > > > > sqlite>
>
> > > > > > > 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
>
> > > > > > > On May 10, 9:34 pm, "Chris Hall"
<christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > > well, something is causing the empty
attributes to be set to "NULL"
> > > > > > > > instead of nil.
>
> > > > > > > > here''s some data i have in
ansqlite3db
>
> > > > > > > > sqlite> 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 thesqlite3console and
> > > > > > > > check out the schema for the table
> > > > > > > > $>sqlite3db/development_db
> > > > > > > > sqlite> .schema <table_name>
>
> > > > > > > > and see what the default is
>
> > > > > > > > let us know what you find out
>
> > > > > > > > On 5/10/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > > > 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
>
> > > > > > > > > On May 10, 8:53 am, "Chris
Hall" <christopher.k.h...@gmail.com> wrote:
> > > > > > > > > > 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 whereassqlite3is 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.
>
> > > > > > > > > > On 5/9/07, mikew
<wyatt.m...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > > > > > > > > good evening (and
morning, also!) everyone. this is my first attempt
> > > > > > > > > > > at usingsqlite3for a
rails app, and so far, not so good
>
> > > > > > > > > > > when
>
> ...
>
> read more »

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---