Connection to AS400 with Ecto

Questions / Help
,
1

Hello all,
I’m a complete newbie to Elixir (for now I never wrote down a row in Elixir).
I have to complete an easy job and I would like to try Elixir.

I have to connect to an AS400, to dump some tables and to transform them in a Json structure.

What an occasion to try some easy programming with Elixir!

I’m here to ask you, please, some help.

I already put in place a DNS odbc connection to the AS400 in a Debian machine (via unixodbc package and the AS400 drivers).
The connection works fine and the AS400 server responds.
I could realize the script in any language I know (Java, PHP, Perl). But I would try with Elixir.

The first question is this one:
how can I query the AS400 via Ecto?
Is there any specific drivers or language lbraries to use with ODBC connections?

Thank you in advance for your help
(and just in case, sorry for my grammar errors: I’m not an English native speaker).

2

Hi, I don’t think that there’s a native db/2 client to use with ecto. You could try the erlang odbc driver though I haven’t got any experience with it and you’ll also probably need to write connection pooling logic yourself

3

Thank you for your answer.

Unlikely ( being a beginner :wink: ) I don’t have any idea about how to call an Erlang module from inside Elixir.
Please can you point about some learning resources explaining the possibility to have Erlang modules inside Elixir?

In addition I found this resource in which the author explain how to handle a SQL Server connection via odbc (unlikely not a db2 case here):

Could this resource be useful for me in some way?

To be clear: my intent is to try Elixir programming with some easy task (in this case read some tables from a db and generate some JSON files), but I wouldn’t put myself in difficulties due to lack of db drivers or similarities.

Thank you in advance for your answers!

4

Yeah, I guess that article you linked to can be a good guide to get you started on :odbc. Also calling erlang functions from Elixir is not hard, for example odbc:erl_connect(...) would become :odbc.erl_connect(...) in Elixir.

But for a newcomer I’m afraid the complexity might prove too much and dissuade from trying the language, so if you just want to get a feel for common tasks (like the one you described) in Elixir I’d go with Postgres & Ecto. Ecto has excellent support for Postgres, and even if you’re not familiar with that particular db server my guess is it would be a lot easier to figure out how to use Postgres than attempting to create a robust wrapper around odbc and db/2. Docs around Ecto are great and the community is very familiar with Ecto & Postgres setups so it will also be much, much easier to find help.

PS. if you are open to trying Ecto with Postgres, I like this docker-compose that also sets pgadmin up you

5

Thank you very much for your reply.

Unlikely I have to try another task, because the db from which I’m trying to read the tables is not an option. It is a requirement. What a shame!

I will do some connection tests andd if things go hard will try another language (Elixir next time).

6

Maybe indeed the connection works.

:odbc.start()
{:ok, _conn} = :odbc.connect('DSN=mydsn;UID=myusr;PWD=mypwd', [])

:odbc.stop()

I don’t have any error.
How could I test if the connection actually works?
(At the moment I don’t have access to the tables involved in the task: I must wait until Operations’ men put the authorization in place on the db involeved).

But I have already access to the server. I setup the DNS on a Debian machine and it works from bash.

Maybe it works also with elixir:

:odbc.start()
{:ok, _conn} = :odbc.connect('DSN=mydsn;UID=myusr;PWD=mypwd', [])

:odbc.stop()

But I need to have a message with
“connection works”, “connection failed” or similar.

Thank you in advance for your replies.

7

Try executing a statement that doesn’t involve any tables and see what you get back, like SELECT 1+1. If you get back a 2 you’re good. If you get any errors about permissions you may still be OK- it may mean that the ops team have yet to work their magic. I guess as long you are getting any response other than :econnrefused or ‘server unreachable’ or similar you should probably be OK.

1 Like
8

Thank you for your answer and your hint!

:odbc.start()
{:ok, _conn} = :odbc.connect('DSN=mydsn;UID=myusr;PWD=mypwd', [])
:odbc.sql_query(conn, 'select 1+1')
:odbc.stop()

This doesn’t produce any errors. But how can I print the result?

(tried

IO.puts :odbc.sql_query(conn, 'select 1+1')

but without success ).

In addition, where I can find a manual on odbc connection?

9

Hi, a quick google search for ‘erlang odbc driver’ returned this getting started guide and the odbc module reference. Did you have something different in mind?

As to the code snippet you shared- I assume that matching on {:ok, _conn} is a typo and in your code you do keep the conn ref (without the leading _) for usage in the next statement.

According to the docs :odbc.select_query/2 returns a 3-element tuple so you should be able to do something like (I don’t have access to a db/2 instance to test this though)

{:selected, _cols, [{result}]} = :odbc.sql_query(conn, "SELECT 1+1")
IO.inspect(result, label: "Result")

But I’m not sure sure what you mean by ‘no success’. Do you get some runtime error? Could you share the output?

1 Like
10

Thank you for your support.

Me too was arrived at Erlang docs, but - as complete beginner of Elixir and Erlang as I am - I was a little bit confused about. But I’m reading the docs, and I starting to understand.

