odbc(3)
💖 odbc 💖
okay bestie, welcome to the Erlang ODBC application — it's the odbc module, it lives in section 3 of the manual, and it's literally your backstage pass to relational SQL databases. 🎀
✨ NAME ✨
odbc — the Erlang ODBC application, no cap.
🌸 DESCRIPTION 🌸
So here's the vibe: this application gives you an Erlang interface to talk to relational SQL databases. It's built on top of Microsoft's ODBC interface, which means — and this is the part everybody forgets — you need an ODBC driver for the database you wanna connect to. No driver, no connection, periodt. 🦋
NOTE: okay so listen up because this is important. The functionsfirst/[1,2],last/[1,2],next/[1,2],prev[1,2]andselect/[3,4]all assume there's a result set associated with the connection to work on. Callingselect_count/[2,3]is what associates such a result set with the connection. If you callselect_countagain, it removes the current result set association and creates a new one. And if you call a function that doesn't operate on an associated result set — likesql_query/[2,3]— that will also remove the current result set association. And alas 😭 some drivers only support sequential traversal of the result set, i.e. they don't support what the ODBC world calls scrollable cursors. The effect of that? Functions likefirst/[1,2],last/[1,2],prev[1,2], etc. will return{error, driver_does_not_support_function}. Not me getting betrayed by my own driver 💀
🫶 Error Handling
The error handling strategy and all the possible error sources are described in the Erlang ODBC User's Guide. Go read it, bestie, it's giving essential reading energy.
📚 References
\Microsoft ODBC 3.0, Programmer's Reference and SDK Guide — and also peep http://msdn.microsoft.com/
🌈 DATA TYPES 🌈
col_name() :: string().— the name of a column in the result set. cute, simple, iconic.common_reason() :: connection_closed | extended_error() | term().— an explanation of what went wrong. For common errors there will be atom descriptions. (The original says "decriptions" btw, we don't gatekeep typos here 💅)-opaque connection_reference()— an opaque reference to an ODBC connection, as returned byconnect/2. Opaque means don't peek behind the curtain, bestie, fr fr.extended_error() :: {string(), integer(), term()}.— extended error type with ODBC and native database error codes, plus the base reason that would have been returned ifextended_errorshad not been enabled.n_rows() :: integer().— the number of affected rows forUPDATE,INSERT, orDELETEqueries. For other query types the value is driver defined, so highkey just ignore it, okay?odbc_data_type()— the data type used by ODBC. To learn which Erlang data type corresponds to an ODBC data type, go check the Erlang to ODBC data type mapping in the User's Guide. Here's the whole menu, verbatim:
odbc_data_type() ::
sql_integer | sql_smallint | sql_tinyint |
{sql_decimal, Precision :: integer(), Scale :: integer()} |
{sql_numeric, Precision :: integer(), Scale :: integer()} |
{sql_char, Size :: integer()} |
{sql_wchar, Size :: integer()} |
{sql_varchar, Size :: integer()} |
{sql_wvarchar, Size :: integer()} |
{sql_float, Precision :: integer()} |
{sql_wlongvarchar, Size :: integer()} |
{sql_float, Precision :: integer()} |
sql_real | sql_double | sql_bit |
atom().row() :: tuple() | list().— a tuple, with the number of elements selected from columns in a database row, containing the values of the columns, like{value(), value() ... value()}.
When the tuple_row option is set to off, this is a list containing the values of the columns in a database row, like [value(), value() ... value()].
Please see connect/2. 👀
selected() :: {selected, [col_name()], [row()]}.— the return value for queries that select data from database tables.updated() :: {updated, n_rows()}.— the return value for queries that update database tables.value() :: null | term().— the Erlang data type that corresponds to the ODBC data type being handled. slay.
💅 FUNCTIONS 💅
commit(ConnectionReference, CommitMode, TimeOut) -> ok | {error, Reason}
commit(ConnectionReference, CommitMode, TimeOut) ->
ok | {error, Reason}
when
ConnectionReference :: connection_reference(),
CommitMode :: commit | rollback,
TimeOut :: erlang:timeout(),
Reason ::
not_an_explicit_commit_connection |
process_not_owner_of_odbc_connection |
common_reason().Deprecated: odbc:commit/3 is deprecated; legacy protocol support will be dropped in OTP-30, it does not really provide backend transparency and known usage is low. 😭 so like, heads up, this one's on its way out.
Commits or rollbacks a transaction. You need this on connections where automatic commit is turned off. That's it, that's the function. 🎀
connect(ConnectionStr, Options) -> {ok, ConnectionReferense} | {error, Reason}
connect(ConnectionStr, Options) ->
{ok, ConnectionReferense} | {error, Reason}
when
ConnectionStr :: string(),
Options ::
[{auto_commit, on | off} |
{timeout, timeout()} |
{binary_strings, on | off} |
{tuple_row, on | off} |
{scrollable_cursors, on | off} |
{trace_driver, on | off} |
{extended_errors, on | off}],
ConnectionReferense :: connection_reference(),
Reason ::
port_program_executable_not_found |
common_reason().Deprecated: odbc:connect/2 is deprecated; legacy protocol support will be dropped in OTP-30, it does not really provide backend transparency and known usage is low. 💔
Opens a connection to the database. Now here's the lore: the connection is associated with the process that created it and can only be accessed through it. This function may spawn new processes to handle the connection. Those processes will terminate if the process that created the connection dies, or if you call disconnect/1. So like, keep your creator process alive, bestie. 🙈
If automatic commit mode is turned on, each query is considered an individual transaction and will be automatically committed after it has been executed. If you want more than one query to be part of the same transaction, you should turn automatic commit mode off. Then you'll have to call commit/3 explicitly to end a transaction. No shortcuts. ✨
The default timeout is infinity — yes, infinity, she has no concept of time.
If the option binary_strings is turned on, all strings will be returned as binaries, and strings inputted to param_query will be expected to be binaries. The user needs to ensure that the binary is in an encoding that the database expects. By default this option is turned off.
As default, result sets are returned as a lists of tuples. The TupleMode option still exists to keep some degree of backwards compatibility. If the option is set to off, result sets will be returned as a lists of lists instead of a lists of tuples. (Lists on lists on lists, it's giving nesting era 🪆)
Scrollable cursors are nice but they cause some overhead. For some connections, speed might be more important than flexible data access — in that case you can disable scrollable cursor for a connection, limiting the API but gaining speed. 🏎️
NOTE: Turning the scrollable_cursors option off is noted to make old odbc-drivers able to connect that will otherwise fail. so like, lowkey a lifesaver for the ancient ones.
If trace mode is turned on, this tells the ODBC driver to write a trace log to the file SQL.LOG that is placed in the current directory of the erlang emulator. This information may be useful if you suspect there might be a bug in the erlang ODBC application, and it might be relevant for you to send this file to our support. Otherwise you will probably not have much use of this. 🤷♀️
NOTE: For more information about theConnectStr, see the description of the functionSQLDriverConnectin [1].
The extended_errors option enables extended ODBC error information when an operation fails. Rather than returning {error, Reason}, the failing function will return {error, {ODBCErrorCode, NativeErrorCode, Reason}}. Note that this information is probably of little use when writing database-independent code, but it can be of assistance in providing more sophisticated error handling when dealing with a known underlying database. 🧚
ODBCErrorCodeis the ODBC error string returned by the ODBC driver.NativeErrorCodeis the numeric error code returned by the underlying database. The possible values and their meanings are dependent on the database being used.Reasonis as per theReasonfield when extended errors are not enabled.
NOTE: The current implementation spawns a port program written in C that utilizes the actual ODBC driver. There is a default timeout of5000msec for this port program to connect to the Erlang ODBC application. This timeout can be changed by setting an application specific environment variable'port_timeout'with the number of milliseconds for the ODBC application. E.g.:[{odbc, [{port_timeout, 60000}]}]to set it to 60 seconds. ⏰
describe_table(ConnectionReference, Table, TimeOut) -> {ok, Description} | {error, Reason}
describe_table(ConnectionReference, Table, TimeOut) ->
{ok, Description} | {error, Reason}
when
ConnectionReference ::
connection_reference(),
Table :: string(),
TimeOut :: erlang:timeout(),
Description ::
[{col_name(), odbc_data_type()}],
Reason ::
process_not_owner_of_odbc_connection |
common_reason().Deprecated: odbc:describe_table/3 is deprecated; legacy protocol support will be dropped in OTP-30, it does not really provide backend transparency and known usage is low. ✨
Queries the database to find out the ODBC data types of the columns of the table Table. Basically it's the "so what are you?" function. 💅
disconnect(ConnectionReferense) -> ok | {error, Reason}
disconnect(ConnectionReferense) -> ok | {error, Reason}
when
ConnectionReferense :: connection_reference(),
Reason ::
process_not_owner_of_odbc_connection |
extended_error().Deprecated: odbc:disconnect/1 is deprecated; legacy protocol support will be dropped in OTP-30, it does not really provide backend transparency and known usage is low. 😭
okay bestie that's part 1 and there's more coming, so don't you dare close the tab 🫶
disconnect 💅
Periodt, let's close this thing out — disconnect closes a connection to a database. ✨
And not just the connection, bestie — it will also terminate all processes that may have been spawned when the connection was opened. everybody out, the party's over 🫶
The good news: this call will always succeed. The wild news: if the connection cannot be disconnected gracefully, it will be brutally killed 💀 no cap. She does not do gentle goodbyes.
⚠️ However, you may receive an error message as a result if you try to disconnect a connection started by another process. Rude? yes. Real? also yes 🙈
first/2 🥲
first(ConnectionReference, TimeOut) -> Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
TimeOut :: erlang:timeout(),
Result :: selected(),
Reason ::
result_set_does_not_exist |
driver_does_not_support_function |
scrollable_cursors_disabled |
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:first/2 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: returns the first row of the result set and parks a cursor right at this row. iconic behaviour, honestly 👑
last/2 🥲
last(ConnectionReference, TimeOut) -> Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
TimeOut :: erlang:timeout(),
Result :: selected(),
Reason ::
result_set_does_not_exist |
driver_does_not_support_function |
scrollable_cursors_disabled |
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:last/2 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: returns the last row of the result set and positions a cursor at this row. saving the best for last, literally ✨
next/2 🥲
next(ConnectionReference, TimeOut) -> Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
TimeOut :: erlang:timeout(),
Result :: selected(),
Reason ::
result_set_does_not_exist |
driver_does_not_support_function |
scrollable_cursors_disabled |
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:next/2 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: returns the next row of the result set relative the current cursor position and positions the cursor at this row.
And here's the lil detail that trips people up: if the cursor is already sitting at the last row of the result set when you call this, what you get back is {selected, ColNames,[]} — e.i. the list of row values is empty, which is the polite way of saying no more data to fetch, bestie, that's it, we're done 🎀
param_query/4 🥲
param_query(ConnectionReference, SQLQuery, Params, TimeOut) ->
Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
SQLQuery :: string(),
Params ::
[{odbc_data_type(), [value()]}] |
[{odbc_data_type(),
in | out | inout,
[value()]}],
TimeOut :: erlang:timeout(),
Result :: selected() | updated(),
Reason ::
driver_does_not_support_function |
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:param_query/4 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: executes a parameterized SQL query. Want an example? go look at "Using the Erlang API" in the Erlang ODBC User's Guide, it's got you 🫶
📌 NOTE: Use the functiondescribe_table/[2,3]to find out which ODBC data type that is expected for each column of that table. If a column has a data type that is described with capital letters, alas it is not currently supported by theparam_queryfunction. And to learn which Erlang data type corresponds to an ODBC data type, see the Erlang to ODBC data type mapping in the User's Guide. iykyk 💅
prev/2 🥲
prev(ConnectionReference, TimeOut) -> Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
TimeOut :: erlang:timeout(),
Result :: selected(),
Reason ::
result_set_does_not_exist |
driver_does_not_support_function |
scrollable_cursors_disabled |
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:prev/2 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: returns the previous row of the result set relative the current cursor position and positions the cursor at this row. going backwards? in this economy? okay girl we support it 🦋
select/4 🥲
select(ConnectionReference, Position, N, TimeOut) ->
Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
Position ::
next |
{relative, integer()} |
{absolute, integer()},
N :: integer(),
TimeOut :: erlang:timeout(),
Result :: selected(),
Reason ::
result_set_does_not_exist |
driver_does_not_support_function |
scrollable_cursors_disabled |
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:select/4 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: selects N consecutive rows of the result set. Here's how Position works, bestie, write it down 📝
- If
Positionisnext, it's semantically the equivalent of callingnext/[1,2]Ntimes. - If
Positionis{relative, Pos}, thenPosis used as an offset from the current cursor position to figure out the first selected row. - If
Positionis{absolute, Pos}, thenPosis the number of the first row selected.
Once this function has returned, the cursor is positioned at the last selected row. If there's less then N rows left of the result set, the length of Rows will be less than N. And if the first row you wanna select happens to be beyond the last row of the result set, you get {selected, ColNames,[]} — e.i. the list of row values is empty, indicating there's no more data to fetch 🌸
select_count/3 🥲
select_count(ConnectionReference, SQLQuery, TimeOut) ->
{ok, NrRows} | {error, Reason}
when
ConnectionReference :: connection_reference(),
SQLQuery :: string(),
TimeOut :: erlang:timeout(),
NrRows :: n_rows(),
Reason ::
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:select_count/3 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: executes a SQL SELECT query and associates the result set with the connection. A cursor is positioned before the first row in the result set, and the tuple {ok, NrRows} is returned. cute, informative, slay ✨
📌 NOTE: Some drivers may not have the information of the number of rows in the result set, and thenNrRowswill have the valueundefined. not all drivers are doing their homework 🙈
sql_query/3 🥲
sql_query(ConnectionReference, SQLQuery, TimeOut) ->
Result | {error, Reason}
when
ConnectionReference :: connection_reference(),
SQLQuery :: string(),
TimeOut :: erlang:timeout(),
Result :: updated() | selected(),
Reason ::
process_not_owner_of_odbc_connection |
common_reason().😭 Deprecated: odbc:sql_query/3 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: executes a SQL query or a batch of SQL queries. Rundown 💅
- If it's a SELECT query, the result set comes back in the format
{selected, ColNames, Rows}. - For other query types, you get the tuple
{updated, NRows}. - And for batched queries — if the driver supports them — this function can also return a list of result tuples.
📌 NOTE: Some drivers may not have the information of the number of affected rows available, and then the return value may be{updated, undefined}. Also, the list of column names is ordered in the same way as the list of values of a row — e.g. the firstColNameis associated with the firstValuein aRow. keep them lined up, bestie 🧚
start/1 🥲
start(Type) -> ok | {error, Reason}
when
Type :: permanent | transient | temporary,
Reason :: term().😭 Deprecated: odbc:start/1 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: starts the odbc application. The default type is temporary. See application(3) 👑
stop/0 🥲
stop() -> ok.😭 Deprecated: odbc:stop/0 is deprecated; Legacy protocol support will be dropped in OTP-30, does not really provide backend transparency and known usage is low.
What it does: stops the odbc application. See application(3) 🌈
aaaand that's the whole entire page, bestie, you made it to the end 🎀💖 this lil masterpiece was brought to you by the fine people at Ericsson AB, running version odbc 2.17, sitting pretty as page odbc(3) in your manual. go forth and query responsibly, no cap 👑✨
📦 where this page comes from
- ubuntu stonking: erlang-doc 1:29.0.6+dfsg-1 · Apache-2.0
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms