Friday, April 23, 2010

Getting started with #firebird and #lua on #ubuntu

I needed lua for a work related project and for database persistence i use firebird

So on ubuntu lucid lynx i have installed this way the Firebird Lua client

sudo apt-get install luarocks
sudo luarocks install fbclient

Tested with my local django db , Here is my test script for firebird connection

local fb = require 'fbclient.class'
local at = fb.attach('localhost:/var/lib/firebird/2.1/data/firedjango.fdb', 'SYSDBA', 'masterkey')
local sql = 'SELECT a.ID, a.FIRST_NAME FROM BOOKS_AUTHOR a where a.FIRST_NAME=?'
for st, id, firstname in at:exec(sql,'Jack') do
print(id, firstname)
end
at:close()


Then i wanted to insert some values
for that i have used exec_imediate and the interesting fact you must use double quote for insert statement otherwise you wil get Column unknown JACK if you try to put the name in single quote

local fb = require 'fbclient.class'
local at = fb.attach('localhost:/var/lib/firebird/2.1/data/firedjango.fdb', 'SYSDBA', 'masterkey')
local InsertSql = "INSERT INTO BOOKS_AUTHOR (ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (null, 'Jack', 'Jack', 'Jack')";
at:exec_immediate(InsertSql);
at:close()


The complete example would be with insert and read like this

local fb = require 'fbclient.class'
local at = fb.attach('localhost:/var/lib/firebird/2.1/data/firedjango.fdb', 'SYSDBA', 'masterkey')
local InsertSql = "INSERT INTO BOOKS_AUTHOR (ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (null, 'Jack', 'Sparrow', 'Jack@example.com')";
at:exec_immediate(InsertSql);
local sql = 'SELECT a.ID, a.FIRST_NAME,a.LAST_NAME,a.EMAIL FROM BOOKS_AUTHOR a where a.FIRST_NAME=?'
for st, id, firstname,lastname,email in at:exec(sql,'Jack') do
print(id, firstname,lastname,email)
end
at:close()

No comments: