Troubleshooters.Com and Code Corner and Ruby Revival Present

Ruby Database Connectivity
Copyright (C) 2006 by Steve Litt
Note: All materials in Ruby Revival are provided AS IS. By reading the materials in Ruby Revival you are agreeing to assume all risks involved in the use of the materials, and you are agreeing to absolve the authors, owners, and anyone else involved with Ruby Revival of any responsibility for the outcome of any use of these materials, even in the case of errors and/or omissions in the materials. If you do not agree to this, you must not read these materials.
To the 99.9% of you honest readers who take responsibility for your own actions, I'm truly sorry it is necessary to subject all readers to the above disclaimer.

CONTENTS:

Executive Summary

Ruby has three popular methods of database connectivity, as listed below from lowest to highest level:
  1. Database driver
  2. DBI
  3. ActiveRecord
The database driver method is so low level that a change in databases can require major changes to the application. The ActiveRecord method is so comprehensive that, in my opinion, it's not good for a quick and dirty simple app on an existing database.

The DBI connectivity interface is a database independent wrapper to make SQL calls from your Ruby application. If you've used Perl's DBI::DBD method, you'll instantly recognize Ruby's DBI as the same thing.

Ruby's DBI connectivity interface has some poorly documented gotchas, especially with installation. The purpose of this document is to get you past those gotchas, and then go over various ways you can use DBI to create quick and simple programs.

Remember, for complex apps, especially those where you create the database from scratch, consider ActiveRecord or even Ruby on Rails. For programs where you need to access database specific features, use the database driver interface.

Database Driver Installation

This is documented throughout the net, but in almost every case the documentation is incomplete, and assumes you know a great deal about the system. Therefore, it's hard to perform a "Hello World" app.

At the highest level, here are the steps needed to install Ruby DBI, assuming you already have Ruby installed.
  1. Verify or install Ruby, version 1.8.4 or better
  2. Install and test the low level database driver(s)
  3. Install and test Ruby dbi
DBI will NOT work unless you install the low level database drivers. DBI is an interface to the low level database drivers.

This article covers the installation of database drivers, specifically, the database drivers for MySQL and Postgres.

Verify or install Ruby, version 1.8.4 or better

Perform the following command and note the output:

[slitt@mydesk ~]$ ruby -v
ruby 1.8.4 (2005-12-24) [i686-linux]
[slitt@mydesk ~]$

In the preceding you can see that the returned version is 1.8.4. If you don't have Ruby, install the latest one. If your Ruby is an earlier version, you can either upgrade to the latest, or you can use earlier versions of the database drivers and Ruby DBI. Personally I recommend upgrading your Ruby.

Ruby installation and upgrading is covered elsewhere on the Internet and is beyond the scope of this document.

Install and test the database driver(s)

According to http://ruby-dbi.rubyforge.org/, DBI can interface with the following:
This section will review intallation and testing of the MySQL and Postgres drivers. If you need to install other drivers, look on the web.

MySQL

First, download the Ruby MySQL tarball from http://tmtm.org/downloads/mysql/ruby/. Untar it, go into the /mysql-ruby-2.7.1 directory (or whatever your mysql-ruby version), and read the README.html file. That file contains detailed instructions. I did it as simply as possible:

% ruby extconf.rb
% ruby ./test.rb
% su
# make install

Warning

When I performed the preceding steps, the ruby ./test.rb failed miserably. Nevertheless, the installation went well and I was able to access my MySQL database from Ruby using this driver.

Make sure you have the MySQL daemon running, and you can access MySQL with the mysql executable. Within the mysql environment, create a database called test (create database test logged in as root). Now make this simple hello.rb program:

#!/usr/bin/ruby

require 'mysql'

mysql = Mysql.init()
mysql.connect('localhost')

results = mysql.query("SELECT now();")

results.each{|row|; puts row;}

mysql.close()

Here's what the output should look like:

[slitt@mydesk ~]$ ./hello.rb
2006-08-17 16:00:08
[slitt@mydesk ~]$

