odbc

v2.16

ODBC Release Notes

This document describes the changes made to the odbc application.

ODBC 2.16

Fixed Bugs and Malfunctions

Enhance specs of timeout for improving documentation and dialyzer analysis.

Own Id: OTP-19604 Aux Id: PR-9574

Improvements and New Features

Updated odbc configure to enable easier use of iodbc driver.

Own Id: OTP-19456 Aux Id: PR-9083

Added support for compiling Erlang/OTP for Windows on ARM64.

Own Id: OTP-19480 Aux Id: PR-8734

When compiling C/C++ code on Unix systems, the compiler hardening flags suggested by the Open Source Security Foundation are now enabled by default. To disable them, pass --disable-security-hardening-flags to configure.

Own Id: OTP-19519 Aux Id: PR-9441

The license and copyright header has changed format to include an SPDX-License-Identifier. At the same time, most files have been updated to follow a uniform standard for license headers.

Own Id: OTP-19575 Aux Id: PR-9670

ODBC 2.15

Improvements and New Features

Figures in the documentation have been improved.

Own Id: OTP-19130 Aux Id: PR-7226

ODBC 2.14.3

Fixed Bugs and Malfunctions

The order of libs in the Makefile has been changed to avoid finding the system's libei instead of Erlang's libei.

Own Id: OTP-19030 Aux Id: GH-8244, PR-8258

Improvements and New Features

The documentation has been migrated to use Markdown and ExDoc.

Own Id: OTP-18955 Aux Id: PR-8026

ODBC 2.14.2

Improvements and New Features

Use spec for API doc

Own Id: OTP-18926

ODBC 2.14.1

Improvements and New Features

Allow larger column sizes than 8001 in case DB supports it.

Own Id: OTP-18539

ODBC 2.14

Improvements and New Features

Input for configure scripts adapted to autoconf 2.71.

Own Id: OTP-17414 Aux Id: PR-4967

ODBC 2.13.5

Fixed Bugs and Malfunctions

Commit of generated configure script.

Own Id: OTP-17420 Aux Id: OTP-17398, GH-4821

ODBC 2.13.4

Fixed Bugs and Malfunctions

Fix compiler warnings produced by the clang compiler.

Own Id: OTP-17105 Aux Id: PR-2872

ODBC 2.13.3.1

Fixed Bugs and Malfunctions

Commit of generated configure script.

Own Id: OTP-17420 Aux Id: OTP-17398, GH-4821

ODBC 2.13.3

Fixed Bugs and Malfunctions

Make sure odbc c-process exits when erlang process orders it to shutdown.

Own Id: OTP-17188 Aux Id: ERL-1448

ODBC 2.13.2

Fixed Bugs and Malfunctions

Fixed usage of AC_CONFIG_AUX_DIRS() macros in configure script sources.

Own Id: OTP-17093 Aux Id: ERL-1447, PR-2948

ODBC 2.13.1

Improvements and New Features

Changes in order to build on the Haiku operating system.

Thanks to Calvin Buckley

Own Id: OTP-16707 Aux Id: PR-2638

ODBC 2.13

Fixed Bugs and Malfunctions

Fix various compiler warnings on 64-bit Windows.

Own Id: OTP-15800

Improvements and New Features

Rewrite due to the removal of erl_interface legacy functions.

Own Id: OTP-16544 Aux Id: OTP-16328

ODBC 2.12.4.1

Fixed Bugs and Malfunctions

Commit of generated configure script.

Own Id: OTP-17420 Aux Id: OTP-17398, GH-4821

ODBC 2.12.4

Improvements and New Features

Minor adjustments made to build system for parallel configure.

Own Id: OTP-15340 Aux Id: OTP-14625

ODBC 2.12.3

Fixed Bugs and Malfunctions

Enhance error handling to avoid stack corruption

Own Id: OTP-15667 Aux Id: ERL-808, PR-2065

ODBC 2.12.2

Fixed Bugs and Malfunctions

Improved documentation.

Own Id: OTP-15190

ODBC 2.12.1

Fixed Bugs and Malfunctions

Removed all old unused files in the documentation.

Own Id: OTP-14475 Aux Id: ERL-409, PR-1493

ODBC 2.12

Improvements and New Features

Change configure to skip odbc for old MACs, the change in PR-1227 is not backwards compatible with old MACs, and we do not see a need to continue support for such old versions. However it is still possible to make it work on such machines using the --with-odbc configure option.

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-14083

ODBC 2.11.3

Fixed Bugs and Malfunctions

ODBC build configure has been updated to accept Mac OS X El Capitan. Fixed by Lee Bannard.

Own Id: OTP-13781

ODBC 2.11.2

Improvements and New Features

Configure enhancement for better handling program paths used in the build process

Own Id: OTP-13559

ODBC 2.11.1

Improvements and New Features

New application variable to set timeout of internal communication setup between the erlang code and the c-port program that interfaces the odbc driver. This can be useful if you have an underlying system that is slow due to heavy load at startup.

With this environment variable you can easily bypass and tailor odbc to the needs of the underlying actual system without changing the configuration. Which is a good thing because this value is very system specific.

Own Id: OTP-12935

ODBC 2.11

Improvements and New Features

Change license text from Erlang Public License to Apache Public License v2

Own Id: OTP-12845

ODBC 2.10.22

Fixed Bugs and Malfunctions

OS X Mavericks is based on Darwin version 13.x, and Yosemite on 14.x. Change the ODBC configure.in script to recognize these versions.

Own Id: OTP-12260

Improvements and New Features

The commands longer than 127 chars sent to odbc server crashed it, e.g. a connection string with driver path and some additional parameters.

Own Id: OTP-12346

Distribute autoconf helpers to applications at build time instead of having multiple identical copies committed in the repository.

Own Id: OTP-12348

ODBC 2.10.21

Fixed Bugs and Malfunctions

Fix compiler warnings reported by LLVM

Own Id: OTP-12138

Implement --enable-sanitizers[=sanitizers]. Similar to debugging with Valgrind, it's very useful to enable -fsanitize= switches to catch bugs at runtime.

Own Id: OTP-12153

ODBC 2.10.20

Fixed Bugs and Malfunctions

