1odbc(3)                    Erlang Module Definition                    odbc(3)
2
3
4

NAME

6       odbc - Erlang ODBC application
7

DESCRIPTION

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
14   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
23       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
29

COMMON DATA TYPES

31       Here follows type definitions that are used by more than  one  function
32       in the ODBC API.
33
34   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
40
41        connection_reference() - as returned by connect/2
42
43        time_out() = milliseconds() | infinity
44
45        milliseconds() = integer() >= 0
46
47        common_reason() = connection_closed | extended_error() | term() - some kind of
48        explanation of what went wrong
49
50        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
54        string() = list of ASCII characters
55
56        col_name() = string() - Name of column in the result set
57
58        col_names() - [col_name()] - e.g. a list of the names of the
59                  selected columns in the result set.
60
61        row() = {value()} - Tuple of column values e.g. one row of the
62                  result set.
63
64        value() = null | term() - A column value.
65
66        rows() = [row()] - A list of rows from the result set.
67
68        result_tuple() =
69             {updated, n_rows()} | {selected, col_names(), rows()}
70
71        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
75        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
87
88        precision() = integer()
89
90        scale() = integer()
91
92        size() = integer()
93

ERROR HANDLING

95       The  error  handling strategy and possible errors sources are described
96       in the Erlang ODBC User's Guide.
97

EXPORTS

99       commit(Ref, CommitMode) ->
100       commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason}
101
102              Types:
103
104                 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
110              Commits  or rollbacks a transaction. Needed on connections where
111              automatic commit is turned off.
112
113       connect(ConnectStr, Options) -> {ok, Ref} | {error, Reason}
114
115              Types:
116
117                 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
137              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
143              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
150              The default timeout is infinity
151
152              >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
158              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
163              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
168          Note:
169              Turning  the  scrollable_cursors option off is noted to make old
170              odbc-drivers able to connect that will otherwhise fail.
171
172
173              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
180          Note:
181              For more information about the ConnectStr see description of the
182              function SQLDriverConnect in [1].
183
184
185              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
193                * ODBCErrorCode is the ODBC error string returned by the  ODBC
194                  driver.
195
196                * 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
200                * Reason  is  as per the Reason field when extended errors are
201                  not enabled.
202
203          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
212
213       disconnect(Ref) -> ok | {error, Reason}
214
215              Types:
216
217                 Ref = connection_reference()
218                 Reason     =      process_not_owner_of_odbc_connection      |
219                 extended_error()
220
221              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 cannot
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
228       describe_table(Ref, Table) ->
229       describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Rea‐
230       son}
231
232              Types:
233
234                 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
240              Queries the database to find out the ODBC data types of the col‐
241              umns of the table Table.
242
243       first(Ref) ->
244       first(Ref, Timeout) -> {selected, ColNames, Rows} | {error, Reason}
245
246              Types:
247
248                 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
256              Returns the first row of the result set and positions  a  cursor
257              at this row.
258
259       last(Ref) ->
260       last(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
261
262              Types:
263
264                 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
272              Returns the last row of the result set and positions a cursor at
273              this row.
274
275       next(Ref) ->
276       next(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
277
278              Types:
279
280                 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
287              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
294       param_query(Ref, SQLQuery, Params) ->
295       param_query(Ref, SQLQuery, Params, TimeOut) ->  ResultTuple  |  {error,
296       Reason}
297
298              Types:
299
300                 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
312              Executes  a  parameterized  SQL  query.  For  an example see the
313              "Using the Erlang API" in the Erlang ODBC User's Guide.
314
315          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
323
324       prev(Ref) ->
325       prev(ConnectionReference,  TimeOut)  ->  {selected,  ColNames,  Rows} |
326       {error, Reason}
327
328              Types:
329
330                 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
338              Returns the previous row of the result set relative the  current
339              cursor position and positions the cursor at this row.
340
341       start() ->
342       start(Type) -> ok | {error, Reason}
343
344              Types:
345
346                 Type = permanent | transient | temporary
347
348              Starts  the  odbc  application.  Default  type is temporary. See
349              application(3)
350
351       stop() -> ok
352
353              Stops the odbc application. See application(3)
354
355       sql_query(Ref, SQLQuery) ->
356       sql_query(Ref,  SQLQuery,  TimeOut)  ->  ResultTuple  |   [ResultTuple]
357       |{error, Reason}
358
359              Types:
360
361                 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
369              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
376          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
381              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
385
386
387       select_count(Ref, SelectQuery) ->
388       select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error,  Rea‐
389       son}
390
391              Types:
392
393                 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
401              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
405          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
409
410       select(Ref, Position, N) ->
411       select(Ref,  Position,  N,  TimeOut)  ->  {selected,  ColNames, Rows} |
412       {error, Reason}
413
414              Types:
415
416                 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
432              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

REFERENCES

446       [1]: Microsoft ODBC 3.0, Programmer's Reference and SDK Guide
447       See also http://msdn.microsoft.com/
448
449
450
451Ericsson AB                       odbc 2.12.4                          odbc(3)
Impressum