require 'fb'
include Fb
db = Database.new( :database => "localhost:c:/var/fbdata/readme.fdb", :username => 'sysdba', :password => 'masterkey')
conn = db.connect rescue db.create.connect
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}") }
ary = conn.query("SELECT * FROM TEST WHERE ID = 0 OR ID = 9") ary.each {|row| puts "ID: #{row[0]}, Name: #{row[1]}" }
ary = conn.query(:hash, "SELECT * FROM TEST WHERE ID = 0 OR ID = 9") ary.each {|row| puts "ID: #{row['ID']}, Name: #{row['NAME']}" }
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 end end puts "We updated a total of #{total_updated} rows."
deleted = conn.execute("DELETE FROM TEST WHERE ID > ? AND ID < ?", 0, 9) puts "Expecting to delete 8 rows, we have deleted #{deleted}."
conn.transaction
for i in 10..1000 conn.execute("INSERT INTO TEST VALUES (?, ?)", i, "Jane #{i}") end
conn.rollback
janes = conn.query("SELECT * FROM TEST WHERE ID >= 10") puts "Expecting zero rows, we find #{janes.size} Janes."
conn.transaction
10.upto(19) do |i| conn.execute("INSERT INTO TEST (ID, NAME) VALUES (?, ?)", i, "Sue #{i}") end
conn.commit
cursor = conn.execute("SELECT * FROM TEST") while row = cursor.fetch(:hash) break if row['NAME'] =~ /e 13/ end cursor.close
conn.execute("SELECT * FROM TEST") do |cursor| while row = cursor.fetch(:hash) break if row['NAME'] =~ /e 13/ end end
conn.transaction do 20.upto(25) do |i| conn.execute("INSERT INTO TEST VALUES (?, ?)", i, "George #{i}") end end
begin conn.transaction do execute("INSERT INTO TEST VALUES (0, 'Trouble')") puts "This line should never be executed." end rescue puts "Rescued." end
trouble = conn.query("SELECT * FROM TEST WHERE NAME = 'Trouble'") puts "Expecting zero rows, we find #{trouble.size} 'Trouble' rows."
conn.transaction("SNAPSHOT") do
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}") end end
hi = conn.query("SELECT * FROM TEST WHERE ID >= ?", 100) puts "Expecting zero rows, we find #{hi.size} Hi rows." end
conn.transaction("READ COMMITTED") do
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}") end end
hello = conn.query("SELECT * FROM TEST WHERE ID >= ?", 200) puts "Expecting ten rows, we find #{hello.size}." end
conn.close