Application upgrade (appup) files are corrected for the following applications:

asn1, common_test, compiler, crypto, debugger, dialyzer, edoc, eldap, erl_docgen, et, eunit, gs, hipe, inets, observer, odbc, os_mon, otp_mibs, parsetools, percept, public_key, reltool, runtime_tools, ssh, syntax_tools, test_server, tools, typer, webtool, wx, xmerl

A new test utility for testing appup files is added to test_server. This is now used by most applications in OTP.

(Thanks to Tobias Schlager)

Own Id: OTP-11744

Improvements and New Features

Removed warnings at compile time by adding missing include file (Thanks to Anthony Ramine)

Own Id: OTP-11569

Apple has removed iODBC in OS X 10.9 Mavericks, but forgot to remove all binaries, adopt configure so that will be possible to build odbc with own installation.

Own Id: OTP-11630

ODBC 2.10.19

Fixed Bugs and Malfunctions

Updated configure test for header files sql.h and sqlext.h to function correctly on windows.

Own Id: OTP-11574

ODBC 2.10.18

Improvements and New Features

Configure now also checks for the existence of the sql.h header file

Own Id: OTP-11483

ODBC 2.10.17

Fixed Bugs and Malfunctions

The format of the xml source for documentation is corrected in order to conform to the DTDs and to pass xmllint without errors.

Own Id: OTP-11193

Improvements and New Features

Introduced functionality for inspection of system and build configuration.

Own Id: OTP-11196

Prevent odbcserver crash if it's executed and supplied incorrect data to stdin. Thanks to Sergei Golovan.

Own Id: OTP-11233

ODBC 2.10.16

Improvements and New Features

Fix a 64bit related bug in odbcserver. Thanks to Satoshi Kinoshita.

Own Id: OTP-10993

Postscript files no longer needed for the generation of PDF files have been removed.

Own Id: OTP-11016

Fix checking for odbc in standard locations when "with-odbc" flag present. Thanks to Alexey Saltanov.

Own Id: OTP-11126

ODBC 2.10.15

Improvements and New Features

Fixed calling odbc:param_query/3 and odbc:param_query/4 with unparameterized query string and empty parameters list. Thanks to Danil Onishchenko.

Own Id: OTP-10798

ODBC 2.10.14

Improvements and New Features

Under Unix enable TCP_NODELAY to disable Nagel's socket algorithm. Thanks to Andy Richards

Impact: Performance gain on Unix systems

Own Id: OTP-10506

Added extended_errors option to ODBC

When enabled, this option alters the return code of ODBC operations that produce errors to include the ODBC error code as well as the native error code, in addition to the ODBC reason field which is returned by default. Thanks to Bernard Duggan.

Own Id: OTP-10603

Where necessary a comment stating encoding has been added to Erlang files. The comment is meant to be removed in Erlang/OTP R17B when UTF-8 becomes the default encoding.

Own Id: OTP-10630

Some examples overflowing the width of PDF pages have been corrected.

Own Id: OTP-10665

Fix aotocommit for Oracle ODBC driver in Linux. Thanks to Danil Onishchenko.

Own Id: OTP-10735

ODBC 2.10.13

Fixed Bugs and Malfunctions

Add support for NULL value in odbc:param_query

Support atom 'null' in odbc:param_query as database NULL value Fix "ODBC: received unexpected info:{tcp_closed, ...}" when connection is terminated. Fix possible access violation with 64bit ODBC. Thanks to Maxim Zrazhevskiy

Own Id: OTP-10206

ODBC 2.10.12

Fixed Bugs and Malfunctions

An ODBC process should exit normally if its client exits with 'shutdown'

There is nothing strange about the client shutting down, so the ODBC process should exit normally to avoid generating a crash report for a non-problem. (Thanks to Magnus Henoch)

Own Id: OTP-9716

Improvements and New Features

Erlang/OTP can now be built using parallel make if you limit the number of jobs, for instance using 'make -j6' or 'make -j10'. 'make -j' does not work at the moment because of some missing dependencies.

Own Id: OTP-9451

ODBC 2.10.11

Fixed Bugs and Malfunctions

When using output parameters the internal odbc state was not correctly cleaned causing the next call to param_query to misbehave.

Own Id: OTP-9444

XML files have been corrected.

Own Id: OTP-9550 Aux Id: OTP-9541

Improvements and New Features

Add code to handle old ODBC drivers on solaris. Also adds tests with MySQL.

Own Id: OTP-8407

Odbc now supports SQL_WLONGVARCHAR, thanks to Hanfei Shen for the patch.

Own Id: OTP-8493

ODBC 2.10.10

Fixed Bugs and Malfunctions

Better error messages for connection issues.

Own Id: OTP-9111

ODBC 2.10.9

Improvements and New Features

Ipv6 is now supported on Windows as well as on UNIX for internal socket communication. (ODBC uses sockets instead of the "Erlang port pipes" as some ODBC-drivers are known to mess with stdin/stdout.)

Loopback address constants are used when connecting the c-side to the erlang-side over local socket API avoiding getaddrinfo problems, and the {ip, loopback} option is added as a listen option on the erlang-side. Also cleaned up the TIME_STAMP contribution.

Own Id: OTP-8917

ODBC 2.10.8

Improvements and New Features

ODBC now handles the types SQL_WCHAR and SQL_WVARCHAR. Thanks to Juhani Ränkimies. ODBC also has a new connection option to return all strings as binaries and also expect strings to be binaries in the param_query function. These changes provides some unicode support.

Own Id: OTP-7452

Now supports SQL_TYPE_TIMESTAMP on the format {{YY, MM, DD}, {HH, MM, SS}}. Thanks to Juhani Ränkimies.

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-8511

ODBC 2.10.7

Fixed Bugs and Malfunctions

The odbc application can now be compiled on FreeBSD. (Thanks to Kenji Rikitake.)

Own Id: OTP-8444

Improvements and New Features

Cross compilation improvements and other build system improvements.

Most notable:

Lots of cross compilation improvements. The old cross compilation support was more or less non-existing as well as broken. Please, note that the cross compilation support should still be considered as experimental. Also note that old cross compilation configurations cannot be used without modifications. For more information on cross compiling Erlang/OTP see the $ERL_TOP/INSTALL-CROSS.md file.

Support for staged install using DESTDIR. The old broken INSTALL_PREFIX has also been fixed. For more information see the $ERL_TOP/INSTALL.md file.

Documentation of the release target of the top Makefile. For more information see the $ERL_TOP/INSTALL.md file.

make install now by default creates relative symbolic links instead of absolute ones. For more information see the $ERL_TOP/INSTALL.md file.

$ERL_TOP/configure --help=recursive now works and prints help for all applications with configure scripts.

Doing make install, or make release directly after make all no longer triggers miscellaneous rebuilds.

Existing bootstrap system is now used when doing make install, or make release without a preceding make all.

The crypto and ssl applications use the same runtime library path when dynamically linking against libssl.so and libcrypto.so. The runtime library search path has also been extended.

The configure scripts of erl_interface and odbc now search for thread libraries and thread library quirks the same way as ERTS do.

The configure script of the odbc application now also looks for odbc libraries in lib64 and lib/64 directories when building on a 64-bit system.

The config.h.in file in the erl_interface application is now automatically generated in instead of statically updated which reduces the risk of configure tests without any effect.

(Thanks to Henrik Riomar for suggestions and testing)

(Thanks to Winston Smith for the AVR32-Linux cross configuration and testing)

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-8323

The documentation is now possible to build in an open source environment after a number of bugs are fixed and some features are added in the documentation build process.

- The arity calculation is updated.

- The module prefix used in the function names for bif's are removed in the generated links so the links will look like "http://www.erlang.org/doc/man/erlang.html#append_element-2" instead of "http://www.erlang.org/doc/man/erlang.html#erlang:append_element-2".

- Enhanced the menu positioning in the html documentation when a new page is loaded.

- A number of corrections in the generation of man pages (thanks to Sergei Golovan)

- The legal notice is taken from the xml book file so OTP's build process can be used for non OTP applications.

Own Id: OTP-8343

odbc:param_query() now properly indicates if nothing was updated. (Thanks to Paul Oliver.)

Own Id: OTP-8347

Known Bugs and Problems

The ODBC test cases are failing for linux and MacOSX There is problems with setting of options on odbc-connections, and the odbcserver just exits with an exit code.

Own Id: OTP-8407

ODBC 2.10.6

Fixed Bugs and Malfunctions

Applied a patch from Andrew Thompson, which fixes some error cases.

Own Id: OTP-8291

Improvements and New Features

The documentation is now built with open source tools (xsltproc and fop) that exists on most platforms. One visible change is that the frames are removed.

Own Id: OTP-8250

ODBC 2.10.5

Fixed Bugs and Malfunctions

A missing return statement in a non void function has been fixed in odbc. (Thanks to Nico Kruber)

Own Id: OTP-7978

ODBC 2.10.4

Improvements and New Features

param_query now handles the in_or_out parameter correctly.

Own Id: OTP-7720

Changed the internal socket use so that it will become more robust to non-functional ipv6 and fallback on ipv4.

Own Id: OTP-7721

ODBC 2.10.3

Improvements and New Features

Configure update for mac.

Own Id: OTP-7418

Known Bugs and Problems

describe_table/[2,3] on mac gives an empty result

Own Id: OTP-7478

ODBC 2.10.2

Fixed Bugs and Malfunctions

SQLINTEGERs where not retrieved correctly on 64 bit platforms as an SQLINTEGER is defined to be a 32 bit integer and not a true long.

Own Id: OTP-7297

ODBC 2.10.1

Improvements and New Features

Now supports out and input parameters for stored procedures.

Own Id: OTP-7019

ODBC is now prebuilt for SLES10 in the commercial build and parameters to error_logger:error_report/1 has been corrected.

Own Id: OTP-7294

Parametrized queries will now work correctly when using Erlang R12B-2 on Linux (SuSE 10.3), MySQL 5.0.45, myodbc 3.51 and unixODBC 2.2.12. Earlier it could happen that an error was returned even though data was correctly inserted into the database.

Own Id: OTP-7307

Known Bugs and Problems

SQLINTEGERs are not retrieved correctly on 64 bit platforms as an SQLINTEGER seems to be defined to be a 32 bit integer and not a true long.

Own Id: OTP-7297

ODBC 2.10

Improvements and New Features

Enhanced configure to among other things work better when there is a library found but it is not usable e.i. 32 bit library in 64 bit build.

Own Id: OTP-7062

ODBC 2.0.9

Improvements and New Features

The odbc application now has to be explicitly started and stopped e.i. it will not automatically be started as a temporary application as it did before. Although a practical feature when testing things in the shell, it is not desirable that people take advantage of this and not start the odbc application in a correct way in their products. Added functions to the odbc API that calls application:start/stop.

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-6984

Changed Makefile.in so that odbc is not disabled on 64-bits architectures. It was earlier disabled due to that it had never been tested in that environment.

Own Id: OTP-6987

ODBC 2.0.8

Improvements and New Features

Minor Makefile changes.

Own Id: OTP-6689

ODBC 2.0.7

Fixed Bugs and Malfunctions

When using a parameterized query on a windows platform the data was inserted in the table on the sql-server but the connection was lost, this seems to be due to a compiler error that has now been worked around, but further investigation is ongoing to verify that that really was the problem.

Own Id: OTP-5504

param_query/[3,4] could return an unexpected row count for some drivers, in this case a postgresdriver.

Own Id: OTP-6363

ODBC 2.0.6

Fixed Bugs and Malfunctions

pthread header and library mismatch on linux systems (at least some SuSE and Debian) with both NPTL and Linuxthreads libraries installed.

Own Id: OTP-5981

Improvements and New Features

Changed configure to find odbc in /usr/local too

Own Id: OTP-5966

Known Bugs and Problems

When using a parameterized query on a windows platform the data is inserted in the table on the sql-server but for some reason the connection is lost.

Own Id: OTP-5504

ODBC 2.0.5

Fixed Bugs and Malfunctions

Fixed bug, reported error when deleting nonexisting rows, thanks to Laura M. Castro for reporting this.

Own Id: OTP-5759

Known Bugs and Problems