If you get errors, check the mysql.connect line. You might need to specify a user, password, database, or other things. The generic form of this function is:
connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, sock=nil, flag=nil)
Once you get the right output, you've proven you can interface to MySQL. The only remaining question is this: can you write and read data. Let's start with a simple program to create a table of rocks in database test, and load that table:


#!/usr/bin/ruby

require 'mysql'

mysql = Mysql.init()
mysql.connect('localhost')
mysql.select_db('test')

mysql.query("DROP TABLE IF EXISTS rocks")

mysql.query("CREATE TABLE rocks (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), rockname CHAR(20) NOT NULL);")

mysql.query("INSERT INTO rocks (rockname) values('Granite');")
mysql.query("INSERT INTO rocks (rockname) values('Coal');")
mysql.query("INSERT INTO rocks (rockname) values('Quartz');")

mysql.close()

The preceding program, when running, should produce the no output, so the only way to prove it worked is to view the results in the mysql environment. Run the program, and verify that within the mysql environment that table has been created. Use the show tables and the describe rocks commands to prove the program worked. If there are errors, check your typing, make sure the test database was created, and check permissions within the database (grant and the like).

Once that's done, let's modify the program to actually read the rows that were inserted.


[slitt@mydesk ~]$ ./hello.rb
2006-08-17 16:00:08
[slitt@mydesk ~]$





In the mysql executable, create a database called test and then create the following hello.rb program:

#!/usr/bin/ruby

require 'mysql'

$maxwidth = 12 # MAXIMUM WIDTH OF FIELD DISPLAYS

###### INITIALIZE AND CONNECT TO DATABASE test
mysql = Mysql.init()
mysql.connect('localhost')
mysql.select_db('test')

###### CREATE TABLE rocks, DROPPING OLD ONE IF NEEDED
mysql.query("DROP TABLE IF EXISTS rocks")

qstring = "CREATE TABLE rocks ("
qstring += "id INT UNSIGNED NOT NULL AUTO_INCREMENT, "
qstring += "PRIMARY KEY (id), "
qstring += "rockname CHAR(20) NOT NULL);"

mysql.query(qstring)

###### FILL TABLE rocks WITH DATA
mysql.query("INSERT INTO rocks (rockname) values('Granite');")
mysql.query("INSERT INTO rocks (rockname) values('Coal');")
mysql.query("INSERT INTO rocks (rockname) values('Quartz');")

###### PRINT OUT rocks FIELD NAMES AND VALUES
puts
results = mysql.query("describe rocks;")
results.each do |row|
column = row[0]
print column
($maxwidth - column.length).times {print " "}
end
puts
puts

results = mysql.query("SELECT * from rocks;")
results.each do |row|
row.each do |column|
print column
($maxwidth - column.length).times {print " "}
end
puts
end

###### SHUT DOWN THE DATABASE CONNECTION
mysql.close()

At the left is a simple but complete database program, minus any error checking. The CREATE TABLE query is so complex that the string is assembled on several lines.

The printing out of field names and values is interesting. The field names are yielded as an array of fields, one field per row, and each attribute of the field is a column. The first attribute is the field name, hence the reference to row[0].

The SELECT * FROM rocks query returns an array of rows, each row being an array of column values. Therefore we nested loop to write all the rows and columns in the expected format.

The columns and column names are kept aligned with this code:
($maxwidth - column.length).times {print " "}

The output of the preceding code should look like this:

[slitt@mydesk ~]$ ./hello.rb

id rockname

1 Granite
2 Coal
3 Quartz
[slitt@mydesk ~]$

I could get into ever deeper details about using the Mysql interface, but I suspect you'll usually opt for the more portable DBI or ActiveRecord methods, so all that's necessary is proof that your Mysql interface works, and if you get the preceding output from the preceding program, you've proved it.

Once you get your output to look like the preceding, you've performed a low level interface to MySQL and you're therefore done.

Postgres

There are two low level Postgres interfaces: The Pure Ruby one, and the faster but harder to install native one. My reading on the web tells me these are mutually exclusive -- you can't install both.

From what I understand, installing the pure ruby one is as simple as this command:
gem install postgres-pr
With the native one, you need to download the driver code from http://ruby.scripting.ca/postgres/.

NOTE

