Module mysql

Description
Data Types
Function Index
Function Details

MySQL client.

Description

MySQL client.

The connection() type is a gen_server reference as described in the documentation for gen_server:call/2,3, e.g. the pid or the name if the gen_server is locally registered.

Data Types

connection()

connection() = atom() | {Name::atom(), Node::atom()} | {global, GlobalName::term()} | {via, Module::atom(), ViaName::term()} | pid()

query_filtermap_fun()

query_filtermap_fun() = fun((row()) -> query_filtermap_res()) | fun((column_names(), row()) -> query_filtermap_res())

query_filtermap_res()

query_filtermap_res() = boolean() | {true, term()}

query_result()

query_result() = ok | {ok, column_names(), rows()} | {ok, [{column_names(), rows()}, ...]} | {error, server_reason()}

server_reason()

server_reason() = {Code::integer(), SQLState::binary() | undefined, Message::binary()}

Function Index

affected_rows/1	Returns the number of inserted, updated and deleted rows of the last executed query or prepared statement.
autocommit/1	Returns true if auto-commit is enabled and false otherwise.
change_user/3	Equivalent to `change_user(Conn, Username, Password, [])`.
change_user/4	Changes the user of the active connection without closing and and re-opening it.
encode/2	Encodes a term as a MySQL literal so that it can be used to inside a query.
execute/3	Executes a prepared statement with the default query timeout as given to start_link/1.
execute/4	Executes a prepared statement.
execute/5	Executes a prepared statement.
in_transaction/1	Returns true if the connection is in a transaction and false otherwise.
insert_id/1	Returns the last insert-id.
prepare/2	Creates a prepared statement from the passed query.
prepare/3	Creates a prepared statement from the passed query and associates it with the given name.
query/2
query/3
query/4
query/5	Executes a parameterized query with a timeout and applies a filter/map function to the result rows.
start_link/1	Starts a connection gen_server process and connects to a database.
stop/1
stop/2	Stops a connection process and closes the connection.
transaction/2	This function executes the functional object Fun as a transaction.
transaction/3	This function executes the functional object Fun as a transaction.
transaction/4	This function executes the functional object Fun with arguments Args as a transaction.
unprepare/2	Deallocates a prepared statement.
warning_count/1	Returns the number of warnings generated by the last query/2 or execute/3 calls.

Function Details

affected_rows/1

affected_rows(Conn::connection()) -> integer()

Returns the number of inserted, updated and deleted rows of the last executed query or prepared statement. If found_rows is set on the connection, for update operation the return value will equal to the number of rows matched by the query.

autocommit/1

autocommit(Conn::connection()) -> boolean()

Returns true if auto-commit is enabled and false otherwise.

change_user/3

change_user(Conn, Username, Password) -> Result

Conn = connection()
Username = iodata()
Password = iodata()
Result = ok

Equivalent to change_user(Conn, Username, Password, []).

See also: change_user/4.

change_user/4

change_user(Conn, Username, Password, Options) -> Result

Conn = connection()
Username = iodata()
Password = iodata()
Options = [Option]
Result = ok
Option = {database, iodata()} | {queries, [iodata()]} | {prepare, [NamedStatement]}
NamedStatement = {StatementName::atom(), Statement::iodata()}

Changes the user of the active connection without closing and and re-opening it. The currently active session will be reset (ie, user variables, temporary tables, prepared statements, etc will be lost) independent of whether the operation succeeds or fails.

If change user is called when a transaction is active (ie, neither committed nor rolled back), calling change_user will fail with an error exception and change_user_in_transaction as the error message.

If the change user operation fails for other reasons (eg authentication failure), an error exception occurs, and the connection process exits with reason change_user_failed. The connection can not be used any longer if this happens.

For a description of the database, queries and prepare options, see start_link/1.

See also: start_link/1.

encode/2

encode(Conn::connection(), Term::term()) -> iodata()

Encodes a term as a MySQL literal so that it can be used to inside a query. If backslash escapes are enabled, backslashes and single quotes in strings and binaries are escaped. Otherwise only single quotes are escaped.