When using a parameterized query on a windows platform the data is inserted in the table on the sql-server but for some reason the connection is lost.

Own Id: OTP-5504

Odbc 2.0.4

Improvements and New Features

/usr was added as a default place for configure to look for the odbc library on unix/linux platforms.

Own Id: OTP-5501

A legacy timer in the c port program was set to infinity. All timeout handling is handled by the erlang code and a extra timeout in the c code will just lead to confusion if it is released.

Own Id: OTP-5502

Known Bugs and Problems

When using a parameterized query on a windows platform the data is inserted in the table on the sql-server but for some reason the connection is lost.

Own Id: OTP-5504

Odbc 2.0.3

Improvements and New Features

odbc now uses configure as all "normal" applications instead of providing special Makefiles for each commercial supported platform. This also makes it easier to build odbc on non supported platforms.

Own Id: OTP-5437

odbc 2.0.2

Fixed Bugs and Malfunctions

When issuing a batch of queries and one of the queries fail the odbc port program crashed. This is no longer the case.

Own Id: OTP-5176

odbc 2.0.1

Improvements and New Features

Added use of the socket option TCP_NODELAY, as in the case of Erlang odbc the Nagel algorithm will never help, but always cause an unnecessary delay.

Own Id: OTP-5100

odbc 2.0

Improvements and New Features

Erlang ODBC now handles batches of queries and can return multiple result sets.

Own Id: OTP-4642 Aux Id: seq7766

The old interface that became deprecated in odbc 1.0 has now been removed.

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-4794

The port program now sends different exit codes to Erlang when it exits due to failure. This instead of sending the same exit code and then trying to write to stderr. Erlang encodes the exit code to a descriptive atom.

Own Id: OTP-4813

Erlang ODBC now supports parameterized queries for the most common ODBC data types.

Own Id: OTP-4821

SQL_NUMERIC and SQL_DECIMAL columns are converted to integer and float values if possible.

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-4826

Result sets are now by default returned as a list of tuples which is the most intuitive and useful mapping. To keep some degree of backwards compatibility you may turn this off to get the old behavior that result sets are returned as lists of lists. However do not use this in new code as it is considered a deprecated feature that eventually will disappear.

* POTENTIAL INCOMPATIBILITY *

Own Id: OTP-4850

The odbc implementation now mostly uses sockets to communicate between the c and the erlang process, this is to avoid a lot of problems arising from different odbc-drivers doing strange things that disturbed the port-program communication mechanism.

Own Id: OTP-4875

Overview

Purpose

The purpose of the Erlang ODBC application is to provide the programmer with an ODBC interface that has a Erlang/OTP touch and feel. So that the programmer may concentrate on solving his/her actual problem instead of struggling with pointers and memory allocation which is not very relevant for Erlang. This user guide will give you some information about technical issues and provide some examples of how to use the Erlang ODBC interface.

About ODBC

Open Database Connectivity (ODBC) is a Microsoft standard for accessing relational databases that has become widely used. The ODBC standard provides a c-level application programming interface (API) for database access. It uses Structured Query Language (SQL) as its database access language.

About the Erlang ODBC 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. The Erlang ODBC application is designed using the version 3.0 of the ODBC-standard, however using the option {scrollable_cursors, off}for a connection has been known to make it work for at least some 2.X drivers.

Examples

Setting things up

As the Erlang ODBC application is dependent on third party products there are a few administrative things that needs to be done before you can get things up and running.

The first thing you need to do, is to make sure you have an ODBC driver installed for the database that you want to access. Both the client machine where you plan to run your erlang node and the server machine running the database needs the the ODBC driver. (In some cases the client and the server may be the same machine).

Secondly you might need to set environment variables and paths to appropriate values. This may differ a lot between different os's, databases and ODBC drivers. This is a configuration problem related to the third party product and hence we cannot give you a standard solution in this guide.

The Erlang ODBC application consists of both Erlang and C code. The C code is delivered as a precompiled executable for windows, solaris and linux (SLES10) in the commercial build. In the open source distribution it is built the same way as all other application using configure and make. You may want to provide the the path to your ODBC libraries using --with-odbc=PATH.

Note

The Erlang ODBC application should run on all Unix dialects including Linux, Windows 2000, Windows XP and NT. But currently it is only tested for Solaris, Windows 2000, Windows XP and NT.

Using the Erlang API

The following dialog within the Erlang shell illustrates the functionality of the Erlang ODBC interface. The table used in the example does not have any relevance to anything that exist in reality, it is just a simple example. The example was created using sqlserver 7.0 with servicepack 1 as database and the ODBC driver for sqlserver with version 2000.80.194.00.

 1 > odbc:start().
      ok

Connect to the database

 2 > {ok, Ref} = odbc:connect("DSN=sql-server;UID=aladdin;PWD=sesame", []).
      {ok,<0.342.0>}

Create a table

 3 > odbc:sql_query(Ref, "CREATE TABLE EMPLOYEE (NR integer,
      FIRSTNAME  char varying(20), LASTNAME  char varying(20), GENDER char(1),
      PRIMARY KEY(NR))").
      {updated,undefined}

Insert some data

 4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
      {updated,1}

Check what data types the database assigned for the columns. Hopefully this is not a surprise, some times it can be! These are the data types that you should use if you want to do a parameterized query.

 5 > odbc:describe_table(Ref, "EMPLOYEE").
      {ok, [{"NR", sql_integer},
            {"FIRSTNAME", {sql_varchar, 20}},
            {"LASTNAME", {sql_varchar, 20}}
            {"GENDER", {sql_char, 1}}]}

Use a parameterized query to insert many rows in one go.

 6 > odbc:param_query(Ref,"INSERT INTO EMPLOYEE (NR, FIRSTNAME, "
                  "LASTNAME, GENDER) VALUES(?, ?, ?, ?)",
                   [{sql_integer,[2,3,4,5,6,7,8]},
                    {{sql_varchar, 20},
                             ["John", "Monica", "Ross", "Rachel",
                             "Piper", "Prue", "Louise"]},
                   {{sql_varchar, 20},
                             ["Doe","Geller","Geller", "Green",
                              "Halliwell", "Halliwell", "Lane"]},
                   {{sql_char, 1}, ["M","F","M","F","F","F","F"]}]).
      {updated, 7}

