Creating a simple Elixir application to test database connectivity to a legacy Oracle database (SCOTT) using 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
Dont 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<from n0 in "emp", where: n0.ename == "SMITH",
select: n0.empno>
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