small medium large xlarge

19009479_n00_pragsmall
03 May 2008, 23:36
Ray Schamp (6 posts)

Hi, I’m working my way through the book, I’m at the step where we add a price column, and am getting an error I don’t know what to do with:

ray-schamps-computer:~/Sites/ror/depot Ray$ rake db:migrate        
(in /Users/Ray/Sites/ror/depot)
== 2 AddPriceToProduct: migrating =============================================
-- add_column(:products, :price, :decimal, {:precision=>8, :default=>0, :scale=>2})
rake aborted!
SQLite3::SQLException: near "ADD": syntax error: ALTER TABLE products ADD "price" decimal(8,2) DEFAULT 0

(See full trace by running task with --trace)

After it didn’t work initially, I copied and pasted from the download link:

class AddPriceToProduct < ActiveRecord::Migration
  def self.up
    add_column :products, :price, :decimal,
      :precision => 8, :scale => 2, :default => 0
  end

  def self.down
    remove_column :products, :price
  end
end

Can someone tell me what’s up?

Thanks, Ray

PS I wish this forum comments box had a preview.

Samr_small_pragsmall
04 May 2008, 00:22
Sam Ruby (634 posts)

Try these commands directly against sqlite3. Can you tell me if they work? What version of sqlite are you running?

$ sqlite3 test.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> CREATE TABLE products ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255) DEFAULT NULL, "description" varchar(255) DEFAULT NULL, "image_url" varchar(255) DEFAULT NULL, "created_at" datetime DEFAULT NULL, "updated_at" datetime DEFAULT NULL);
sqlite> ALTER TABLE products ADD "price" decimal(8,2) DEFAULT 0;
sqlite> .exit
19009479_n00_pragsmall
04 May 2008, 02:14
Ray Schamp (6 posts)

Thanks for responding!

Apparently I am running 3.1.3, so since you have 3.4.2 I will look into upgrading. Here’s what I get:

ray-schamps-computer:~/Sites/ror/depot Ray$ sqlite3 test.db
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> CREATE TABLE products ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255) DEFAULT NULL, "description" varchar(255) DEFAULT NULL, "image_url" varchar(255) DEFAULT NULL, "created_at" datetime DEFAULT NULL, "updated_at" datetime DEFAULT NULL);
sqlite> ALTER TABLE products ADD "price" decimal(8,2) DEFAULT 0;
SQL error: near "ADD": syntax error
sqlite> ALTER TABLE products ADD [price] decimal(8,2) DEFAULT 0;
SQL error: near "ADD": syntax error
sqlite> .exit

After I updated sqlite, your tests work, but rails gives the same error. In my searches online I found a few other people with the similar problems if a program shipped expecting sqlite3 > 3.1, but didn’t warn OS 10.4 users to upgrade. It’s strange that the SQL syntax would have changed between versions…

I will see if rails sees the new version of sqlite after a restart.

Update: no, it was not a restart it needed, but a sudo gem install sqlite3-ruby. Works now!

Thanks a lot, Ray

Samr_small_pragsmall
04 May 2008, 02:18
Sam Ruby (634 posts)

It looks like support for ALTER TABLE ADD COLUMN was added to “SQLite3”:http://www.sqlite.org/changes.html in version 3.2.0.

I have found “this”:http://wiki.rubyonrails.org/rails/pages/HowtoUseSQLite : On Mac OS X 10.4 Tiger, try uninstalling the gem (sudo gem uninstall sqlite3), then use DarwinPorts to install SWIG (sudo port install swig), then re-install the SQLite3 gem (sudo gem install sqlite3).

If you can verify that this works for you, I’ll update the instructions in the book.

19009479_n00_pragsmall
04 May 2008, 13:42
Ray Schamp (6 posts)

Someone else will have to confirm this because I already built the latest version of sqlite3 and replaced my old binary with that one. It was pretty painless. Thanks for your help though.

I have found a few strange areas of the beta book as I’m reading it straight through – is it useful for people to post copy errors to this forum, or do you have proofreaders for that?

Samr_small_pragsmall
04 May 2008, 15:57
Sam Ruby (634 posts)

There will be proofreaders, but any errors that are reported will be addressed, and undoubtedly will make the copyeditors jobs easier.

Dave_gnome_head_isolated_pragsmall
04 May 2008, 21:32
Dave Thomas (390 posts)

Ray:

However, it’s probably easier both for you and for us to log errors in the errata pages—just click the link at foot of each page and you’ll be taken there.

Dave

Generic-user-small
09 May 2008, 03:17
defucius tai (3 posts)

hi, I am having the same problems with the ADD command with sqlite3 3.1.3. and the instruction above with the SWIG and to upgrade sqlite3 on macos 10.4 does not work for me.

19009479_n00_pragsmall
09 May 2008, 04:16
Ray Schamp (6 posts)

The way I fixed it was to download the latest stable release of sqlite3 for 10.4 and build it from the source, which was easy, no snags:

untar the package make a new directory in the folder that holds the source cd into that, and run @>../configure@ Let it go, and then run @> make@

