Phoenix get SQL-Data from mongoDB/Oracle (by sql-queries not ORM)

Phoenix Forum Questions / Help
,
1

Hello everybody,

I am very new on the Phoenix/Elixir-topic and want to test, if the application is suitable to replace our old web-reporting-tool (based on Pythons flask).

My “problem”: we have data both from a mongodb and also from a productive oracle database. I don’t need CRUD ORM-functionalities, but only complex SQL-statements.

I already know, that there is no implementation for Oracle in Ecto. But can I get SQL-data directly without a ORM?

Thank you in advance.

Greetings
AXEL

4 Likes
2

You can use erlang ODBC for Oracle
http://erlang.org/doc/apps/odbc/databases.html
For mongo you can use

3 Likes
3

Has anybody seen a connection to an oracle DB from erlang or elixir with odbc?

2 Likes
4

Looking at old erlang mailing list they used to run the ODBC test suit against Oracle on solaris. My guess would be Erlang Solutions is very likely interfacing with Oracle at least for some of their client projects.

1 Like
5

I had in the past, it was a pain. Currently I am interacting with Oracle via a PostgreSQL Foreign Interface though, which makes things much easier. ^.^

3 Likes
6

Oohhhh please tell me more! Do you have a link to an example or something like this?

1 Like
7

This is what we are using in production. It just wraps an oracle connection/tables/etc… into PostgreSQL as just any other normal PostgreSQL table/etc… then you just access it like normal PostgreSQL.

2 Likes
8

Thanks to all for the fast replies. I begin to love the Elixir-Community from the first step. Very nice and helpful. I will try the suggestions on my virtual dev machine at the office tomorrow and report the results.

EDIT: I will give the ODBC-sollution a try first. If it not works, I think I have to install postgres. Didn’t used it so far, but should be no big problem.

9

Thank you very much. I’ve seen this PostgreSQL package before, but it doesn’t solve my main problem. I have a complete solution written in PL/SQL - so not only the data is stored in the oracle tables, but the whole business logic is a layer of stored procedures above it. You don’t select the data from the tables directly or insert/update, but you only call stored procedures.
In Ruby, which we currently use in this project, it is a dream because with ruby-plsql you can deal with the oracle stored procedures like they are ruby library functions.

10

Please don’t forget to report if you are successful with odbc :blush:

11

Perhaps a separate API is a good idea for our problems. I also already thought about a PHP or Python (Flask) solution in the backend. Would be one-time-effort for different solutions.

12

I will keep it in mind. But programming is only a secondary activity on my job, so I can’t promise to get it done right tomorrow.

13

PHP and oracle are not really good friends.

We will migrate our project from oracle+ruby to postgres+elixir. For the migration process I will go for export+erlport. So I am going build a “inner api” with ruby in a elixir project. It’s not fun when you fetch huge data sets from a cursor but for most cases it will do the job fine.

If you think about a ruby solution or layer and you need a hand please feel free to get in touch.

1 Like
14

Also an interresting idea.

15

OK… the ODBC-Driver ist set up, but I can’t get the Erlang ODBC-Module to work in Elixir. Got an (UndefinedFunctionError) function :odbc.start/0 is undefined (module :odbc is not available) :odbc.start()

Although the ODBC-Library is in the Erlang-Lib-Directory. Do I have to import the module specifically in Elixir (iex)? The examples I found always use :odbc.start() without any imports.

EDIT: Driver is working in Python with ODBC-Library. Hope someone could help with the Elixir-Problem.

16

Did you add :odbc to your applications in your mix.exs file?

17

At this state I have no project yet. Just testing in the iex-environment. I fixed the problem in the afternoon by reinstalling the Erlang-package.

Following the instructions on the internet I tried:

Erlang/OTP 19 [erts-8.1] [source-4cc2ce3] [64-bit] [smp:4:4] [async-threads:10] [hipe] [kernel-poll:false]

Interactive Elixir (1.3.3) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> :odbc.start()
:ok
iex(2)> {:ok, conn} = :odbc.connect('DSN=Oracle_new',[])
** (MatchError) no match of right hand side value: {:error, :connection_closed}
    
iex(2)> 
08:39:02.022 [error] [79, 68, 66, 67, 58, 32, 114, 101, 99, 101, 105, 118, 101, 100, 32, 117, 110, 101, 120, 112, 101, 99, 116, 101, 100, 32, 105, 110, 102, 111, 58, 32, [123, ['tcp_closed', 44, '#Port<0.1395>'], 125], '\n']

08:39:02.069 [error] GenServer #PID<0.88.0> terminating
** (stop) {:port_exit, :collecting_of_driver_information_faild}
Last message: {#Port<0.1393>, {:exit_status, 23}}
State: {:state, #Port<0.1393>, {#PID<0.81.0>, #Reference<0.0.4.230>}, #PID<0.81.0>, :undefined, :on, :undefined, :undefined, :on, :connecting, :undefined, 0, [#Port<0.1391>, #Port<0.1392>], #Port<0.1394>, #Port<0.1395>}

It works on sqlplus with this DSN, it works on isql and it works on Python with the pyodbc-package. Can’t find a hint about what driver-information is missing.

18

Its been a long time since I accessed Oracle that way but I remember the connection string being… significant in length. Also are your credentials baked into that DSN?

19

Tried it with DSN, without DSN (direct driver-implementation), with credentials and without. Every combination worked with the other programms but not with the Erlang-ODBC.

For example:

:odbc.connect('DSN=Oracle_new;UID=usr;PWD=passw',[])

or

:odbc.connect('Driver={Driver};UID=usr;PWD=passw',[])

He checks against the DB. I tried a wrong user or password and got an ORA-error. So driver and crendetials should be correct. Very mysterious.

I also played arround with the suggestion from @schaary embedding Ruby/Python code in Elixir. That worked but I had to “string-jsonify” the data back and forth because Elixir obviously don’t understand the Ruby-Atoms and Enum-Types (or I made a mistake). Not the best solution, but in case of need feasible.

20

No errors when starting odbc at all? Have you enabled the SASL erlang logger to get reports from odbc? Is the SSL library started too?