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.4.2. Updating a Record:

dbi_conn.execute "UPDATE persons SET city = 'Madrid' WHERE city = 'San Francisco'"

2.4.3. Deleting a Record:

dbi_conn.execute "DELETE FROM persons WHERE city = '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." },
  ]
)

3.5. Read and scan Data

puts User.find(1).attributes

puts Todo.find_by(user_id: 14).attributes