Fetch all data in the table employee

 7> odbc:sql_query(Ref, "SELECT * FROM EMPLOYEE").
    {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],
          [{1,"Jane","Doe","F"},
           {2,"John","Doe","M"},
           {3,"Monica","Geller","F"},
           {4,"Ross","Geller","M"},
           {5,"Rachel","Green","F"},
           {6,"Piper","Halliwell","F"},
           {7,"Prue","Halliwell","F"},
           {8,"Louise","Lane","F"}]]}

Associate a result set containing the whole table EMPLOYEE to the connection. The number of rows in the result set is returned.

 8 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE").
      {ok,8}

You can always traverse the result set sequential by using next

 9 > odbc:next(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}

 10 > odbc:next(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}

If your driver supports scrollable cursors you have a little more freedom, and can do things like this.

 11 > odbc:last(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{8,"Louise","Lane","F"}]}

 12 > odbc:prev(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{7,"Prue","Halliwell","F"}]}

 13 > odbc:first(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}

 14 > odbc:next(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}

Fetch the fields FIRSTNAMEand NRfor all female employees

 15 > odbc:sql_query(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
     {selected,["FIRSTNAME","NR"],
          [{"Jane",1},
           {"Monica",3},
           {"Rachel",5},
           {"Piper",6},
           {"Prue",7},
           {"Louise",8}]}

Fetch the fields FIRSTNAMEand NRfor all female employees and sort them on the field FIRSTNAME.

 16 > odbc:sql_query(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'
      ORDER BY FIRSTNAME").
    {selected,["FIRSTNAME","NR"],
          [{"Jane",1},
           {"Louise",8},
           {"Monica",3},
           {"Piper",6},
           {"Prue",7},
           {"Rachel",5}]}

Associate a result set that contains the fields FIRSTNAME and NRfor all female employees to the connection. The number of rows in the result set is returned.

 17 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
      {ok,6}

A few more ways of retrieving parts of the result set when the driver supports scrollable cursors. Note that next will work even without support for scrollable cursors.

 18 > odbc:select(Ref, {relative, 2}, 3).
    {selected,["FIRSTNAME","NR"],[{"Monica",3},{"Rachel",5},{"Piper",6}]}

 19 > odbc:select(Ref, next, 2).
      {selected,["FIRSTNAME","NR"],[{"Prue",7},{"Louise",8}]}

 20 > odbc:select(Ref, {absolute, 1}, 2).
      {selected,["FIRSTNAME","NR"],[{"Jane",1},{"Monica",3}]}

 21 > odbc:select(Ref, next, 2).
    {selected,["FIRSTNAME","NR"],[{"Rachel",5},{"Piper",6}]}

 22 > odbc:select(Ref, {absolute, 1}, 4).
      {selected,["FIRSTNAME","NR"],
                [{"Jane",1},{"Monica",3},{"Rachel",5},{"Piper",6}]}

Select, using a parameterized query.

 23 > odbc:param_query(Ref, "SELECT * FROM EMPLOYEE WHERE GENDER=?",
      [{{sql_char, 1}, ["M"]}]).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],
                [{2,"John", "Doe", "M"},{4,"Ross","Geller","M"}]}

Delete the table EMPLOYEE.

 24 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE").
      {updated,undefined}

Shut down the connection.

 25 > odbc:disconnect(Ref).
      ok

Shut down the application.

 26 > odbc:stop().
    =INFO REPORT==== 7-Jan-2004::17:00:59 ===
    application: odbc
    exited: stopped
    type: temporary

    ok

Databases

Databases

If you need to access a relational database such as sqlserver, mysql, postgres, oracle, cybase etc. from your erlang application using the Erlang ODBC interface is a good 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 regularly tested for sqlserver and postgres.

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 column with the ANSI data type integer, but when retrieving values from this column the driver reports that it is of type SQL_DECIMAL(0, 38) and not SQL_INTEGER as you may have expected.

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 cannot 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.

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 inputting data using sql_query/[2,3] 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')").

You may also input data using param_query/3,4 and then the input data will have the Erlang type corresponding to the ODBC type of the column.See ODBC to Erlang mapping

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(size) | String | Binary (configurable)
SQL_WCHAR(size) | Unicode binary encoded as UTF16 little endian.
SQL_NUMERIC(p,s) when (p >= 0 and p <= 9 and s == 0) | Integer
SQL_NUMERIC(p,s) when (p >= 10 and p <= 15 and s == 0) or (s <= 15 and s > 0) | Float
SQL_NUMERIC(p,s) when p >= 16 | String
SQL_DECIMAL(p,s) when (p >= 0 and p <= 9 and s == 0) | Integer
SQL_DECIMAL(p,s) when (p >= 10 and p <= 15 and s == 0) or (s <= 15 and s > 0) | Float
SQL_DECIMAL(p,s) when p >= 16 | String
SQL_INTEGER | Integer
SQL_SMALLINT | Integer
SQL_FLOAT | Float
SQL_REAL | Float
SQL_DOUBLE | Float
SQL_VARCHAR(size) | String | Binary (configurable)
SQL_WVARCHAR(size) | Unicode binary encoded as UTF16 little endian.

Table: 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 | {{YY, MM, DD}, {HH, MM, SS}}
SQL_LONGVARCHAR | String | Binary (configurable)
SQL_WLONGVARCHAR(size) | Unicode binary encoded as UTF16 little endian.
SQL_BINARY | String | Binary (configurable)
SQL_VARBINARY | String | Binary (configurable)
SQL_LONGVARBINARY | String | Binary (configurable)
SQL_TINYINT | Integer
SQL_BIT | Boolean

Table: Mapping of extended ODBC data types to the Erlang data types returned to the Erlang application.

Note

To find out which data types will be returned for the columns in a table use the function describe_table/2,3

Batch handling

Grouping of SQL queries can be desirable in order to reduce network traffic. Another benefit can be that the data source sometimes can optimize execution of a batch of SQL queries.

Explicit batches an procedures described below will result in multiple results being returned from sql_query/[2,3]. while with parameterized queries only one result will be returned from param_query/[2,3].

