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
andoracle
.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 typeinteger
, 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 value1
.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}
whereNrRows
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.
- CHARACTER (size), CHAR (size)
- NUMERIC (precision, scale), DECIMAL (precision, scale), DEC (precision, scale ) precision - total number of digits, scale - total number of decimal places
- INTEGER, INT, SMALLINT
- FLOAT (precision)
- REAL
- DOUBLE PRECISION
- CHARACTER VARYING(size), CHAR VARYING(size)
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 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.