Help customizing an Ecto adapter

Questions / Help
, ,
1

Hello friends,

I am new to Elixir, so I appeal for your patience; I am 52 years old and I have gone through languages like Basic, Cobol, a bit of C, xBase languages like DBase, Clipper, Foxpro, Delphi Object Pascal, C#, Javascript among others.

I have been following the Elixir language with great interest since its launch, I have seen it evolve and I have closely observed the growth of frameworks like Phoenix, although always as a spectator.

I tell you my story, a few weeks ago we were presented with the opportunity to do a project for a client, it is a module that complements existing software, said software uses “Firebird SQL” as a database, and knowing that there was an adapter For Ecto, we decided to explore, it is a great opportunity to take the first step towards Elixir and what better than “doing”

The adapter does not have the maturity that I would like, there are things that I could not do, such as using migrations, I thought it does not matter, we will create the tables by other means since there is already a mechanism in the other software to do it, we just take care that the nomenclature of fields and that the types we use are friendly to Ecto.

And now finally, the reason that brings me to this forum is a problem I have with the Firebird Adapter for Ecto that occurs when trying to obtain paginated query results.

It seems that the adapter is not as popular as I would like and that is why I bring my question to this forum, I am going to leave here the link to the issue I made in the GitHub repository:

https://github.com/nakagami/ecto_firebird/issues/3

Given that the author seems to have trouble solving this problem, and that I have been trying to solve it for a day and a half without success, I dare to extend it here.

I went as far as modifying the following function after the things I tried didn’t work, it’s more of a hack than a solution but in my desperation I thought this would solve it (I planned to polish the code once I had at least one successful run). :

Ecto.Adapters.Firebird.Connection


  def prepare_execute(conn, name, sql, params, opts) do
     has_limit = String.contains?(sql, "FIRST ?")
     has_offset = String.contains?(sql, "SKIP ?")

     params = adjust_order_params(params, has_limit, has_offset)
     opts = Keyword.put(opts, :cast_params, params)

     Firebirdex.prepare_execute(conn, name, sql, params, opts)
   end

I managed to have params with the values in the order I want, but the Firebirdex driver when executing the query is still receiving the values in the wrong order, I saw that in “opts” there was cast_params with a list not updated with my changes so I decided to update that information too, without any effect, which makes it obvious that this is not the place where I can get what I need.

I thought that perhaps I would find an Ecto guru here, who could shed light on the path that must be taken to obtain the desired co-behavior.

I appreciate any advice you can give me.

Greetings
MSand

2 Likes
2

I believe the place that drives that specific parameter order is here:

I don’t know how a specific adapter would go about customizing that value - the note just below it suggests there’s already an unavoidable adapter-specific ordering concern with MySQL and variables in on clauses.

3

Thanks I appreciate your interest in my problem :slight_smile:, I think that making changes to the Ecto code directly is a path that I prefer not to follow, reviewing the Firebird documentation I see that starting with version 3.0 the following clauses can be used:

OFFSET and FETCH clause

Function:
SQL:2008 compliant equivalent for FIRST/SKIP, and an alternative for ROWS.
The OFFSET clause specifies the number of rows to skip. The FETCH clause
specifies the number of rows to fetch.

 OFFSET and FETCH can be applied independently on top-level and nested query
 expressions.

I’ll try to modify the adapter to use OFFSET and FETCH instead of FIRST and SKIP, I think Ecto will be happy with the order of the values in this case.

I’ll come back here if I run into difficulties

4

I have decided on an ugly hack using the non-standard firebird ROWS n TO m clauses, in the issue on github I explain a little more.

As it is my first question here, should I somehow mark the question with a solution or how should I proceed ?

Edit: I found how to mark as solved, was in front of me :slight_smile:

2 Likes