Ruby/Informix: Examples

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