yes:
{:ok, _conn}
it is a typo from a previous try.

Both:

:odbc.start()
{:ok, _conn} = :odbc.connect('DSN=mydsn;UID=myusr;PWD=mypwd', [])
:odbc.stop()

and

:odbc.start()
{:ok, conn} = :odbc.connect('DSN=mydsn;UID=myusr;PWD=mypwd', [])
:odbc.sql_query(conn, 'select 1+1')
:odbc.stop()

Both snippets of code work without errors.

This is the output:

17:24:26.393 [info] Application odbc exited: :stopped

This:


:odbc.start()
{:ok, conn} = :odbc.connect('DSN=mydsn;UID=myusr;PWD=mypwd', [])
{:selected, _cols, [{result}]} = :odbc.sql_query(conn, "SELECT 1+1")
IO.inspect(result, label: "Result")

:odbc.stop()

Unlikely produce this error, that I don’t understand completely.


 (FunctionClauseError) no function clause matching in :odbc.sql_query/3    
    
    The following arguments were given to :odbc.sql_query/3:
    
        # 1
        #PID<0.104.0>
    
        # 2
        "SELECT 1+1"
    
        # 3
        :infinity
    
    (odbc 2.13.1) odbc.erl:186: :odbc.sql_query/3
    try_odbc.ex:19: (file)
    (elixir 1.10.4) lib/code.ex:926: Code.require_file/2
[Finished in 0.7s with exit code 1]

I checked the manual, :odbc.sql_query/3 has a third optional parameter, that is timeout.
:odbc.sql_query/2 is a shortcut to :odbc.sql_query/3 with timeout option to infinity.
I tried with

:odbc.sql_query(conn, “SELECT 1+1”,5)

(at this point I don’t know if the third parameter is expressed in seconds or milliseconds)
but nothing really changed.

Back in the old days, I learnt Perl with a mix of good books (Learning Perl, Programming Perl and more) and a great support from Perl Community (mainly Perl Monks).
I think this is a terrific mix (good learning resources + community) and other languages with no such great community have moved me away from them.

So thank you very much for your help.

11

(at this point I don’t know if the third parameter is expressed in seconds or milliseconds)

In the Types section of the function you can notice that it says TimeOut = time_out(). The time_out() type is declared at the top of the page in the COMMON DATA TYPES section.

It states:

 time_out() = milliseconds() | infinity
 milliseconds() = integer() >= 0

Hope it helps! Welcome to the forum! :smile:

1 Like
12

Can you try with ’SELECT 1+1’ (notice single quotes)? When Erlang docs mention strings, it means character lists in Elixir.

1 Like
13

Hope it helps! Welcome to the forum!

Thank you!

Thank you all for your replies.

{:selected, _cols, [{result}]} = :odbc.sql_query(conn, 'SELECT 1+1')
It seems it works, but I have an error directly from DB2:

> ** (MatchError) no match of right hand side value: {:error, '[IBM][Programma di controllo ODBC di System i Access][DB2 per i5/OS]SQL0104 - Token <FINE-ISTRUZIONI> non valido. Token validi: + - AS <IDENTIFIER>. SQLSTATE IS: 42000'}
>     try_odbc.ex:19: (file)
>     (elixir 1.10.4) lib/code.ex:926: Code.require_file/2
> [Finished in 0.8s with exit code 1]

The error message is in Italian.

SQL0104 - Token non valido. Token validi: + - AS . SQLSTATE IS: 42000’

It means

SQL0104 - Token not valid. Valid Token: + - AS . SQLSTATE IS: 42000’

So the good news it that the server responds (thank you all).
The bad one is that I have to learn to query DB2 with is SQL dialect.
Here perhaps it seems not so happy with the numbers.

14

Ah, of course- erlang strings are charlists in Elixir- totally botched that :slight_smile:

15

You’re right: DB/400 has its own dialect (and it’s a little different than standard DB2).

Try SELECT 1 + 1 FROM SYSIBM.SYSDUMMY.

(EDIT: changed SYSDUMMY1 to SYSDUMMY.)

1 Like
16

Thank you!

It works with:

'SELECT 1 + 1 FROM SYSIBM.SYSDUMMY1'

it doesn’t work with:

I’ve got:


SYSDUMMY in SYSIBM di tipo *FILE non trovato.

it means:
SYSDUMMY in SYSIBM of type *FILE not found.

Just to recap, this is the correct code:

odbc.start()
{:ok, conn} = :odbc.connect('DSN=MYDSN;UID=MYUSER;PWD=MYPWD', [])
{:selected, _cols, [{result}]} = :odbc.sql_query(conn, 'SELECT 1 + 1 FROM SYSIBM.SYSDUMMY1')
IO.inspect(result, label: "Result")

:odbc.stop()

It prints 2

Thank you all for your help.

In order to be useful to others with ODBC/AS400 connection problems also in future, I will recap (in next days) also the sysadmin steps to have odbc connection put in place in a Debian server (that for me are trivial, but maybe it is an useful information here).

