MICROSOFT PROFESSIONAL EDITIONS
`
`•••••1
`4400
`
`TM
`MICROSOFT®
`WINDOWST.
`
`01)I3L 2.0
`Programmer's Reference
`and SDK Guide
`
`The Microsoft Open
`Database Standard for
`Microsoft Windows TM
`and Windows NT m
`
`Microsoft
`PR F S S
`
`Page 1 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`IODBC 2.0
`
`Programmer's Reference
`and SDK Guide
`
`For Microsoft Windows-
`and Windows NTTM
`
`You'll find detailed coverage of these
`features, new with version 2.0:
`
`• 32-bit application development
`support on Windows 3.1 and
`Windows NT
`• Support for scrollable cursors
`through a driver-independent
`Cursor Library
`• An improved set of ODBC 1.0
`single-tier drivers for several
`popular data formats
`• Sample C++ classes to help
`developers write C++ classes or
`applications
`• Templates that provide a
`ready-to-use base for writing
`drivers and auto-test DLLs
`• Numerous code samples and
`working tools to help developers
`write ODBC-enabled applications
`• Improved and integrated
`installation procedure for the SDK
`and drivers
`
`U.S.A. (cid:9)
`U.K. (cid:9)
`Canada (cid:9)
`
`$24.95
`£21.95
`$32.95
`[Recommended]
`
`The Microsoft Open Database Connectivity (ODBC)
`interface is an emerging industry standard and a
`component of Microsoft's Windows Open Services
`Architecture (WOSA). A C language programming
`interface, the ODBC interface enables applications to
`access data from a variety of database management
`systems using Structured Query Language (SQL) as
`a standard. This capability allows for maximum
`interoperability, making it possible for a developer to
`develop, compile, and ship an application without
`tying it to a specific database management system.
`Users can then add modules called database drivers,
`which link the application to their choice of database
`management systems.
`The Microsoft ODBC Software Development Kit
`(SDK), version 2.0, is a set of software components
`and tools designed to help you develop ODBC
`drivers and ODBC-enabled applications for the
`Windows 3.1 and Windows NT operating systems.
`This volume contains both the ODBC Programmer's
`Reference and the SDK Guide—the most complete,
`accurate, and up-to-date information on Microsoft
`ODBC available anywhere.
`In addition to providing the complete ODBC API
`reference, the Programmer's Reference provides an
`introduction to ODBC and detailed information
`about developing applications, developing drivers,
`and installing and configuring ODBC software. The
`ODBC SDK Guide offers the technical information
`you need to install the SDK; manage data sources
`and drivers; and use ODBC tools such as the ODBC
`Test Interface, ODBC Spy, and the Driver Setup
`Toolkit.
`
`Microsoft
`
`P R I S 1/4,
`
`ISBN -55615-658-8
`
`90000
`
`Microsoft Professional
`Editions are distributed
`by Microsoft Press.
`
`556 1 565 88
`
`
`1 9 78
`
`I
`
`Page 2 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Programmer's Reference
`
`Microsoft® Open
`Database Connectivity"
`Software Development Kit
`Version 2.0
`
`For the Microsoft WindowsTM and Windows NTni Operating Systems
`
`Page 3 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`PUBLISHED BY
`Microsoft Press
`A Division of Microsoft Corporation
`One Microsoft Way
`Redmond, Washington 98052-6399
`
`Copyright 1992, 1993, 1994 by Microsoft Corporation
`
`All rights reserved. No part of the contents of this book may be reproduced or transmitted
`in any form or by any means without the written permission of the publisher.
`
`Library of Congress Cataloging-in-Publication Data
`Microsoft ODBC 2.0 programmer's reference and SDK guide : for
`Microsoft Windows and Windows NT / Microsoft Corporation.
`p. (cid:9)
`cm.
`Includes index.
`ISBN 1-55615-658-8
`1. Microsoft Windows (Computer file) 2. Windows NT. 3. ODBC.
`I. Microsoft Corporation.
`QA76.76.W56M56323 1994
`005.75' 8--dc20 (cid:9)
`
`94-5039
`C I P
`
`Lucida Typeface Software. © 1985-1988 and 1990 by Bigelow & Holmes.
`U.S. Patent Nos. D289420, D289421, D289422, D289773
`
`U. S. Patent No. 4955066
`
`Printed and bound in the United States of America.
`
`2 3 4 5 6 7 8 9 MLML 9 8 7 6 5 4
`
`For Programmer's Reference: Paradox is a registered trademark of Ansa Software, a Borland Company.
`Apple is a registered trademark of Apple Computer, Inc. dBASE is a registered trademark of Borland Interna-
`tional, Inc. CompuServe is a registered trademark of CompuServe, Inc. DEC is a registered trademark of Digital
`Equipment Corporation. SQLBase is a registered trademark of Gupta Technologies, Inc. Informix is a registered
`trademark of Informix Software, Inc. Ingres is a trademark of Ingres Corporation. DB2, IBM, and OS/2 are
`registered trademarks of International Business Machines Corporation. Microsoft, Microsoft Access, and MS are
`registered trademarks and Win32, Windows, and Windows NT are trademarks of Microsoft Corporation in the
`U.S. and other countries. Novell is a registered trademark of Novell, Inc. Oracle is a registered trademark of
`Oracle Corporation. SYBASE is a registered trademark of Sybase, Inc. NonStop is a trademark of Tandem
`Computers Inc. UNIX is a registered trademark of UNIX Systems Laboratories. X/Open is a trademark of
`X/Open Company Limited in the U.K. and other countries.
`Document No. DB33920-0494
`
`For SDK Guide: Paradox is a registered trademark of Ansa Software, a Borland Company. dBASE is a
`registered trademark of Borland International, Inc. CompuServe is a registered trademark of CompuServe,
`Inc. Intel is a registered trademark of Intel Corporation. CodeView, FoxPro, Microsoft, Microsoft Access,
`MS, MS-DOS, Visual Basic, and Win32 are registered trademarks and Win32s, Windows, and Windows NT
`are trademarks of Microsoft Corporation in the U.S. and other countries. Btrieve is a registered tradmark
`of Novell, Inc.
`Document No. DB33919-0494
`
`Page 4 of 434
`
`RA v. AMS
`Ex. 1020
`
`(cid:9)
`

`
`iii
`
`Contents
`
`About This Manual xv
`Organization of this Manual xv
`Audience xvi
`Document Conventions xvi
`Where to Find Additional Information xvii
`
`Part 1 Introduction to ODBC
`
`Chapter 1 ODBC Theory of Operation 3
`ODBC History 3
`ODBC Interface 4
`ODBC Components 5
`Application 5
`Driver Manager 6
`Driver 6
`Data Source 7
`Types of Drivers 7
`Single-Tier Configuration 7
`Multiple-Tier Configuration 8
`Network Example 10
`Matching an Application to a Driver 11
`ODBC Conformance Levels 11
`API Conformance Levels 11
`SQL Conformance Levels 13
`How to Select a Set of Functionality 14
`Connections and Transactions 15
`
`Chapter 2 A Short History of SQL 17
`SQL Background Information 17
`ANSI 1989 Standard 17
`Embedded SQL 18
`Current ANSI Specification 19
`Dynamic SQL 19
`Call Level Interface 19
`Interoperability 20
`
`Page 5 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`iv (cid:9)
`
`Contents
`
`Part 2 Developing Applications
`
`Chapter 3 Guidelines for Calling ODBC Functions 23
`General Information 23
`Determining Driver Conformance Levels 23
`Determining API Conformance Levels 24
`Determining SQL Conformance Levels 24
`Using the Driver Manager 24
`Calling ODBC Functions 25
`Buffers 25
`Input Buffers 26
`Output Buffers 26
`Environment, Connection, and Statement Handles 27
`Using Data Types 28
`ODBC Function Return Codes 28
`
`Chapter 4 Basic Application Steps 29
`
`Chapter 5 Connecting to a Data Source 31
`About Data Sources 31
`Initializing the ODBC Environment 32
`Allocating a Connection Handle 32
`Connecting to a Data Source 33
`ODBC Extensions for Connections 33
`Connecting to a Data Source With SQLDriverConnect 33
`Connection Browsing With SQLBrowseConnect 35
`Connection Browsing Example for SQL Server 36
`Connection Browsing Example for DAL 37
`Translating Data 38
`Additional Extension Functions 39
`
`Chapter 6 Executing SQL Statements 41
`Allocating a Statement Handle 42
`Executing an SQL Statement 43
`Prepared Execution 43
`Direct Execution 44
`Setting Parameter Values 44
`Performing Transactions 45
`
`Page 6 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Contents
`
`ODBC Extensions for SQL Statements 46
`Retrieving Information About the Data Source's Catalog 46
`Sending Parameter Data at Execution Time 47
`Specifying Arrays of Parameter Values 48
`Executing Functions Asynchronously 48
`Using ODBC Extensions to SQL 49
`Date, Time, and Timestamp Data 50
`Scalar Functions 52
`LIKE Predicate Escape Characters 53
`Outer Joins 54
`Procedures 55
`Additional Extension Functions 56
`
`Chapter 7 Retrieving Results 57
`Assigning Storage for Results (Binding) 57
`Determining the Characteristics of a Result Set 58
`Fetching Result Data 58
`Using Cursors 59
`ODBC Extensions for Results 60
`Retrieving Data from Unbound Columns 60
`Assigning Storage for Row sets (Binding) 60
`Column-Wise Binding 61
`Row-Wise Binding 61
`Retrieving Rowset Data 61
`Using Block and Scrollable Cursors 62
`Block Cursors 62
`Scrollable Cursors 62
`Specifying the Cursor Type 64
`Specifying Cursor Concurrency 64
`Using Bookmarks 65
`Modifying Result Set Data 67
`Executing Positioned Update and Delete Statements 67
`Modifying Data with SQLSetPos 68
`Processing Multiple Results 70
`
`Page 7 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`vi (cid:9)
`
`Contents
`
`Chapter 8 Retrieving Status and Error Information 71
`Function Return Codes 71
`Retrieving Error Messages 72
`ODBC Error Messages 72
`Error Text Format 73
`Sample Error Messages 74
`Single-Tier Driver 74
`Multiple-Tier Driver 74
`Gateways 75
`Driver Manager 76
`Processing Error Messages 76
`
`Chapter 9 Terminating Transactions and Connections 77
`Terminating Statement Processing 77
`Terminating Transactions 78
`Terminating Connections 78
`
`Chapter 10 Constructing an ODBC Application 79
`Sample Application Code 79
`Static SQL Example 79
`Interactive Ad Hoc Query Example 83
`Testing and Debugging an Application 87
`Installing and Configuring ODBC Software 87
`
`Part 3 Developing Drivers
`
`Chapter 11 Guidelines for Implementing ODBC Functions 91
`Role of the Driver Manager 91
`Validating Arguments 92
`Checking State Transitions 93
`Checking for General Errors 93
`Elements of ODBC Functions 94
`General Information 94
`Supporting ODBC Conformance Levels 94
`Supporting API Conformance Levels 95
`Supporting SQL Conformance Levels 95
`Buffers 95
`Input Buffers 96
`Output Buffers 96
`Environment, Connection, and Statement Handles 97
`
`Page 8 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Contents (cid:9)
`
`vii
`
`Data Type Support 98
`ODBC Function Return Codes 98
`Driver-Specific Data Types, Descriptor Types, Information Types, and
`Options 99
`Yielding Control to Windows 100
`
`Chapter 12 Application Use of the ODBC Interface 101
`
`Chapter 13 Establishing Connections 103
`About Data Sources 103
`Establishing a Connection to a Data Source 104
`ODBC Extensions for Connections 105
`Connecting to a Data Source With SQLDriverConnect 106
`Connection Browsing With SQLBrowseConnect 108
`Connection Browsing Example for SQL Server 108
`Connection Browsing Example for DAL 110
`Translating Data 111
`Additional Extension Functions 112
`
`Chapter 14 Processing an SQL Statement 113
`Allocating a Statement Handle 114
`Executing an SQL Statement 115
`Prepared Execution 115
`Direct Execution 116
`Supporting Parameters 117
`Supporting Transactions 118
`ODBC Extensions for SQL Statements 118
`Returning Information About the Data Source's Catalog 118
`Accepting Parameter Data at Execution Time 119
`Accepting Arrays of Parameter Values 120
`Supporting Asynchronous Execution 120
`Supporting ODBC Extensions to SQL 121
`Date, Time, and Timestamp Data 122
`Scalar Functions 124
`LIKE Predicate Escape Characters 125
`Outer Joins 126
`Procedures 127
`Additional Extension Functions 128
`
`Page 9 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`viii (cid:9)
`
`Contents
`
`Chapter 15 Returning Results 129
`Assigning Storage for Results (Binding) 129
`Returning Information About a Result Set 130
`Returning Result Data 130
`Supporting Cursors 131
`ODBC Extensions for Results 131
`Returning Data from Unbound Columns 131
`Assigning Storage for Rowsets (Binding) 132
`Column-Wise Binding 132
`Row-Wise Binding 132
`Returning Rowset Data 133
`Supporting Block and Scrollable Cursors 134
`Block Cursors 134
`Scrollable Cursors 134
`Supporting the Cursor Types 136
`Supporting Cursor Concurrency 137
`Using Bookmarks 138
`Modifying Result Set Data 138
`Processing Positioned Update and Delete Statements 138
`Modifying Data with SQLSetPos 140
`Returning Multiple Results 141
`
`Chapter 16 Returning Status and Error Information 143
`Returning. Return Codes 143
`Returning Error Messages 144
`Constructing ODBC Error Messages 144
`Error Text Format 145
`Error Handling Rules 146
`Documenting Error Mappings 146
`Sample Error Messages 147
`Single-Tier Driver 147
`Multiple-Tier Driver 148
`Gateways 149
`Driver Manager 150
`
`Chapter 17 Terminating Transactions and Connections 151
`Terminating Statement Processing 151
`Terminating Transactions 152
`Terminating Connections 152
`
`Page 10 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Contents (cid:9)
`
`ix
`
`Chapter 18 Constructing an ODBC Driver 153
`Testing and Debugging a Driver 153
`Installing and Configuring ODBC Software 154
`
`Part 4 Installing and Configuring ODBC Software
`
`Chapter 19 Installing ODBC Software 157
`Redistributing ODBC Files 157
`Creating Your Own Setup Program 159
`Installing the Software Interactively 159
`Installing the Software Silently 159
`Installing Individual ODBC Components 159
`Constructing the ODBC.INF File 160
`Structure of the ODBC.INF File 160
`[Source Media Descriptions] Section 161
`[ODBC Drivers] Section 161
`Driver Specification Sections 162
`Driver Keyword Sections 163
`[ODBC Translators] Section 165
`Translator Specification Sections 166
`Installation Properties 166
`Structure of the ODBCINST.INI File 167
`[ODBC Drivers] Section 168
`Driver Specification Sections 169
`Default Driver Specification Section 170
`[ODBC Translators] Section 170
`Translator Specification Sections 170
`
`Chapter 20 Configuring Data Sources 173
`Creating Your Own Data Source — Management Program 173
`Adding, Modifying, and Deleting Data Sources 173
`Specifying a Default Data Source 174
`Specifying a Default Translator 174
`Structure of the ODBC.INI File 175
`[ODBC Data Sources] Section 176
`Data Source Specification Sections 176
`Default Data Source Specification Section 177
`ODBC Options Section 178
`
`Page 11 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Contents
`
`Part 5 API Reference
`
`Chapter 21 Function Summary 181
`ODBC Function Summary 181
`Setup DLL Function Summary 186
`Installer DLL Function Summary 186
`Translation DLL Function Summary 187
`
`Chapter 22 ODBC Function Reference 189
`Arguments 189
`ODBC Include Files 192
`Diagnostics 192
`Tables and Views 192
`Catalog Functions 192
`Search Pattern Arguments 193
`SQLAllocConnect 195
`SQLAllocEnv 197
`SQLAllocStmt 199
`SQLBindCol 201
`SQLBindParameter 209
`SQLBrowseConnect 223
`SQLCancel. 232
`SQLColAttributes 235
`SQLColumnPrivileges 242
`SQLColumns 247
`SQLConnect 254
`SQLDataSources 260
`SQLDescribeCol 263
`SQLDescribeParam 268
`SQLDisconnect 272
`SQLDriverConnect 274
`SQLDrivers 283
`SQLError 287
`SQLExecDirect 290
`SQLExecute 298
`SQLExtendedFetch 304
`SQLFetch 319
`SQLForeignKeys 324
`SQLFreeConnect 333
`SQLFreeEnv 335
`
`Page 12 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Contents (cid:9)
`
`xi
`
`SQLFreeStmt 337
`SQLGetConnectOption 340
`SQLGetCursorName 342
`SQLGetData 344
`SQLGetFunctions 354
`SQLGetInfo 359
`SQLGetStmtOption 387
`SQLGetTypeInfo 390
`SQLMoreResults 398
`SQLNativeSql 401
`SQLNumParams 404
`SQLNumResultCols 406
`SQLParamData 409
`SQLParamOptions 412
`SQLPrepare 416
`SQLPrimaryKeys 422
`SQLProcedureColumns 426
`SQLProcedures 433
`SQLPutData 440
`SQLRowCount 448
`SQLSetConnectOption 450
`SQLSetCursorName 459
`SQLSetParam 462
`SQLSetPos 463
`SQLSetScrollOptions 478
`SQLSetStmtOption 482
`SQLSpecialColumns 491
`SQLStatistics 498
`SQLTablePrivileges 505
`SQLTables 511
`SQLTransact 516
`
`Chapter 23 Setup DLL Function Reference 521
`ConfigDSN 522
`ConfigTranslator 525
`
`Chapter 24 Installer DLL Function Reference 527
`SQLConfigDataSource 528
`SQLCreateDataS ource 530
`SQLGetAvailableDrivers 531
`
`Page 13 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`xii (cid:9)
`
`Contents
`
`Appendixes
`
`SQLGetlnstalledDrivers 532
`SQLGetPrivateProfileString 533
`SQLGetTranslator 534
`SQLlnstallDriver 536
`SQLInstallDriverManager 538
`SQLInstallODBC 539
`SQLManageDataSources 543
`SQLRemoveDefaultDataSource 546
`SQLRemoveDSNFromlni 547
`SQLWriteDSNTolni 548
`SQLWritePrivateProfileString 549
`
`Chapter 25 Translation DLL Function Reference 551
`SQLDataSourceToDriver 552
`SQLDriverToDataSource 555
`
`Appendix A ODBC Error Codes 561
`
`Appendix B ODBC State Transition Tables 575
`Environment Transitions 577
`Connection Transitions 579
`Statement Transitions 584
`
`Appendix C SQL Grammar 597
`Parameter Data Types 598
`Parameter Markers 598
`SQL Statements 599
`Elements Used in SQL Statements 604
`List of Reserved Keywords 612
`
`Appendix D Data Types 615
`SQL Data Types 616
`Minimum SQL Data Types 616
`Core SQL Data Types 617
`Extended SQL Data Types 618
`C Data Types 619
`Core C Data Types 619
`Extended C Data Types 620
`Bookmark C Data Type 621
`
`Page 14 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Contents (cid:9)
`
`xiii
`
`ODBC 1.0 C Data Types 621
`Default C Data Types 622
`Transferring Data in its Binary Form 623
`Precision, Scale, Length, and Display Size 624
`Precision 624
`Scale 626
`Length 627
`Display Size 628
`Converting Data from SQL to C Data Types 629
`SQL to C: Character 631
`SQL to C: Numeric 633
`SQL to C: Binary 636
`SQL to C: Date 637
`SQL to C: Time 638
`SQL to C: Timestamp 639
`SQL to C Data Conversion Examples 640
`Converting Data from C to SQL Data Types 641
`C to SQL: Character 644
`C to SQL: Numeric 646
`C to SQL: Bit 647
`C to SQL: Binary 648
`C to SQL: Date 649
`C to SQL: Time 650
`C to SQL: Timestamp 651
`C to SQL Data Conversion Examples 652
`
`Appendix E Comparison Between Embedded SQL and ODBC 653
`ODBC to Embedded SQL 653
`Embedded SQL to ODBC 656
`Declarative Statements 656
`Data Definition Statements 656
`Data Manipulation Statements 657
`Dynamic SQL Statements 658
`Transaction Control Statements 659
`Association Management Statements 659
`Diagnostic Statement 660
`
`Appendix F Scalar Functions 661
`String Functions 662
`Numeric Functions 664
`
`Page 15 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`xiv (cid:9)
`
`Contents
`
`Index 691
`
`Time and Date Functions 666
`System Functions 669
`Explicit Data Type Conversion 670
`
`Appendix G ODBC Cursor Library 673
`Using the ODBC Cursor Library 673
`Executing Positioned Update and Delete Statements 674
`Code Example 675
`Implementation Notes 678
`Cursor Library Cache 678
`Column Data 678
`Length of Column Data 679
`Row Status 679
`Location of Cache 679
`SQL Statements 679
`Positioned Update and Delete Statements 680
`SELECT FOR UPDATE Statements 680
`Batched SQL Statements 681
`Constructing Searched Statements 681
`ODBC Functions 682
`SQLBindCol 682
`SQLExtendedFetch 683
`SQLFreeStmt 684
`SQLGetData 684
`SQLGetFunctions 685
`SQLGetInfo 685
`SQLGetStmtOption 686
`SQLNativeSql 686
`SQLRowCount 686
`SQLSetConnectOption 687
`SQLSetPos 687
`SQLSetScrollOptions 687
`SQLSetStmtOption 687
`SQLTransact 688
`ODBC Cursor Library Error Codes 689
`
`Page 16 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`About This Manual
`
`XV
`
`The Microsoft® Open Database Connectivity` (ODBC) interface is a C
`programming language interface for database connectivity. This manual addresses
`the following questions:
`
`n What is the PDBC interface?
`n What features does ODBC offer?
`n How do applications use the interface?
`
`The following topics provide information about the organization of this manual,
`describe the knowledge necessary to use the ODBC interface effectively, set out
`the typographic conventions used, and give a listing of references that provide
`information about Structured Query Language (SQL) standards and SQL in
`conjunction with relational databases.
`
`Organization of this Manual
`
`This manual is organized into the following parts:
`
`n Part 1 Introduction to ODBC, providing conceptual information about the
`ODBC interface and a brief history of Structured Query Language;
`n Part 2 Developing Applications, containing information for developing
`applications using the ODBC interface;
`n Part 3 Developing Drivers, containing information for developing drivers that
`support ODBC function calls;
`n Part 4 Installing and Configuring ODBC Software, providing information
`about installation and a setup DLL function reference;
`n Part 5 API Reference, containing syntax and semantic information for all
`ODBC functions.
`
`Page 17 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`xvi (cid:9)
`
`ODBC Programmer's Reference
`
`Audience
`
`The ODBC software development kit is available for use with the C programming
`language run with the Microsoft WindowsTM operating system and the Microsoft
`Windows NTTM operating system. Use of the ODBC interface spans four areas:
`SQL statements, ODBC function calls, C programming, and Windows
`programming. For information about Windows programming, see the Microsoft
`Windows and Microsoft Windows NT Software Development Kit development
`tools for building Microsoft Windows applications. This manual assumes:
`
`n A working knowledge of the C programming language.
`• General DBMS knowledge and a familiarity with SQL.
`
`Document Conventions
`
`This manual uses the following typographic conventions.
`
`Format (cid:9)
`
`Used for
`
`WIN.INI (cid:9)
`
`argument (cid:9)
`
`SQLTransact (cid:9)
`
`Uppercase letters indicate filenames, SQL statements,
`macro names, and terms used at the operating-system
`command level.
`RETCODE SQLFetch ( hdbc ) This font is used for sample command lines and program
`code.
`Italicized words indicate information that the user or the
`application must provide, or word emphasis.
`Bold type indicates that syntax must be typed exactly as
`shown, including function names.
`Brackets indicate optional items; if in bold text, brackets
`must be included in the syntax.
`A vertical bar separates two mutually exclusive choices in a
`syntax line.
`Braces delimit a set of mutually exclusive choices in a
`syntax line; if in bold text, braces must be included in the
`syntax.
`An ellipsis indicates that arguments can be repeated several
`times.
`A column of three dots indicates continuation of previous
`lines of code.
`
`[ (cid:9)
`
`I (cid:9)
`
`{ (cid:9)
`
`Page 18 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Where to Find Additional Information
`
`ODBC Programmer's Reference (cid:9)
`
`xvii
`
`For more information about SQL, the following standards are available:
`
`n Database Language—SQL with Integrity Enhancement, ANSI, 1989 ANSI
`X3.135-1989.
`n X/Open and SQL Access Group SQL CAE specification (1992).
`n Database Language—SQL: ANSI X3H2 and ISO/IEC JTC1/SC21/WG3
`9075:1992 (SQL-92).
`
`In addition to standards and vendor-specific SQL guides, there are many books
`that describe SQL, including:
`
`n Date, C. J.: A Guide to the SQL Standard (Addison-Wesley, 1989).
`n Emerson, Sandra L., Darnovsky, Marcy, and Bowman, Judith S.: The
`Practical SQL Handbook (Addison-Wesley, 1989).
`n Groff, James R. and Weinberg, Paul N.: Using SQL (Osborne McGraw-Hill,
`1990).
`n Gruber, Martin: Understanding SQL (Sybex, 1990).
`n Hursch, Jack L. and Carolyn J.: SQL, The Structured Query Language (TAB
`Books, 1988).
`n Pascal, Fabian: SQL and Relational Basics (M & T Books, 1990).
`n Trimble, J. Harvey, Jr. and Chappell, David: A Visual Introduction to SQL
`(Wiley, 1989).
`n Van der Lans, Rick F.: Introduction to SQL (Addison-Wesley, 1988).
`n Vang, Soren: SQL and Relational Databases (Microtrend Books, 1990).
`n Viescas, John: Quick Reference Guide to SQL (Microsoft Corp., 1989).
`
`Page 19 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`CHAPTER 1
`
`ODBC Theory of Operation
`
`3
`
`The Open Database Connectivity (ODBC) interface allows applications to access
`data in database management systems (DBMS) using Structured Query Language
`(SQL) as a standard for accessing data.
`
`The interface permits maximum interoperability—a single application can access
`different database management systems. This allows an application developer to
`develop, compile, and ship an application without targeting a specific DBMS.
`Users can then add modules called database drivers that link the application to
`their choice of database management systems.
`
`ODBC History
`
`In the traditional database world, application has usually meant a program that
`performed a specific database task with a specific DBMS in mind such as payroll,
`financial analysis, or inventory management. Such applications have typically
`been written using embedded SQL. While embedded SQL is efficient and is
`portable across different hardware and operating system environments, the source
`code must be recompiled for each new environment.
`
`Embedded SQL is not optimal for applications that need to analyze data stored in
`databases such as DB2® and Oracle®, and prefer to do so from within a familiar
`application interface, such as a Microsoft Excel® spreadsheet. Under the
`traditional approach to database access, one version of Microsoft Excel would
`have to be precompiled with the IBM® precompiler and another with the Oracle
`precompiler, clearly a radical departure from simply buying a single packaged
`product.
`
`ODBC offers a new approach: provide a separate program to extract the database
`information, and then have a way for applications to import the data. Since there
`are and probably always will be many viable communication methods, data
`protocols, and DBMS capabilities, the ODBC solution is to allow different
`technologies to be used by defining a standard interface. This solution leads to the
`
`Page 20 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`4 (cid:9)
`
`Part 1 Introduction to ODBC
`
`idea of database drivers—dynamic-link libraries that an application can invoke
`on demand to gain access to a particular data source through a particular
`communications method, much like a printer driver running under Windows.
`ODBC provides the standard interface that allows both application writers and
`providers of libraries to shuttle data between applications and data sources.
`
`ODBC Interface
`
`The ODBC interface defines the following:
`
`n A library of ODBC function calls that allow an application to connect to a
`DBMS, execute SQL statements, and retrieve results.
`n SQL syntax based on the X/Open and SQL Access Group (SAG) SQL CAE
`specification (1992).
`n A standard set of error codes.
`n A standard way to connect and log on to a DBMS.
`n A standard representation for data types.
`
`The interface is flexible:
`
`n Strings containing SQL statements can be explicitly included in source code or
`constructed on the fly at run time.
`n The same object code can be used to access different DBMS products.
`n An application can ignore underlying data communications protocols between
`it and a DBMS product.
`n Data values can be sent and retrieved in a format convenient to the application.
`
`The ODBC interface provides two types of function calls:
`
`n Core functions are based on the X/Open and SQL Access Group Call Level
`Interface specification.
`n Extended functions support additional functionality, including scrollable
`cursors and asynchronous processing.
`
`To send an SQL statement, include the statement as an argument in an ODBC
`function call. The statement need not be customized for a specific DBMS.
`Appendix C, "SQL Grammar," contains an SQL syntax based on the X/Open and
`SQL Access Group SQL CAE specification (1992). We recommend that ODBC
`applications use only the SQL syntax defined in Appendix C to ensure maximum
`interoperability.
`
`Page 21 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`ODBC Components
`
`Chapter 1 ODBC Theory of Operation (cid:9)
`
`5
`
`The ODBC architecture has four components:
`
`n Application Performs processing and calls ODBC functions to submit SQL
`statements and retrieve results.
`n Driver Manager Loads drivers on behalf of an application.
`n 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.
`n 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.
`
`The Driver Manager and driver appear to an application as one unit that processes
`ODBC function calls. The following diagram shows the relationship between the
`four components. The following paragraphs describe each component in more
`detail.
`
`ODBC Interface
`
`Application
`
`An application using the ODBC interface performs the following tasks:
`
`n Requests a connection, or session, with a data source.
`n Sends SQL requests to the data source.
`n Defines storage areas and data formats for the results of SQL requests.
`n Requests results.
`
`Page 22 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`6 (cid:9)
`
`Part 1 Introduction to ODBC
`
`n Processes errors.
`n Reports results back to a user, if necessary.
`n Requests commit or rollback operations for transaction control.
`n Terminates the connection to the data source.
`
`An application can provide a variety of features external to the ODBC interface,
`including mail, spreadsheet capabilities, online transaction processing, and report
`generation; the application may or may not interact with users.
`
`Driver Manager
`The Driver Manager, provided by Microsoft, is a dynamic-link library (DLL) with
`an import library. The primary purpose of the Driver Manager is to load drivers.
`The Driver Manager also performs the following:
`
`n Uses the ODBC.INI file or registry to map a data source name to a specific
`driver dynamic-link library (DLL).
`n Processes several ODBC initialization calls.
`n Provides entry points to ODBC functions for each driver.
`n Provides parameter validation and sequence validation for ODBC calls.
`
`Driver
`
`A driver is a DLL that implements ODBC function calls and interacts with a data
`source.
`
`The Driver Manager loads a driver when the application calls the
`SQLBrowseConnect, SQLConnect, or SQLDriverConnect function.
`
`A driver performs the following tasks in response to ODBC function calls from an
`application:
`
`n Establishes a connection to a data source.
`n Submits requests to the data source.
`n Translates data to or from other formats, if requested by the application.
`n Returns results to the application.
`n Formats errors into standard error codes and returns them to the application.
`n Declares and manipulates cursors if necessary. (This operation is invisible to
`the application unless there is a request for access to a cursor name.)
`n Initiates transactions if the data source requires explicit transaction initiation.
`(This operation is invisible to the application.)
`
`Page 23 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Data Source
`
`Chapter 1 ODBC Theory of Operation (cid:9)
`
`7
`
`In this manual, DBMS refers to the general features and functionality provided by
`an SQL database management system. 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.
`
`An application establishes a connection with a particular vendor's DBMS product
`on a particular operating system, accessible by a particular network. For example,
`the application might establish connections to:
`
`n An Oracle DBMS running on an OS/2® operating system, accessed by Novell®
`netware.
`n A local Xbase file, in which case the network and remote operating system are
`not part of the communication path.
`n A Tandem NonStop" SQL DBMS running on the Guardian 90 operating
`system, accessed via a gateway.
`
`Types of Drivers
`
`ODBC defines two types of drivers:
`
`n Single-tier The driver processes both ODBC calls and SQL statements. (In
`this case, the driver performs part of the data source functionality.)
`n Multiple-tier The driver processes ODBC calls and passes SQL statements
`to the data source.
`
`One system can contain both types of configurations.
`
`The following paragraphs describe single-tier and multiple-tier configurations in
`more detail.
`
`Single-Tier Configuration
`In a single-tier implementation, the database file is processed directly by the
`driver. The driver processes SQL statements and retrieves information from the
`database. A driver that manipulates an Xbase file is an example of a single-tier
`implementation.
`
`A single-tier driver may limit the set of SQL statements that may be submitted.
`The minimum set of SQL statements that must be supported by a single-tier driver
`is defined in Appendix C, "SQL Grammar."
`
`Page 24 of 434
`
`RA v. AMS
`Ex. 1020
`
`

`
`Part 1 Introduction to ODBC
`
`The following diagram shows two types of single-tier configurations.
`
`System A
`
`Application
`Driver Manager
`n Driver (includes data
`access software)
`Data storage
`
`Client B
`
`Server B