GNU.WIKI: The GNU/Linux Knowledge Base

  [HOME] [PHP Manual] [HowTo] [ABS] [MAN1] [MAN2] [MAN3] [MAN4] [MAN5] [MAN6] [MAN7] [MAN8] [MAN9]

  [0-9] [Aa] [Bb] [Cc] [Dd] [Ee] [Ff] [Gg] [Hh] [Ii] [Jj] [Kk] [Ll] [Mm] [Nn] [Oo] [Pp] [Qq] [Rr] [Ss] [Tt] [Uu] [Vv] [Ww] [Xx] [Yy] [Zz]


NAME

       odbc - Erlang ODBC application

DESCRIPTION

       This  application  provides  an  Erlang  interface  to communicate with
       relational SQL-databases.  It  is  built  on  top  of  Microsofts  ODBC
       interface  and  therefore  requires that you have an ODBC driver to the
       database that you want to connect to.

   Note:
       The  functions  first/[1,2],  last/[1,2],  next/[1,2],  prev[1,2]   and
       select/[3,4]  assumes  there  is  a  result  set  associated  with  the
       connection  to  work  on.  Calling  the   function   select_count/[2,3]
       associates  such a result set with the connection. Calling select_count
       again will remove the current result set association and create  a  new
       one.  Calling a function which dose not operate on an associated result
       sets, such as sql_query/[2,3],  will  remove  the  current  result  set
       association.

       Alas  some drivers only support sequential traversal of the result set,
       e.i. they do not support what in the ODBC world is known as  scrollable
       cursors.  This will have the effect that functions such as first/[1,2],
       last/[1,2],      prev[1,2],      etc      will      return      {error,
       driver_does_not_support_function}

COMMON DATA TYPES

       Here  follows  type definitions that are used by more than one function
       in the ODBC API.

   Note:
       The type TimeOut has the default value infinity, so for instance:
       commit(Ref,  CommitMode)  is  the  same  as   commit(Ref,   CommitMode,
       infinity).  If the timeout expires the client will exit with the reason
       timeout.

        connection_reference() - as returned by connect/2

        time_out() = milliseconds() | infinity

        milliseconds() = integer() >= 0

        common_reason() = connection_closed | extended_error() | term() - some kind of
        explanation of what went wrong

        extended_error() = {string(), integer(), Reason} - extended error type with ODBC
        and native database error codes, as well as the base reason that would have been
        returned had extended_errors not been enabled.

        string() = list of ASCII characters

        col_name() = string() - Name of column in the result set

        col_names() - [col_name()] - e.g. a list of the names of the
                  selected columns in the result set.

        row() = {value()} - Tuple of column values e.g. one row of the
                  result set.

        value() = null | term() - A column value.

        rows() = [row()] - A list of rows from the result set.

        result_tuple() =
             {updated, n_rows()} | {selected, col_names(), rows()}

        n_rows() = integer() - The number of affected rows for UPDATE,
                  INSERT, or DELETE queries. For other query types the value
                  is driver defined, and hence should be ignored.

        odbc_data_type() = sql_integer | sql_smallint | sql_tinyint |
             {sql_decimal, precision(), scale()} |
             {sql_numeric, precision(), scale()} |
             {sql_char, size()} |
             {sql_wchar, size()} |
             {sql_varchar, size()} |
             {sql_wvarchar, size()}|
             {sql_float, precision()} |
             {sql_wlongvarchar, size()} |
             {sql_float, precision()} |
             sql_real | sql_double | sql_bit | atom()

        precision() = integer()

        scale() = integer()

        size() = integer()

ERROR HANDLING

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

