# Erlang

MySQL/OTP is a driver for connecting Erlang/OTP applications to MySQL and MariaDB databases. It is a native implementation of the MySQL protocol in Erlang.

### MySQL/OTP (Erlang/OTP Client for MariaDB/MySQL)

MySQL/OTP is a native Erlang/OTP client designed for connecting Erlang applications to MySQL and MariaDB databases. It implements the MySQL protocol directly in Erlang, offering robust features for database interaction within an OTP framework.

#### 1. What is MySQL/OTP?

MySQL/OTP acts as a client library, enabling Erlang applications to communicate with MySQL and MariaDB servers. It provides a native Erlang implementation of the MySQL protocol, allowing for efficient and idiomatic database operations within the Erlang/OTP ecosystem.

#### 2. Key Features

* **Mnesia Style Transactions:** Supports nestable transactions that align with Erlang's Mnesia database transaction model.
* **SSL Support:** Secure connections using SSL/TLS.
* **Authentication Methods:** Supports `caching_sha2_password` and `mysql_native_password`.
* **Parametrized Queries:** Utilizes cached unnamed prepared statements for efficient and secure queries.
* **Interruptible Slow Queries:** Allows interrupting slow queries without terminating the connection.
* **Protocol Support:** Implements both the binary protocol (for prepared statements) and the text protocol (for plain queries).

#### 3. Installation

You can add MySQL/OTP as a dependency in your Erlang/OTP project using `erlang.mk`, `rebar` (v2 or v3), or `mix` (for Elixir projects).

**a. Example with `rebar.config` (rebar3):**

Add the following to your `rebar.config` file in the `deps` section:

```erlang
{deps, [
    {mysql, ".*", {git, "https://github.com/mysql-otp/mysql-otp.git", {tag, "2.0.0"}}} % Use the latest stable tag or master
]}.
```

Then run `rebar3 compile` or `rebar3 deps get` to fetch and compile the dependency.

#### 4. Basic Usage

Here are common operations using the `mysql` module:

**a. Connect to the Database:**

Establish a connection to your MariaDB/MySQL server. SSL is optional.

```erlang
% Basic connection
{ok, Pid} = mysql:start_link([{host, "localhost"}, {user, "myuser"}, {password, "mypass"}, {database, "mydb"}]).

% Connection with SSL (example with CA certificate)
% {ok, Pid} = mysql:start_link([{host, "localhost"},
%                                {user, "myuser"},
%                                {password, "mypass"},
%                                {database, "mydb"},
%                                {ssl, [{server_name_indication, disable}, {cacertfile, "/path/to/ca.pem"}]}
%                              ]).
```

Replace `"localhost"`, `"myuser"`, `"mypass"`, and `"mydb"` with your database details.

**b. Execute a SELECT Query (Parameterized):**

```erlang
% Prepared statement with parameters
{ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT id, name FROM mytable WHERE status = ?">>, [<<"active">>]),
io:format("Columns: ~p~n", [ColumnNames]),
io:format("Rows: ~p~n", [Rows]).
```

**c. Manipulate Data (INSERT/UPDATE/DELETE):**

```erlang
% Insert data
ok = mysql:query(Pid, "INSERT INTO mytable (col1, col2) VALUES (?, ?)", [<<"value1">>, 123]),
io:format("Insert successful!~n").

% Get info about the last query
LastInsertId = mysql:insert_id(Pid),
AffectedRows = mysql:affected_rows(Pid),
WarningCount = mysql:warning_count(Pid),
io:format("Last Insert ID: ~p, Affected Rows: ~p, Warnings: ~p~n", [LastInsertId, AffectedRows, WarningCount]).
```

**d. Mnesia-style Transaction (Nestable):**

```erlang
% Example of a nested transaction
mysql:transaction(Pid, fun() ->
    ok = mysql:query(Pid, "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1"),
    mysql:transaction(Pid, fun() ->
        ok = mysql:query(Pid, "UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
    end),
    {atomic, ok} % or {atomic, YourResult}
end).
io:format("Transaction completed.~n").
```

**e. Execute Multiple Queries:**

```erlang
% Example with multiple queries and multiple result sets
{ok, Results} = mysql:query(Pid, "SELECT 1 AS a; SELECT 'hello' AS b;"),
io:format("Multiple Query Results: ~p~n", [Results]).
% Results format: [{[ColumnNames], [Rows]}, {[ColumnNames], [Rows]}]
```

**f. Graceful Timeout Handling:**

You can specify a timeout for queries. If the query exceeds the timeout, it will be interrupted.

```erlang
% Query with a 1000ms (1 second) timeout
{ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT SLEEP(5)">>, 1000),
io:format("Query interrupted, result: ~p~n", [Rows]). % SLEEP() typically returns 1 when interrupted
```

**g. Close the Connection:**

Always close the connection when it's no longer needed.

```erlang
mysql:stop(Pid).
io:format("Connection closed.~n").
```

#### Further Resources:

* [MySQL/OTP GitHub Repository](https://github.com/mysql-otp/mysql-otp)
* [Erlang/OTP Documentation](https://www.google.com/search?q=http://erlang.org/doc/man/mysql.html\&authuser=1) (if available on the official Erlang docs)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/connectors/other/mariadb-connector-erlang-guide.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.