Code required to use the Informix extension
require 'informix'
Connecting to a database
db = Informix.connect('stores')
Creating a table
db.execute('create table env(id int, name varchar(10), value varchar(30))')
Inserting a record
stmt = db.prepare('insert into env values(0, "EDITOR", "vi")')
stmt.execute
A bulk insert (faster)
cur = db.cursor('insert into env values(?, ?, ?)')
db.transaction {
cur.open
id = 1
ENV.each {|k, v|
cur.put(id, k, v)
id += 1
}
cur.close
}
Selecting records and updating them at the same time
cur = db.cursor('select * from env for update')
update = db.prepare("update env set value = ? where current of #{cur.id}")
cur.open.each_hash { |r| update.execute(r['value'].upcase) if r['id']%2 == 0 }
cur.close
A parameterized query that returns only one record
stmt = db.prepare('select value from env where name = ?')
result = stmt.execute("SHELL")
puts "SHELL is #{result["value"]}"
Using a cursor to traverse a table
cursor = db.cursor('select name, value from env')
cursor.open.each {|row|
puts row*" = "
}.close
Deleting records with a parameterized query
n = db.prepare('delete from env where id < ?') {|stmt| stmt.execute(3) }
puts "#{n} records deleted"
Dropping a table
db.execute('drop table env')
Storing Smart Large Objects (CLOB/BLOB)
Slob = Informix::Slob
db.execute("create table album (filename varchar(30), picture blob)")
stmt_insert = db.prepare("insert into album values(?, ?)")
Dir.glob("*jpg") {|filename|
slob = db.slob(Slob::BLOB)
slob.write(File.read(filename)) # same as slob << File.read(filename)
slob.close
stmt_insert.execute(filename, slob)
}
Retrieving Smart Large Objects (CLOB/BLOB)
cur = db.cursor("select filename, picture from album")
cur.open.each_hash {|r|
slob = r['picture'].open
File.open(r['filename'], "w") {|f|
f.print r['picture'].read(r['picture'].size)
}
slob.close
}.close