ÖÐÎâÄϹËΩһЦ

1) History
    1989    SQL89
    1992    ODBC v1.0, SQL92
    1994    ODBC v2.0
    1995    ISO/IEC 9075-3:1995 Call-Level Interface (SQL/CLI)
    1996    ODBC v3.0
    1999    ISO/IEC 9075-3:1999 Call-Level Interface (SQL/CLI)
    2003    ISO/IEC 9075-3:2003 Call-Level Interface (SQL/CLI)

2) ODBC - Open Database Connectivity
    ODBC is an API introduced by Microsoft that allows applications to access databases by using SQL
    Purpose - the ability of a single application to access different DBMS  (database management system) with the same code
    ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS
    Standards supported in Microsoft ODBC: ANSI SQL, ISO 9075, X/Open SQL CLI


3) ODBC is ¡­
    A specification for a database API
    This API is independent of any one DBMS, operating system or language
    API¡¯s functions are implemented by developers of DBMS-specific drivers
    Applications call this drivers functions  to access data in a DBMS-independent manner
    A Driver Manager manages communication between applications and drivers.


4) Component
    Application
        Performs processing and calls ODBC functions to submit SQL statements and retrieve results
    Driver manager
        Loads and unloads drivers on behalf of an application
        Processes ODBC function calls or passes them to a driver
    ODBC driver
        Processes ODBC function calls, Initiating transactions (this is transparent to the application)
        Submits SQL requests to a specific data source, and returns results to the application
    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


5) Driver Manager
    Microsoft ODBC Driver Manager
     - de facto standard ODBC driver for Windows platforms
    
    Open Source ODBC Driver-Manager Project
     - unixODBC (LGPL and GPL License)
        The most common driver-manager for non-Windows platforms
        Shipped by most Linux distributions
        Used by several vendors IBM (DB2, Informix), Oracle, SAP (Ingres) use

 - iODBC (LGPL and BSD License)

6) Pros&Cons
    Pros
        Interoperability for legacy ODBC-enabled applications
        Well-known APIs for Programmers
        May support other interfaces (JDBC) and languages (Java, .Net Languages)
    
    Cons
        More larger code size
        Performance degradation due to additional ODBC-tier


7) Conformance level
    ODBC API Conformance Level
        - Level 0  (a.k.a. Core Level)
                Connect, Bind, Prepare, Execute, Fetch
                Transaction
                Error
        
        - Level 1
                Set connection/statement options
                Retrieve catalog info. (table/column, statistics)
                Retrieve available data types
                Retrieve functions supported by driver
        
        - Level 2
                Retrieve primary/foreign keys info.
                Retrieve privileges info.
                Retrieve stored procedure info.
    
    ODBC SQL Conformance Level
        - Minimum Level (a subset of the Entry level of SQL-92)
                Data Definition Language
                CREATE TABLE, DROP TABLE
                
                Data Manipulation Language
                simple SELECT (no nesting), INSERT, UPDATE, DELETE
                
                Expressions
                simple expression (such as A>B+C)
                
                Data types
                CHAR, VARCHAR, or LONG VARCHAR
    
        - Core Level (include Minimum Level)
                Data Definition Language
                CREATE INDEX, DROP INDEX, ALTER TABLE, CREATE VIEW, DROP VIEW, GRANT, REVOKE
                
                Data Manipulation Language
                full SELECT
                
                Expressions
                subquery, set functions such as SUM and MIN
                
                Data Types
                DECIMAL, NUMERIC, SMALLINT, INTEGER, REAL, FLOAT, DOUBLE    
    
        - Extended Level (include Minimum and Core Level)
                Data Manipulation Language
                Outer joins, positioned UPDATE, positioned DELETE, SELECT FOR UPDATE, and unions
                
                Expressions
                scalar functions such as SUBSTRING, ABS, date, time, and timestamp literals
                
                Data types
                BIT, TINYINT, BIGINT, BINARY, VARBINARY, LONG VARBINARY, DATE, TIME, TIMESTAMP
                
                Batch SQL statements and Procedure calls 

8£©two ways to execute SQL clause
    a). direct execution
               SQLExecdirect
               SQLBindCol
               SQLFetch
    b). preprocess-execute (parameter binding or bulk execution)
               SQLPrepare
               SQLBindParameter
               SQLExecute

9£©Reference
    MSDN :ODBC Programmer's Reference