Explicit batches

The most basic form of a batch is created by semicolons separated SQL queries, for example:

"SELECT * FROM FOO; SELECT * FROM BAR" or
"INSERT INTO FOO VALUES(1,'bar'); SELECT * FROM FOO"

Procedures

Different databases may also support creating of procedures that contains more than one SQL query. For example, the following SQLServer-specific statement creates a procedure that returns a result set containing information about employees that work at the department and a result set listing the customers of that department.

 CREATE PROCEDURE DepartmentInfo (@DepartmentID INT) AS
 SELECT * FROM Employee WHERE department = @DepartmentID
 SELECT * FROM Customers WHERE department = @DepartmentID

Parameterized queries

To effectively perform a batch of similar queries, you can use parameterized queries. This means that you in your SQL query string will mark the places that usually would contain values with question marks and then provide lists of values for each parameter. For instance you can use this to insert multiple rows into the EMPLOYEE table while executing only a single SQL statement, for example code see "Using the Erlang API" section in the "Getting Started" chapter.

Error handling

Strategy

On a conceptual level starting a database connection using the Erlang ODBC API is a basic client server application. The client process uses the API to start and communicate with the server process that manages the connection. The strategy of the Erlang ODBC application is that programming faults in the application itself will cause the connection process to terminate abnormally.(When a process terminates abnormally its supervisor will log relevant error reports.) Calls to API functions during or after termination of the connection process, will return {error, connection_closed}. Contextual errors on the other hand will not terminate the connection it will only return {error, Reason}to the client, where Reason may be any erlang term.

Clients

The connection is associated with the process that created it and can only be accessed through it. The reason for this is to preserve the semantics of result sets and transactions when select_count/[2,3] is called or auto_commit is turned off. Attempts to use the connection from another process will fail. This will not effect the connection. On the other hand, if the client process dies the connection will be terminated.

Timeouts

All request made by the client to the connection are synchronous. If the timeout is used and expires the client process will exit with reason timeout. Probably the right thing to do is let the client die and perhaps be restarted by its supervisor. But if the client chooses to catch this timeout, it is a good idea to wait a little while before trying again. If there are too many consecutive timeouts that are caught the connection process will conclude that there is something radically wrong and terminate the connection.

Guards

All API-functions are guarded and if you pass an argument of the wrong type a runtime error will occur. All input parameters to internal functions are trusted to be correct. It is a good programming practise to only distrust input from truly external sources. You are not supposed to catch these errors, it will only make the code very messy and much more complex, which introduces more bugs and in the worst case also covers up the actual faults. Put your effort on testing instead, you should trust your own input.

The whole picture

As the Erlang ODBC application relies on third party products and communicates with a database that probably runs on another computer in the network there are plenty of things that might go wrong. To fully understand the things that might happen it facilitate to know the design of the Erlang ODBC application, hence here follows a short description of the current design.

Note

Please note that design is something, that not necessarily will, but might change in future releases. While the semantics of the API will not change as it is independent of the implementation.

When you do application:start(odbc) the only thing that happens is that a supervisor process is started. For each call to the API function connect/2 a process is spawned and added as a child to the Erlang ODBC supervisor. The supervisors only tasks are to provide error-log reports, if a child process should die abnormally, and the possibility to do a code change. Only the client process has the knowledge to decide if this connection managing process should be restarted.

The erlang connection process spawned by connect/2, will open a port to a c-process that handles the communication with the database through Microsoft's ODBC API. The erlang port will be kept open for exit signal propagation, if something goes wrong in the c-process and it exits we want know as mush as possible about the reason. The main communication with the c-process is done through sockets. The C-process consists of two threads, the supervisor thread and the database handler thread. The supervisor thread checks for shutdown messages on the supervisor socket and the database handler thread receives requests and sends answers on the database socket. If the database thread seems to hang on some database call, the erlang control process will send a shutdown message on the supervisor socket, in this case the c-process will exit. If the c-process crashes/exits it will bring the erlang-process down too and vice versa i.e. the connection is terminated.

Error types

The types of errors that may occur can be divide into the following categories.

Configuration problems - Everything from that the database was not set up right to that the c-program that should be run through the erlang port was not compiled for your platform.

Errors discovered by the ODBC driver - If calls to the ODBC-driver fails due to circumstances that cannot be controlled by the Erlang ODBC application programmer, an error string will be dug up from the driver. This string will be the Reason in the {error, Reason}return value. How good this error message is will of course be driver dependent. Examples of such circumstances are trying to insert the same key twice, invalid SQL-queries and that the database has gone off line.

Connection termination - If a connection is terminated in an abnormal way, or if you try to use a connection that you have already terminated in a normal way by calling disconnect/1, the return value will be{error, connection_closed}. A connection could end abnormally because of an programming error in the Erlang ODBC application, but also if the ODBC driver crashes.

Contextual errors - If API functions are used in the wrong context, the Reason in the error tuple will be a descriptive atom. For instance if you try to call the function last/[1,2]without first calling select_count/[2,3]to associate a result set with the connection. If the ODBC-driver does not support some functions, or if you disabled some functionality for a connection and then try to use it.

odbc

Erlang ODBC application

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}

Error Handling

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

References

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

Summary

Types: Types used in ODBC application

col_name()

Name of column in the result set.

common_reason()

An explanation of what went wrong. For common errors there will be atom decriptions.

connection_reference()

Opaque reference to an ODBC connection as returnded by connect/2.

extended_error()

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.

n_rows()

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()

Data type used by ODBC, to learn which Erlang data type corresponds to an ODBC data type see the Erlang to ODBC data type mapping in the User's Guide.

row()

A tuple, with the number of elements selected from columns in a database row, containg the values of the columns such as {value(), value() ... value()}.

selected()

Return value for queries that select data from database tabels.

updated()

Return value for queries that update database tables.

value()

Erlang data type that corresponds to the ODBC data type being handled.

Functions

commit(ConnectionReference, CommitMode)

Equivalent to commit/3.

commit(ConnectionReference, CommitMode, TimeOut)

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

connect(ConnectionStr, Options)

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.

describe_table(ConnectionReference, Table)

Equivalent to describe_table/3.

