[Erlang Systems]

3 Databases

3.1 Databases

If you need to access a relational database such as sqlserver, mysql, postgress, oracle, cybase etc. from your erlang application using the Erlang ODBC interface is the way to go about it.

The Erlang ODBC application should work for any relational database that has an ODBC driver. But currently it is only tested for sqlserver and oracle.

3.2 Database independence

The Erlang ODBC interface is in principal database independent, e.i. an erlang program using the interface could be run without changes towards different databases. But as SQL is used it is alas possible to write database dependent programs. Even though SQL is an ANSI-standard meant to be database independent, different databases have proprietary extensions to SQL defining their own data types. If you keep to the ANSI data types you will minimize the problem. But unfortunately there is no guarantee that all databases actually treats the ANSI data types equivalently. For instance an installation of Oracle Enterprise release 8.0.5.0.0 for unix will accept that you create a table with the ANSI data type integer, but internally it will use an oracle data type. This alas is not transparent, so when retrieving the data it will have a different data type, in this case it will result in that the erlang user will get the string "1" instead of the value 1.

Another obstacle is that some drivers do not support scrollable cursors which has the effect that the only way to traverse the result set is sequentially, with next, from the first row to the last, and once you pass a row you can not go back. This means that some functions in the interface will not work together with certain drivers. A similar problem is that not all drivers support "row count" for select queries, hence resulting in that the function select_count/[3,4] will return {ok, undefined} instead of {ok, NrRows} where NrRows is the number of rows in the result set.

3.3 Data types

The following is a list of the ANSI data types. For details turn to the ANSI standard documentation. Usage of other data types is of course possible, but you should be aware that this makes your application dependent on the database you are using at the moment.

When inputing data the values will always be in string format as they are part of an SQL-query. Example:

      odbc:sql_query(Ref, "INSERT INTO TEST VALUES(1, 2, 3)").
    

Note!

Note that when the value of the data to input is a string, it has to be quoted with '. Example:

    odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
      

When selecting data from a table, all data types are returned from the database to the ODBC driver as an ODBC data type. The tables below shows the mapping between those data types and what is returned by the Erlang API.

ODBC Data Type Erlang Data Type
SQL_CHAR String
SQL_NUMERIC Float
SQL_DECIMAL String
SQL_INTEGER Integer
SQL_SMALLINT Integer
SQL_FLOAT Float
SQL_REAL Float
SQL_DOUBLE Float
SQL_VARCHAR String
Mapping of ODBC data types to the Erlang data types returned to the Erlang application.

ODBC Data Type Erlang Data Type
SQL_TYPE_DATE String
SQL_TYPE_TIME String
SQL_TYPE_TIMESTAMP String
SQL_LONGVARCHAR String
SQL_BINARY String
SQL_VARBINARY String
SQL_LONGVARBINARY String
SQL_BIGINT String
SQL_TINYINT Integer
SQL_BIT Boolean
Mapping of extended ODBC data types to the Erlang data types returned to the Erlang application.

Copyright © 1991-2003 Ericsson Utvecklings AB