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 se‐
16 lect/[3,4] assumes there is a result set associated with the connection
17 to work on. Calling the function select_count/[2,3] associates such a
18 result set with the connection. Calling select_count again will remove
19 the current result set association and create a new one. Calling a
20 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-server;UID=al‐
119 addin;PWD=sesame" where DSN is your ODBC Data Source Name,
120 UID is a database user id and PWD is the password for that
121 user. These are usually the attributes required in the con‐
122 nection string, but some drivers have other driver specific
123 attributes, for example "DSN=Oracle8;DBQ=gandalf;UID=al‐
124 addin;PWD=sesame" where DBQ is your TNSNAMES.ORA entry name
125 e.g. some Oracle specific configuration attribute.
126 Options = [] | [option()]
127 All options has default values.
128 option() = {auto_commit, on | off} | {timeout, millisec‐
129 onds()} | {binary_strings, on | off} | {tuple_row, on | off}
130 | {scrollable_cursors, on | off} | {trace_driver, on | off} |
131 {extended_errors, on | off}
132 Ref = connection_reference() - should be used to access the
133 connection.
134 Reason = port_program_executable_not_found | common_reason()
135 Opens a connection to the database. The connection is associated
137 with the process that created it and can only be accessed
138 through it. This function may spawn new processes to handle the
139 connection. These processes will terminate if the process that
140 created the connection dies or if you call disconnect/1.
141 If automatic commit mode is turned on, each query will be con‐
143 sidered as an individual transaction and will be automatically
144 committed after it has been executed. If you want more than one
145 query to be part of the same transaction the automatic commit
146 mode should be turned off. Then you will have to call commit/3
147 explicitly to end a transaction.
148 The default timeout is infinity
150 >If the option binary_strings is turned on all strings will be
152 returned as binaries and strings inputed to param_query will be
153 expected to be binaries. The user needs to ensure that the bi‐
154 nary is in an encoding that the database expects. By default
155 this option is turned off.
156 As default result sets are returned as a lists of tuples. The
158 TupleMode option still exists to keep some degree of backwards
159 compatibility. If the option is set to off, result sets will be
160 returned as a lists of lists instead of a lists of tuples.
161 Scrollable cursors are nice but causes some overhead. For some
163 connections speed might be more important than flexible data ac‐
164 cess and then you can disable scrollable cursor for a connec‐
165 tion, limiting the API but gaining speed.
166 Note:
168 Turning the scrollable_cursors option off is noted to make old
169 odbc-drivers able to connect that will otherwhise fail.
170 If trace mode is turned on this tells the ODBC driver to write a
173 trace log to the file SQL.LOG that is placed in the current di‐
174 rectory of the erlang emulator. This information may be useful
175 if you suspect there might be a bug in the erlang ODBC applica‐
176 tion, and it might be relevant for you to send this file to our
177 support. Otherwise you will probably not have much use of this.
178 Note:
180 For more information about the ConnectStr see description of the
181 function SQLDriverConnect in [1].
182 The extended_errors option enables extended ODBC error informa‐
185 tion when an operation fails. Rather than returning {error, Rea‐
186 son}, the failing function will reutrn {error, {ODBCErrorCode,
187 NativeErrorCode, Reason}}. Note that this information is proba‐
188 bly of little use when writing database-independent code, but
189 can be of assistance in providing more sophisticated error han‐
190 dling when dealing with a known underlying database.
191 * ODBCErrorCode is the ODBC error string returned by the ODBC
193 driver.
194 * NativeErrorCode is the numberic error code returned by the
196 underlying database. The possible values and their meanings
197 are dependent on the database being used.
198 * Reason is as per the Reason field when extended errors are
200 not enabled.
201 Note:
203 The current implementation spawns a port program written in C
204 that utilizes the actual ODBC driver. There is a default timeout
205 of 5000 msec for this port programm to connect to the Erlang
206 ODBC application. This timeout can be changed by setting an ap‐
207 plication specific environment variable 'port_timeout' with the
208 number of milliseconds for the ODBC application. E.g.: [{odbc,
209 [{port_timeout, 60000}]}] to set it to 60 seconds.
210 disconnect(Ref) -> ok | {error, Reason}
213 Types:
215 Ref = connection_reference()
217 Reason = process_not_owner_of_odbc_connection | extended_er‐
218 ror()
219 Closes a connection to a database. This will also terminate all
221 processes that may have been spawned when the connection was
222 opened. This call will always succeed. If the connection cannot
223 be disconnected gracefully it will be brutally killed. However
224 you may receive an error message as result if you try to discon‐
225 nect a connection started by another process.
226 describe_table(Ref, Table) ->
228 describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Rea‐
229 son}
230 Types:
232 Ref = connection_reference()
234 Table = string() - Name of databas table.
235 TimeOut = time_out()
236 Description = [{col_name(), odbc_data_type()}]
237 Reason = common_reason()
238 Queries the database to find out the ODBC data types of the col‐
240 umns of the table Table.
241 first(Ref) ->
243 first(Ref, Timeout) -> {selected, ColNames, Rows} | {error, Reason}
244 Types:
246 Ref = connection_reference()
248 TimeOut = time_out()
249 ColNames = col_names()
250 Rows = rows()
251 Reason = result_set_does_not_exist | driver_does_not_sup‐
252 port_function | scrollable_cursors_disabled |
253 process_not_owner_of_odbc_connection | common_reason()
254 Returns the first row of the result set and positions a cursor
256 at this row.
257 last(Ref) ->
259 last(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
260 Types:
262 Ref = connection_reference()
264 TimeOut = time_out()
265 ColNames = col_names()
266 Rows = rows()
267 Reason = result_set_does_not_exist | driver_does_not_sup‐
268 port_function | scrollable_cursors_disabled |
269 process_not_owner_of_odbc_connection | common_reason()
270 Returns the last row of the result set and positions a cursor at
272 this row.
273 next(Ref) ->
275 next(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
276 Types:
278 Ref = connection_reference()
280 TimeOut = time_out()
281 ColNames = col_names()
282 Rows = rows()
283 Reason = result_set_does_not_exist |
284 process_not_owner_of_odbc_connection | common_reason()
285 Returns the next row of the result set relative the current cur‐
287 sor position and positions the cursor at this row. If the cursor
288 is positioned at the last row of the result set when this func‐
289 tion is called the returned value will be {selected, Col‐
290 Names,[]} e.i. the list of row values is empty indicating that
291 there is no more data to fetch.
292 param_query(Ref, SQLQuery, Params) ->
294 param_query(Ref, SQLQuery, Params, TimeOut) -> ResultTuple | {error,
295 Reason}
296 Types:
298 Ref = connection_reference()
300 SQLQuery = string() - a SQL query with parameter mark‐
301 ers/place holders in form of question marks.
302 Params = [{odbc_data_type(), [value()]}] |[{odbc_data_type(),
303 in_or_out(), [value()]}]
304 in_or_out = in | out | inout
305 Defines IN, OUT, and IN OUT Parameter Modes for stored pro‐
306 cedures.
307 TimeOut = time_out()
308 Values = term() - Must be consistent with the Erlang data
309 type that corresponds to the ODBC data type ODBCDataType
310 Executes a parameterized SQL query. For an example see the "Us‐
312 ing the Erlang API" in the Erlang ODBC User's Guide.
313 Note:
315 Use the function describe_table/[2,3] to find out which ODBC
316 data type that is expected for each column of that table. If a
317 column has a data type that is described with capital letters,
318 alas it is not currently supported by the param_query function.
319 Too know which Erlang data type corresponds to an ODBC data type
320 see the Erlang to ODBC data type mapping in the User's Guide.
321 prev(Ref) ->
324 prev(ConnectionReference, TimeOut) -> {selected, ColNames, Rows} | {er‐
325 ror, Reason}
326 Types:
328 Ref = connection_reference()
330 TimeOut = time_out()
331 ColNames = col_names()
332 Rows = rows()
333 Reason = result_set_does_not_exist | driver_does_not_sup‐
334 port_function | scrollable_cursors_disabled |
335 process_not_owner_of_odbc_connection | common_reason()
336 Returns the previous row of the result set relative the current
338 cursor position and positions the cursor at this row.
339 start() ->
341 start(Type) -> ok | {error, Reason}
342 Types:
344 Type = permanent | transient | temporary
346 Starts the odbc application. Default type is temporary. See ap‐
348 plication(3)
349 stop() -> ok
351 Stops the odbc application. See application(3)
353 sql_query(Ref, SQLQuery) ->
355 sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple] |{er‐
356 ror, Reason}
357 Types:
359 Ref = connection_reference()
361 SQLQuery = string() - The string may be composed by several
362 SQL-queries separated by a ";", this is called a batch.
363 TimeOut = time_out()
364 ResultTuple = result_tuple()
365 Reason = process_not_owner_of_odbc_connection | common_rea‐
366 son()
367 Executes a SQL query or a batch of SQL queries. If it is a SE‐
369 LECT query the result set is returned, on the format {selected,
370 ColNames, Rows}. For other query types the tuple {updated,
371 NRows} is returned, and for batched queries, if the driver sup‐
372 ports them, this function can also return a list of result tu‐
373 ples.
374 Note:
376 Some drivers may not have the information of the number of af‐
377 fected rows available and then the return value may be {updated,
378 undefined} .
379 The list of column names is ordered in the same way as the list
381 of values of a row, e.g. the first ColName is associated with
382 the first Value in a Row.
383 select_count(Ref, SelectQuery) ->
387 select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error, Rea‐
388 son}
389 Types:
391 Ref = connection_reference()
393 SelectQuery = string()
394 SQL SELECT query.
395 TimeOut = time_out()
396 NrRows = n_rows()
397 Reason = process_not_owner_of_odbc_connection | common_rea‐
398 son()
399 Executes a SQL SELECT query and associates the result set with
401 the connection. A cursor is positioned before the first row in
402 the result set and the tuple {ok, NrRows} is returned.
403 Note:
405 Some drivers may not have the information of the number of rows
406 in the result set, then NrRows will have the value undefined.
407 select(Ref, Position, N) ->
410 select(Ref, Position, N, TimeOut) -> {selected, ColNames, Rows} | {er‐
411 ror, Reason}
412 Types:
414 Ref = connection_reference()
416 Position = next | {relative, Pos} | {absolute, Pos}
417 Selection strategy, determines at which row in the result
418 set to start the selection.
419 Pos = integer()
420 Should indicate a row number in the result set. When used
421 together with the option relativeit will be used as an off‐
422 set from the current cursor position, when used together
423 with the option absoluteit will be interpreted as a row
424 number.
425 N = integer()
426 TimeOut = time_out()
427 Reason = result_set_does_not_exist | driver_does_not_sup‐
428 port_function | scrollable_cursors_disabled |
429 process_not_owner_of_odbc_connection | common_reason()
430 Selects N consecutive rows of the result set. If Position is
432 next it is semantically equivalent of calling next/[1,2] N
433 times. If Position is {relative, Pos}, Pos will be used as an
434 offset from the current cursor position to determine the first
435 selected row. If Position is {absolute, Pos}, Pos will be the
436 number of the first row selected. After this function has re‐
437 turned the cursor is positioned at the last selected row. If
438 there is less then N rows left of the result set the length of
439 Rows will be less than N. If the first row to select happens to
440 be beyond the last row of the result set, the returned value
441 will be {selected, ColNames,[]} e.i. the list of row values is
442 empty indicating that there is no more data to fetch.
443
445 [1]: Microsoft ODBC 3.0, Programmer's Reference and SDK Guide
446 See also http://msdn.microsoft.com/
447Ericsson AB odbc 2.13.3.1 odbc(3)