eRuby: Using Ruby DBI for database connectivity--tutorial.jcwcn.com

When you create a web application you sometimes need database transparency in your code. By this I mean you want your code to work regardless of the type or brand of database choosen by the end user.

Ruby DBI is not a true connection layer. Many install it thinking that it will help them connect to their MySQL database and then they find that nothing works. Ruby DBI is an abstration layer or so called "wrapper" class that filters multiple differences in database code into a single interface. So if a Oracle database, MySQL and PostGreSQL all have different syntax for selecting a database row it will not matter in your code. In your code the selection of a row will be the same for all three.

Using Ruby DBI has its pluses as described earlier but it also has some drawbacks. One of these is that since the DBI module has no connectivity capabilities on it's own you have to install other software to support it. This means that if you want to support six different database you will first have to make sure that there are six "real" and working connection drivers present.

If you are planning only to support one database type then you may want to skip using DBI. The reason for this is that DBI can limit the what the database is capable of doing. You may want to use a new feature that has no DBI counterpart. You may find that when using DBI with certain databases you lose performance. If these are not concerns then DBI is a good solution for maintaining database portability.

About MySQL

Because MySQL is the most popular of the listed database I will use it as a basis for the examples and instructions for use of DBI. There will be other list of examples in later turorials.

Installing a DBI module for MySQL

Before you install the Ruby DBI module for MySQL you will have to install Ruby MySQL. As mentioned DBI will not work on it's own. So before you continue here you will should follow the previous tutorial on installing Ruby MySQL. Windows users can also find information in the table below. Notice that though DBI supports many databases Ruby does not. If you know of a source for database drivers that are missing in this table please contact us.

DB	DBI dependacies	Driver Linux	Driver Windows
ADO	Requires win32ole, part of the Ruby standard library.
DB2	Depends on ruby-db2 there is also a DB2 package for Rails that contains DB2 and all the drivers for Ruby.		site
Frontbase	Depends on ruby-frontbase	download
Firebird	Depends on fireruby package	download	download
mSQL	Depends on ruby- msql
MySQL	Depends on ruby-mysql	site	download
ODBC	Depends on the ruby-odbc	download	download
Oracle	Depends on the the ruby-oci8	download	download
PostGreSQL	Depends on either the postgres or postgres-pr	download	download gem download zip
Proxy	Depends on distributed Ruby (DRb), part of the Ruby standard library.
SQLite3	Depends on the sqlite3	download	download
SQLRelay	Depends on the sqlrelay package at http://sqlrelay.sourceforge.net/

DBI Installation

Once you have installed the database drivers needed and possibly database servers then you can move on to getting DBI installed. This installation routine is the same on both Linux and Windows machines with the exception of expanding the compressed archives. If you are using Windows then you will need a zip file handler that has support for *.tar.gz files. Most of the lastest software has this, I use WinRar but 7-zip is an excellent open source and free windows archiving program. 7-zip supports just about every format you could think of and does command line if you want. You can also install Cygwin and follow the Linux way of doing things.

   http://ruby-dbi.sourceforge.net/

After downloading the compressed tar file, which you should unpack after downloading it. For example, if the current version is 0.0.19, the distribution file can be unpacked using either of the following commands:

   % tar zxf ruby-dbi-all-0.0.19.tar.gz  
   % gunzip < ruby-dbi-all-0.0.19.tar.gz | tar xf -

You will want to run the following command to get a list of the names to use in your configuration of the setup.

   % ruby setup.rb --help

will get you a list of dbd driver names.

   % ruby setup.rb config

Normally you would change directories at the command prompt and start your installation by configuring the setup.rb script in the DBI directory. This command without arguements looks like this:

   % ruby setup.rb config

If you use this command without parameter then you almost certainly will get an error on installation. This is because without parameters the install script will try and install all DBI drivers regardless of the fact that there are no backing database drivers present. To avoid this installation error should set specific parameters for those databases that are actually available . For example, to configure only the main DBI for MySQL and PostGreSQL use the following. Take notice of the comma used to seperate the different driver names. It is also best to use the commands this way because

   % ruby setup.rb config --with=dbi,dbd_mysql,dbd_postgre

After configuring the distribution, build and install it:

   % ruby setup.rb setup     % ruby setup.rb install

You may need to run the installation command as root in Linux. The previous commands work on Cygwin if you are using this environment. In plain Windows the command prompt should already be running in administrator level for either the Windows native command prompt. Those percentage chararacters would be replaced by a Windows directory indicator

 c:\> ruby setup.rb config --with=dbi,dbd_mysql,dbd_postgre

Module DBD

Constants

API_VERSION: Use this in your DBD driver to ensure it is used with the correct DBD API-Version

Module DBI

Constants

VERSION

Version of the DBI Interface

SQL_FETCH_NEXT

