Connection to Oracle DB

Questions / Help
1

Hi,

I continue my Elixir exploration and now I’m trying to fetch data from our databases that are Oracle DB.
I’ve found I must use the Erlang :odbc module and pass through ODBC connection.

I’ve installed the Oracle ODBC driver and set a Data Source but when I try to connect, that fails.

Below my actions and their results:

iex(18)> :odbc.start()
:ok
iex(19)> {ok, ref} = :odbc.connect('DSN=SIRENE_REC;UID=account;PWD=the_password', [])

13:25:21.953 [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.1505>'], 125], '\n'] {:error, :connection_closed}

13:25:21.954 [error] GenServer #PID<0.125.0> terminating
** (stop) {:port_exit, :collecting_of_driver_information_faild} Last message: {#Port<0.1503>, {:exit_status, 23}} State: {:state, #Port<0.1503>, {#PID<0.81.0>, #Reference<0.1271860022.1836056577.135740>}, #PID<0.81.0>, :undefined, :on, :undefined, :undefined, :on, :connecting, :undefined, 0, [#Port<0.1501>, #Port<0.1502>], #Port<0.1504>, #Port<0.1505>}

For my noob level, I’m lost :blush:

2

Have you done the prechecks from the Erlang ODBC Guide?

In the getting started section there are some things listed to consider and make sure first:

  • The first thing you need to do, is to make sure you have an ODBC driver installed for the database that you want to access. Both the client machine where you plan to run your erlang node and the server machine running the database needs the the ODBC driver. (In some cases the client and the server may be the same machine).
    *Secondly you might need to set environment variables and paths to appropriate values. This may differ a lot between different os’s, databases and ODBC drivers. This is a configuration problem related to the third party product and hence we can not give you a standard solution in this guide.
  • The Erlang ODBC application consists of both Erlang and C code. The C code is delivered as a precompiled executable for windows, solaris and linux (SLES10) in the commercial build. In the open source distribution it is built the same way as all other application using configure and make. You may want to provide the the path to your ODBC libraries using --with-odbc=PATH.

The payload of the error, which got mangled in some way (perhaps something in the chain has problem with IO-Lists?) says roughly that there was an unexpected {'tcp_closed', '#Port<0.1505>'}. Further I have to assume that this is printed by some erlang subroutine and therefore 'tcp_closed is an atom. Anyway. It boils down to a closed TCP connection, which may have many reasons, but I’ve not used any ODBC so far.

Therefore I want to ask you if you can access to that database from another program that uses ODBC, does this program see the same set of environment variables as your erlang program? Has your version of erlang been compiled with a proper value for --with-odbc?

3

My sole test is the “Test connection” from the ODBC configuration window and the connection works in that case. I don’t have other program using ODBC (currently, all programs are in Java and we use JDBC).
About Erlang compliation options, I don’t know :confused: I use the Erlang version provided by the Elixir Windows installer.
I’ve no idea how it is compiled.

4

I tried my ODBC connection after installing MS ODBC Test program.
The connection works fine with this tools (connection, queries, …).

5

I’ve just read how compile Erlang OTP on Windows :fearful:
When I see all needed steps, I’m discouraged.

6

Instead of using ODBC (which kind of sucks), have you thought about using a direct Oracle driver like jamdb_oracle or so?

I personally mount my work Oracle database as a FDW inside my PostgreSQL database. ^.^

2 Likes
7
1 Like
8

Interesting, how does it compare to this?

9

Adapter to Oracle Database. Using DBConnection and ODBC

it’s still basically just Erlang ODBC under the covers.

Do you know of any attempt to do a JDBC - Erlang Bridge?

I have a reporting tool built on Elixir using ODBC, but a prospective new client is on T24 (JBASE), i’m looking for possible ways to connect, so far Erlang ODBC does not work for this as i do not have a suitable driver.

SQL Squirrel is able to connect via jbase-jdbc, hence the search for a jdbc-erlang connector.

T24 JBASE has a jbase_agent that is basically a socket acceptor, so it makes me hopeful that a pure Elixir/ erlang driver might even be the way to go (if i can just understand how jdbc interacts with JBASE)

jAGENT

jBASE jAgent is a server-side jBASE component which must be listening on a user-defined TCP port on the remote jBASE instance. It accepts socket connections to process incoming requests from Client Applications implementing the jRemote Client API. Please refer to the jBASE jAgent user guide for more information on how to configure and start jAgent.

Sample Project

There is a sample project in github using c# and jAgent. Click here to view.

1 Like
10

All the lower-case j prefixes make me think java. You could always write a Java Erlang node (the java api comes with the erlang distribution) for Elixir to talk to. ^.^

2 Likes