Support of binding parameters

[llxff]

Hello,

The work you've done looks promising! But seems like there is no support of bind variables. I have found a few examples in the tests of oranif and could make simple code which binds one variable and it seems working:

# INSERT INTO foo(value) VALUES (:1) [param]
param = "example"

%{var: v} = oranif(slave, :conn_newVar, [conn, :DPI_ORACLE_TYPE_VARCHAR, :DPI_NATIVE_TYPE_BYTES, byte_size(param), byte_size(param), true, false, :null])
oranif(slave, :stmt_bindByName, [statement, ":1", v]), label: :stmt_bindByName
oranif(slave, :var_setFromBytes, [v, 0, param]), label: :var_setFromBytes

oranif(slave, :stmt_execute, [statement, []])

So I think theoretically it is doable to add this feature to the library, but I couldn't find any docs about the mapping between data types in Erlang and ODPI and how to basically use bind variables.

I would really appreciate if you could point me to any docs or code where I could find more information about bind variables and how to use them with oranif 🙏

[c-bik]

Hi @llxff ,

Great to see you are digging in and apologies for lack of documentation. As you can see I haven't wrapped everything of oranif into oralixir. It was meant to be developed into a ecto adapter but I got busy elsewhere in the mean time. But from your code snippet I can tell you that you already are on right track. You can also extend it for multiple variables too (:1, :2... etc).

Can you please advice a desired interface signature you are looking for (besides the currently available oranif direct erlang interface)?

Oranif, on the other hand is a thin NIF wrapper of Oracle's ODPI-C with API-to-API mapped. So you can also try to use their documentation (and samples to guide you). See:

• conn_newVar
• possible values of second parameter: oracleTypeNum
• possible values of third parameter: nativeTypeNum

Data type binding are quite simple like integer -> integer, float -> float, binary -> varchar2 etc. I can write a simple test case (eunit) to demonstrate it.

I would like to help you out so you can continue to use this. Please let me know 😃

[llxff]

Thank you for your answer!

I can't say about some specific signatures for now, but these are the features I am interested in:

1. Bind variables support, so it is possible to work with inserts and updates
2. Correct typecast (for example odbc casts number(38, 0) to charlist/binary)
3. Support of *LOB > 4000 bytes (for storing json)
4. Support of timestamps with microseconds (DateTime/NaiveDateTime)
5. Support of RAW (for storing uuids)
6. Toggle autocommit mode (as far as I understood it is possible to do by adding :DPI_MODE_EXEC_COMMIT_ON_SUCCESS as an argument to stmt_execute)

So basically I want to make this code work:

Setup:

CREATE TABLE demo(payload clob, name varchar2(255), inserted_at timestamp(9), uuid raw(16))

Code:

sql = "insert into demo (payload, name, inserted_at, uuid) values (:1, :2, :3, :4)"
params = [
  String.duplicate("a", 5000),
  "example",
  DateTime.utc_now(),
  Ecto.UUID.generate()
]

OraLixir.prepare_execute(pid, "name", sql, params, [])

[c-bik]

Thanks. I can TDD with this. oranif may need some support for this as well. I assume similar support in direction of SELECT is also needed. I will see what I can provide after the weekend.

[llxff]

I have tried to insert CLOB values, it worked. But I found that select from the table with CLOB field doesn't work:

CREATE TABLE demo (field clob);
insert into demo (field) values ('value');

iex> OraLixir.prepare_execute(pid, "name", "select * from demo", [], [])
%OraLixir.Result{
   columns: [
     %{
       name: 'FIELD',
       nullOk: true,
       typeInfo: %{
         clientSizeInBytes: 0,
         dbSizeInBytes: 0,
         defaultNativeTypeNum: :DPI_NATIVE_TYPE_LOB,
         fsPrecision: 0,
         objectType: :featureNotImplemented,
         ociTypeCode: 112,
         oracleTypeNum: :DPI_ORACLE_TYPE_CLOB,
         precision: 0,
         scale: 0,
         sizeInChars: 0
       }
     }
   ],
   rows: [
     [
       error: %{
         api: :data_get,
         args: [#Reference<0.1772885590.343277591.65512>],
         node: nil,
         oranifFile: 'c_src/dpiData_nif.c',
         oranifLine: 369,
         reason: 'Unsupported nativeTypeNum'
       }
     ]
   ]
 }
}

Did I something wrong?

[c-bik]

'Unsupported nativeTypeNum'

Nope you didn't do anything wrong. As I suspected, oranif needs work!

[c-bik]

@llxff see: https://github.com/KonnexionsGmbH/oranif/pull/3 for a WIP of LOB (and family) support. (FYI: Update)

[llxff]

@c-bik cool, thank you!

I have made a research and came to this idea:

https://github.com/erlangbureau/jamdb_oracle has good support of building SQL queries for Oracle, but it has limited support of Oracle protocol. So I have tried to merge jamdb_oracle and OraLixir into one adapter: took SQL builder from jamdb_oracle and oranif wrapper from OraLixir. I connected it to a dummy application and tried to play with different queries: DDL, selects, inserts, tried to run them with and without Ecto.Adapters.SQL.Sandbox. And after some adjustments, it works!

Of course, there are still many errors: for instance, it is unstable in tests and crashes with some low-level errors. But I think it is doable to fix these issues because SQL builder works and oranif has all the features needed to work with Oracle types.

What do you think if I create a new library based on my research and the work done in jamdb_oracle and OraLixir?

[c-bik]

@c-bik cool, thank you!

You are welcome 🙂

I have made a research and came to this idea:

https://github.com/erlangbureau/jamdb_oracle has good support of building SQL queries for Oracle, but it has limited support of Oracle protocol. So I have tried to merge jamdb_oracle and OraLixir into one adapter: took SQL builder from jamdb_oracle and oranif wrapper from OraLixir. I connected it to a dummy application and tried to play with different queries: DDL, selects, inserts, tried to run them with and without Ecto.Adapters.SQL.Sandbox. And after some adjustments, it works!

Awesome!!! 🎉🥳

Of course, there are still many errors: for instance, it is unstable in tests and crashes with some low-level errors. But I think it is doable to fix these issues because SQL builder works and oranif has all the features needed to work with Oracle types.

What do you think if I create a new library based on my research and the work done in jamdb_oracle and OraLixir?

Great Idea! That was always my goal with OraLixir.

The Query builder part of OraLixir is still a work in progress. If you are willing to contribute a PR directly to OraLixir changing the SQL builder part from jamdb then it will make OraLixir complete. Either way, please feel free to use OraLixir code base for it. I am open to also re architect OraLixir if you so advice.

@cruzfarfan is also quite interested in this project (hope he still it). I will love it if OraLixir evolves into something useful.

TIA