SQL_FETCH_PRIOR

SQL_FETCH_FIRST

SQL_FETCH_LAST

SQL_FETCH_ABSOLUTE

Constants for StatementHandle#fetch_scroll.

SQL_BIT

SQL_TINYINT

SQL_SMALLINT

SQL_INTEGER

SQL_BIGINT

SQL_FLOAT

SQL_REAL

SQL_DOUBLE

SQL_NUMERIC

SQL_DECIMAL

SQL_CHAR

SQL_VARCHAR

SQL_LONGVARCHAR

SQL_DATE

SQL_TIME

SQL_TIMESTAMP

SQL_BINARY

SQL_VARBINARY

SQL_LONGVARBINARY

SQL_OTHER

Constants representing SQL types.

Exceptions

Example:

   require "dbi"

   begin
       # connect to the MySQL server
       dbh = DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")
       # get server version string and display it
       row = dbh.select_one("SELECT VERSION()")
       puts "Server version: " + row[0]
   rescue DBI::DatabaseError => e
       puts "An error occurred"
       puts "Error code: #{e.err}"
       puts "Error message: #{e.errstr}"
   ensure
       # disconnect from server
       dbh.disconnect if dbh
   end

Warning < RuntimeError

For important warnings such as data truncation, etc.

Error < RuntimeError

Base class of all other error exceptions.
Use this to catch all errors.

InterfaceError < Error

Exception for errors related to the DBI interface rather
than the database itself.

NotImplementedError < InterfaceError

Exception raised if the DBD driver has not specified
a mandatory method (not in Python API 2.0).

DatabaseError < Error

Exception for errors related to the database.

Has three attributes: err, errstr and state.

DataError < DatabaseError

Exception for errors due to problems with the processed
data, such ase division by zero, numeric value out of range, etc.

OperationalError < DatabaseError

Exception for errors related to the database's operation which
are not necessarily under the control of the programmer, such as
unexpected disconnect, datasource name not found, transaction
could not be processed, a memory allocation error occurred during
processing, etc.

IntegrityError < DatabaseError

Exception raised when the relational integrity of the database
is affected, e.g., a foreign key check fails.

InternalError < DatabaseError

Exception raised when the database encounters an internal error,
e.g., the cursor is not valid anymore, the transaction is out of sync.

ProgrammingError < DatabaseError

Exception raised for programming errors, e.g., table not found
or already exists, syntax error in SQL statement, wrong number
of parameters specified, etc.

NotSupportedError < DatabaseError

Raised if, e.g., commit is called for a database that does not
support transactions.

Module functions

   require "dbi"

   begin
       # connect to the MySQL server
       dbh = DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")
       # get server version string and display it
       row = dbh.select_one("SELECT VERSION()")
       puts "Server version: " + row[0]
   rescue DBI::DatabaseError => e
       puts "An error occurred"
       puts "Error code: #{e.err}"
       puts "Error message: #{e.errstr}"
   ensure
       # disconnect from server
       dbh.disconnect if dbh
   end

DBI.connect( driver_url, user=nil, auth=nil, params=nil )

Connect to the database specified by driver_url, which may
look like "dbi:Oracle:oracle.neumann".

Returns a DBI::DatabaseHandle object, or if called with a code-block,
calls this block with the new DBI::DatabaseHandle as parameter and
calls disconnect after calling the block if it was not yet disconnected by
the user.

DBI.available_drivers

Returns an Array of all available DBD drivers.
The strings which represent the DBD drivers are partial DSNs
(e.g., "dbi:Oracle:").

DBI.data_sources( driver )

Returns all available DSNs for the driver, which
is a partial DSN (e.g., "dbi:Oracle:").

DBI.disconnect_all( driver=nil )

Disconnects all active connections of driver or
all drivers if driver is nil.

DBI.trace(mode=nil, output=nil)

Sets the trace mode for all subsequently created Handles to these values.

If a parameter is nil, the value is not changed.
mode defaults to 2 if it is nil, and output to STDERR if a value was not
previously set.
For mode, the values 0, 1, 2 or 3 are allowed.

Note: Tracing is only activated if you load the module "dbi/trace", because tracing currently
depends on AspectR > 0.3.3.

Class DBI::Handle

Abstract base class for all "Handles" (DriverHandle, DatabaseHandle, StatementHandle).

Instance Methods

func( function, *values )

Calls the driver-specific extension function named by
function with values as parameters.

trace(mode=nil, output=nil)

Sets the trace mode for this handle as well as for all sub-handles (in the case of DriverHandle and
DatabaseHandle).

If a parameter is nil, the value is not changed.
mode defaults to 2 if it is nil, and output to STDERR if a value was not
previously set.
For mode, the values 0, 1, 2 or 3 are allowed.

Note: Tracing is only activated if you load the module "dbi/trace", because tracing currently
depends on AspectR > 0.3.3.

