Ruby
You can use LeanXcale’s SQL interface from Ruby directly or by using Active Record.
1. Prerequisites
The SQL interface in LeanXcale is exposed to Ruby through our ODBC connector, which must be installed first. Please refer to the documentation to install the ODBC connector for Linux or Windows and then come back here and continue installing the necessary packages depending on if you’re choosing to connect to LeanXcale using Ruby directly, or you want to use Active Record.
2. Quick Start using Ruby directly
2.1. Install
Install the DBI, Active Record and ODBC Adapter packages using gem
:
gem install dbi
gem install dbd-odbc
gem install ruby-odbc
2.2. Connect to LeanXcale
The first step is to connect to the database with the same DSN name we previously included in the odbc.ini/.odbc.ini
file when installing the LeanXcale ODBC connector:
dbi_conn = DBI.connect("DBI:ODBC:LeanXcale")
Keep in mind that if you are connecting to a LeanXcale database working with security, you will need some changes in your ODBC configuration. Check them here.
Ruby automatically closes the connection when your script ends, but you may close it manually if needed:
dbi_conn.disconnect if dbi_conn
2.3. Create a table
To create a new table in LeanXcale, run the following block of code:
dbi_conn.execute <<-eos
CREATE TABLE persons (socialsecurity VARCHAR(50), firstname VARCHAR(40),
lastname VARCHAR(40), city VARCHAR(50), PRIMARY KEY(socialsecurity))
eos
2.4. Insert, update and delete
2.4.1. Inserting one Record:
Inserting one row is a straightforward task:
dbi_conn.execute <<-eos
INSERT INTO persons (socialsecurity, firstname, lastname, city)
VALUES ('728496923-J', 'John', 'Smith', 'San Francisco')
eos
Or, using prepared statements:
stmt = dbi_conn.prepare "INSERT INTO persons (socialsecurity, firstname, lastname, city) VALUES (?, ?, ?, ?)"
stmt.execute ["7691241241-Z", "Carl", "Parson", "New York"]
2.5. Read and scan Data
sth = dbi_conn.execute "SELECT * FROM persons WHERE city = 'Madrid'"
colCount = sth.column_names.size
colNames = ""
sth.column_names.each do |name|
colNames.concat(name)
colNames.concat('\t')
end
puts colNames
while row = sth.fetch
rowValues = ""
(0..colCount - 1).each do |n|
val = row[n]
if val.nil?
val = "<<NULL>>"
end
rowValues.concat(val)
rowValues.concat("|")
end
puts rowValues
end
sth.finish
3. Quick Start using Ruby with Active Record
3.1. Installing the Active Record adapter
To use Active Record with LeanXcale, you can download the Ruby Active Record Adapter gem from the Drivers page. Also, DBI, Active Record and the ODBC Adapter packages must be installed. We recommend installing these versions, as our team has tested them:
gem install dbi
gem install activerecord -v '5.0.1'
gem install odbc_adapter -v '5.0.5'
gem install lx_odbc_adapter-1.9.9.gem
3.2. Connect to LeanXcale
The first step is to connect to the database with the same DSN name we previously included in the odbc.ini/.odbc.ini
file when installing the LeanXcale ODBC connector:
require "odbc_adapter"
require "lx_odbc_adapter"
require "pry"
ODBCAdapter.register(/leanxcale/i, ODBCAdapter::Adapters::LeanxcaleODBCAdapter)
options = { adapter: "odbc" }
options[:dsn] = "LeanXcale"
ActiveRecord::Base.establish_connection(options)
3.3. Create a table
To create a new table with a relation, you can use the following code:
ActiveRecord::Schema.define do
create_table :users, force: true, id: false do |t|
t.primary_key :id
t.string :first_name
t.string :last_name
t.integer :letters
t.timestamps null: false
end
create_table :todos, force: true do |t|
t.integer :user_id
t.text :body
t.boolean :published, default: false
t.timestamps null: false
end
end
3.4. Insert, update and delete
3.4.1. Inserting one Record:
Inserting rows is straightforward with Active Record:
class User < ActiveRecord::Base
has_many :todos, dependent: :destroy
scope :lots_of_letters, -> { where(arel_table[:letters].gt(10)) }
create(
[
{ id: 1, first_name: "Kevin", last_name: "Deisz", letters: 10 },
{ id: 10, first_name: "Michal", last_name: "Klos", letters: 10 },
{ id: 11, first_name: "Jason", last_name: "Dsouza", letters: 11 },
{ id: 12, first_name: "Ash", last_name: "Hepburn", letters: 10 },
{ id: 13, first_name: "Sharif", last_name: "Younes", letters: 12 },
{ id: 14, first_name: "Ryan", last_name: "Brüwn", letters: 9 },
]
)
end
Then, for its relation:
class Todo < ActiveRecord::Base
belongs_to :user
end
User.find(1).todos.create(
[
{ body: "Lorem ipsum dolor sit amet, consectetur adipiscing elit.", published: true },
{ body: "Praesent ut dolor nec eros euismod hendrerit." },
{ body: "Curabitur lacinia metus eget interdum volutpat." },
]
)
User.find(12).todos.create(
[
{ body: "Nulla sollicitudin venenatis turpis vitae finibus.", published: true },
{ body: "Proin consectetur id lacus vel feugiat.", published: true },
{ body: "Pellentesque augue orci, aliquet nec ipsum ultrices, cursus blandit metus." },
{ body: "Nulla posuere nisl risus, eget scelerisque leo congue non." },
{ body: "Curabitur eget massa mollis, iaculis risus in, tristique metus." },
]
)
User.find(14).todos.create(
[
{ body: "In hac habitasse platea dictumst.", published: true },
{ body: "Integer molestie ornare velit, eu interdum felis euismod vitae." },
]
)