I've read on the Internet that you can install the native postgres driver using gem (as user root) like this:
gem install postgres
Another site recommended using the version number, like this:
gem install ruby-postgres-0.7.1.2005.12.21.gem
I personally feel more comfortable using the actual source tarball, so that's what's outlined here.

The following will install the Ruby native driver if your system already has all Postgres include, library and other files in the "right" places:
ruby extconf.rb
make
su
make install
The preceding didn't work on my system -- I got a bunch of errors on the make command referencing variables I figured would be in a Postgres include file, so I performed the following command:
locate -i postgres | less
That command revealed several .h files in the /usr/include/pgsql tree, so I did the following:
make clean
ruby extconf.rb --with-pgsql-include-dir=/usr/include/pgsql
make
This time the make command had a couple warnings, but no errors. I then went on to become root and run the make install command.

Once your Ruby postgres interface is installed, whether by gem or compilation, whether pure ruby or native, you need to test it. Here's the most basic hello world test program:

#!/usr/bin/ruby
require 'postgres'

If you run the preceding program and there is no output, that's a good thing. If the Postgres module hadn't installed, it would display an error. Now let's get it to interact with Postgres:

#!/usr/bin/ruby
require 'postgres'

conn = PGconn.connect("localhost", 5432, '', '', "test", "myid", "mypass")

res = conn.exec('select tablename, tableowner from pg_tables')

res.each do |row|
row.each do |column|
print column
(20-column.length).times{print " "}
end
puts
end

The preceding code prints a list of tables in the database, with their owners. The following is the tail end of the output:

pg_group            postgres            
pg_proc postgres
pg_rewrite postgres
pg_type postgres
pg_attribute postgres
pg_class postgres
pg_tablespace postgres
pg_inherits postgres
pg_index postgres
pg_operator postgres
[slitt@mydesk ~]$

Now let's do some actual data manipulation and reading:
#!/usr/bin/ruby
require 'postgres'

$maxwidth=12

conn = PGconn.connect("localhost", 5432, '', '', "test", "myid", "mypass")

###### DROP ANY EXISTING rocks TABLE ######
begin
res = conn.exec("SELECT id FROM rocks;")
rescue # rocks table doesn't exist -- this is legitimate
else # rocks table exists, so delete it
puts 'DELETING rocks...'
res = conn.exec("DROP TABLE rocks;")
end

###### CREATE AND POPULATE rocks TABLE ######
begin
res = conn.exec("CREATE TABLE rocks (id serial, rockname char(20));")
res = conn.exec("INSERT INTO ROCKS (rockname) values ('Diamond');")
res = conn.exec("INSERT INTO ROCKS (rockname) values ('Ruby');")
res = conn.exec("INSERT INTO ROCKS (rockname) values ('Emerald');")
rescue Postgres::PGError => e
puts "Error creating and filling rocks table."
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
conn.close() if conn
end

###### PRINT COLUMN NAMES AS BANNER ABOVE DATA ######
puts
begin
res = conn.exec('SELECT column_name FROM test.information_schema.columns WHERE table_name=\'rocks\';')

rescue Postgres::PGError => e
puts "Error selecting column names."
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
conn.close() if conn
ensure
end

res.each do |row|
print row[0]
($maxwidth-row[0].length).times{print " "}
end
puts
puts

###### PRINT CONTENTS OF ROWS OF rocks TABLE ######
begin
res = conn.exec('SELECT * FROM rocks;')

rescue Postgres::PGError => e
puts "Error retrieving rocks rows."
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
conn.close() if conn
end

res.each do |row|
row.each do |column|
print column
($maxwidth-column.length).times{print " "}
end
puts
end
puts

If it works right, the preceding code should produce this output:

[slitt@mydesk ~]$ ./hello.rb  
NOTICE: CREATE TABLE will create implicit sequence "rocks_id_seq" for serial column "rocks.id"

id rockname

1 Diamond
2 Ruby
3 Emerald

[slitt@mydesk ~]$

But it might not. This code has lots of gotchas.

Gotchas

The first thing you'll notice is the contortions necessary to delete the rocks table. Deleting a nonexisting table creates a program stopping error. Creating an existing table likewise stops the program. Unlike MySQL, there's "drop if exist" in Postgres. So I had to kludge it with a begin/rescue/else/ensure/end. This code should work whether or not the table exists. Should.

Unless, of course, user myid isn't the owner of the table, in which case the whole program goes up in smoke. Because this is only educational, I haven't tested for that type of thing.

On certain errors, the rescue clauses might terminate with an error of their own -- something about an undefined variable. If you need to troubleshoot that kind of thing, comment out the rescue clause and all the code it contains.

Install and test Ruby dbi

Did you notice how different the MySQL and Postgres driver calls were from each other. Imagine writing a whole app with low level driver calls, and then switching DBMSs. You'd need to redo your whole program, even if the driver calls themselves used portable SQL.

The DBI layer interfaces with all the low level database drivers to give the programmer a single unified interface to the database. Of course, the DBI layer is limited by the particular database's limitations. MySQL has no triggers or stored procedures, so if you access MySQL through DBI you'll still not have triggers and stored procedures. But to the extent that databases are similar, you'll have a unified interface to them all.

As mentioned earlier, the desired low level database drivers must already be installed in order to compile Ruby DBI to interface with those drivers. If you haven't installed the necessary drivers, do so, possibly with help from the preceding article.

Download Ruby DBI from http://rubyforge.org/projects/ruby-dbi/. As of 8/17/2006 the filename is dbi-0.1.1.tar.gz. Then cd into a scratchpad directory, and untar it, producing a dbi-0.1.1 directory. cd into that directory, and do the following:
ruby setup.rb config --with=dbi,dbd_pg,dbd_mysql
ruby setup.rb setup
su
ruby setup.rb install
If things have gone right, Ruby DBI is now installed. But of course you must test that hypothesis.

Hello World

To test your Ruby DBI installation, write this hello.rb:

#!/usr/bin/ruby
require 'dbi'

def connect_to_mysql()
puts "\nConnecting to MySQL..."
#return DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")

return DBI.connect('DBI:Mysql:test', 'myid', '')
end

def connect_to_postgres()
puts "\nConnecting to Postgres..."
return DBI.connect('DBI:Pg:test', 'myid', '')
end

def exercise_database(dbh)
query = dbh.prepare("SELECT * FROM rocks")
query.execute()

while row = query.fetch() do
puts row
end
end

def main()
dbh = connect_to_mysql()
exercise_database(dbh)
dbh.disconnect

dbh = connect_to_postgres()
exercise_database(dbh)
dbh.disconnect
end

main()
Note that this code works only if you've already run the exercises in the Database Driver Installation section, because it relys on the right data being in table rocks in database test, and relys on user myid having full rights to that table.

If you get the dreaded "in `load_driver': Unable to load driver " error, check the following for whichever driver didn't load:
  1. The low level drive must have been installed.
  2. The corresponding database server must be running and its commandline interface (psql or mysql) must function.
  3. The driver name must be spelled right in the DBI.connect() call. For instance, refer to Postgres as "Pg" or "pg", not "Postgres" or "postgres" in the DBI.connect() call.

Looking at the preceding, you see that except for the database connection call (DBI.connect()), you access these two different databases identically. This is the value of DBI.

Next, we'll look at a program that drops, creates, loads and reads the table.
#!/usr/bin/ruby
require 'dbi'

$maxwidth=16

$rock_names = {
'm' => ['Marble', 'Marcite', 'Mudrock'],
'p' => ['Pummace', 'Puddingstone']
}

def do_query(dbh, querystring)
#puts "dia #{querystring}"
query = dbh.prepare(querystring)
query.execute()
return query
end

def rocks_exists(dbh)
exists = true
begin
query = dbh.prepare("select * from rocks")
query.execute
rescue
exists = false
end
return exists
end



def connect_to_mysql()
puts "\nConnecting to MySQL..."
#return DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")

return DBI.connect('DBI:Mysql:test', 'myid', '')
end

def connect_to_postgres()
puts "\nConnecting to Postgres..."
return DBI.connect('DBI:Pg:test', 'myid', '')
end


def exercise_database(dbh, db_abbrev)

###### RECREATE TABLE, DELETE IF NECESSARY
if rocks_exists(dbh)
do_query(dbh,"DROP TABLE rocks")
end