2 Likes
17

I think this will be useful if you ever want to use Ecto with it and build a DB2 adapter :smile:
http://blog.plataformatec.com.br/2018/11/building-a-new-mysql-adapter-for-ecto-part-i-hello-world/

1 Like
18

While we’re on the topic, here’s something else that might be useful to know: IBM offers ODBC support for both legacy and modern file formats.

The AS400 supports DDL and SQL, as we would expect from a traditional database platform.

In addition, the AS400 supports SQL access to the legacy format, often called “physical files”.

I mention this because the AS400’s native character encoding is EBCDIC, and therefore this is the character encoding you’ll see in the physical files.**

In my (limited) experience, this can pose a real challenge for applications intended to work with richer character encodings, such as Unicode.

(**) I think the character encoding for file fields can be changed, but that might makes life harder for RPG programmers, so it’s probably uncommon.

19

Thank you for point out on a topic that probably will hit me on my face :wink:

I have DuckDuckGone the web and I found this resourse:

That could be useful:
iconv -f *fromcode* -t *tocode* *EBCDIC_input_file*

dd if= *EBCDIC_input_file* of= *ASCII_output_file* conv=ascii

20

Please allow me to clarify.

The term “file” has multiple meanings for the AS400.

First there is the “physical file” which I mentioned before, and it contains “records” composed of “fields”.

For ODBC purposes, we can pretend it’s a SQL table (but it’s important to know that it’s not really a SQL table).

An AS400 “physical file” is completely different than a file in a file system.

The AS400’s file system is called the IFS (Integrated File System), which can store any file: plain-text, images, etc.

We can work with the IFS using a UNIX-like shell called Qshell (or simply QSH).

(It also supports the usual drag and drop; I’ve mapped MS Windows drives to the IFS and editors like TextPad and Notepad++ can open plain-text files without issue.)

The command-line approach you found from unix.com might help with reading files from the IFS, but it probably won’t help for SQL and ODBC.

Also, I see that you are in Italy, and therefore your AS400 might be configured for multi-language support, meaning your data might not be in EBCDIC and you might not have to worry.

I suggest checking with your admins.

What follows is my USA experience with an AS400 that had everything in EBCDIC.

Asking the admins to create SQL tables with a Unicode encoding would work, along with ensuring any code that interacts with legacy “physical files” follows EBCDIC rules (if applicable).

For example, in my experience the ASCII range of characters work fine in EBCDIC, but characters like ™ (and special characters copy+pasted from rich text, like Microsoft Word) will silently transform into junk during an INSERT or UPDATE.

(I was also using IBM’s specific AS400 JDBC driver for Java, so my experience might have been smoother than plain ODBC might offer.)

In summary, the AS400 has (at least) three forms of durable storage:

  • “physical files”
    • contain “records” composed of “fields”
    • layout is defined using a language called DDS
    • EBCDIC char encoding is likely
    • easily accessible from RPG programs
    • accessible via ODBC
  • SQL tables
    • layout is defined using DDL
    • char encoding dictated by DDL (EBCDIC by default?)
    • accessible from RPG programs via modern “free-form” syntax
    • accessible via ODBC
  • IFS files
    • reside in a file system
    • not accessible via ODBC, to my knowledge

There’s some nuance in the details, but (a) this is enough detail for now and (b) my memory is a bit hazy.

Finally, I don’t mean to dissuade you from pursuing Ecto + AS400 (because that would be awesome), but it might end up being a big challenge.

First up, some pioneering work might be needed because Ecto doesn’t currently support the IBM DB2 dialect, and the AS400’s SQL (DB400) is a sub-dialect of DB2.

But then again, this might not matter too much; I’ll explain.

Regarding the AS400 platform itself, organizations using it tend to have used it for decades, meaning:

  • some of the data structure may predate modern database design
  • the file/table layouts likely serve the purposes of the RPG programs that use them, and may not focus on data consistency as a whole

In other words, it’s likely the existing data structure has grown organically over a long period of time and might be very messy from the perspective of modern data tools like Ecto.

Also, the AS400 has a feature called “journaling”, which is how the AS400 supports transactions.

Journaling is on a table-by-table basis and it’s not uncommon for journaling to be disabled, meaning no database transactions.

Any RPG code using unjournaled tables will need to be updated to enabling journaling, so it’s not a simple task.

If you plan on accessing legacy data then you might need to write a lot of SQL by hand, and also program defensively if journaling is not enabled.

When I was tasked with accessing an AS400 via Java, I chose to use a lightweight library called Mybatis because it is designed to work with handwritten SQL without going crazy, and I needed both SQL and my sanity in order to work with the legacy nature of the system. :wink:

If I needed to do the same thing in Clojure I would use HugSQL.

Ecto or the Elixir ecosystem may already has something similar; AyeSQL looks promising.

Again, I’m sharing my experience in the spirit of knowing what you’re up against, and not telling you “don’t do it”.

If you’ve got the motivation and energy, by all means do it! :rocket:

2 Likes