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()

option() = {name, ServerName::server_name()} | {host, inet:socket_address() | inet:hostname()} | {port, integer()} | {user, iodata()} | {password, iodata()} | {database, iodata()} | {connect_mode, synchronous | asynchronous | lazy} | {connect_timeout, timeout()} | {allowed_local_paths, [binary()]} | {log_warnings, boolean()} | {log_slow_queries, boolean()} | {keep_alive, boolean() | timeout()} | {prepare, [{StatementName::statement_name(), Statement::query()}]} | {queries, [query()]} | {query_timeout, timeout()} | {found_rows, boolean()} | {query_cache_time, non_neg_integer()} | {tcp_options, [gen_tcp:connect_option()]} | {ssl, term()}

query()

query() = iodata()

query_filtermap_fun()

query_filtermap_fun() = fun((row()) -> query_filtermap_res()) | fun(([column_name()], row()) -> query_filtermap_res())

query_filtermap_res()

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

query_param()

query_param() = term()

query_result()

query_result() = ok | {ok, [column_name()], [row()]} | {ok, [{[column_name()], [row()]}, ...]} | {error, server_reason()}

row()

row() = [term()]

server_name()

server_name() = {local, Name::atom()} | {global, GlobalName::term()} | {via, Via::module(), ViaName::term()}

server_reason()

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

statement_id()

statement_id() = integer()

statement_name()

statement_name() = atom()

statement_ref()

statement_ref() = statement_id() | statement_name()

transaction_result()

transaction_result(Result) = {atomic, Result} | {aborted, Reason::term()}

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	Executes a plain query.
query/3	Executes a query.
query/4	Executes a query.
query/5	Executes a query.
reset_connection/1
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, [query()]} | {prepare, [NamedStatement]}
NamedStatement = {StatementName::statement_name(), Statement::query()}

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, {error, Reason} will be returned. Specifically, if the operation itself fails (eg authentication failure), change_user_failed will be returned as the reason, while if the operation itself succeeds but one of the given initial queries or prepares fails, the reason will reflect the cause for the failure. In any case, the connection process will exit with the same reason and cannot be used any longer.

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 = statement_ref()
Params = [query_param()]
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 = statement_ref()
Params = [query_param()]
FilterMap = query_filtermap_fun()
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 = statement_ref()
Params = [query_param()]
FilterMap = query_filtermap_fun()
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 = query()
StatementId = statement_id()
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 = statement_name()
Query = query()
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()

Executes a plain query.

See also: query/5.

query/3

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

Conn = connection()
Query = query()
Timeout = timeout()
Params = [query_param()]
FilterMap = query_filtermap_fun()
Result = query_result()

Executes a query.

See also: query/5.

query/4

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

Conn = connection()
Query = query()
Timeout = timeout()
Params = [query_param()]
Result = query_result()

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

Conn = connection()
Query = query()
Timeout = timeout()
FilterMap = query_filtermap_fun()
Result = query_result()

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

Conn = connection()
Query = query()
Params = [query_param()]
FilterMap = query_filtermap_fun()
Result = query_result()

Executes a query.

See also: query/5.

query/5

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

Conn = connection()
Query = query()
Timeout = timeout()
Params = [query_param()]
FilterMap = query_filtermap_fun()
Result = query_result()

Executes a query.

Parameters

Conn is identifying a connection process started using mysql:start_link/1.

Query is the query to execute, as a binary or a list.

Params, FilterMap and Timeout are optional.

If Params (a list) is specified, the query is performed as a prepared statement. A prepared statement is created, executed and then cached for a certain time (specified using the option {query_cache_time, Milliseconds} to start_link/1). If the same query is executed again during this time, it does not need to be prepared again. If Params is omitted, the query is executed as a plain query. To force a query without parameters to be executed as a prepared statement, an empty list can be used for Params.

If FilterMap (a fun) is specified, the function is applied to each row to filter or perform other actions on the rows, in a way similar to how lists:filtermap/2 works, before the result is returned to the caller. See below for details.

Timeout specifies the time to wait for a response from the database. If omitted, the timeout given in start_link/1 is used.

Return value

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}, ...]}. This is typically the case if multiple queries are specified at the same time, separated by semicolons.

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

FilterMap details

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.

Examples

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, FilterMap).

reset_connection/1

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

Conn = connection()
Reason = server_reason()

start_link/1

start_link(Options::[option()]) -> {ok, pid()} | ignore | {error, term()}

Starts a connection gen_server process and connects to a database. To disconnect use mysql:stop/1,2.

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_mode, synchronous | asynchronous | lazy}

Specifies how and when the connection process should establish a connection to the MySQL server.

synchronous (default): The connection will be established as part of the connection process' start routine, ie the returned connection process will already be connected and ready to use, and any on-connect prepares and queries will have been executed.
asynchronous: The connection process will be started and returned to the caller before really establishing a connection to the server and executing the on-connect prepares and executes. This will instead be done immediately afterwards as the first action of the connection process.
lazy: Similar to asynchronous mode, but an actual connection will be established and the on-connect prepares and queries executed only when a connection is needed for the first time, eg. to execute a query.

{connect_timeout, Timeout}

The maximum time to spend for start_link/1.

{allowed_local_paths, [binary()]}

This option allows you to specify a list of directories or individual files on the client machine which the server may request, for example when executing a LOAD DATA LOCAL INFILE query. Only absolute paths without relative components such as .. and . are allowed. The default is an empty list, meaning the client will not send any local files to the server.

{log_warnings, boolean()}

Whether to fetch warnings and log them using error_logger; default true.

{log_slow_queries, boolean()}

Whether to log slow queries using error_logger; default false. Queries are flagged as slow by the server if their execution time exceeds the value in the long_query_time variable.

{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.

{ssl, Options}

Additional options for ssl:connect/3.
The verify option, if not given explicitly, defaults to verify_peer.
The server_name_indication option, if omitted, defaults to the value of the host option if it is a hostname string, otherwise no default value is set.

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, TransactionFun) -> TransactionResult

Conn = connection()
TransactionFun = fun(() -> Result)
TransactionResult = transaction_result(Result)

This function executes the functional object Fun as a transaction.

See also: transaction/4.

transaction/3

transaction(Conn, TransactionFun, Retries) -> TransactionResult

Conn = connection()
TransactionFun = fun(() -> Result)
Retries = non_neg_integer() | infinity
TransactionResult = transaction_result(Result)

This function executes the functional object Fun as a transaction.

See also: transaction/4.

transaction/4

transaction(Conn, TransactionFun, Args, Retries) -> TransactionResult

Conn = connection()
TransactionFun = fun((...) -> Result)
Args = list()
Retries = non_neg_integer() | infinity
TransactionResult = transaction_result(Result)

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 = statement_ref()
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