querystring = "CREATE TABLE rocks ("
if db_abbrev == 'm'
querystring += "id INT UNSIGNED NOT NULL AUTO_INCREMENT, "
querystring += "PRIMARY KEY (id), "
elsif db_abbrev == 'p'
querystring += "id serial, "
end
querystring += "rockname CHAR(20) NOT NULL)"
do_query(dbh, querystring)

###### INSERT VALUES INTO DATABASE, ACCORDING TO WHICH DATABASE
$rock_names[db_abbrev].each do |rock_name|
do_query(dbh, "INSERT INTO rocks (rockname) values (\'#{rock_name}\');")
end

###### QUERY FOR TABLE VALUES
query = dbh.prepare("SELECT * FROM rocks")
query.execute()

###### ITERATE OVER RESULTS OF QUERY
while row = query.fetch() do
row.each do |field|
field = field.to_s
print field
($maxwidth - field.length).times {print " "}
end
puts
end
end

def main()
dbh = connect_to_mysql()
exercise_database(dbh, 'm')
dbh.disconnect

dbh = connect_to_postgres()
exercise_database(dbh, 'p')
dbh.disconnect
end

main()
This  program is for the most part database independent. Naturally, the DBI.connect() calls are different. Also, the CREATE TABLE calls are different for the simple reason that the two databases use a different SQL syntax to create tables. In this version, we created a function called rocks_exists() to detect existence of the rocks table, rather than using MySQL's IF EXISTS SQL syntax, because Postgres has no equivalent syntax.

Other than those, we've creates a very generic database app that doesn't care which database it works with. To the extent that a database has standard SQL and you can live without extensions, you can write a "one size fits all" app using Ruby DBI.

Here's the output of the preceding program:
[slitt@mydesk ~]$ ./test.rb

Connecting to MySQL...
1 Marble
2 Marcite
3 Mudrock

Connecting to Postgres...
NOTICE: CREATE TABLE will create implicit sequence "rocks_id_seq" for serial column "rocks.id"
1 Pummace
2 Puddingstone
[slitt@mydesk ~]$

That was all very nice, but several commands required different syntax between the two databases because MySQL and Postgres used differing SQL commands for CREATE TABLE. Also note that the code used a rather kludgy rocks_exists() function because Postgres' DROP TABLE SQL command lacks the IF EXISTS clause.

Unfortunately, Ruby-DBI provides no help for those two situations, but can help elsewhere. Look at this program, which querys all the tables in the connected database, regardless of DBMS (MySQL or Postres, etc):
#!/usr/bin/ruby
require 'dbi'

$maxwidth=16



def connect_to_mysql()
puts "\nConnecting to MySQL..."
#return DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")

return DBI.connect('DBI:Mysql:test', 'myid', '')
end

def connect_to_postgres()
puts "\nConnecting to Postgres..."
return DBI.connect('DBI:Pg:test', 'myid', '')
end

def show_tables_and_columns(handle)
handle.tables.each do |table|
puts
puts "TABLE #{table}..."
handle.columns(table).each do |columninfo|
print " COLUMN "
print columninfo['name']
puts "..."
columninfo.each do |infopiece|
infopiece.each_with_index do |keyval, index|
if index == 0
print " "
print keyval
print ": "
else
puts keyval
end
end
end
end
end
end


def main()
dbh = connect_to_mysql()
show_tables_and_columns(dbh)
dbh.disconnect

dbh = connect_to_postgres()
show_tables_and_columns(dbh)
dbh.disconnect
end

main()

Unable to load Postgres driver?

If you get something like this when trying DBI with a Postgres database, you have a driver problem:
[slitt@mydesk ~]$ ./test.rb
/usr/local/lib/ruby/site_ruby/1.8/dbi.rb:329:in `load_driver': Unable to load driver 'postgres' (DBI::InterfaceError)
from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:227:in `_get_full_driver'
from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect'
from ./test.rb:10
[slitt@mydesk ~]$

