Requests a connection, or session, with a data source.Open Database Connectivity (ODBC)
Open Database Connectivity (ODBC) is an interface for accessing data in a heterogeneous environment of relational and non-relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor-neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases.
ODBC Architecture
The ODBC architecture has four basic components, Application, Driver Manager, Driver and Data Source.
Application
Performs processing and calls ODBC functions to submit SQL statements and retrieve results. An application using the ODBC interface performs the following tasks:·
Requests a connection, or session, with a data source.
Sends SQL requests to the data source.
Requests results.
Processes errors.
Requests commit or rollback operations for transaction control.
Terminates the connection to the data source.
Driver Manager
Loads drivers on behalf of an application. The primary purpose of the Driver Manager is to load drivers. The Driver Manager also performs the following:
Map a data source name to a specific driver dynamic-link library (DLL).
Processes several ODBC initialization calls.
Provides entry points to ODBC functions for each driver.
Provides parameter validation and sequence validation for ODBC calls.
Driver
Processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application. If necessary, the driver modifies an application’s request so that the request conforms to syntax supported by the associated DBMS.
A driver performs the following tasks in response to ODBC function calls from an application:
Establishes a connection to a data source.
Submits requests to the data source.
Returns results to the application.
Declares and manipulates cursors if necessary.
The Driver Manager and driver appear to an application as one unit that processes ODBC function calls.
Data source
Consists of the data the user wants to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS. A data source is a specific instance of a combination of a DBMS product and any remote operating system and network necessary to access it.
How do these components interact ?
The ODBC application uses a language or vocabulary of commands (which is referred to as "ODBC") to request data from, or to send data to, the back-end or server DBMS (Data Source). However, the DBMS doesn't understand the ODBC client request until the command passes through the ODBC Driver for that specific DBMS. This ODBC driver is software that resides on the front-end. The ODBC driver translates the command into a format that the ODBC Server can understand. The ODBC Server sends the answer back to the ODBC Driver, which translates the answer into a format that the ODBC Client can understand.
The following diagram shows the relationship between the four components.
What are the main advantages of ODBC
Application developers don't need to modify their applications to allow them to access data from several back-ends. As long as there is an ODBC Driver for a particular back-end, an ODBC-enabled front-end can access it. ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces, because it provides a universal data access interface. One ODBC Driver for a particular DBMS allows any ODBC-enabled application to be an ODBC client. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases.
EAGLE ODBC : Basic Application Steps
The ODBC implementation within Eagle is based on the DB command, to allow a straightforward approach for all those existing application which already use the DB command. A specific Database Monitor is no longer required, because all functions are performed by the database driver and Eagle.
To interact with a data source, an Eagle application must perform the following :
Initialize a conversation with the Driver Manager.
This is achieved using the command :
DB INITIATE
Connect to the data source, specifying the data source name and the database name.
This is achieved using the command :
DB DATABASE <database_name> "DATA_SOURCE_NAME"
It is possible to use this option without parameters (i.e. "DB DATABASE<cr>"), and in this case the driver Manager displays a dialogue box which allows to browse for the required Data Source and database.
Process one or more SQL statements.
This is achieved using the command :
DB <SQL_PHRASE>
Specify the separator character which must be used between single fields of the result set.
This is achieved using the command :
DB SEPARATOR ","
Perform a commit transaction.
This is achieved using the command :
DB COMMIT
Perform a rollback transaction.
This is achieved using the command :
DB ROLLBACK
If the statement returns a result set, retrieve each row of the result. This is achieved using through the command :
READ DB,<list_of_variables>
Check the end of the result set.
This is achieved using the command :
EOF DB : <command>
Terminates the connection to the Data Source.
This is done through the command :
DB EXIT
Terminate the conversation with the Driver Manager.
This is achieved using the command :
DB TERMINATE
Examples of Use
Here follow some simple examples of how Eagle can interact with Microft Excel and Microsoft Access.
1) Eagle connects to MS Excel and reads data from a spreadsheet.
STRING BUFFER # Load the Driver Manager DB INITIATE # Connects to an MS Excel database DB DATABASE d:\samples\sample.xls "Excel Files" # Define the Separator character between fields DB SEPARATOR ","
# Read all rows from SHEET1 table DB SELECT * FROM "sheet1$" LABEL 1 READ DB,(l) BUFFER EOF DB : goto 5 TELL 'The value is ^BUFFER' GOTO 1 LABEL 5 # Disconnects from the MS Excel database DB EXIT # Unload the Driver Manager DB TERMINATE
2) Eagle connects to MS Excel and adds a new table (SAMPLE) with three rows in it. This example assumes that the Driver Manager has already been loaded.
# Connects to an MS Excel database DB DATABASE d:\samples\sample.xls "Excel Files" # Create a new table "sample" DB CREATE TABLE sample \ DB (CODE char(10),ADDRESS char(40),VALUE integer) DB INSERT INTO "sample$" (CODE,ADDRESS,VALUE) \ DB VALUES ( '001', 'First Address', 10 ) DB INSERT INTO "sample$" (CODE,ADDRESS,VALUE) \ DB VALUES ( '002', 'Second Address', 510 ) DB INSERT INTO "sample$" (CODE,ADDRESS,VALUE) \ VALUES ( '003', 'Third Address', 125 ) # Disconnects from the MS Excel database DB EXIT
3) Eagle connects to MS Excel and updates rows in the SAMPLE table under special conditions. This example assumes that the Driver Manager has already been loaded.
# Connects to an MS Excel database DB DATABASE d:\samples\sample.xls "Excel Files" # Updates the new table DB UPDATE "sample$" SET VALUE = (VALUE + 10) \ DB WHERE VALUE <200 # Disconnects from the MS Excel database DB EXIT
4) Eagle connects to MS Access and reads data from the existing EMPLOYEES table. This example assumes that the Driver Manager has already been loaded.
STRING BUFFER # Connects to an MS Access database DB DATABASE d:\samples\Northwind.mdb "MS Access 7.0 Database" # Read all rows from EMPLOYEES table DB SELECT * FROM "employees" LABEL 1 READ DB,(l) BUFFER EOF DB : goto 5 TELL 'The value is ^ BUFFER ' GOTO 1 LABEL 5 # Retrieve a single column in a specific row DB SELECT Title from "employees" WHERE EmployeeID = 6 READ DB,(l) BUFFER EOF DB : tell 'Invalid Employee ID' ELSE tell 'EMPLOYEE ID=6 has the following TITLE : ^BUFFER' # Disconnects from the MS Access database DB EXIT
5) Eagle connects to MS Access and updates rows in the table EMPLOYEES table under special conditions. This example assumes that the Driver Manager has already been loaded.
# Connects to an MS Access database DB DATABASE d:\samples\Northwind.mdb "MS Access 7.0 Database" DB UPDATE "employees" SET Title = 'New Title' WHERE EmployeeID=6 # Disconnects from the MS Access database DB EXIT
How to setup a proper conversation
There are a few parameters that need some tuning before any database transaction is performed. Each of the following paramenters has a corresponding entry in the Eagle Configuration settings (ini) or (reg).
SQL_ACCESS_MODE
This can be defined as SQL_MODE_READ_ONLY or SQL_MODE_READ_WRITE.
The default is SQL_MODE_READ_WRITE.
When the access mode is SQL_MODE_READ_ONLY then the connection is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source.
Example :
SQL_ACCESS_MODE = SQL_MODE_READ_WRITE
SQL_AUTOCOMMIT
This can be defined as SQL_AUTOCOMMIT_ON or SQL_AUTOCOMMIT_OFF.
When SQL_AUTOCOMMIT_OFF, the driver uses manual-commit mode, and the application must explicitly commit or roll back transactions. When SQL_AUTOCOMMIT_ON, the driver uses auto-commit mode. Each statement is committed immediately after it is executed. This is the default.
Example
SQL_AUTOCOMMIT = SQL_AUTOCOMMIT_ON
When a driver is in autocommit mode, the DB COMMIT and DB ROLLBACK are not processed.
SQL_LOGIN_TIMEOUT
A value corresponding to the number of seconds to wait for a login request to complete before returning to the application. The default is driver-dependent and must be nonzero. If the value is 0, the timeout is disabled and a connection attempt will wait indefinitely.
Example
SQL_LOGIN_TIMEOUT = 5
The following uses the driver default
SQL_LOGIN_TIMEOUT =
SQL_TXN_ISOLATION
Transaction isolation level refers to the degree to which multiple interleaved transactions are prevented from interfering with each other in a multi-user database system. Ideally, one would like to have "serialised" transactions - that is, the interleaved execution of any set of concurrent transactions will produce the same effect as some (unspecified) serial execution of those same transactions. The ANSI SQL 2 standard defines three specific ways in which the serialisation of a transaction may be violated (with the implication
that these are the only permitted violations):
1. Dirty Read: Transaction T1 modifies a row. T2 then reads the row. Now T1 performs a rollback - so, T2 has seen a row that never really existed.
2. Non-repeatable Read: T1 retrieves a row; then T2 updates that row and T1 retrieves the "same" row again. T1 has now effectively retrieved the "same" row twice and has seen two different values for it.
3. Phantoms: T1 reads a set of rows that satisfy certain search conditions. T2 then insert one or more rows that satisfy the same search condition. If T1 repeats the read, it will see rows that did not exist previously - "phantoms".
These three phenomena are referred to as P1, P2 and P3, respectively. The various isolation levels are defined by SQL2 in terms of which of these violations of serialisation they permit.
They are:
1. READ_UNCOMMITTED - Permits P1, P2 and P3.
2. READ_COMMITTED - Permits P2 and P3. Does not permit P1.
3. REPEATABLE_READ - Permits P3. Does not permit P1 and P2.
4. SERIALIZABLE - Does not permit any of P1, P2 and P3.
ODBC defines five isolation levels: SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, SQL_TXN_SERIALIZABLE, and SQL_TXN_VERSIONING. The first four correspond to ANSI isolation levels 1-4 respectively. SQL_TXN_VERSIONING provides SERIALIZABLE transactions, but does so without a significant impact on concurrency.
Transaction isolation is achieved by locking protocols. The various tables are or parts thereof are locked so that two writers cannot access it at the same time, or preventing reader access when writing is being done, and so on. One of the side effects of this is to drastically reduce concurrency. Typically, isolation levels 3 and 4 are achieved by locking protocols which drastically reduce concurrency. SQL_TXN_VERSIONING refers a non-locking way of achieving levels 3 and 4, thereby increasing concurrency.
As a result of these differences in implementation, an inter-operable ODBC application must explicitly set the transaction isolation level, instead of using the various implementations defined locking levels.
Example
SQL_TXN_ISOLATION = SQL_TXN_VERSIONING
The user can obtain more detailed information about errors which occur in the conversation with the Data Source through the SQL_ERROR variable from the Eagle configuration file or registry entry. The value of this variable is the combination of two different error codes, separated by the Separator character: the ODBC error code, whose values are listed below, and the Native error code.
Example :
SQL_ERROR = 07001 , 48
The character string value returned for the ODBC error code consists of a two character class value followed by a three character subclass value. The assignment of class and subclass values is defined by ANSI SQL-92.
The Native error code is an integer value which is specific to the Data Source.
Code Description 00000 Success 01000 General warning 01002 Disconnect error 01004 Data truncated 01006 Privilege not revoked 01S00 Invalid connection string attribute 01S01 Error in row 01S02 Option value changed 01S03 No rows updated or deleted 01S04 More than one row updated or deleted 01S05 Cancel treated as 01S06 Attempted fetch before result set returned the first rowset 07001 Wrong number of parameters 07006 Restricted data type attribute violation 07S01 Invalid use of default parameter 08001 Unable to connect to data source 08002 Connection in use 08003 Connection not open 08004 Data source rejected establishment of connection 08007 Connection failure during transaction 08S01 Communication link failure 21S01 Insert value list does not match column list 21S02 Degree of derived table does not match column list 22001 String data right truncation 22002 Indicator variable required but not supplied 22003 Numeric value out of range 22005 Error in assignment 22008 Datetime field overflow 22012 Division by zero 22026 String data, length mismatch 23000 Integrity constraint violation 24000 Invalid cursor state 25000 Invalid transaction state 28000 Invalid authorization specification 34000 Invalid cursor name 37000 Syntax error or access violation 3C000 Duplicate cursor name 40001 Serialization failure 42000 Syntax error or access violation 70100 Operation aborted IM001 Driver does not support this function IM002 Data source name not found and no default driver specified IM003 Specified driver could not be loaded IM004 Driver’s SQLAllocEnv failed IM005 Driver’s SQLAllocConnect failed IM006 Driver’s SQLSetConnect-Option failed IM007 No data source or driver specified; dialog prohibited IM008 Dialog failed IM009 Unable to load translation DLL IM010 Data source name too long IM011 Driver name too long IM012 DRIVER keyword syntax error IM013 Trace file error S0001 Base table or view already exists S0002 Base table not found S0011 Index already exists S0012 Index not found S0021 Column already exists S0022 Column not found S0023 No default for column S1000 General error S1001 Memory allocation failure S1002 Invalid column number S1003 Program type out of range S1004 SQL data type out of range S1008 Operation cancelled S1010 Function sequence error S1011 Operation invalid at this time S1012 Invalid transaction operation code specified S1015 No cursor name available S1090 Invalid string or buffer length S1091 Descriptor type out of range S1092 Option type out of range S1093 Invalid parameter number S1095 Function type out of range S1096 Information type out of range S1097 Column type out of range S1098 Scope type out of range S1099 Nullable type out of range S1100 Uniqueness option type out of range S1101 Accuracy option type out of range S1103 Direction option out of range S1105 Invalid parameter type S1106 Fetch type out of range S1107 Row value out of range S1108 Concurrency option out of range S1109 Invalid cursor position S1110 Invalid driver completion S1111 Invalid bookmark value S1C00 Driver not capable S1T00 Timeout expired
List of available Data Sources
The following is the list of the Data Sources Names (DSN, in the ODBC terminology) which are available with the standard ODBC Software Development Kit. The entries here are what the Eagle application must specify as the second parameter of the DB DATABASE command.
dBASE Files
Microsoft DBASE driver (*.dbf)
Excel Files
Microsoft EXCEL driver (*.xls)
FoxPro Files
Microsoft FOXPRO driver (*.dbf)
MS Access Database
Microsoft ACCESS driver (*.mdb)
Paradox Files
Microsoft PARADOX driver (*.db)
Text Files
Microsoft TEXT driver (*.txt, *.csv)
The following is the list of some of the currently available ODBC drivers :
Adabas
Btrieve
C-ISAM
CA-Datacom
CA-DB
CA-IDMS
Cincom SUPRA Server
Clipper
dBase
DEC Rdb
DEC RMS
DP4
EDA/SQL
FirstSQL
FOCUS
Gupta SQLBase
HP ALLBASE/SQL
HP IMAGE/SQL
IBM AS/400
IBM DB2
IBM DB2-DDCS/2
IBM DB2/2
IBM DB2/6000
IBM IMS
IBM SQL/DS
IDSII
Informix
Ingres
Integra-SQL
Interbase
MDBS IV
Microsoft Access
Microsoft Excel
Microsoft Foxpro
Model 204
MUMPS
Netware SQL
Objectivity/DB
Oracle
Paradox
PICK
Progress
Quadbase
R:BASE
Raima
Red Brick Warehouse
RFMII
SAS
SESAM/SQL
Sharebase
SQL Server
System 2000
Tandem Non-stop SQL
Tandem Enscribe
Teradata
Text
TOTAL
UDS/SQL
UFAS
UNIFY
Unisys DMS-1100 and RDMS-1100
UniSQL
VSAM
WATCOM SQL
White Cross Parallel RDBMS
XDB