Class DBI::DatabaseHandle

Superclass

DBI::Handle

Instance Methods

connected?

Returns true if the connection was not yet disconnected
by calling disconnect, otherwise false.

disconnect

Disconnects the connection.

prepare( stmt )

prepare( stmt ) {|statement_handle| aBlock}

Prepares the SQL statement stmt and returns a
DBI::StatementHandle, or if called with a code-block,
calls the block with the handle as its parameter and after that
calls #finish onto the handle to free all resources

execute( stmt, *bindvars )

execute( stmt, *bindvars ) {|statement_handle| aBlock}

Immediately executes the SQL statement stmt after binding
the values in bindvars to the placeholders in the statement.

Returns a DBI::StatementHandle, or if called with a code-block,
calls the block with the handle as its parameter and after that
calls #finish onto the handle to free all resources.

do( stmt, *bindvars )

Same as execute except the RPC (Row Processed Count) is returned
rather than a DBI::StatementHandle.

select_one( stmt, *bindvars)

Executes the statement after binding the values to the placeholders in the statement, then returns the
first row as a reference to a DBI::Row object.

select_all( stmt, *bindvars)

Executes the statement after binding the values to the parameters, then returns all
resulting rows as an array of DBI::Row objects.

If called as an iterator, the passed DBI::Row objects are only references.

tables

Returns a list of all tables and views.

columns( table )

Gets more information about the columns of the table table.
Returns an array containing a DBI::ColumnInfo object for each column
in the table.

ping

Returns true if the connection is active, otherwise false.

In contrast to connected?, ping tests if the connection is
still active by executing some SQL or doing something else.

quote( value )

Quotes the given value value in database-specific fashion and returns the result.

commit

Commits the current transaction.

rollback

Rolls back the current transaction.

transaction {|database_handle| aBlock}

First commits the current transaction, then
executes the given block where the parameter is
the object itself (the database handle). If the
block raises an exception, then it rolls back the transaction;
otherwise, it commits the transaction.

[attr]

[attr] = val

Gets or sets the attribute attr.
An attribute can for example be "AutoCommit", which can be set to
true or false. Attributes are database dependent.

Class DBI::StatementHandle

Superclass

DBI::Handle

Mixins

Enumerable

Instance Methods

bind_param( param, value, attribs=nil )

Binds the value value to a placeholder.
The placeholder is represented by param, which is either a
String representing the name of the
placeholder used in the SQL statement (e.g., Oracle: "SELECT * FROM EMP WHERE ENAME = :ename"),
or an integer that indicates the number of the placeholder.
Placeholder numbers begin at 1.

attribs is not yet used in this version, but could later be a hash containing more information
like parameter type, etc.

execute( *bindvars )

Executes the statement after binding the values in bindvars to the placeholders in the statement.

finish

Frees the resources for the statement.
After calling finish, no other operation on this
statement is valid.

cancel

Frees any result set resources which were made after a call
to execute.
After calling this method, calls to any of the fetch methods
are no longer valid.

column_names

Returns an Array of all column names.

column_info

Returns an Array containing a DBI::ColumnInfo object for each column
in the result set.

rows

Returns the RPC (Row Processed Count) of the last executed statement, or
nil if no such exists.

fetchable?

Returns true if you can fetch rows using fetch, etc.

fetch

Returns a DBI::Row object, or nil if there are
no more rows to fetch.

When called as an iterator, the block is called for each row
until no more rows are available. Each row is passed to the
block as a DBI::Row object.

Note that the returned or passed DBI::Row object is only a reference and
should be copied (dup) if it is stored elsewhere.

each {|row| aBlock }

Same as fetch called as an iterator.

fetch_array

Returns the current row as an Array or nil if no more
rows are available.

Can also be called as an iterator.

fetch_hash

Returns the current row as a Hash or nil if no more
rows are available.

Can also be called as an iterator.

fetch_many( cnt )

Returns an Array of the next cnt rows, which are
stored as DBI::Row objects.

Returns the empty array [] if there are no more rows.

fetch_all

Same as fetch_many except that all rows are returned.

fetch_scroll( direction, offset=1 )

direction is one of the following constants:

SQL_FETCH_NEXT
SQL_FETCH_PRIOR
SQL_FETCH_FIRST
SQL_FETCH_LAST
SQL_FETCH_ABSOLUTE
SQL_FETCH_RELATIVE

offset is a positive or negative number (only when SQL_FETCH_RELATIVE is used).

fetch_scroll does not automatically free the result set if no more rows are available,
e.g., if you get the last row.

Returns a DBI::Row object, or nil if no row is available.

Note that the returned DBI::Row object is only a reference and
should be copied (dup) if it is stored elsewhere.

[attr]

[attr] = val

Gets or sets the attribute attr.

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]