Note what @> sqlite3 –version@ and @>which sqlite3@ returns

run @> make install@ and see if @> sqlite3 –version@ returns something different. If it’s the new version, you’re all set. If it’s the old one still, run @> which sqlite3@, and this should return the location of the new binary. I just replaced my old binary with the new one, and then it worked after that.

Sorry if these instructions are confusing, I’m sure there’s a better way to do this.

-Ray

Generic-user-small
22 May 2008, 21:14
Rob Sandusky (5 posts)

I am running SQLite v. 3.1.3 as well (on OS X Tiger 10.4.11) and am having the same problems as defucius tai notes above.

Is there a simple fix for this yet that works for us newbies who aren’t as familiar with the nuances of UNIX, such as building applications from source?

I seem to be spending more time updating, troubleshooting and searching for answers than learning Rails. (2nd edition seemed much simpler).

Samr_small_pragsmall
22 May 2008, 22:10
Sam Ruby (634 posts)

I’m running Leopard, so I don’t have this problem, but from what I gather, the following should work:

sudo port upgrade sqlite3
gem install sqlite-ruby -- --with-sqlite-dir=/opt/local

You can verify which version of sqlite3 Ruby sees with the following code (can be put into a file and executed from the command line, or run in irb or in script/console):

require 'rubygems'
require 'sqlite3'
db = SQLite3::Database.new('db/development.sqlite3')
puts db.execute('select sqlite_version()')

If this works for you, I’ll update the book, and make this code available for easy download.

Generic-user-small
22 May 2008, 22:41
Rob Sandusky (5 posts)

Under Tiger (10.4.11):

the “port” command is unrecognized at the UNIX prompt: sudo: port: command not found

Entering in the second bit of code pasted into a .rb file and running with ruby produces an error:

/usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.1/lib/sqlite3/errors.rb:94:in check': could not open database: unable to open database file (SQLite3::CantOpenException) from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.1/lib/sqlite3/database.rb:112:in initialize’ from checkSQLite.rb:5:in `new’ from checkSQLite.rb:5

Did I miss something? (Pardon my dunder-headedness, but am still a bit of a newbie.)

Samr_small_pragsmall
22 May 2008, 23:30
Sam Ruby (634 posts)

Now I am starting to understand the scope of the problem. Prior versions of the book pointed users to two sets of instructions: one that allegedly was easier and was based on ports, and one that sounds scary and involved building from source. The former set of instructions were on the web, and are long gone. The latter set of instructions still sound scary (but their bark is truly worse than their bite).

The “instructions to install ports”:http://darwinports.com/install/ on Tiger, however, also look a bit scary. But perhaps it is not too bad. Step one is to run an installer. Step two also is simply to run an installer. Step three requires you to add two lines to a file. Step four is yet another installer (or build from source). Step five is simply one command.

Can you try these and see if they work for you?

If you get past that, you will be all set up to install all sorts of useful packages, not just the latest sqlite3.

Oh, and the reason you got an error on the test program is that it was designed to be run inside the depot directory. Here’s something that is a bit more standalone:

require 'rubygems'
require 'sqlite3'
tempname = "test.sqlite#{3+rand}"
db = SQLite3::Database.new(tempname)
puts db.execute('select sqlite_version()')
db.close
File.unlink(tempname)
Generic-user-small
23 May 2008, 13:35
Rob Sandusky (5 posts)

The instructions for installing ports seem to have worked (though it took several hours for downloads and installation per the Darwin Ports instruction page – might be worth letting your readers know in advance). It completed and reported a successful update of SQLite3 to v. 3.5.9 (and running sqlite3 at the Unix prompt verifies that v.3.5.9 is installed.)

I then ran: sudo gem install sqlite-ruby – –with-sqlite-dir=/opt/local

and encountered the following error:

ERROR:  Error installing sqlite-ruby:
        ERROR: Failed to build gem native extension.

/usr/local/bin/ruby extconf.rb install sqlite-ruby -- --with-sqlite-dir=/opt/local
checking for main() in -lsqlite... no
checking for sqlite.h... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Regarding the test program, it too worked with the changes you noted, however it reports back that SQLite3 version 3.1.3 (obviously since the gem update failed as noted above)

Samr_small_pragsmall
23 May 2008, 20:47
Sam Ruby (634 posts)

my bad. should be sqlite3-ruby, thus:

sudo gem install sqlite3-ruby --with-sqlite-dir=/opt/local

And, yes, I believe that the correct incantation is with a 3 in sqlite3-ruby and no 3 in sqlite-dir.

Generic-user-small
23 May 2008, 23:20
Rob Sandusky (5 posts)

Hmm…. doesn’t like the –with option:

sudo gem install sqlite3-ruby --with-sqlite-dir=/opt/local
Password:
ERROR:  While executing gem ... (OptionParser::InvalidOption)
    invalid option: --with-sqlite-dir=/opt/local

For fun (and because I love getting error messages), I tried the following:

