How to set autocommit to false when runs sql.

[OpakAlex]

We have some sql for create 2 tables.

sql1 = "CREATE TABLE schema_migrations (version NUMBER, inserted_at TIMESTAMP)"

and next

sql2 = "CREATE TABLE schema_migrations1 (version NUMBER, inserted_at TIMESTAMP)"

I have my sessions in pool:

    oci = :erloci.new([Enum.into(opts, %{})])
    session = oci.get_session(credentials[:tns], credentials[:user], credentials[:password])
    {:ok, session}

Next i run some command:

session.prep_sql(sql1).exec_stmt()
session.prep_sql(sql2).exec_stmt()

It's works, but i want wrap in transaction...

session.prep_sql("BEGIN").exec_stmt()
session.prep_sql(sql1).exec_stmt()
session.prep_sql(sql2).exec_stmt()
session.prep_sql("COMMIT").exec_stmt()

How i can do it?

[OpakAlex]

Now i use SET TRANSACTION NAME 'some-name' if it's good?

[c-bik]

I would build all SQLs into a BEGIN .. END block at string level: So instead of

session.prep_sql("BEGIN").exec_stmt()
session.prep_sql(sql1).exec_stmt()
session.prep_sql(sql2).exec_stmt()
session.prep_sql("COMMIT").exec_stmt()

I would

session.prep_sql("BEGIN "++sql1++" "++sql2++" COMMIT END").exec_stmt()

I don't know elixir syntax, so assumed ++ is supported as in erlang

Example: https://github.com/K2InformaticsGmbH/erloci/blob/master/test/erloci_test.erl#L699-L754

However SET TRANSACTION NAME 'some-name' gives you better control, as you can COMMIT or ROLLBACK as separate statements.

In the end your choice of approach will depend on your use case requirement.

[OpakAlex]

ok, good i can use SET TRANSACTION NAME 'some-name' i like it. Thanks!