Note that the preferred way of sending values is by prepared statements or parametrized queries with placeholders.

See also: execute/3, query/3.

execute/3

execute(Conn, StatementRef, Params) -> Result | {error, not_prepared}

Conn = connection()
StatementRef = atom() | integer()
Params = [term()]
Result = query_result()

Executes a prepared statement with the default query timeout as given to start_link/1.

See also: execute/5, prepare/2, prepare/3, prepare/4.

execute/4

execute(Conn, StatementRef, Params, Timeout::FilterMap | Timeout) -> Result | {error, not_prepared}

Conn = connection()
StatementRef = atom() | integer()
Params = [term()]
FilterMap = no_filtermap_fun | query_filtermap_fun()
Timeout = default_timeout | timeout()
Result = query_result()

Executes a prepared statement.

See also: execute/5, prepare/2, prepare/3, prepare/4.

execute/5

execute(Conn, StatementRef, Params, FilterMap, Timeout) -> Result | {error, not_prepared}

Conn = connection()
StatementRef = atom() | integer()
Params = [term()]
FilterMap = no_filtermap_fun | query_filtermap_fun()
Timeout = default_timeout | timeout()
Result = query_result()

Executes a prepared statement.

The FilterMap and Timeout arguments are optional.

If the FilterMap argument is the atom no_filtermap_fun or is omitted, no row filtering/mapping will be applied and all result rows will be returned unchanged.
If the Timeout argument is the atom default_timeout or is omitted, the timeout given in start_link/1 is used.

See query/5 for an explanation of the FilterMap argument.

See also: prepare/2, prepare/3, prepare/4, query/5.

in_transaction/1

in_transaction(Conn::connection()) -> boolean()

Returns true if the connection is in a transaction and false otherwise. This works regardless of whether the transaction has been started using transaction/2,3 or using a plain mysql:query(Connection, "BEGIN").

See also: transaction/2, transaction/4.

insert_id/1

insert_id(Conn::connection()) -> integer()

Returns the last insert-id.

prepare/2

prepare(Conn, Query) -> {ok, StatementId} | {error, Reason}

Conn = connection()
Query = iodata()
StatementId = integer()
Reason = server_reason()

Creates a prepared statement from the passed query.

See also: prepare/3.

prepare/3

prepare(Conn, Name, Query) -> {ok, Name} | {error, Reason}

Conn = connection()
Name = atom()
Query = iodata()
Reason = server_reason()

Creates a prepared statement from the passed query and associates it with the given name.

See also: prepare/2.

query/2

query(Conn, Query) -> Result

Conn = connection()
Query = iodata()
Result = query_result()

See also: query/5.

query/3

query(Conn, Query, Params::Params | FilterMap | Timeout) -> Result

Conn = connection()
Query = iodata()
Timeout = default_timeout | timeout()
Params = no_params | [term()]
FilterMap = no_filtermap_fun | query_filtermap_fun()
Result = query_result()

See also: query/5.

query/4

query(Conn, Query, Params, Timeout) -> Result

Conn = connection()
Query = iodata()
Timeout = default_timeout | timeout()
Params = no_params | [term()]
Result = query_result()

See also: query/5.

query/5

query(Conn, Query, Params, FilterMap, Timeout) -> Result

Conn = connection()
Query = iodata()
Timeout = default_timeout | timeout()
Params = no_params | [term()]
FilterMap = no_filtermap_fun | query_filtermap_fun()
Result = query_result()

Executes a parameterized query with a timeout and applies a filter/map function to the result rows.

A prepared statement is created, executed and then cached for a certain time. If the same query is executed again when it is already cached, it does not need to be prepared again.

The minimum time the prepared statement is cached can be specified using the option {query_cache_time, Milliseconds} to start_link/1.

Results are returned in the form {ok, ColumnNames, Rows} if there is one result set. If there are more than one result sets, they are returned in the form {ok, [{ColumnNames, Rows}, ...]}.