Be sure you've downloaded and created the Postgres driver, and you can exercise it from a Postgres specific program such as this one. Then, be sure you recompile DBI with inclusion for Postgres:
ruby setup.rb config --with=dbi,dbd_pg,dbd_mysql
ruby setup.rb setup
su
ruby setup.rb install

ActiveRecord

The DBI method is instantly familiar to anyone who has done database programming any time since 1995. It basically provides a function with which to hit the database with a string that's really an SQL query. It's instantly obvious to any programmer.

But it's not very object oriented. And it doesn't make use of intelligent defaults on naming conventions. To gain these advantages, you need ActiveRecord.

There's a rumor that ActiveRecord works only with Ruby on Rails. Don't you believe it -- it works with pure Ruby, assuming you've installed ActiveRecord. The way I installed ActiveRecord was to install rails. That's how I got ActiveRecord. But if you haven't installed Rails and don't want to, you can install ActiveRecord with the following command run as root:
gem install activerecord
If the preceding command errors out saying "command not found", you'll need to install Rubygems, like this:
The final command tests to make sure it was actually installed.

Once ActiveRecord is installed, you can use it to access databases. For the exercises in this section, you'll need the test databases with rocks tables you created in previous exercises.

The following program demonstrates ActiveRecord selects, inserts and deletes. It assumes a test database with a rocks table. This was done in previous exercises on this page, but if for some reason you don't have them, run the dbi exercise program. The listing for the ActiveRecord example follows:
#!/usr/bin/ruby
require 'rubygems'
require_gem 'activerecord'

ActiveRecord::Base.establish_connection(
# :adapter => "mysql",
# :adapter => "pg", # adapter not found error, wrong name
:adapter=>"postgresql",
:database => "test"
)

class Rock < ActiveRecord::Base
end

puts
puts "Let's add Bassalt to the list of rocks!"
newrow = Rock.new
newrow.rockname = "Bassalt"
newrow.save
puts
puts "Now let's see the first rock!"
puts Rock.find(:first).rockname
puts
puts "Now let's see all the rocks!"
queryresults = Rock.find(:all)
queryresults.each do |row|
print row.id, " ", row.rockname, "\n"
end

puts
puts "Let's delete Bassalt from the list of rocks!"
Rock.delete_all("rockname = 'Bassalt'")
puts
puts "Now let's view the revised list of rocks!"
queryresults = Rock.find(:all)
queryresults.each do |row|
print row.id, " ", row.rockname, "\n"
end
ActiveRecord performs database access using objects. It's a different way of thinking -- not necessarily better, but different.  It is very database independent because it doesn't use SQL, which, as we all know, in spite of all the propaganda, varies from DBMS to DBMS.

First you call ActiveRecord::Base.establish_connection() to create the connection to the DBMS. Then you define a class called Rock, which by default will operate on a table that starts with a lower case letter and is the plural of the class name, so in other words, rocks. This little piece of magic is done in the ActiveRecord class, using Ruby's abilities to move strings between runtime and compile time and back again.

First we create a new row with Rock.new, then add a name to the row, then save it to the database. The newrow.save call does the actual SQL insert.

Next we query the table using Rock.find. In the first case we find the first record, and in the second case we find all records, and iterate through the results.

Finally, we use Rock.delete_all() to delete all rocks whose name is Bassalt. I used delete_all() instead of just plain delete() because, if you've been experimenting, it's possible you could have several rows named "Bassalt".

In the preceding program, you should be able to toggle between your MySQL database and your Postgres database by uncommenting the appropriate :adapter=> line. In real life it probably won't be that simple. The fact is, Ruby's ActiveRecord was made to mesh with MySQL without significant tweaks. With Postgres, you'll probably need to tweak it a little.

Troubleshooting ActiveRecord

ActiveRecord problems can be tough to diagnose for those not familiar with ActiveRecord. The first step in any troubleshooting activity is to adopt a productive troubleshooting attitude. See the various attitude links on The Universal Troubleshooting Process Page on the Troubleshooters.Com website. Then continue with the document you're now reading.

Read the Error Message

When your ActiveRecord code produces errors, your first move should be to read the error message, carefully. Because Ruby prints an entire call stack with the error, probably the top two or three lines of the error message are the significant ones. Does it say it can't find the adapter? Believe it. Does it say you have no permissions? Investigate that. Does it say there's no such table? Maybe there isn't. Read the error message very carefully.

