Setting up Elixir and Ecto with jamdb_oracle

Posted by Pete McBreen 20 May 2019 at 15:00

Creating a simple Elixir application to test database connectivity to a legacy Oracle database (SCOTT) usign jamdb_oracle. Sorry for the wall of text, but could not find this clearly documented anywhere else, so putting it out here in case I ever need to find it again

C:\Dev\lelixir>mix new ora --sup
* creating README.md
* creating .formatter.exs
* creating .gitignore
* creating mix.exs
* creating config
* creating config/config.exs  
* creating lib
* creating lib/ora.ex
* creating lib/ora/application.ex
* creating test
* creating test/test_helper.exs
* creating test/ora_test.exs

Your Mix project was created successfully.
You can use "mix" to compile it, test it, and more:

    cd ora
    mix test

Run "mix help" for more commands.

C:\Dev\lelixir>cd ora

C:\Dev\lelixir\ora>

Setup for Oracle, using jamdb_oracle, need to edit ./mix.exs , adding in the extra applications that need to run

  # Run "mix help compile.app" to learn about applications.
  def application do
    [
      extra_applications: [:logger, :ecto, :jamdb_oracle],
      mod: {Ora.Application, []}

further down add in the dependencies, specifying the versions the application wants from Hex.pm.

  # Run "mix help deps" to learn about dependencies.
  defp deps do
    [
      {:ecto, "~> 3.0"},
      {:jamdb_oracle, "~>0.3.2"}

Then need to run commands to get the dependencies and to compile them. Note that there are extras pulled in when the library you request also has dependencies.

C:\Dev\lelixir\ora>mix deps.get
Resolving Hex dependencies...
Dependency resolution completed:
New:
  base64url 0.0.1
  connection 1.0.4
  db_connection 2.0.6
  decimal 1.7.0
  ecto 3.1.4
  ecto_sql 3.1.3
  jamdb_oracle 0.3.2
  jose 1.9.0
  telemetry 0.4.0
* Getting ecto (Hex package)
* Getting jamdb_oracle (Hex package)
* Getting ecto_sql (Hex package)
* Getting jose (Hex package)
* Getting base64url (Hex package)
* Getting db_connection (Hex package)
* Getting telemetry (Hex package)
* Getting connection (Hex package)
* Getting decimal (Hex package)

Followed by compilation, I got some warnings here, but still worked later on.

C:\Dev\lelixir\ora>mix compile
==> base64url (compile)
Compiled src/base64url.erl
==> connection
Compiling 1 file (.ex)
Generated connection app
==> jose
Compiling 90 files (.erl)
Compiling 8 files (.ex)
warning: function Poison.EncodeError.exception/1 is undefined 
 (module Poison.EncodeError is not available)
  lib/jose/poison/lexical_encoder.ex:8

Generated jose app
===> Compiling telemetry
==> decimal
Compiling 1 file (.ex)
Generated decimal app
==> db_connection
Compiling 16 files (.ex)
Generated db_connection app
==> ecto
Compiling 54 files (.ex)
Generated ecto app
==> ecto_sql
Compiling 25 files (.ex)
Generated ecto_sql app
==> jamdb_oracle
Compiling 5 files (.erl)
Compiling 3 files (.ex)
warning: function table_exists_query/1 required by behaviour Ecto.Adapters.SQL.Connection 
 is not implemented (in module Ecto.Adapters.Jamdb.Oracle.Connection)
  lib/jamdb_oracle_ecto.ex:138

Generated jamdb_oracle app
==> ora
Compiling 2 files (.ex)
Generated ora app

After that had to type mix ecto.gen.repo, Which gave the output

warning: could not find Ecto repos in any of the apps: [:ora].

You can avoid this warning by passing the -r flag or by setting the
repositories managed by those applications in your config/config.exs:

    config :ora, ecto_repos: [...]

** (Mix) ecto.gen.repo expects the repository to be given as -r MyApp.Repo

Which required the following edits to ./config/config.exs

# This file is responsible for configuring your application
# and its dependencies with the aid of the Mix.Config module.
use Mix.Config

config :ora, Ora.Repo,
  database: "SCOTT", # original Oracle test database
  username: "user",
  password: "pass",
  hostname: "db.domain.name", 
  port: 1521 # default oracle port

 config :ora, ecto_repos: [Ora.Repo]

rerunning the command was successful, with the message

* creating lib/ora
* creating lib/ora/repo.ex
* updating config/config.exs
Don't forget to add your new repo to your supervision tree
(typically in lib/ora/application.ex):

    # For Elixir v1.5 and later
    {Ora.Repo, []}

    # For Elixir v1.4 and earlier
    supervisor(Ora.Repo, [])

And to add it to the list of ecto repositories in your
configuration files (so Ecto tasks work as expected):

    config :ora,
      ecto_repos: [Ora.Repo]

At this point, ./lib/ora/repo.ex needed a minor edit to use the jamdb adapter

defmodule Ora.Repo do
  use Ecto.Repo,
    otp_app: :ora,
    adapter: Ecto.Adapters.Jamdb.Oracle
end

and ./lib/ora/application.ex needed

  def start(_type, _args) do
    # List all child processes to be supervised
    children = [
      {Ora.Repo, []}
      # Starts a worker by calling: Ora.Worker.start_link(arg)
      # {Ora.Worker, arg}
    ]

At this point, OK to test it out

iex -S mix
Compiling 4 files (.ex)
Generated ora app
Interactive Elixir (1.8.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> alias Ora.Repo
Ora.Repo
iex(2)> import Ecto.Query, only: [from: 2]
Ecto.Query
iex(3)> query = from e in "emp", where: e.ename == "SMITH", select: e.empno
#Ecto.Query
iex(4)> Repo.all(query)

16:10:06.896 [debug] QUERY OK source="emp" db=15.0ms
SELECT n0.empno FROM emp n0 WHERE (n0.ename = 'SMITH') []
[7369]
iex(5)>

Mission accomplished, connected to legacy Oracle database (SCOTT) using Elixir and jamdb_oracle

Improving Wetware

Pete's very informal blog

Archives

Pages

Syndicate

Setting up Elixir and Ecto with jamdb_oracle