EXPORTS

       commit(Ref, CommitMode) ->
       commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason}

              Types:

                 Ref = connection_reference()
                 CommitMode = commit | rollback
                 TimeOut = time_out()
                 Reason       =       not_an_explicit_commit_connection      |
                 process_not_owner_of_odbc_connection | common_reason()

              Commits or rollbacks a transaction. Needed on connections  where
              automatic commit is turned off.

       connect(ConnectStr, Options) -> {ok, Ref} | {error, Reason}

              Types:

                 ConnectStr = string()
                   An    example    of    a   connection   string:   "DSN=sql-
                   server;UID=aladdin;PWD=sesame" where DSN is your ODBC  Data
                   Source  Name,  UID  is  a  database  user id and PWD is the
                   password for that user. These are  usually  the  attributes
                   required  in  the  connection string, but some drivers have
                   other   driver    specific    attributes,    for    example
                   "DSN=Oracle8;DBQ=gandalf;UID=aladdin;PWD=sesame"  where DBQ
                   is your TNSNAMES.ORA entry name e.g. some  Oracle  specific
                   configuration attribute.
                 Options = [] | [option()]
                   All options has default values.
                 option()    =   {auto_commit,   on   |   off}   |   {timeout,
                 milliseconds()} | {binary_strings, on | off} | {tuple_row, on
                 | off} | {scrollable_cursors, on | off} | {trace_driver, on |
                 off} | {extended_errors, on | off}
                 Ref = connection_reference() - should be used to  access  the
                 connection.
                 Reason = port_program_executable_not_found | common_reason()

              Opens a connection to the database. 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. These processes will terminate if the  process  that
              created the connection dies or if you call disconnect/1.

              If  automatic  commit  mode  is  turned  on,  each query will be
              considered  as   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  the
              automatic  commit  mode should be turned off. Then you will have
              to call commit/3 explicitly to end a transaction.

              The default timeout is infinity

              >If the option binary_strings is turned on all strings  will  be
              returned  as binaries and strings inputed 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.

              Scrollable  cursors  are nice but causes some overhead. For some
              connections speed might be more  important  than  flexible  data
              access  and  then  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 otherwhise fail.

              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 the ConnectStr see description of the
              function SQLDriverConnect in [1].

              The  extended_errors  option   enables   extended   ODBC   error
              information  when  an  operation  fails.  Rather  than returning
              {error,  Reason},  the  failing  function  will  reutrn  {error,
              {ODBCErrorCode,   NativeErrorCode,   Reason}}.  Note  that  this
              information is probably of little  use  when  writing  database-
              independent  code,  but  can  be of assistance in providing more
              sophisticated  error  handling  when  dealing   with   a   known
              underlying database.

                * ODBCErrorCode  is the ODBC error string returned by the ODBC
                  driver.

                * NativeErrorCode is the numberic error code returned  by  the
                  underlying  database. The possible values and their meanings
                  are dependent on the database being used.

                * Reason is as per the Reason field when extended  errors  are
                  not enabled.

       disconnect(Ref) -> ok | {error, Reason}

              Types:

                 Ref = connection_reference()
                 Reason      =      process_not_owner_of_odbc_connection     |
                 extended_error()

              Closes a connection to a database. This will also terminate  all
              processes  that  may  have  been spawned when the connection was
              opened. This call will always succeed. If the connection can not
              be  disconnected  gracefully it will be brutally killed. However
              you may receive an  error  message  as  result  if  you  try  to
              disconnect a connection started by another process.

       describe_table(Ref, Table) ->
       describe_table(Ref,  Table,  Timeout)  ->  {ok,  Description} | {error,
       Reason}

              Types:

                 Ref = connection_reference()
                 Table = string() - Name of databas table.
                 TimeOut = time_out()
                 Description = [{col_name(), odbc_data_type()}]
                 Reason = common_reason()

              Queries the database to find out the  ODBC  data  types  of  the
              columns of the table Table.

       first(Ref) ->
       first(Ref, Timeout) -> {selected, ColNames, Rows} | {error, Reason}

              Types:

                 Ref = connection_reference()
                 TimeOut = time_out()
                 ColNames = col_names()
                 Rows = rows()
                 Reason          =         result_set_does_not_exist         |
                 driver_does_not_support_function                            |
                 scrollable_cursors_disabled                                 |
                 process_not_owner_of_odbc_connection | common_reason()

              Returns the first row of the result set and positions  a  cursor
              at this row.

       last(Ref) ->
       last(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}

              Types:

                 Ref = connection_reference()
                 TimeOut = time_out()
                 ColNames = col_names()
                 Rows = rows()
                 Reason          =         result_set_does_not_exist         |
                 driver_does_not_support_function                            |
                 scrollable_cursors_disabled                                 |
                 process_not_owner_of_odbc_connection | common_reason()

              Returns the last row of the result set and positions a cursor at
              this row.

       next(Ref) ->
       next(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}

              Types:

                 Ref = connection_reference()
                 TimeOut = time_out()
                 ColNames = col_names()
                 Rows = rows()
                 Reason          =         result_set_does_not_exist         |
                 process_not_owner_of_odbc_connection | common_reason()

              Returns the next row of the  result  set  relative  the  current
              cursor  position  and  positions  the cursor at this row. If the
              cursor is positioned at the last row of the result set when this
              function  is  called  the  returned  value  will  be  {selected,
              ColNames,[]} e.i. the list of row  values  is  empty  indicating
              that there is no more data to fetch.

       param_query(Ref, SQLQuery, Params) ->
       param_query(Ref,  SQLQuery,  Params,  TimeOut) -> ResultTuple | {error,
       Reason}

              Types:

                 Ref = connection_reference()
                 SQLQuery  =  string()  -   a   SQL   query   with   parameter
                 markers/place holders in form of question marks.
                 Params = [{odbc_data_type(), [value()]}] |[{odbc_data_type(),
                 in_or_out(), [value()]}]
                 in_or_out = in | out | inout
                   Defines IN, OUT, and IN  OUT  Parameter  Modes  for  stored
                   procedures.
                 TimeOut = time_out()
                 Values  =  term()  -  Must be consistent with the Erlang data
                 type that corresponds to the ODBC data type ODBCDataType

              Executes a parameterized SQL  query.  For  an  example  see  the
              "Using the Erlang API" in the Erlang ODBC User's Guide.

          Note:
              Use  the  function  describe_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 the param_query  function.
              Too know which Erlang data type corresponds to an ODBC data type
              see the Erlang to ODBC data type mapping in the User's Guide.

       prev(Ref) ->
       prev(ConnectionReference, TimeOut)  ->  {selected,  ColNames,  Rows}  |
       {error, Reason}

              Types:

                 Ref = connection_reference()
                 TimeOut = time_out()
                 ColNames = col_names()
                 Rows = rows()
                 Reason          =         result_set_does_not_exist         |
                 driver_does_not_support_function                            |
                 scrollable_cursors_disabled                                 |
                 process_not_owner_of_odbc_connection | common_reason()

              Returns the previous row of the result set relative the  current
              cursor position and positions the cursor at this row.

       start() ->
       start(Type) -> ok | {error, Reason}

              Types:

                 Type = permanent | transient | temporary

              Starts  the  odbc  application.  Default  type is temporary. See
              application(3erl)

       stop() -> ok

              Stops the odbc application. See application(3erl)

       sql_query(Ref, SQLQuery) ->
       sql_query(Ref,  SQLQuery,  TimeOut)  ->  ResultTuple  |   [ResultTuple]
       |{error, Reason}

              Types:

                 Ref = connection_reference()
                 SQLQuery  =  string() - The string may be composed by several
                 SQL-queries separated by a ";", this is called a batch.
                 TimeOut = time_out()
                 ResultTuple = result_tuple()
                 Reason     =      process_not_owner_of_odbc_connection      |
                 common_reason()

              Executes  a  SQL  query  or  a  batch of SQL queries. If it is a
              SELECT  query  the  result  set  is  returned,  on  the   format
              {selected,  ColNames,  Rows}.  For  other  query types the tuple
              {updated, NRows} is returned, 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} .

              The list of column names is ordered in the same way as the  list
              of  values  of  a row, e.g. the first ColName is associated with
              the first Value in a Row.

       select_count(Ref, SelectQuery) ->
       select_count(Ref, SelectQuery,  TimeOut)  ->  {ok,  NrRows}  |  {error,
       Reason}

              Types:

                 Ref = connection_reference()
                 SelectQuery = string()
                   SQL SELECT query.
                 TimeOut = time_out()
                 NrRows = n_rows()
                 Reason      =      process_not_owner_of_odbc_connection     |
                 common_reason()

              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.

          Note:
              Some drivers may not have the information of the number of  rows
              in the result set, then NrRows will have the value undefined.

       select(Ref, Position, N) ->
       select(Ref,  Position,  N,  TimeOut)  ->  {selected,  ColNames, Rows} |
       {error, Reason}

              Types:

                 Ref = connection_reference()
                 Position = next | {relative, Pos} | {absolute, Pos}
                   Selection strategy, determines at which row in  the  result
                   set to start the selection.
                 Pos = integer()
                   Should  indicate  a row number in the result set. When used
                   together with the option relativeit  will  be  used  as  an
                   offset from the current cursor position, when used together
                   with the option absoluteit will be  interpreted  as  a  row
                   number.
                 N = integer()
                 TimeOut = time_out()
                 Reason          =         result_set_does_not_exist         |
                 driver_does_not_support_function                            |
                 scrollable_cursors_disabled                                 |
                 process_not_owner_of_odbc_connection | common_reason()

              Selects N consecutive rows of the result  set.  If  Position  is
              next  it  is  semantically  equivalent  of  calling next/[1,2] N
              times. If Position is {relative, Pos}, Pos will be  used  as  an
              offset  from  the current cursor position to determine the first
              selected row. If Position is {absolute, Pos}, Pos  will  be  the
              number  of  the  first  row  selected.  After  this function has
              returned the cursor is positioned at the last selected  row.  If
              there  is  less then N rows left of the result set the length of
              Rows will be less than N. If the first row to select happens  to
              be  beyond  the  last  row of the result set, the returned value
              will be {selected, ColNames,[]} e.i. the list of row  values  is
              empty indicating that there is no more data to fetch.

REFERENCES

       [1]: Microsoft ODBC 3.0, Programmer's Reference and SDK Guide
       See also http://msdn.microsoft.com/



  All copyrights belong to their respective owners. Other content (c) 2014-2018, GNU.WIKI. Please report site errors to webmaster@gnu.wiki.
Page load time: 0.092 seconds. Last modified: November 04 2018 12:49:43.