Ruby

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, so you’ll need to install it first. Please, refer to the documentation to install the ODBC connector for Linux or Windows.

2. Quick Start using Ruby directly

2.1. Installing

Install the DBI, Active Record and ODBC Adapter packages using gem:

gem install dbi
gem install dbd-odbc
gem install ruby-odbc

2.2. Connecting to LeanXcale

The first thing to do is connecting to the database using the same DSN name we put in odbc.ini/.odbc.ini file while installing the LeanXcale ODBC connector:

dbi_conn = DBI.connect("DBI:ODBC:myodbc_test_dsn")

Remember that Ruby will automatically close the connection when your script ends, but you can close it by hand if you need so:

dbi_conn.disconnect if dbi_conn

2.3. Creating a table

To create a new table in LeanXcale, you can use 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. Inserting, updating and deleting

2.4.1. Inserting one Record:

Inserting one row is a very easy task:

dbi_conn.execute <<-eos
INSERT INTO persons (socialsecurity, firstname, lastname, city)
VALUES ('728496923-J', 'John', 'Smith', 'San Francisco')
eos

Or with 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. Reading and scanning 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. You will also need to install DBI, Active Record and ODBC Adapter packages. We recommend installing this versions, as they’ve been tested by our team:

gem install dbi
gem install activerecord -v '5.0.1'
gem install odbc_adapter -v '5.0.5'
gem install lx_odbc_adapter-1.5.4.gem

3.2. Connecting to LeanXcale

The first thing to do is connect to the database using the same DSN name we put in odbc.ini/.odbc.ini file while 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] = "myodbc_test_dsn"

ActiveRecord::Base.establish_connection(options)

3.3. Creating 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. Inserting, updating and deleting

3.4.1. Inserting one Record:

Inserting rows is very easy 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

And 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. Reading and scanning Data

puts User.find(1).attributes

puts Todo.find_by(user_id: 14).attributes