For queries that don't return any rows (INSERT, UPDATE, etc.) only the atom ok is returned.

The Params, FilterMap and Timeout arguments are optional.

If the Params argument is the atom no_params or is omitted, a plain query will be executed instead of a parameterized one.
If the FilterMap argument is the atom no_filtermap_fun or is omitted, no row filtering/mapping will be applied and all result rows will be returned unchanged.
If the Timeout argument is the atom default_timeout or is omitted, the timeout given in start_link/1 is used.

If the FilterMap argument is used, it must be a function of arity 1 or 2 that returns either true, false, or {true, Value}.

Each result row is handed to the given function as soon as it is received from the server, and only when the function has returned, the next row is fetched. This provides the ability to prevent memory exhaustion; on the other hand, it can cause the server to time out on sending if your function is doing something slow (see the MySQL documentation on NET_WRITE_TIMEOUT).

If the function is of arity 1, only the row is passed to it as the single argument, while if the function is of arity 2, the column names are passed in as the first argument and the row as the second.

The value returned is then used to decide if the row is to be included in the result(s) returned from the query call (filtering), or if something else is to be included in the result instead (mapping). You may also use this function for side effects, like writing rows to disk or sending them to another process etc.

Here is an example showing some of the things that are possible:

  Query = "SELECT a, b, c FROM foo",
  FilterMap = fun
      %% Include all rows where the first column is < 10.
      ([A|_]) when A < 10 ->
          true;
      %% Exclude all rows where the first column is >= 10 and < 20.
      ([A|_]) when A < 20 ->
          false;
      %% For rows where the first column is >= 20 and < 30, include
      %% the atom 'foo' in place of the row instead.
      ([A|_]) when A < 30 ->
          {true, foo}};
      %% For rows where the first row is >= 30 and < 40, send the
      %% row to a gen_server via call (ie, wait for a response),
      %% and do not include the row in the result.
      (R=[A|_]) when A < 40 ->
          gen_server:call(Pid, R),
          false;
      %% For rows where the first column is >= 40 and < 50, send the
      %% row to a gen_server via cast (ie, do not wait for a reply),
      %% and include the row in the result, also.
      (R=[A|_]) when A < 50 ->
          gen_server:cast(Pid, R),
          true;
      %% Exclude all other rows from the result.
      (_) ->
          false
  end,
  query(Conn, Query, no_params, FilterMap, default_timeout).

start_link/1

start_link(Options) -> {ok, pid()} | ignore | {error, term()}

Options = [Option]
Option = {name, ServerName} | {host, inet:socket_address() | inet:hostname()} | {port, integer()} | {user, iodata()} | {password, iodata()} | {database, iodata()} | {connect_timeout, timeout()} | {log_warnings, boolean()} | {keep_alive, boolean() | timeout()} | {prepare, NamedStatements} | {queries, [iodata()]} | {query_timeout, timeout()} | {found_rows, boolean()} | {query_cache_time, non_neg_integer()}
ServerName = {local, Name::atom()} | {global, GlobalName::term()} | {via, Module::atom(), ViaName::term()}
NamedStatements = [{StatementName::atom(), Statement::iodata()}]

Starts a connection gen_server process and connects to a database. To disconnect just do exit(Pid, normal).

Options:

{name, ServerName}: If a name is provided, the gen_server will be registered with this name. For details see the documentation for the first argument of gen_server:start_link/4.
{host, Host}: Hostname of the MySQL database. Since OTP version 19, it is also possible to specify a local (Unix) Socket by specifying {local, SocketFile}. Default "localhost".
{port, Port}: Port; default 3306 for non-local or 0 for local (Unix) sockets.
{user, User}: Username.
{password, Password}: Password.
{database, Database}: The name of the database AKA schema to use. This can be changed later using the query USE <database>.
{connect_timeout, Timeout}: The maximum time to spend for start_link/1.
{log_warnings, boolean()}: Whether to fetch warnings and log them using error_logger; default true.
{keep_alive, boolean() | timeout()}: Send ping when unused for a certain time. Possible values are true, false and integer() > 0 for an explicit interval in milliseconds. The default is false. For true a default ping timeout is used.
{prepare, NamedStatements}: Named prepared statements to be created as soon as the connection is ready.
{queries, Queries}: Queries to be executed as soon as the connection is ready. Any results are discarded. Typically, this is used for setting time zone and other session variables.
{query_timeout, Timeout}: The default time to wait for a response when executing a query or a prepared statement. This can be given per query using query/3,4 and execute/4. The default is infinity.
{found_rows, boolean()}: If set to true, the connection will be established with CLIENT_FOUND_ROWS capability. affected_rows/1 will now return the number of found rows, not the number of rows changed by the query.
{query_cache_time, Timeout}: The minimum number of milliseconds to cache prepared statements used for parametrized queries with query/3.
{tcp_options, Options}: Additional options for gen_tcp:connect/3. You may want to set {recbuf, Size} and {sndbuf, Size} if you send or receive more than the default (typically 8K) per query.

stop/1

stop(Conn) -> ok

Conn = connection()

See also: stop/2.

stop/2

stop(Conn, Timeout) -> ok

Conn = connection()
Timeout = timeout()

Stops a connection process and closes the connection. The process calling stop will be blocked until the connection process stops or the given timeout expires.

If the connection is not stopped within the given timeout, an exit exception is raised with reason timeout.

If the connection process exits with any other reason than normal, an exit exception is raised with that reason.

transaction/2

transaction(Conn::connection(), Fun::function()) -> {atomic, term()} | {aborted, term()}

This function executes the functional object Fun as a transaction.

See also: transaction/4.

transaction/3

transaction(Conn::connection(), Fun::function(), Retries) -> {atomic, term()} | {aborted, term()}

Retries = non_neg_integer() | infinity

This function executes the functional object Fun as a transaction.

See also: transaction/4.

transaction/4

transaction(Conn::connection(), Fun::function(), Args::list(), Retries) -> {atomic, term()} | {aborted, term()}

Retries = non_neg_integer() | infinity

This function executes the functional object Fun with arguments Args as a transaction.

The semantics are as close as possible to mnesia's transactions. Transactions can be nested and are restarted automatically when deadlocks are detected. MySQL's savepoints are used to implement nested transactions.

Fun must be a function and Args must be a list of the same length as the arity of Fun.

If an exception occurs within Fun, the exception is caught and {aborted, Reason} is returned. The value of Reason depends on the class of the exception.

Note that an error response from a query does not cause a transaction to be rollbacked. To force a rollback on a MySQL error you can trigger a badmatch using e.g. ok = mysql:query(Pid, "SELECT some_non_existent_value"). An exception to this is the error 1213 "Deadlock", after the specified number of retries, all failed. In this case, the transaction is aborted and the error is retured as the reason for the aborted transaction, along with a stacktrace pointing to where the last deadlock was detected. (In earlier versions, up to and including 1.3.2, transactions where automatically restarted also for the error 1205 "Lock wait timeout". This is no longer the case.)

Some queries such as ALTER TABLE cause an *implicit commit* on the server. If such a query is executed within a transaction, an error on the form {implicit_commit, Query} is raised. This means that the transaction has been committed prematurely. This also happens if an explicit COMMIT is executed as a plain query within a managed transaction. (Don't do that!)

Class of exception	Return value
`error` with reason `ErrorReason`	`{aborted, {ErrorReason, Stack}}`
`exit(Term)`	`{aborted, Term}`
`throw(Term)`	`{aborted, {throw, Term}}`

unprepare/2

unprepare(Conn, StatementRef) -> ok | {error, Reason}

Conn = connection()
StatementRef = atom() | integer()
Reason = server_reason() | not_prepared

Deallocates a prepared statement.

warning_count/1

warning_count(Conn::connection()) -> integer()

Returns the number of warnings generated by the last query/2 or execute/3 calls.

Generated by EDoc