Firebird Extension Library for Ruby
C Ruby
Switch branches/tags
Nothing to show
Clone or download
Pull request Compare This branch is 15 commits ahead, 107 commits behind rowland:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.


# Ruby Firebird Extension Library
# Please see the RDoc documentation for API details.
# What follows is a brief overview of how to use this library.
# This file is executable.  However, you may have to adjust the database connection parameters.

# Load the library

require 'fb'

# The library contains on module, Fb.
# Within Fb are four primary classes, Fb::Database, Fb::Connection, Fb::Cursor and Fb::Error.
# For convenience, we'll include these classes into the current context.

include Fb

# The Database class acts as a factory for Connections.
# It can also create and drop databases.

db =
  :database => "localhost:c:/var/fbdata/readme.fdb",
  :username => 'sysdba',
  :password => 'masterkey')

# :database is the only parameter without a default.

# Let's connect to the database, creating it if it doesn't already exist.

conn = db.connect rescue db.create.connect

# We'll need to create the database schema if this is a new database.

conn.execute("CREATE TABLE TEST (ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(20))") if !conn.table_names.include?("TEST")

# Let's insert some test data using a parameterized query.  Note the use of question marks for place holders.

10.times {|id| conn.execute("INSERT INTO TEST VALUES (?, ?)", id, "John #{id}") }

# Here we'll conduct a spot check of the data we have just inserted.

ary = conn.query("SELECT * FROM TEST WHERE ID = 0 OR ID = 9")
ary.each {|row| puts "ID: #{row[0]}, Name: #{row[1]}" }

# Don't like tying yourself down to column offsets?

ary = conn.query(:hash, "SELECT * FROM TEST WHERE ID = 0 OR ID = 9")
ary.each {|row| puts "ID: #{row['ID']}, Name: #{row['NAME']}" }

# Let's change all the names.

total_updated = 0
conn.execute("SELECT ID FROM TEST") do |cursor|
  cursor.each do |row|
    updated = conn.execute("UPDATE TEST SET NAME = ? WHERE ID = ?", "John Doe #{row[0]}", row[0])
    total_updated += updated
puts "We updated a total of #{total_updated} rows."

# Actually, I only need the first and last rows.

deleted = conn.execute("DELETE FROM TEST WHERE ID > ? AND ID < ?", 0, 9)
puts "Expecting to delete 8 rows, we have deleted #{deleted}."

# Using a simple, per-connection transaction strategy, we'll demonstrate rollback and commit.


for i in 10..1000
  conn.execute("INSERT INTO TEST VALUES (?, ?)", i, "Jane #{i}")

# What was I thinking?  Let's roll that back.


# Are they still there?

janes = conn.query("SELECT * FROM TEST WHERE ID >= 10")
puts "Expecting zero rows, we find #{janes.size} Janes."

# Let's try again.


10.upto(19) do |i|
  conn.execute("INSERT INTO TEST (ID, NAME) VALUES (?, ?)", i, "Sue #{i}")

# That's more like it.


# It's important to close your cursor when you're done with it.

cursor = conn.execute("SELECT * FROM TEST")
while row = cursor.fetch(:hash)
  break if row['NAME'] =~ /e 13/

# You may find it easier to use a block.

conn.execute("SELECT * FROM TEST") do |cursor|
  while row = cursor.fetch(:hash)
    break if row['NAME'] =~ /e 13/

# That way the cursor always gets closed, even if an exception is raised.
# Transactions work the same way.  Here's one that should work.

conn.transaction do
  20.upto(25) do |i|
    conn.execute("INSERT INTO TEST VALUES (?, ?)", i, "George #{i}")

# The transaction is automatically committed if no exception is raised in the block.
# We expect trouble in this next example, on account of our primary key.

  conn.transaction do
    execute("INSERT INTO TEST VALUES (0, 'Trouble')")
    puts "This line should never be executed."
  puts "Rescued."

# Is it there?

trouble = conn.query("SELECT * FROM TEST WHERE NAME = 'Trouble'")
puts "Expecting zero rows, we find #{trouble.size} 'Trouble' rows."

# How about demonstrating a more advanced transaction?
# First, we'll start a snapshot transaction.
# This should give us a consistent view of the database.

conn.transaction("SNAPSHOT") do

# Then, we'll open another connection to the database and insert some rows.

  Database.connect(:database => "localhost:c:/var/fbdata/readme.fdb") do |conn2|
    for i in 100...110
      conn2.execute("INSERT INTO TEST VALUES (?, ?)", i, "Hi #{i}")
# Now, let's see if we see them.

  hi = conn.query("SELECT * FROM TEST WHERE ID >= ?", 100)
  puts "Expecting zero rows, we find #{hi.size} Hi rows."

# Now we will try our example again, only with a READ COMMITTED transaction.

conn.transaction("READ COMMITTED") do

# Then, we'll open another connection to the database and insert some rows.

  Database.connect(:database => "localhost:c:/var/fbdata/readme.fdb") do |conn2|
    for i in 200...210
      conn2.execute("INSERT INTO TEST VALUES (?, ?)", i, "Hello #{i}")
# Now, let's see if we see them.

  hello = conn.query("SELECT * FROM TEST WHERE ID >= ?", 200)
  puts "Expecting ten rows, we find #{hello.size}."

# Don't forget to close up shop.


# We could have called conn.drop.
# We could still call db.drop

5. Changes

0.01 - 1999-06-11 - baseline 
0.02 - 1999-07-16 - memory bug in ibconn_cursor, English documents.
0.03 - ???
0.04 - 2001-08-17 - insert, fetch blobs as strings
0.05 - 2001-09-05 - add block syntax for Connection#connect and Connection#execute
0.06 - 2004-11-02 - ???
0.07 - 2004-11-03 - ???

Change Log

2006-05-18 - 2006-06-04

* Forked to create Fb extension.
* Converted from K&R to ANSI C.
* Added ability to create and drop databases.
* Created near-comprehensive unit tests.
* Renamed functions for consistency and improved style.
* Improved error message reporting.
* Added the option of returning rows as Hashes as well as tuples.
* Fixed bug inserting blobs larger than 64K.
* Added methods to retrieve lists of tables, generators, views, stored procedures and roles.
* Added .NET-style connection strings.
* Improved reporting on column types.
* Added the option to report column names in lowercase where they were not already in mixed case (this feature is for Rails).
* Changed automatic transaction semantics so automatically-started transactions are also committed automatically.
* Converted from using a single, global transaction to a transaction per connection.
* Converted from using global buffers to per-cursor input and output buffers.
* Added RDoc documentation.
* Insert, update and delete statements may be given an array of tuples for a parameter and will be executed once per tuple.
* Rewrote README to match new API.


* Properly scaled NUMERIC and DECIMAL types when inserting with parameters.


* Rounded instead of flooring NUMERIC and DECIMAL types.
* Replace deprecated isc_interprete method call for FB2.


* Implement explicit end-of-data flag for cursors to avoid synchronization error in newer Firebird builds.
* Allocated longer buffers to avoid memory access error reported by valgrind
* Tightened up C syntax for stricter compilers.
* Factored symbol and regex creation out to global scope for efficiency.
* Removed global transaction functions that weren't exposed anyway.
* Removed remaining global state. This should allow for better concurrency.