ADO Programmer’s Reference

701 Pages·2004·25.361 MB·English

by  Dave Sussman (auth.)

ADO
Programmer's Reference
Dave Sussman
APress Media, LLC
ADO Programmer's Reference
Copyright © 2004 by Dave Sussman
Originally published by Apress 2004
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN 978-1-59059-342-4 ISBN 978-1-4302-0718-4 (eBook)
DOI 10.1007/978-1-4302-0718-4
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Technical Reviewers: Clive Browning, Robert Chang,james Conard, Mark Homer, Ron Landers,
Kenneth Lo, CarI Prothman, Simon Robinson,john Timney, Khin Walker, Warren Wtltsie, Adrian Young
Editorial Board: Steve Anglin, Dan Appleman, Gary Comell,james Cox, Tony Davis,john Franklin,
Chris Mills, Steven Rycroft, Dominic Shakeshaft,julian Skinner, Martin Streicher,jim Sumser, Karen
Watterson, Gavin Wray,john Zukowski
Assistant Publisher: Grace Wong
Project Manager: Sofia Marchant
Copy Editor: Scott Carter
Production Manager: Kari Brooks
Production Editor: Kelly Wmquist
Proofreader: Greg Teague
Compositor: Dina QJ1an
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley,
CA 94710. Phone [phone], fax [phone], email [email protected], or visit
http://www.apress.com.
The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any
liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly
or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.comin the Downloads section.
You will need to answer questions pertaining to this book in order to successfully download the code.
Contents
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . Ix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . ~ . . . xl
Chapter 1: What Is ADO? . . . . . . . . . . . . . . . . . . . . . . . 1
What is Data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
What Are Data Stores? ................................... 2
About Universal Data Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Existing Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
What Is OLE DB? ..................................... 5
WhyADO? ......................................... 6
ADO and ADO.NET .................................... 7
Data Providers and Data Consumers ........................... 7
Wmdows Distributed interNet Application Architecture (Windows DNA) ....... 9
ADO Availability ..................................... 10
New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Examples .......................................... 14
Language Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Summary .......................................... 22
Chapter 2: The ADO Object Model. . . . . . . . . . . . . . . . . . . 23
The ADO Object Model .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
The ADOX Object Model .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
OLE DB Providers .................................... 39
Asynchronous Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Object Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Summary .......................................... 50
Contents
Chapter 3: The Connection Object .................. 51.
Connection Pooling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Methods of the Connection Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Properties of the Connection Object . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Events of the Connection Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Collections of the Connection Object .......................... 77
Chapter 4: The Command Object . . . . . . . . . . . . . . . . . . . 79
Methods of the Command Object ............................ 79
Properties of the Command Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Collections of the Command Object . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
The ParaIIleter Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
The ParaIIleters Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Retrieving Output ParaIIleters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Chapter 5: The Recordset Object. . . . . . . . . . . . . . . . . . . 1.07
Methods of the Recordset Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Properties of the Recordset Object ........................... 137
Events of the Recordset Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Collections of the Recordset Object . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Dynamic Properties ................................... 158
Chapter 6: The Record Object . . . . . . . . . . . . . . . . . . . . 1.61.
Methods of the Record Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Properties of the Record Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Collections of the Record Object ............................ 172
Fields of a Record. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Chapter 7: The Stream Object . . . . . . . . . . . . . . . . . . . . 1.77
Methods of the StreaIIl Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Properties of the StreaIIl Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Collections of the StreaIIl Object ............................ 188
Chapter 8: Collections. . . . . . . . . . . . . . . . . . . . . . . . 1.89
The Error Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
The Errors Collection .................................. 193
Error EXaIIlples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
The Field Object . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . 198
The Fields Collection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Iv
Contents
The Property Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
The Properties Collection ................................ 214
Indexing .......................................... 215
Using Images with ADO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Chapter 9: Remote Data Services . . . . . . . . . . . . . . . . . . 227
RDS Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
RDS Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Data Source Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
RDS DataSpace ..................................... 260
RDS DataFactory ... , , , , . . . . . . . . . . . . . . . . . . . . . . ........ 261
Data Binding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
Security Issues ...................................... 273
Chapter 10: ADOX Objects and Collections ............. 275
The Catalog Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
The Table Object ..................................... 281
The Index Object .................................... 283
The Key Object ..................................... 285
The Column Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
The Group Object .................................... 291
The User Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
The Procedure Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
The View Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Chapter 11: ADO Multidimensional ................. 307
Multi-Dimension Extensions ................... ........... 309
OLAP Servers ...................................... 310
The Catalog Object .................................... 310
The CubeDef Object ................................... 311
The Dimension Object .................................. 313
The Hierarchy Object .................................. 314
The Level Object . . . . . . . . . . . . . . . . . . . . . . . . . . . .......... 316
The Member Object . . . . . . . . . . . . . . . .................... 318
The Cell Object ..................................... 322
The CellSet Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
The Axis Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
The Position Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
The ADO MD Collections ............................... 329
v
Contents
Chapter 12: Jet Replication Objects . . . . . . . . . . . . . . . . . 331
Creating Replica Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
The Replica Object ................................... 332
The Filter Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
The Filters Collection .................................. 342
TheJetEngine Object .................................. 344
Chapter 13: Data Shaping . . . . . . . . . . . . . . . . . . . . . . 347
Using Data Shaping ................................... 350
The Shape Language. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Multiple Children ............. . . . . . . . . . . . . . . . . . . . . . . . 353
Grandchildren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Summarizing with Shapes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Updating Shaped Recordsets .............................. 357
Data Shaping in Visual Basic 6 ............................. 358
Why Data Shaping? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Reshaping Hierarchies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Grandchild Aggregates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Parameterized Computed Children . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Creating New Shapes .................................. 366
Dynamic Properties ................................... 367
Chapter 14: Performance . . . . . . . . . . . . . . . . . . . . . . 369
Cursor Type, Location, and Locking .......................... 370
Moving Through Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Cache Size ........... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Stored Procedure or Direct SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Parameters ........................................ 387
Connection Pooling ................................... 389
Performance Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
The Performance Test Tool ............................... 390
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Appendix A: ADO Object Summary . . . . . . . . . . . . . . . . . . 393
Appendix B: ADO Constants . . . . . . . . . . . . . . . . . . . . . 415
Appendix C: ADO Properties Collection ............... 475
Appendix D: Schemas . . . . . . . . . . . . . . . . . . . . . . . . 525
vi
Contents
Appendix E: ADO Data Types •• 567
Appendix F: RDS Object Summary 575
. . . . . . . . . . . . . . . .
Appendix G: RDS Constants • • • • • 583
Appendix H: ADOX Object Summary 585
Appendix I: ADOX Constants •••• 599
Appendix J: ADOX Properties Collection 605
Appendix K: ADOMD Object Summary · 611
Appendix L: ADOMD Constants ••• .623
Appendix M: ADOMD Properties Collection · 633
. . . . . . . . . . . . . . . . .
Appendix N: JRO Object Summary · 647
· . . . . . . . . . . . . . . . . . .
Appendix 0: JRO Constants • · 651
· . . . . . . . . . . . . . . . . . .
Appendix P: ADO Error Codes · 653
· . . . . . . . . . . . . . . . . . .
Index . . . . . . . . . . . . · 667
vII
About the Author
Dave Sussman is a developer, trainer and author, living in a quiet country village in Oxfordshire,
surrounded by nothing but fields and trees. One day soon he intends to stop working so hard and get
some form of social life.
Introduction
This book provides a comprehensive guide to the ways in which ADO can be used in all kinds of
applications. It demonstrates the use of ADO both in Web applications written using ASP, and in
compiled applications written using Visual Basic and other languages. It also includes a reference
section for fast access to detailed lists of the properties, methods, and events available in ADO.
What Is ADO?
If you are new to data-access programming with Microsoft technolOgies, you may not yet be aware of
ADO, although it's hard not to have come across it if you've had anything at all to do with ASP
programming. ADO stands for ActiveX Data Objects (although it's sometimes-mistakenly-called
Active Data Objects).
ADO is a set of COM components that provides programmatic access to Microsoft's latest underlying
data-access technologies. You can use ADO through almost any programming or scripting language, as
long as it can instantiate and use COM components.
ADO is based on an underlying data access technology called OLE DB (Object Linking and
Embedding Database). This is a defined set of COM interfaces that all data sources can implement
through special drivers (or providers), thereby exposing their data content in a uniform way. OLE DB
relies on a COM Interface (API), which is designed for use with languages like C++. ADO abstracts
developers from the complex details of the OLE DB interfaces by providing a simplified set of COM
based components and interfaces that can be called from a far wider range of languages.
In other words, ADO gives us a standard way of managing data from all kinds of data stores, not just
relational databases. In Chapter 1, I expand on these concepts to show you just how useful this whole
approach is when building data-access applications.
The design of ADO has also been driven by the ever-increasing role and importance of the Internet in
application development. ADO provides a range of techniques with which remote data access can be
achieved over the Internet by using a Web browser or through custom applications written in a range
of programming languages.