Superior Technology For You

ODBC Programmer's Reference

Part 1 Introduction to ODBC

Chapter 1: Introduction to ODBC

• Why Was ODBC Created?
• What Is ODBC?
• ODBC and the Standard CLI

Chapter 2: An Introduction to SQL and ODBC

• Structured Query Language (SQL)
  • Processing an SQL Statement
  • Embedded SQL
    • Embedded SQL Example
    • Compiling an Embedded SQL Program
    • Static SQL
    • Dynamic SQL
  • SQL Modules
  • Call-Level Interfaces
• Database Access Architecture
  • Network Database Access
  • Standard Database Access Architectures
    • Standard Programming Interface
    • Standard Data Stream Protocol
    • Standard Gateway
  • The ODBC Solution

Chapter 3: ODBC Architecture

• Applications
• The Driver Manager
• Drivers
  • Driver Tasks
  • Driver Architecture
    • File-Based Drivers
    • DBMS-Based Drivers
    • Network Example
    • Other Driver Architectures
• Data Sources
  • Types of Data Sources
    • Machine Data Sources
    • File Data Sources
  • Using Data Sources
  • Data Source Example

Part 2 Developing Applications and Drivers

Chapter 4: ODBC Fundamentals

• Handles
  • Environment Handles
  • Connection Handles
  • Statement Handles
  • Descriptor Handles
  • State Transitions
• Buffers
  • Deferred Buffers
  • Allocating and Freeing Buffers
  • Using Data Buffers
    • Data Buffer Type
    • Data Buffer Address
    • Data Buffer Length
      • Using Length/Indicator Values
      • Data Length, Buffer Length, and Truncation
      • Character Data and C Strings
• Data Types in ODBC
  • Type Identifiers
  • SQL Data Types in ODBC
    • SQL Type Identifiers
    • Retrieving Data Type Information with SQLGetTypeInfo
  • C Data Types in ODBC
  • Data Type Conversions
• Conformance Levels
  • Interface Conformance Levels
    • Core Interface Conformance
    • Level 1 Interface Conformance
    • Level 2 Interface Conformance
    • Function Conformance
    • Attribute Conformance
    • Descriptor Field Conformance
  • SQL Conformance Levels
  • Environment, Connection, and Statement Attributes
  • Tables and Views

Chapter 5: Basic ODBC Application Steps

• Step 1: Connect to the Data Source
• Step 2: Initialize the Application
• Step 3: Build and Execute an SQL Statement
• Step 4a: Fetch the Results
• Step 4b: Fetch the Row Count
• Step 5: Commit the Transaction
• Step 6: Disconnect from the Data Source

Chapter 6: Connecting to a Data Source or Driver

• Allocating the Environment Handle
• Declaring the Application's ODBC Version
• Choosing a Data Source or Driver
• Allocating a Connection Handle
• Connection Attributes
• Establishing a Connection
  • Default Data Source
  • Connecting with SQLConnect
  • Connection Strings
  • Connecting with SQLDriverConnect
    • Driver-Specific Connection Information
    • Prompting the User for Connection Information
    • Connecting Using File Data Sources
    • Connecting Directly to Drivers
  • Connecting with SQLBrowseConnect
    • SQL Server Browsing Example
• Connection Pooling
• Disconnecting from a Data Source or Driver
• Driver Manager's Role in the Connection Process

Chapter 7: Catalog Functions

• Uses of Catalog Data
• Catalog Functions in ODBC
  • Data Returned by Catalog Functions
  • Arguments in Catalog Functions
    • Ordinary Arguments
    • Pattern Value Arguments
    • Identifier Arguments
    • Value List Arguments
  • Schema Views

Chapter 8: SQL Statements

• Constructing SQL Statements
  • Hard-Coded SQL Statements
  • SQL Statements Constructed at Run Time
  • SQL Statements Entered by the User
• Interoperability of SQL Statements
  • Choosing an SQL Grammar
  • Constructing Interoperable SQL Statements
    • Catalog and Schema Usage
    • Catalog Position
    • Quoted Identifiers
    • Identifier Case
    • Escape Sequences
    • Literal Prefixes and Suffixes
    • Parameter Markers in Procedure Calls
    • DDL Statements
• Escape Sequences in ODBC
  • Date, Time, and Timestamp Literals
  • Scalar Function Calls
  • LIKE Predicate Escape Character
  • Outer Joins
  • Procedure Calls

Chapter 9: Executing Statements

• Allocating a Statement Handle
• Statement Attributes
• Executing a Statement
  • Direct Execution
  • Prepared Execution
  • Procedures
    • When to Use Procedures
    • Executing Procedures
  • Batches of SQL Statements
    • Result-Generating and Result-Free Statements
    • Executing Batches
    • Errors and Batches
  • Executing Catalog Functions
• Statement Parameters
  • Binding Parameters
    • Binding Parameter Markers
    • Binding Parameters by Name (Named Parameters)
    • Parameter Binding Offsets
    • Describing Parameters
  • Setting Parameter Values
  • Sending Long Data
  • Retrieving Output Parameters by SQLGetData
  • Procedure Parameters
  • Arrays of Parameter Values
    • Binding Arrays of Parameters
    • Using Arrays of Parameters
• Asynchronous Execution
• Freeing a Statement Handle

Chapter 10: Retrieving Results (Basic)

• Was a Result Set Created?
• Result Set Metadata
  • How is Metadata Used?
  • SQLDescribeCol and SQLColAttribute
• Binding Columns
  • Binding Result Set Columns
  • Using SQLBindCol
• Fetching Data
  • Cursors
  • Fetching a Row of Data
  • Getting Long Data
• Closing the Cursor

Chapter 11: Retrieving Results (Advanced)

• Block Cursors
  • Binding Columns for Use with Block Cursors
    • Column-Wise Binding
    • Row-Wise Binding
  • Using Block Cursors
    • Rowset Size
    • Number of Rows Fetched and Status
    • SQLGetData and Block Cursors; block cursors [ODBC]; cursors [ODBC], block; result sets [ODBC], block cursors
  • Row Status Array
• Scrollable Cursors
  • Scrollable Cursor Types
    • Static Cursors
    • Dynamic Cursors
    • Keyset-Driven Cursors
    • Mixed Cursors
  • Using Scrollable Cursors
    • Determining Cursor Capabilities
    • Setting Up the Cursor
    • Cursor Characteristics and Cursor Type
    • Scrolling and Fetching Rows
  • Relative and Absolute Scrolling
  • Bookmarks
    • Bookmark Types
    • Retrieving Bookmarks
    • Scrolling by Bookmark
    • Updating, Deleting, or Fetching by Bookmark
    • Comparing Bookmarks
• The ODBC Cursor Library
• Multiple Results

Chapter 12: Updating Data

• UPDATE, DELETE, and INSERT Statements
• Positioned Update and Delete Statements
• Simulating Positioned Update and Delete Statements
• Determining the Number of Affected Rows
• Updating Data with SQLSetPos
  • Updating Rows in the Rowset with SQLSetPos
  • Deleting Rows in the Rowset with SQLSetPos
• Updating Data with SQLBulkOperations
  • Updating Rows by Bookmark with SQLBulkOperations
  • Deleting Rows by Bookmark with SQLBulkOperations
  • Inserting Rows with SQLBulkOperations
  • Fetching Rows with SQLBulkOperations
• Long Data and SQLSetPos and SQLBulkOperations

Chapter 13: Descriptors

• Types of Descriptors
• Descriptor Fields
  • Record Count
  • Bound Descriptor Records
  • Deferred Fields
  • Consistency Check
• Allocating and Freeing Descriptors
  • Implicitly Allocated Descriptors
  • Explicitly Allocated Descriptors
  • Initialization of Descriptor Fields
  • Automatic Population of the IPD
  • Freeing Descriptors
• Getting and Setting Descriptor Fields
  • Obtaining Descriptor Handles
  • Retrieving the Values in Descriptor Fields
  • Setting Descriptor Fields
  • Copying Descriptors
  • Using Concise Functions

Chapter 14: Transactions

• Transactions in ODBC
  • Transaction Support
  • Commit Mode
    • Auto-Commit Mode
    • Manual-Commit Mode
    • Setting the Commit Mode
  • Committing and Rolling Back Transactions
  • Effect of Transactions on Cursors and Prepared Statements
• Transaction Isolation
  • Serializability
  • Transaction Isolation Levels
  • Setting the Transaction Isolation Level
  • Scrollable Cursors and Transaction Isolation
• Concurrency Control
  • Concurrency Types
  • Optimistic Concurrency

Chapter 15: Diagnostics

• Return Codes
• Diagnostic Records
  • Header Record
  • Status Records
    • Sequence of Status Records
    • SQLSTATEs
    • Diagnostic Messages
• Using SQLGetDiagRec and SQLGetDiagField
• Implementing SQLGetDiagRec and SQLGetDiagField
  • Diagnostic Handling Rules
  • Role of the Driver Manager
    • Argument Value Checks
    • State Transition Checks
    • General Error Checks
    • Driver Manager Error and Warning Checks
  • Role of the Driver
• Diagnostic Handling Examples
  • File-Based Driver Diagnostic Example
  • DBMS-Based Driver Diagnostic Example
  • Gateways Diagnostic Example
  • Driver Manager Diagnostic Example

Chapter 16: Interoperability

• Is ODBC the Answer?
• Choosing a Level of Interoperability
  • Custom Applications
  • Vertical Applications
  • Generic Applications
• Determining the Target DBMSs and Drivers
• Considering Database Features to Use
• Length of the Product Cycle
• Writing an Interoperable Application
  • Checking Feature Support and Variability
  • Features to Watch For
    • Version Number
    • Multiple Active Statements and Connections
    • Transaction Support in DBMSs
    • Commit and Rollback Behavior
    • NOT NULL in CREATE TABLE Statements
    • Supported Data Types
    • ODBC SQL Grammar
    • Batch Processing
• Testing Interoperable Applications

Chapter 17: Programming Considerations

• Multithreading
• Alignment
• Unicode
  • Unicode Function Arguments
    • Unicode Applications
    • Unicode Drivers
    • Function Mapping in the Driver Manager
  • Unicode Data
• Translation DLLs
• Diagnostic Tools
  • Tracing
    • Trace DLL
    • Trace File
    • Enabling Tracing
    • Dynamic Tracing
• Connection Pooling Considerations
• Visual Studio Analyzer
  • Enabling Visual Studio Analyzer
    • Events Generated by the ODBC Driver Manager
• Driver-Specific Data Types, Descriptor Types, Information Types, Diagnostic Types, and Attributes
• Backward Compatibility and Standards Compliance
  • Affected ODBC Components
  • Types of Changes
  • Application/Driver Compatibility
    • Types of Applications
    • Types of Drivers
    • Compatibility Matrix
  • New Features
    • Block Cursors, Scrollable Cursors, and Backward Compatibility for ODBC 3.x Applications
  • Duplicated Features
  • Behavioral Changes
    • SQLSTATE Mappings
    • Datetime Data Type Changes
  • Writing ODBC 3.x Applications
    • Mapping Replacement Functions for Backward Compatibility of Applications
    • Calling SQLCloseCursor
    • Calling SQLGetDiagField
    • Calling SQLSetPos
    • Cursor Library Operations
    • Mapping the Cursor Attributes1 Information Types
    • SQL_NO_DATA
  • Writing ODBC 3.x Drivers
• ODBC in Windows
  • Standards-Compliant Applications and Drivers
  • Header Files
  • CString Class
  • Creating and Terminating Threads

Part 3 Installing and Configuring the ODBC Software

Chapter 18: Installing ODBC Components

• Installation Components
  • Setup Program
  • Installer DLL
  • Driver Setup DLL
• Usage Counting
• Preventing Repeated Connection Attempts
• ODBC Application Files
  • Redistributable Files
  • ODBC Header Files
• Registry Entries for ODBC Components
  • ODBC Core Subkey
  • ODBC Drivers Subkey
  • Driver Specification Subkeys
  • Default Driver Subkey
  • ODBC Translators Subkey
  • Translator Specification Subkeys

Chapter 19: Configuring Data Sources

• Configuration Components
  • Administration Program
  • The Installer DLL
  • Driver Setup DLLs
  • Translator Setup DLLs
• Registry Entries for Data Sources
  • ODBC Data Sources Subkey
  • Data Source Specification Subkeys
  • Default Subkey
  • ODBC Subkey

Part 4 API Reference

Chapter 20: Function Summary

• ODBC Function Summary
• Setup DLL Function Summary
• Installer DLL Function Summary
• Translation DLL Function Summary

Chapter 21: ODBC API Reference

• SQLAllocConnect
• SQLAllocEnv
• SQLAllocHandle
• SQLAllocStmt
• SQLBindCol
• SQLBindParameter
• SQLBrowseConnect
• SQLBulkOperations
• SQLCancel
• SQLCloseCursor
• SQLColAttribute
• SQLColAttributes
• SQLColumnPrivileges
• SQLColumns
• SQLConnect
• SQLCopyDesc
• SQLDataSources
• SQLDescribeCol
• SQLDescribeParam
• SQLDisconnect
• SQLDriverConnect
• SQLDrivers
• SQLEndTran
• SQLError
• SQLExecDirect
• SQLExecute
• SQLExtendedFetch
• SQLFetch
• SQLFetchScroll
• SQLForeignKeys
• SQLFreeConnect
• SQLFreeEnv
• SQLFreeHandle
• SQLFreeStmt
• SQLGetConnectAttr
• SQLGetConnectOption
• SQLGetCursorName
• SQLGetData
• SQLGetDescField
• SQLGetDescRec
• SQLGetDiagField
• SQLGetDiagRec
• SQLGetEnvAttr
• SQLGetFunctions
• SQLGetInfo
• SQLGetStmtAttr
• SQLGetStmtOption
• SQLGetTypeInfo
• SQLMoreResults
• SQLNativeSql
• SQLNumParams
• SQLNumResultCols
• SQLParamData
• SQLParamOptions
• SQLPrepare
• SQLPrimaryKeys
• SQLProcedureColumns
• SQLProcedures
• SQLPutData
• SQLRowCount
• SQLSetConnectAttr
• SQLSetConnectOption
• SQLSetCursorName
• SQLSetDescField
• SQLSetDescRec
• SQLSetEnvAttr
• SQLSetParam
• SQLSetPos
• SQLSetScrollOptions
• SQLSetStmtAttr
• SQLSetStmtOption
• SQLSpecialColumns
• SQLStatistics
• SQLTablePrivileges
• SQLTables
• SQLTransact

Chapter 22: Setup DLL API Reference

• ConfigDriver
• ConfigDSN
• ConfigTranslator

Chapter 23: Installer DLL API Reference

• SQLConfigDataSource
• SQLConfigDriver
• SQLCreateDataSource
• SQLGetConfigMode
• SQLGetInstalledDrivers
• SQLGetPrivateProfileString
• SQLGetTranslator
• SQLInstallDriverEx
• SQLInstallDriverManager
• SQLInstallerError
• SQLInstallTranslator
• SQLInstallTranslatorEx
• SQLManageDataSources
• SQLPostInstallerError
• SQLReadFileDSN
• SQLRemoveDefaultDataSource
• SQLRemoveDriver
• SQLRemoveDriverManager
• SQLRemoveDSNFromIni
• SQLRemoveTranslator
• SQLSetConfigMode
• SQLValidDSN
• SQLWriteDSNToIni
• SQLWriteFileDSN
• SQLWritePrivateProfileString

Chapter 24: Translation DLL Function Reference

• SQLDataSourceToDriver
• SQLDriverToDataSource

Appendixes

Appendix A: ODBC Error Codes

Appendix B: ODBC State Transition Tables

• Environment Transitions
• Connection Transitions
• Statement Transitions
• Descriptor Transitions

Appendix C: SQL Grammar

• SQL Minimum Grammar
  • Elements Used in SQL Statements
  • Data Type Support
  • Parameter Data Types
  • Parameter Markers
• ODBC Escape Sequences
  • Date, Time, and Timestamp Escape Sequences
  • GUID Escape Sequences
  • Interval Escape Sequences
  • LIKE Escape Sequence
  • Outer Join Escape Sequence
  • Procedure Call Escape Sequence
  • Scalar Function Escape Sequence
• Literals in ODBC
  • Interval Literal Syntax
  • Numeric Literal Syntax
• List of Reserved Keywords

Appendix D: Data Types

• Using Data Type Identifiers
• SQL Data Types
  • Example SQLGetTypeInfo Result Set
• C Data Types
  • 64-Bit Integer Structures
• Data Type Identifiers and Descriptors
• Pseudo-Type Identifiers
  • Default C Data Types
  • Bookmark C Data Type
  • SQL_ARD_TYPE
  • SQL_C_TCHAR
• Transferring Data in Its Binary Form
• Guidelines for Interval and Numeric Data Types
  • Interval Data Types
    • C Interval Structure
    • Interval Data Type Precision
    • Interval Data Type Length
    • Interval Literals
    • Overriding Default Leading and Seconds Precision for Interval Data Types
  • Numeric Literals
    • Rules for Conversions
  • Overriding Default Precision and Scale for Numeric Data Types
• Constraints of the Gregorian Calendar
• Column Size, Decimal Digits, Transfer Octet Length, and Display Size
  • Column Size
  • Decimal Digits
  • Transfer Octet Length
  • Display Size
• Converting Data from SQL to C Data Types
  • SQL to C: Character
  • SQL to C: Numeric
  • SQL to C: Bit
  • SQL to C: Binary
  • SQL to C: Date
  • SQL to C: GUID
  • SQL to C: Time
  • SQL to C: Timestamp
  • SQL to C: Year-Month Intervals
  • SQL to C: Day-Time Intervals
  • SQL to C Data Conversion Examples
• Converting Data from C to SQL Data Types
  • C to SQL: Character
  • C to SQL: Numeric
  • C to SQL: Bit
  • C to SQL: Binary
  • C to SQL: Date
  • C to SQL: GUID
  • C to SQL: Time
  • C to SQL: Timestamp
  • C to SQL: Year-Month Intervals
  • C to SQL: Day-Time Intervals
  • C to SQL Data Conversion Examples

Appendix E: Scalar Functions

• String Functions
• Numeric Functions
• Time, Date, and Interval Functions
• System Functions
• Explicit Data Type Conversion Function
• SQL-92 CAST Function

Appendix F: ODBC Cursor Library

• Using the ODBC Cursor Library
• Executing Positioned Update and Delete Statements
• Cursor Library Code Example
• Implementation Notes
  • Cursor Library Cache
    • Column Data
    • Length of Column Data
    • Row Status
    • Location of Cache
  • Processing SQL Statements
    • Processing Positioned Update and Delete Statements
    • Processing SELECT FOR UPDATE Statements
    • Processing Batches of SQL Statements
    • Constructing Searched Statements
  • ODBC Functions
    • ODBC Functions Executed by the Cursor Library
    • ODBC Functions Not Executed by the Cursor Library
    • SQLBindCol in the Cursor Library
    • SQLBindParameter in the Cursor Library
    • SQLBulkOperations and the Cursor Library
    • SQLCloseCursor
    • SQLEndTran in the Cursor Library
    • SQLExtendedFetch in the Cursor Library
    • SQLFetch in the Cursor Library
    • SQLFetchScroll in the Cursor Library
    • SQLFreeStmt in the Cursor Library
    • SQLGetData in the Cursor Library
    • SQLGetDescField and SQLGetDescRec in the Cursor Library
    • SQLGetFunctions in the Cursor Library
    • SQLGetInfo in the Cursor Library
    • SQLGetStmtAttr in the Cursor Library
    • SQLGetStmtOption in the Cursor Library
    • SQLNativeSql in the Cursor Library
    • SQLRowCount in the Cursor Library
    • SQLSetConnectAttr in the Cursor Library
    • SQLSetDescField and SQLSetDescRec in the Cursor Library
    • SQLSetEnvAttr and the Cursor Library
    • SQLSetPos in the Cursor Library
    • SQLSetScrollOptions in the Cursor Library
    • SQLSetStmtAttr in the Cursor Library
• ODBC Cursor Library Error Codes

Appendix G: Driver Guidelines for Backward Compatibility

• Block Cursors, Scrollable Cursors, and Backward Compatibility
  • What the Driver Manager Does
  • What the Driver Does
• Mapping Deprecated Functions
  • SQLAllocConnect Mapping
  • SQLAllocEnv Mapping
  • SQLAllocStmt Mapping
  • SQLBindParam Mapping
  • SQLColAttributes Mapping
  • SQLError Mapping
  • SQLFreeConnect Mapping
  • SQLFreeEnv Mapping
  • SQLFreeStmt Mapping
  • SQLGetConnectOption Mapping
  • SQLGetStmtOption Mapping
  • SQLInstallTranslator Mapping
  • SQLParamOptions Mapping
  • SQLSetConnectOption Mapping
  • SQLSetParam Mapping
  • SQLSetScrollOptions Mapping
  • SQLSetStmtOption Mapping
  • SQLTransact Mapping
• Behavioral Changes and ODBC 3.x Drivers
  • Datetime Data Types
  • Backward Compatibility of C Data Types
  • Fixed-Length Bookmarks
  • SQLGetInfo Support
  • Returning SQL_NO_DATA
  • Calling SQLSetPos to Insert Data
  • Loading by Ordinal

ODBC Glossary