describe_table(ConnectionReference, Table, TimeOut)

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

disconnect(ConnectionReferense)

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 cannot 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.

first(ConnectionReference)

Equivalent to first/2.

first(ConnectionReference, TimeOut)

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

last(ConnectionReference)

Equivalent to last/2.

last(ConnectionReference, TimeOut)

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

next(ConnectionReference)

Equivalent to next/2.

next(ConnectionReference, TimeOut)

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(ConnectionReference, SQLQuery, Params)

Equivalent to param_query/4.

param_query(ConnectionReference, SQLQuery, Params, TimeOut)

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

prev(ConnectionReference)

Equivalent to prev/2.

prev(ConnectionReference, TimeOut)

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

select(ConnectionReference, Position, N)

Equivalent to select/4.

select(ConnectionReference, Position, N, TimeOut)

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.

select_count(ConnectionReference, SQLQuery)

Equivalent to select_count/3.

select_count(ConnectionReference, SQLQuery, TimeOut)

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.

sql_query(ConnectionReference, SQLQuery)

Equivalent to sql_query/3.

sql_query(ConnectionReference, SQLQuery, TimeOut)

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.

start()

Equivalent to start/1.

start(Type)

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

stop()

Stops the odbc application. See application(3)

Types: Types used in ODBC application

col_name()

-type col_name() :: string().

Name of column in the result set.

common_reason()

-type common_reason() :: connection_closed | extended_error() | term().

An explanation of what went wrong. For common errors there will be atom decriptions.

connection_reference()

-opaque connection_reference()

Opaque reference to an ODBC connection as returnded by connect/2.

extended_error()

-type extended_error() :: {string(), integer(), term()}.

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.

n_rows()

-type 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()

-type odbc_data_type() ::
          sql_integer | sql_smallint | sql_tinyint |
          {sql_decimal, Precision :: integer(), Scale :: integer()} |
          {sql_numeric, Precision :: integer(), Scale :: integer()} |
          {sql_char, Size :: integer()} |
          {sql_wchar, Size :: integer()} |
          {sql_varchar, Size :: integer()} |
          {sql_wvarchar, Size :: integer()} |
          {sql_float, Precision :: integer()} |
          {sql_wlongvarchar, Size :: integer()} |
          {sql_float, Precision :: integer()} |
          sql_real | sql_double | sql_bit |
          atom().

Data type used by ODBC, to learn which Erlang data type corresponds to an ODBC data type see the Erlang to ODBC data type mapping in the User's Guide.

row()

-type row() :: tuple() | list().

A tuple, with the number of elements selected from columns in a database row, containg the values of the columns such as {value(), value() ... value()}.

When the tuple_row option is set to off this is a list containing the values of the columns in a database row such as [value(), value() ... value()].

Please see connect/2.

selected()

-type selected() :: {selected, [col_name()], [row()]}.

Return value for queries that select data from database tabels.

updated()

-type updated() :: {updated, n_rows()}.

Return value for queries that update database tables.

value()

(not exported)

-type value() :: null | term().

Erlang data type that corresponds to the ODBC data type being handled.

Functions

commit(ConnectionReference, CommitMode)

-spec commit(ConnectionReference, CommitMode) -> ok | {error, Reason}
                when
                    ConnectionReference :: connection_reference(),
                    CommitMode :: commit | rollback,
                    Reason ::
                        not_an_explicit_commit_connection | process_not_owner_of_odbc_connection |
                        common_reason().

Equivalent to commit/3.

commit(ConnectionReference, CommitMode, TimeOut)

-spec commit(ConnectionReference, CommitMode, TimeOut) -> ok | {error, Reason}
                when
                    ConnectionReference :: connection_reference(),
                    CommitMode :: commit | rollback,
                    TimeOut :: erlang:timeout(),
                    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(ConnectionStr, Options)