Experiment

Where is it erroring out? Comment out everything below the ActiveRecord::Base.establish_connection() call and see if the error persists. If yes, you have a connection problem. If not, you're connecting fine, but can't insert, delete or whatever. If you narrow it down to a statement, you'll have a much better idea what's going on.

Round up the usual suspects

If you cannot connect, it's likely either you used the wrong name for the adapter string, the database server isn't running, or you haven't loaded the adapter's DBMS specific interface and compiled Ruby-DBI to recognize that interface. The error message will probably resemble the following:
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/
abstract/connection_specification.rb:79:in `establish_connection':
database configuration specifies nonexistent pg adapter (ActiveRecord::AdapterNotFound)

Remember, the adapter name for Postgres is not "pg", even though that's the string DBI uses. Go figure! With ActiveRecord, use the adapter string "postgresql". If the string is right, it's probably a problem with DBI or the database specific interfaces. Rerun and if necessary fix the exercises done earlier in this document.

To test for the database server running, try these (for MySQL and Postgres respectively):

You might get permission errors. Here's a typical one:
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/
abstract_adapter.rb:88:in `log':
PGError: ERROR: permission denied for relation rocks (ActiveRecord::StatementInvalid)

If you get this, try different users. If it's Postgres, start with the superuser -- user postgres. Be sure not to identify a user in the call to ActiveRecord::Base.establish_connection(), but instead log into Linux's postgres user and running the problem from there. If it runs as user postgres, you've proven it's a permissions problem. Logged into psql as user postgres, grant all priveleges on the database and the table to the user in question. You can later revoke any dangerous priveleges for security's sake, but you want to quickly diagnose the problem.

Another troubleshooting step is to try the same activities in the psql environment logged in as the original user. If you can't accomplish the same activities, it's obviously not a problem in your program -- it's something in the database, probably lack of permissions.

Be sure you're querying the right table. Class Rock sends SQL queries to table rocks. Once again, this is Ruby magic accomplished in the ActiveRecord parent class, it can be overridden, but the code is much easier to write with the defaults.

Diagnostic Tests

Once you've read the error message correctly, rounded up the usual suspects, commented out enough to know where the error gets thrown, it's time to perform diagnostic tests to figure out what's wrong. See The Universal Troubleshooting Process Page on the Troubleshooters.Com website.

Conclusion

Ruby presents three different methods to access relational databases like MySQL, Postgres, Oracle and the like:
  1. Database driver
  2. DBI
  3. ActiveRecord
The database drivers are each hand crafted to access a specific database. They have different method names from each other, meaning that if you switch back end databases, you'll need to rewrite substantial portions of your program. The only reason to use the database specific drivers is to take advantage of database specific features, and possibly to make the program run a little faster and use a little less memory. With the database specific drivers, you manipulate the database by passing SQL statements as strings via the interface methods.

Ruby-DBI is a wrapper around all the database drivers, giving you a united method set to do the majority of your database activities. The only remaining database dependencies are caused by the fact that the different databases support slightly different SQL syntaxes. With DBI, you manipulate the database by passing SQL statements as strings via the interface methods.

Whereas the database drivers and DBI provide a simple vehicle to use an SQL statement, the ActiveRecord technique provides database access without the need for SQL statements. The ActiveRecord techniques match objects to database rows. The advantages are almost zero database dependencies within the subset of capabilities provided by ActiveRecord, and a set of defaults (such as automatic naming of tables based on class names) that make coding a database app much quicker and easier.

If you know SQL, you can pretty much write an app using database drivers or DBI today. With ActiveRecord, you'll need to learn a new set of skills, complete with a set of methods specific to Ruby's ActiveRecord class. You wouldn't do this to create a one-off app, but if you are or plan to be a frequent Ruby programmer and you plan to frequently hit databases from your Ruby programs, learning ActiveRecord will benefit you greatly.  Ruby on Rails uses ActiveRecord for the vast majority of its database access, so if you use or plan to use Ruby on Rails, ActiveRecord is a must.

Troubleshooters.ComCode Corner * Linux Library