sudo gem install sqlite3-ruby – with-sqlite3-dir=/opt/local

added a space both before and after the “–” … I figured, why not? How bad could it be?

It seems to have worked. When I run the test script, I now get the correct version of SQLite: 3.5.9. So there’s some progress.

When I run the rake db:migrate in the depot directory (from a fresh shell window), it too seems to complete. So looks like this might be the fix we’ve been looking for (?)

Thanks for the assist.

Samr_small_pragsmall
24 May 2008, 01:51
Sam Ruby (634 posts)

Glad to see it all worked out. I suspect that the real truth is that the with-sqlite-dir option was neither required nor supported and eventually you found a syntax which caused it to be ignored.

I’ve updated this section for the next beta. Hopefully in this way your experiences will help people save time.

Generic-user-small
15 Aug 2008, 07:00
Jon Yamokoski (2 posts)

Just wanted to add my two cents in case anyone else like me stumbles across this post looking for answers…

I am using Tiger (10.4.11 to be exact) and the above did not work out for me, so I found another method that ended up working like a charm. I just uninstalled my /usr/local/ version of ruby and reinstalled it using macports (sudo port install ruby). That way sqlite3 and ruby were both “compiled” the same way and shared similar paths (just make sure you set your PATH correctly in your profile, in this case: export PATH=/opt/local/bin:/opt/local/sbin:$PATH). After that I installed rubygems from source, and then rails and sqlite3-ruby via gems (e.g., sudo gem install rails).

Now I am able to proceed through all of the book’s examples without a hitch!

Generic-user-small
23 Oct 2008, 00:27
Brian Bannister (2 posts)

Here’s a vote for installing ruby, sqlite3 and gems via the ports system.

I’ve been trying for a few hours to get my source installed rails installation to recognise my ports installed sqlite3 with no luck.

sudo gem install sqlite3-ruby -- --with-sqlite-dir=/opt/local

looks like it works but when I examine which database rails is using in irb it still only sees sqlite 3.1.3.

If you’re just looking to learn rails quickly then using ports to install avoids this problem:

export PATH=/opt/local/bin:/opt/local/sbin:$PATH
sudo port install sqlite3 ruby rb-rubygems
sudo gem install rails
sudo gem install sqlite3-ruby
Generic-user-small
23 Oct 2008, 00:27
Brian Bannister (2 posts)

Here’s a vote for installing ruby, sqlite3 and gems via the ports system.

I’ve been trying for a few hours to get my source installed rails installation to recognise my ports installed sqlite3 with no luck.

sudo gem install sqlite3-ruby -- --with-sqlite-dir=/opt/local

looks like it works but when I examine which database rails is using in irb it still only sees sqlite 3.1.3.

If you’re just looking to learn rails quickly then using ports to install avoids this problem:

export PATH=/opt/local/bin:/opt/local/sbin:$PATH
sudo port install sqlite3 ruby rb-rubygems
sudo gem install rails
sudo gem install sqlite3-ruby
Generic-user-small
23 Jan 2009, 02:44
Yaron (2 posts)

Hey, I’m using OS X 10.4.11 and I just tried to follow Brian’s instructions and irb still insists that my sqlite3 is version 3.1.3

before I followed Brian’s instructions I tried following “these instructions”:http://accidentaltechnologist.com/ruby/ruby-on-rails/setting-up-sqlite3-for-ruby-on-rails-development/ but the results are the same in both methods I still get the @SQLite3::SQLException: near “ADD”: syntax error: ALTER TABLE “products” ADD “price” decimal(8,2) DEFAULT 0@ message

Generic-user-small
24 Jan 2009, 11:19
Yaron (2 posts)

found out what I did wrong, I was using a slightly older version of Xcode, I’m still not sure I have the latest version for Tiger but Brian’s instructions worked for me with “Xcode 2.4.1”:https://connect.apple.com/cgi-bin/WebObjects/MemberSite.woa/wa/getSoftware?bundleID=19681

Generic-user-small
24 May 2009, 14:28
Chris Baker (1 post)

Has there been any clarification on this recently?

I’m running Leopard 10.5.7 and XCode 3.1.2. The stock sqlite3 is 3.4.0, but the most recent version installed by ports is 3.6.14.1. Testing with @which@ returns the following:

$ which -a sqlite3
/opt/local/bin/sqlite3
/usr/bin/sqlite3

$ /opt/local/bin/sqlite3 --version
3.6.14.1

$ /usr/bin/sqlite3 --version
3.4.0

However, when I run the Ruby script in the book / posted above (using @sqlite3-ruby@ version 1.2.4) it returns @sqlite3@ version 3.4.0 still.

Without success, I’ve tried various incantations:

sudo gem install sqlite3-ruby
sudo gem install sqlite3-ruby -- --with-sqlite-dir=/opt/local
sudo gem install sqlite3-ruby -- --with-sqlite-dir=/opt/local/bin

Any ideas as to how to get this correctly bound to the version of @sqlite3@ installed in @/opt/local/bin@?

Thanks!

You must be logged in to comment