-spec connect(ConnectionStr, Options) -> {ok, ConnectionReferense} | {error, Reason}
                 when
                     ConnectionStr :: string(),
                     Options ::
                         [{auto_commit, on | off} |
                          {timeout, timeout()} |
                          {binary_strings, on | off} |
                          {tuple_row, on | off} |
                          {scrollable_cursors, on | off} |
                          {trace_driver, on | off} |
                          {extended_errors, on | off}],
                     ConnectionReferense :: connection_reference(),
                     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 inputted 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 otherwise 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 return {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 numeric 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.

Note

The current implementation spawns a port program written in C that utilizes the actual ODBC driver. There is a default timeout of 5000 msec for this port program to connect to the Erlang ODBC application. This timeout can be changed by setting an application specific environment variable 'port_timeout' with the number of milliseconds for the ODBC application. E.g.: [{odbc, [{port_timeout, 60000}]}] to set it to 60 seconds.

describe_table(ConnectionReference, Table)

-spec describe_table(ConnectionReference, Table) -> {ok, Description} | {error, Reason}
                        when
                            ConnectionReference :: connection_reference(),
                            Table :: string(),
                            Description :: [{col_name(), odbc_data_type()}],
                            Reason :: process_not_owner_of_odbc_connection | common_reason().

Equivalent to describe_table/3.

describe_table(ConnectionReference, Table, TimeOut)

-spec describe_table(ConnectionReference, Table, TimeOut) -> {ok, Description} | {error, Reason}
                        when
                            ConnectionReference :: connection_reference(),
                            Table :: string(),
                            TimeOut :: erlang:timeout(),
                            Description :: [{col_name(), odbc_data_type()}],
                            Reason :: process_not_owner_of_odbc_connection | common_reason().

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

disconnect(ConnectionReferense)

-spec disconnect(ConnectionReferense) -> ok | {error, Reason}
                    when
                        ConnectionReferense :: 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 cannot 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.

first(ConnectionReference)

-spec first(ConnectionReference) -> Result | {error, Reason}
               when
                   ConnectionReference :: connection_reference(),
                   Result :: selected(),
                   Reason ::
                       result_set_does_not_exist | driver_does_not_support_function |
                       scrollable_cursors_disabled | process_not_owner_of_odbc_connection |
                       common_reason().

Equivalent to first/2.

first(ConnectionReference, TimeOut)

-spec first(ConnectionReference, TimeOut) -> Result | {error, Reason}
               when
                   ConnectionReference :: connection_reference(),
                   TimeOut :: erlang:timeout(),
                   Result :: selected(),
                   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(ConnectionReference)

-spec last(ConnectionReference) -> Result | {error, Reason}
              when
                  ConnectionReference :: connection_reference(),
                  Result :: selected(),
                  Reason ::
                      result_set_does_not_exist | driver_does_not_support_function |
                      scrollable_cursors_disabled | process_not_owner_of_odbc_connection |
                      common_reason().

Equivalent to last/2.

last(ConnectionReference, TimeOut)

-spec last(ConnectionReference, TimeOut) -> Result | {error, Reason}
              when
                  ConnectionReference :: connection_reference(),
                  TimeOut :: erlang:timeout(),
                  Result :: selected(),
                  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(ConnectionReference)

-spec next(ConnectionReference) -> Result | {error, Reason}
              when
                  ConnectionReference :: connection_reference(),
                  Result :: selected(),
                  Reason ::
                      result_set_does_not_exist | driver_does_not_support_function |
                      scrollable_cursors_disabled | process_not_owner_of_odbc_connection |
                      common_reason().

Equivalent to next/2.

next(ConnectionReference, TimeOut)

-spec next(ConnectionReference, TimeOut) -> Result | {error, Reason}
              when
                  ConnectionReference :: connection_reference(),
                  TimeOut :: erlang:timeout(),
                  Result :: selected(),
                  Reason ::
                      result_set_does_not_exist | driver_does_not_support_function |
                      scrollable_cursors_disabled | 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(ConnectionReference, SQLQuery, Params)

-spec param_query(ConnectionReference, SQLQuery, Params) -> Result | {error, Reason}
                     when
                         ConnectionReference :: connection_reference(),
                         SQLQuery :: string(),
                         Params ::
                             [{odbc_data_type(), [value()]}] |
                             [{odbc_data_type(), in | out | inout, [value()]}],
                         Result :: selected() | updated(),
                         Reason ::
                             driver_does_not_support_function | process_not_owner_of_odbc_connection |
                             common_reason().

Equivalent to param_query/4.

param_query(ConnectionReference, SQLQuery, Params, TimeOut)

-spec param_query(ConnectionReference, SQLQuery, Params, TimeOut) -> Result | {error, Reason}
                     when
                         ConnectionReference :: connection_reference(),
                         SQLQuery :: string(),
                         Params ::
                             [{odbc_data_type(), [value()]}] |
                             [{odbc_data_type(), in | out | inout, [value()]}],
                         TimeOut :: erlang:timeout(),
                         Result :: selected() | updated(),
                         Reason ::
                             driver_does_not_support_function | process_not_owner_of_odbc_connection |
                             common_reason().

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. To learn which Erlang data type corresponds to an ODBC data type see the Erlang to ODBC data type mapping in the User's Guide.

prev(ConnectionReference)

-spec prev(ConnectionReference) -> Result | {error, Reason}
              when
                  ConnectionReference :: connection_reference(),
                  Result :: selected(),
                  Reason ::
                      result_set_does_not_exist | driver_does_not_support_function |
                      scrollable_cursors_disabled | process_not_owner_of_odbc_connection |
                      common_reason().

Equivalent to prev/2.

prev(ConnectionReference, TimeOut)

-spec prev(ConnectionReference, TimeOut) -> Result | {error, Reason}
              when
                  ConnectionReference :: connection_reference(),
                  TimeOut :: erlang:timeout(),
                  Result :: selected(),
                  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.

select(ConnectionReference, Position, N)

-spec select(ConnectionReference, Position, N) -> Result | {error, Reason}
                when
                    ConnectionReference :: connection_reference(),
                    Position :: next | {relative, integer()} | {absolute, integer()},
                    N :: integer(),
                    Result :: selected(),
                    Reason ::
                        result_set_does_not_exist | driver_does_not_support_function |
                        scrollable_cursors_disabled | process_not_owner_of_odbc_connection |
                        common_reason().

Equivalent to select/4.

select(ConnectionReference, Position, N, TimeOut)

-spec select(ConnectionReference, Position, N, TimeOut) -> Result | {error, Reason}
                when
                    ConnectionReference :: connection_reference(),
                    Position :: next | {relative, integer()} | {absolute, integer()},
                    N :: integer(),
                    TimeOut :: erlang:timeout(),
                    Result :: selected(),
                    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.

select_count(ConnectionReference, SQLQuery)

-spec select_count(ConnectionReference, SQLQuery) -> {ok, NrRows} | {error, Reason}
                      when
                          ConnectionReference :: connection_reference(),
                          SQLQuery :: string(),
                          NrRows :: n_rows(),
                          Reason :: process_not_owner_of_odbc_connection | common_reason().

Equivalent to select_count/3.

select_count(ConnectionReference, SQLQuery, TimeOut)

-spec select_count(ConnectionReference, SQLQuery, TimeOut) -> {ok, NrRows} | {error, Reason}
                      when
                          ConnectionReference :: connection_reference(),
                          SQLQuery :: string(),
                          TimeOut :: erlang:timeout(),
                          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.

sql_query(ConnectionReference, SQLQuery)

-spec sql_query(ConnectionReference, SQLQuery) -> Result | {error, Reason}
                   when
                       ConnectionReference :: connection_reference(),
                       SQLQuery :: string(),
                       Result :: updated() | selected(),
                       Reason :: process_not_owner_of_odbc_connection | common_reason().

Equivalent to sql_query/3.

sql_query(ConnectionReference, SQLQuery, TimeOut)

-spec sql_query(ConnectionReference, SQLQuery, TimeOut) -> Result | {error, Reason}
                   when
                       ConnectionReference :: connection_reference(),
                       SQLQuery :: string(),
                       TimeOut :: erlang:timeout(),
                       Result :: updated() | selected(),
                       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.

start()

-spec start() -> ok | {error, Reason} when Reason :: term().

Equivalent to start/1.

start(Type)

-spec start(Type) -> ok | {error, Reason}
               when Type :: permanent | transient | temporary, Reason :: term().

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

stop()

-spec stop() -> ok.

Stops the odbc application. See application(3)