ODBC

Index

Part 1 Introduction to ODBC
Organization of this Manual Audience Document Conventions About the Code Examples Recommended Reading Chapter 1 Introduction Introduction Why Was ODBC Created? What is ODBC? ODBC and the Standard CLI Chapter 2 An Introduction to SQL and ODBC An Introduction to SQL and ODBC Structured Query Language (SQL) Structured Query Language (SQL) Processing an SQL Statement Embedded SQL Embedded SQL Embedded SQL Example Compiling an Embedded SQL Program Static SQL Dynamic SQL SQL Modules Call-Level Interfaces Database Access Architecture Database Access Architecture Network Database Access Standard Database Access Architectures Standard Database Access Architectures Standard Programming Interface Standard Data Stream Protocol Standard Gateway The ODBC Solution Chapter 3 ODBC Architecture ODBC Architecture Applications The Driver Manager Drivers Drivers Driver Tasks Driver Architecture Driver Architecture File-Based Drivers DBMS-Based Drivers Network Example Other Driver Architectures Data Sources Data Sources Types of 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 ODBC Fundamentals Handles Handles Environment Handles Connection Handles Statement Handles Descriptor Handles State Transitions Buffers Buffers Deferred Buffers Allocating and Freeing Buffers Using Data 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 Data Types in ODBC Type Identifiers SQL Data Types in ODBC SQL Data Types in ODBC SQL Type Identifiers Retrieving Data Type Information with SQLGetTypeInfo C Data Types in ODBC Data Type Conversions Conformance Levels Conformance Levels Interface 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 Application Steps Basic 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 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 Establishing a Connection Default Data Source Connecting with SQLConnect Connection String Connecting with SQLDriverConnect 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 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 Catalog Functions Uses of Catalog Data Catalog Functions in ODBC Catalog Functions in ODBC Data Returned by Catalog Functions Arguments in Catalog Functions Arguments in Catalog Functions Ordinary Arguments Pattern Value Arguments Identifier Arguments Value List Arguments Schema Views Chapter 8 SQL Statements SQL Statements Constructing 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 Interoperability of SQL Statements Choosing an SQL Grammar Constructing Interoperable SQL Statements 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 Escape Sequences in ODBC Date, Time, and Timestamp Literals Datetime Interval Literals Scalar Function Calls LIKE Predicate Escape Character Outer Joins Procedure Calls Chapter 9 Executing Statements Executing Statements Allocating a Statement Handle Statement Attributes Executing a Statement Executing a Statement Direct Execution Prepared Execution Procedures Procedures When to Use Procedures Executing Procedures Batches of SQL Statements Batches of SQL Statements Result-Generating and Result-Free Statements Executing Batches Errors and Batches Executing Catalog Functions Statement Parameters Statement Parameters Binding 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 Arrays of Parameter Values Binding Arrays of Parameters Using Arrays of Parameters Asynchronous Execution Freeing a Statement Handle Chapter 10 Retrieving Results (Basic) Retrieving Results (Basic) Was a Result Set Created? Result Set Metadata Result Set Metadata How is Metadata Used? SQLDescribeCol and SQLColAttribute Binding Result Set Columns Binding Result Set Columns Overview Using SQLBindCol Fetching Data Fetching Data Cursors Fetching a Row of Data Getting Long Data Closing the Cursor Chapter 11 Retrieving Results (Advanced) Retrieving Results (Advanced) Column Binding Offsets Block Cursors Block Cursors Binding Columns for Use with Block Cursors Binding Columns for Use with Block Cursors Column-Wise Binding Row-Wise Binding Using Block Cursors Using Block Cursors Rowset Size Number of Rows Fetched and Status SQLGetData and Block Cursors Row Status Array Scrollable Cursors Scrollable Cursors Scrollable Cursor Types Scrollable Cursor Types Static Cursors Dynamic Cursors Keyset-Driven Cursors Mixed Cursors Using Scrollable 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 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 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 Data with SQLSetPos Updating Rows in the Rowset with SQLSetPos Deleting Rows in the Rowset with SQLSetPos Updating Data with SQLBulkOperations 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 Descriptors Types of Descriptors Descriptor Fields Descriptor Fields Record Count Bound Descriptor Records Deferred Fields Consistency Check Allocating and Freeing Descriptors 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 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 Transactions in ODBC Transactions in ODBC Transaction Support Commit Mode 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 Transaction Isolation Serializability Transaction Isolation Levels Setting the Transaction Isolation Level Scrollable Cursors and Transaction Isolation Concurrency Control Concurrency Control Concurrency Types Optimistic Concurrency Chapter 15 Diagnostics Diagnostics Return Codes Diagnostic Records Diagnostic Records Header Record Status Records Status Records Sequence of Status Records SQLSTATEs Diagnostic Messages Using SQLGetDiagRec and SQLGetDiagField Implementing SQLGetDiagRec and SQLGetDiagField Implementing SQLGetDiagRec and SQLGetDiagField Diagnostic Handling Rules Role of the Driver Manager 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 Diagnostic Handling Examples File-Based Driver Diagnostic Example DBMS-Based Driver Diagnostic Example Gateways Diagnostic Example Driver Manager Diagnostic Example Chapter 16 Interoperability Interoperability Is ODBC the Answer? Choosing a Level of Interoperability 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 Writing an Interoperable Application Checking Feature Support and Variability Features to Watch For 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 Programming Considerations Multithreading Alignment Translation DLLs Tracing Tracing Trace DLL Trace File Enabling Tracing Enabling Tracing Dynamic Tracing Driver-Specific Data Types, Descriptor Types, Information Types, Diagnostic Types, and Attributes Backward Compatibility and Standards Compliance Backward Compatibility and Standards Compliance Affected ODBC Components Types of Changes Application/Driver Compatibility Application/Driver Compatibility Types of Applications Types of Drivers Compatibility Matrix New Features New Features Block Cursors, Scrollable Cursors, and Backward Compatibility for ODBC 3.0 Applications Duplicated Features Behavioral Changes Behavioral Changes SQLSTATE Mappings Datetime Data Type Changes Writing ODBC 3.0 Applications Writing ODBC 3.0 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.0 Drivers ODBC in Windows ODBC in Windows Header Files CString Class Creating and Terminating Threads Part 3 Installing and Configuring ODBC Software
Chapter 18 Installing ODBC Components Installing ODBC Components Installation Components Installation Components Setup Program Installer DLL Driver Setup DLL Usage Counting Redistributable Files Registry Entries for ODBC Components 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 Configuring Data Sources Configuration Components Configuration Components Administration Program The Installer DLL Driver Setup DLLs Translator Setup DLLs Registry Entries for Data Sources 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 Function Summary ODBC Function Summary Setup DLL Function Summary Installer DLL Function Summary Translation DLL Function Summary Chapter 21 ODBC API Reference 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 Setup DLL API Reference ConfigDriver ConfigDSN ConfigTranslator Chapter 23 Installer DLL API Reference 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 Translation DLL Function Reference SQLDataSourceToDriver SQLDriverToDataSource Appendixes
Appendix A ODBC Error Codes ODBC Error Codes Appendix B ODBC State Transition Tables ODBC State Transition Tables Environment Transitions Connection Transitions Statement Transitions Descriptor Transitions Appendix C SQL Grammar SQL Grammar SQL Minimum Grammar SQL Minimum Grammar Elements Used in SQL Statements Data Type Support Parameter Data Types Parameter Markers ODBC Escape Sequences ODBC Escape Sequences Date, Time, and Timestamp Escape Sequences Interval Escape Sequences Like Escape Sequence Outer Join Escape Sequence Procedure Call Escape Sequence Scalar Function Escape Sequence Literals in ODBC Literals in ODBC Interval Literal Syntax Numeric Literal Syntax List of Reserved Keywords Appendix D Data Types Data Types Using Data Type Identifiers SQL Data Types C Data Types C Data Types 64-Bit Integer Structures Data Type Identifiers and Descriptors Pseudo Type Identifiers Pseudo Type Identifiers Default C Data Types Bookmark C Data Type SQL_ARD_TYPE Transferring Data in its Binary Form Guidelines for Interval and Numeric Data Types Guidelines for Interval and Numeric Data Types Interval 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 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, and Display Size Column Size Decimal Digits Transfer Octet Length Display Size Converting Data from SQL to C Data Types 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: 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 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: 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 Scalar Functions ODBC and SQL-92 Scalar Functions String Functions Numeric Functions Time, Date, and Interval Functions System Functions Explicit Data Type Conversion Explicit Data Type Conversion SQL-92 Cast Function Appendix F ODBC Cursor Library ODBC Cursor Library Using the ODBC Cursor Library Executing Positioned Update and Delete Statements Cursor Library Code Example Implementation Notes Implementation Notes Cursor Library Cache Cursor Library Cache Column Data Length of Column Data Row Status Location of Cache Processing SQL Statements 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 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 in the Cursor Library 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 Driver Guidelines for Backward Compatibility Block Cursors, Scrollable Cursors, and Backward Compatibility Block Cursors, Scrollable Cursors, and Backward Compatibility What the Driver Manager Does What the Driver Does Mapping Deprecated Functions 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 SQLParamOptions Mapping SQLSetConnectOption Mapping SQLSetParam Mapping SQLSetScrollOptions Mapping SQLSetStmtOption Mapping SQLTransact Mapping Behavioral Changes and ODBC 3.0 Drivers Datetime Data Types Backward Compatibility of C Data Types Fixed-Length Bookmarks SQLGetInfo Support Returning SQL_NO_DATA Calling SQLSetPos to Insert Data SQLInstallTranslator Mapping Loading by Ordinal Glossary
Glossary