1odbc(3) Erlang Module Definition odbc(3)
2
6 odbc - Erlang ODBC application
7
9 This application provides an Erlang interface to communicate with rela‐
10 tional SQL-databases. It is built on top of Microsofts ODBC interface
11 and therefore requires that you have an ODBC driver to the database
12 that you want to connect to.
13 Note:
15 The functions first/[1,2], last/[1,2], next/[1,2], prev[1,2] and
16 select/[3,4] assumes there is a result set associated with the connec‐
17 tion to work on. Calling the function select_count/[2,3] associates
18 such a result set with the connection. Calling select_count again will
19 remove the current result set association and create a new one. Calling
20 a function which dose not operate on an associated result sets, such as
21 sql_query/[2,3], will remove the current result set association.
22 Alas some drivers only support sequential traversal of the result set,
24 e.i. they do not support what in the ODBC world is known as scrollable
25 cursors. This will have the effect that functions such as first/[1,2],
26 last/[1,2], prev[1,2], etc will return {error, driver_does_not_sup‐
27 port_function}
28
31 Here follows type definitions that are used by more than one function
32 in the ODBC API.
33 Note:
35 The type TimeOut has the default value infinity, so for instance:
36 commit(Ref, CommitMode) is the same as commit(Ref, CommitMode, infin‐
37 ity). If the timeout expires the client will exit with the reason time‐
38 out.
39 connection_reference() - as returned by connect/2
42 time_out() = milliseconds() | infinity
44 milliseconds() = integer() >= 0
46 common_reason() = connection_closed | extended_error() | term() - some kind of
48 explanation of what went wrong
49 extended_error() = {string(), integer(), Reason} - extended error type with ODBC
51 and native database error codes, as well as the base reason that would have been
52 returned had extended_errors not been enabled.
53 string() = list of ASCII characters
55 col_name() = string() - Name of column in the result set
57 col_names() - [col_name()] - e.g. a list of the names of the
59 selected columns in the result set.
60 row() = {value()} - Tuple of column values e.g. one row of the
62 result set.
63 value() = null | term() - A column value.
65 rows() = [row()] - A list of rows from the result set.
67 result_tuple() =
69 {updated, n_rows()} | {selected, col_names(), rows()}
70 n_rows() = integer() - The number of affected rows for UPDATE,
72 INSERT, or DELETE queries. For other query types the value
73 is driver defined, and hence should be ignored.
74 odbc_data_type() = sql_integer | sql_smallint | sql_tinyint |
76 {sql_decimal, precision(), scale()} |
77 {sql_numeric, precision(), scale()} |
78 {sql_char, size()} |
79 {sql_wchar, size()} |
80 {sql_varchar, size()} |
81 {sql_wvarchar, size()}|
82 {sql_float, precision()} |
83 {sql_wlongvarchar, size()} |
84 {sql_float, precision()} |
85 sql_real | sql_double | sql_bit | atom()
86 precision() = integer()
89 scale() = integer()
91 size() = integer()
93
95 The error handling strategy and possible errors sources are described
96 in the Erlang ODBC User's Guide.
97
99 commit(Ref, CommitMode) ->
100 commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason}
101 Types:
103 Ref = connection_reference()
105 CommitMode = commit | rollback
106 TimeOut = time_out()
107 Reason = not_an_explicit_commit_connection |
108 process_not_owner_of_odbc_connection | common_reason()
109 Commits or rollbacks a transaction. Needed on connections where
111 automatic commit is turned off.
112 connect(ConnectStr, Options) -> {ok, Ref} | {error, Reason}
114 Types:
116 ConnectStr = string()
118 An example of a connection string: "DSN=sql-
119 server;UID=aladdin;PWD=sesame" where DSN is your ODBC Data
120 Source Name, UID is a database user id and PWD is the pass‐
121 word for that user. These are usually the attributes
122 required in the connection string, but some drivers have
123 other driver specific attributes, for example "DSN=Ora‐
124 cle8;DBQ=gandalf;UID=aladdin;PWD=sesame" where DBQ is your
125 TNSNAMES.ORA entry name e.g. some Oracle specific configu‐
126 ration attribute.
127 Options = [] | [option()]
128 All options has default values.
129 option() = {auto_commit, on | off} | {timeout, millisec‐
130 onds()} | {binary_strings, on | off} | {tuple_row, on | off}
131 | {scrollable_cursors, on | off} | {trace_driver, on | off} |
132 {extended_errors, on | off}
133 Ref = connection_reference() - should be used to access the
134 connection.
135 Reason = port_program_executable_not_found | common_reason()
136 Opens a connection to the database. The connection is associated
138 with the process that created it and can only be accessed
139 through it. This function may spawn new processes to handle the
140 connection. These processes will terminate if the process that
141 created the connection dies or if you call disconnect/1.
142 If automatic commit mode is turned on, each query will be con‐
144 sidered as an individual transaction and will be automatically
145 committed after it has been executed. If you want more than one
146 query to be part of the same transaction the automatic commit
147 mode should be turned off. Then you will have to call commit/3
148 explicitly to end a transaction.
149 The default timeout is infinity
151 >If the option binary_strings is turned on all strings will be
153 returned as binaries and strings inputed to param_query will be
154 expected to be binaries. The user needs to ensure that the
155 binary is in an encoding that the database expects. By default
156 this option is turned off.
157 As default result sets are returned as a lists of tuples. The
159 TupleMode option still exists to keep some degree of backwards
160 compatibility. If the option is set to off, result sets will be
161 returned as a lists of lists instead of a lists of tuples.
162 Scrollable cursors are nice but causes some overhead. For some
164 connections speed might be more important than flexible data
165 access and then you can disable scrollable cursor for a connec‐
166 tion, limiting the API but gaining speed.
167 Note:
169 Turning the scrollable_cursors option off is noted to make old
170 odbc-drivers able to connect that will otherwhise fail.
171 If trace mode is turned on this tells the ODBC driver to write a
174 trace log to the file SQL.LOG that is placed in the current
175 directory of the erlang emulator. This information may be useful
176 if you suspect there might be a bug in the erlang ODBC applica‐
177 tion, and it might be relevant for you to send this file to our
178 support. Otherwise you will probably not have much use of this.
179 Note:
181 For more information about the ConnectStr see description of the
182 function SQLDriverConnect in [1].
183 The extended_errors option enables extended ODBC error informa‐
186 tion when an operation fails. Rather than returning {error, Rea‐
187 son}, the failing function will reutrn {error, {ODBCErrorCode,
188 NativeErrorCode, Reason}}. Note that this information is proba‐
189 bly of little use when writing database-independent code, but
190 can be of assistance in providing more sophisticated error han‐
191 dling when dealing with a known underlying database.
192 * ODBCErrorCode is the ODBC error string returned by the ODBC
194 driver.
195 * NativeErrorCode is the numberic error code returned by the
197 underlying database. The possible values and their meanings
198 are dependent on the database being used.
199 * Reason is as per the Reason field when extended errors are
201 not enabled.
202 Note:
204 The current implementation spawns a port programm written in C
205 that utilizes the actual ODBC driver. There is a default timeout
206 of 5000 msec for this port programm to connect to the Erlang
207 ODBC application. This timeout can be changed by setting an
208 application specific environment variable 'port_timeout' with
209 the number of milliseconds for the ODBC application. E.g.:
210 [{odbc, [{port_timeout, 60000}]}] to set it to 60 seconds.
211 disconnect(Ref) -> ok | {error, Reason}
214 Types:
216 Ref = connection_reference()
218 Reason = process_not_owner_of_odbc_connection |
219 extended_error()
220 Closes a connection to a database. This will also terminate all
222 processes that may have been spawned when the connection was
223 opened. This call will always succeed. If the connection can not
224 be disconnected gracefully it will be brutally killed. However
225 you may receive an error message as result if you try to discon‐
226 nect a connection started by another process.
227 describe_table(Ref, Table) ->
229 describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Rea‐
230 son}
231 Types:
233 Ref = connection_reference()
235 Table = string() - Name of databas table.
236 TimeOut = time_out()
237 Description = [{col_name(), odbc_data_type()}]
238 Reason = common_reason()
239 Queries the database to find out the ODBC data types of the col‐
241 umns of the table Table.
242 first(Ref) ->
244 first(Ref, Timeout) -> {selected, ColNames, Rows} | {error, Reason}
245 Types:
247 Ref = connection_reference()
249 TimeOut = time_out()
250 ColNames = col_names()
251 Rows = rows()
252 Reason = result_set_does_not_exist | driver_does_not_sup‐
253 port_function | scrollable_cursors_disabled |
254 process_not_owner_of_odbc_connection | common_reason()
255 Returns the first row of the result set and positions a cursor
257 at this row.
258 last(Ref) ->
260 last(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
261 Types:
263 Ref = connection_reference()
265 TimeOut = time_out()
266 ColNames = col_names()
267 Rows = rows()
268 Reason = result_set_does_not_exist | driver_does_not_sup‐
269 port_function | scrollable_cursors_disabled |
270 process_not_owner_of_odbc_connection | common_reason()
271 Returns the last row of the result set and positions a cursor at
273 this row.
274 next(Ref) ->
276 next(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
277 Types:
279 Ref = connection_reference()
281 TimeOut = time_out()
282 ColNames = col_names()
283 Rows = rows()
284 Reason = result_set_does_not_exist |
285 process_not_owner_of_odbc_connection | common_reason()
286 Returns the next row of the result set relative the current cur‐
288 sor position and positions the cursor at this row. If the cursor
289 is positioned at the last row of the result set when this func‐
290 tion is called the returned value will be {selected, Col‐
291 Names,[]} e.i. the list of row values is empty indicating that
292 there is no more data to fetch.
293 param_query(Ref, SQLQuery, Params) ->
295 param_query(Ref, SQLQuery, Params, TimeOut) -> ResultTuple | {error,
296 Reason}
297 Types:
299 Ref = connection_reference()
301 SQLQuery = string() - a SQL query with parameter mark‐
302 ers/place holders in form of question marks.
303 Params = [{odbc_data_type(), [value()]}] |[{odbc_data_type(),
304 in_or_out(), [value()]}]
305 in_or_out = in | out | inout
306 Defines IN, OUT, and IN OUT Parameter Modes for stored pro‐
307 cedures.
308 TimeOut = time_out()
309 Values = term() - Must be consistent with the Erlang data
310 type that corresponds to the ODBC data type ODBCDataType
311 Executes a parameterized SQL query. For an example see the
313 "Using the Erlang API" in the Erlang ODBC User's Guide.
314 Note:
316 Use the function describe_table/[2,3] to find out which ODBC
317 data type that is expected for each column of that table. If a
318 column has a data type that is described with capital letters,
319 alas it is not currently supported by the param_query function.
320 Too know which Erlang data type corresponds to an ODBC data type
321 see the Erlang to ODBC data type mapping in the User's Guide.
322 prev(Ref) ->
325 prev(ConnectionReference, TimeOut) -> {selected, ColNames, Rows} |
326 {error, Reason}
327 Types:
329 Ref = connection_reference()
331 TimeOut = time_out()
332 ColNames = col_names()
333 Rows = rows()
334 Reason = result_set_does_not_exist | driver_does_not_sup‐
335 port_function | scrollable_cursors_disabled |
336 process_not_owner_of_odbc_connection | common_reason()
337 Returns the previous row of the result set relative the current
339 cursor position and positions the cursor at this row.
340 start() ->
342 start(Type) -> ok | {error, Reason}
343 Types:
345 Type = permanent | transient | temporary
347 Starts the odbc application. Default type is temporary. See
349 application(3)
350 stop() -> ok
352 Stops the odbc application. See application(3)
354 sql_query(Ref, SQLQuery) ->
356 sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple]
357 |{error, Reason}
358 Types:
360 Ref = connection_reference()
362 SQLQuery = string() - The string may be composed by several
363 SQL-queries separated by a ";", this is called a batch.
364 TimeOut = time_out()
365 ResultTuple = result_tuple()
366 Reason = process_not_owner_of_odbc_connection | common_rea‐
367 son()
368 Executes a SQL query or a batch of SQL queries. If it is a
370 SELECT query the result set is returned, on the format
371 {selected, ColNames, Rows}. For other query types the tuple
372 {updated, NRows} is returned, and for batched queries, if the
373 driver supports them, this function can also return a list of
374 result tuples.
375 Note:
377 Some drivers may not have the information of the number of
378 affected rows available and then the return value may be
379 {updated, undefined} .
380 The list of column names is ordered in the same way as the list
382 of values of a row, e.g. the first ColName is associated with
383 the first Value in a Row.
384 select_count(Ref, SelectQuery) ->
388 select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error, Rea‐
389 son}
390 Types:
392 Ref = connection_reference()
394 SelectQuery = string()
395 SQL SELECT query.
396 TimeOut = time_out()
397 NrRows = n_rows()
398 Reason = process_not_owner_of_odbc_connection | common_rea‐
399 son()
400 Executes a SQL SELECT query and associates the result set with
402 the connection. A cursor is positioned before the first row in
403 the result set and the tuple {ok, NrRows} is returned.
404 Note:
406 Some drivers may not have the information of the number of rows
407 in the result set, then NrRows will have the value undefined.
408 select(Ref, Position, N) ->
411 select(Ref, Position, N, TimeOut) -> {selected, ColNames, Rows} |
412 {error, Reason}
413 Types:
415 Ref = connection_reference()
417 Position = next | {relative, Pos} | {absolute, Pos}
418 Selection strategy, determines at which row in the result
419 set to start the selection.
420 Pos = integer()
421 Should indicate a row number in the result set. When used
422 together with the option relativeit will be used as an off‐
423 set from the current cursor position, when used together
424 with the option absoluteit will be interpreted as a row
425 number.
426 N = integer()
427 TimeOut = time_out()
428 Reason = result_set_does_not_exist | driver_does_not_sup‐
429 port_function | scrollable_cursors_disabled |
430 process_not_owner_of_odbc_connection | common_reason()
431 Selects N consecutive rows of the result set. If Position is
433 next it is semantically equivalent of calling next/[1,2] N
434 times. If Position is {relative, Pos}, Pos will be used as an
435 offset from the current cursor position to determine the first
436 selected row. If Position is {absolute, Pos}, Pos will be the
437 number of the first row selected. After this function has
438 returned the cursor is positioned at the last selected row. If
439 there is less then N rows left of the result set the length of
440 Rows will be less than N. If the first row to select happens to
441 be beyond the last row of the result set, the returned value
442 will be {selected, ColNames,[]} e.i. the list of row values is
443 empty indicating that there is no more data to fetch.
444
446 [1]: Microsoft ODBC 3.0, Programmer's Reference and SDK Guide
447 See also http://msdn.microsoft.com/
448Ericsson AB odbc 2.12.1 odbc(3)