ODBC, OLE DB, ADO, and ASP

1. ODBC, OLE DB, ADO, and ASP Kroenke Chapter 12

2. Object-Oriented Concepts • Since the concept of “object” will be used frequently, it is time to revisit it. • An object-oriented programming object is an abstraction that is defined by its properties and methods. • An abstraction is a generalization of something. • A property specifies set of characteristics of an object. • A method refers to actions that an object can perform. • A collection is an object that contains a group of other objects.

3. Standards • Because database applications today reside in a heterogeneous environment, various standards have been developed for accessing database servers. • Some of the important standards are: • ODBC (Open Database Connectivity) • the early standard for relational databases. • OLE DB • Microsoft’s object-oriented interface for relational and other databases. • ADO (Active Data Objects) • Microsoft’s standard providing easier access to OLE DB data for the non-object-oriented programmer.

4. The Web Server Data Environment • A Web server needs to publish applications that involve different data types.

5. The Role of the ODBC Standard- Open DataBase Connectivity –

6. The Role of OLE DB- Object Linking and Embedding -

7. The Role of ADO- Active Data Object -

8. Open Database Connectivity (OBDC) • The Open Database Connectivity (ODBC) standard provides a DBMS-independent means for processing relational database data. • It was developed in the early 1990s by an industry committee and has been implemented by the database vendors. • The goal is to allow a developer to create a single application that can access databases supported by different DBMS products without needing to be changed or recompiled.

9. ODBC Architecture

10. OBDC Components • OBDC consists of • data source • application program • driver manager • DBMS driver. • Adata source is the database and its associated DBMS, operating system, and network platform. • An ODBC data source can be a relational database, a file server, or a spreadsheet. • Anapplications program issues requests to create a connection with a data source.

11. OBDC Components • A driver manager determines the type of DBMS for a given ODBC data source and loads that driver in memory. • A DBMS driver processes ODBC requests and submits specific SQL statements to a given type of data source. • A single-tier driver processes both ODBC calls and SQL statements. • A multiple-tier driver processes ODBC calls, but passes the SQL requests to the database server.

12. ODBC Driver • Provides the standard of data access • ODBC drivers exist for a large variety of data sources. • ODBC operates with a variety of operating systems and drivers exist for non-relational data such as spreadsheets, text and XML files • Because ODBC dates back more than ten years, it offers connectivity to a wider variety of data sources than other data-access APIs • More drivers exist for ODBC than drivers or providers exist for newer APIs such as OLE DB, JDBC and ADO.NET

13. ODBC: Drawbacks • Administering a large number of client machines can involve a diversity of drivers and DLLs • This complexity can increase system administration overhead

14. DLL (Dynamic Link Library) • If two programs call the same routine, that code would be duplicated. Instead, any code which many applications share could be separated into a DLL which only exists as a single disk file and a single instance in memory • DLLs provide the standard benefits of shared libraries (Modularity) • Modularity allows changes to be made to code and data in a single self-contained DLL shared by several applications without any change to the applications themselves • Allows the use of generic interfaces for plug-ins. • A single interface may be developed which allows old as well as new modules to be integrated seamlessly at run-time into pre-existing applications, without any modification to the application itself

15. Microsoft ODBC • Microsoft released the first ODBC product as a set of DLLs for Microsoft Windows • As of 2006 Microsoft ships its own ODBC with every supported version of Windows: Microsoft Data Access Components. (MDAC) • MDAC consists of: • ADO (ActiveX Data Objects) • OLE DB • ODBC (Open Database Connectivity) Also includes some now-deprecated components • Microsoft Jet Database Engine • MSDASQL (OLE DB provider for ODBC)

16. iODBC(Independent Open DataBase Connectivity) • An open source, platform-independent implementation of both the ODBC and X/Open specifications, generally used on platforms other than Microsoft Windows • OpenLink Software maintains and supports the iODBC project and distributes its software under the LGPL and/or BSD License • Apple bundles iODBC into Mac OS X and Darwin, starting with Darwin 6.0 and Mac OS X v10.2 • Note: X/Open is a consortium of UNIX folks, which merged with Open Source Foundation

17. unixODBC • The most common driver-manager for non-Microsoft Windows platforms • Most Linux distributions now ship it, including (for example) Red Hat, Mandriva and Gentoo • IBM (DB2, Informix), Oracle and SAP (Ingres) use it for their own drivers

18. Sources of ODBC drivers • Catalog of ODBC Drivers at SQLSummit.com • ODBC Drivers at unixODBC.org

19. ODBC Driver Types:ODBC Single-Tier Driver

20. ODBC Driver Types:ODBC Multiple-Tier Driver

21. Conformance Levels • Levels of conformance balance the scope of the OBDC standard. • There are two types of conformance levels: • ODBC conformance levels concern the features and functions that are made available through the driver’s application program interface (API). • A driver API is a set of functions that the application can call to receive services. • SQL conformance levels specify which SQL statements, expressions, and data types a driver can process.

22. OBDC Conformance Levels • Core API • Level 1 API • Level 2 API

23. OBDC Conformance Level: Core API • Connect to data sources • Prepare and execute SQL statements • Retrieve data from a result set • Commit or roll back transactions • Retrieve error information

24. OBDC Conformance Level:Level 1 API • Core API • + • Connect to data sources with driver-specific information • Send and receive partial results • Retrieve catalog information • Retrieve information about driver options, capabilities, and functions

25. OBDC Conformance Level:Level 2 API • Core API • Level 1 API • + • Browse possible connections and data sources • Retrieve native form of SQL • Call a translation library • Process a scrollable cursor.

26. OBDC Conformance Level: Minimum SQL Grammar • CREATE TABLE • DROP TABLE • Simple SELECT • INSERT, UPDATE, DELETE • Simple expressions such as • A > B + C • CHAR, VARCHAR, LONGVARCHAR datatypes

27. OBDC Conformance Level: Core SQL Grammar • Minimum SQL Grammar • ALTER TABLE, CREATE INDEX, DROP INDEXES • CREATE VIEW, DROP VIEW • GRANT, REVOKE • Full SELECT, including subqueries • Aggregate functions such as SUM, COUNT, MAX, MIN, AVG • Data types: DECIMAL, NUMBERIC, SMALLINT, INTEGER, REAL, FLOAT, DOUBLE PRECISION

28. OBDC Conformance Level: Extended SQL Grammar • Core SQL Grammar • Outer joins • UPDATE and DELETE using cursor positions • Scalar functions such as SUBSTRING, ABS • Literals for date, time, and timestamp • Batch SQL statements • Stored procedures

29. ODBC Data Sources • A data source is an ODBC data structure that identifies a database and the DBMS that processes it. • Three types of data source names: • A file data source is a file that can be shared among database users having the same DBMS driver and privilege. • A system data source is local to a single computer and may be used by the operating system and any user on that system. • System data sources are recommended for Web servers. • To define a system data source name, the type of driver and the database need to be specified. • A user data source is available only to the user who created it.

30. OLE- Object Linking and Embedding - • A distributed object system and protocol developed by Microsoft • Allows embedding and linking to documents and other objects • OLE allows an editor to "farm out" part of a document to another editor and then reimport it. • For example, a desktop publishing system might send some text to a word processor or a picture to a bitmap editor using OLE. • The main benefit of using OLE, next to reduced file size, is the ability to create a master file. • References to data in this file can be made and the master file can then have changed data which will then take effect in the referenced document.

31. OLE • Applications • Managing compound documents • Transferring data between different applications using drag and drop and clipboard operations • Embedding • Central to much use of multimedia in Web pages, which tend to embed video, animation (include Flash animations), and music files within the HTML code.

32. How OLE works • OLE servers and clients communicate with system libraries using virtual function tables (VTBLs). • The VTBL consists of a structure of function pointers that the system library can use to communicate with the server or client. • OLE 1.0 later evolved to become an architecture for software components known as the component object model (COM), and later DCOM • When an OLE object is placed on the clipboard, it is stored in native Windows formats such as a bitmap, as well as being stored in its own, native format. • This native format allows an OLE-aware application to embed a portion of another document cut or copied to the clipboard by the user by storing it in the current document. • OLE 2.0 was renamed to Active X • Used by Web designers to embed multimedia files in Web pages

33. OLE DB • OLE DB is an implementation of the Microsoft OLE object standard. • OLE DB objects are COM objects and support all required interfaces for such objects. • OLE DB breaks the features and functions of a DBMS into COM objects, making it easier for vendors to implement portions of functionality. • This characteristic overcomes a major disadvantage of ODBC. • With ODBC, a vendor must create an ODBC driver for almost all DBMS features and functions in order to participate in ODBC at all.

34. OLE DB Goals • Create object interfaces for DBMS functionality pieces: • Query, update, transaction management, etc. • Increase flexibility: • Allow data consumers to use only the objects they need. • Allow data providers to expose pieces of DBMS functionality. • Providers can deliver functionality in multiple interfaces. • Interfaces are standardized and extensible. • Provide object interfaces over any type of data: • Relational and non-relational database, ODBC or native, VSAM and other files, Email, etc. • Do not force data to be converted or moved from where it is.

35. OLE DB Basic Constructs • There are data consumers and data providers: • Data consumers - Users of OLE DB functionality. • Data providers - Sources of OLE DB functionality. • An interface is a set of objects and the properties and methods they expose in that interface: • Objects may expose different properties and methods in different interfaces. • An implementation is how an object accomplishes its tasks: • Implementations are hidden from the outside world and may be changed without impacting the users of the objects.

36. COM (Component Object Model) • Introduced by Microsoft in 1993. • Used to enable interprocess communication and dynamic object creation in any programming language that supports the technology. • The term COM is often used in the software development world as an umbrella term that encompasses the OLE, ActiveX, COM+ and DCOM technologies. • Although COM was introduced in 1993, Microsoft did not begin emphasizing the name COM until 1997.

37. COM • Language-neutral way of implementing objects such that they can be used in environments different from the one they were created in, even across machine • Allows reuse of objects with no knowledge of their internal implementation because it forces component implementers to provide well-defined interfaces that are separate from the implementation. The different allocation semantics of languages are accommodated by making objects responsible for their own creation and destruction through reference-counting. • DCOM – Distributed Component Object Model • Deprecated by .NET.

38. COM+ • Significant extension of COM with Windows 2000 • A component, if coded properly, could be reused by new calls to its initializing routine without unloading it from memory. • Components could also be distributed (called from another machine) as was previously only possible with DCOM.

39. Active Data Objects (ADO) • Active Data Objects (ADO) characteristics: • A simple object model for OLE DB data consumers • It can be used from VBScript, JScript, Visual Basic, Java, C#, C++ • It is a single Microsoft data access standard • Data access objects are the same for all types of OLE DB data

40. Invoking ADO from Active Server Pages • In Microsoft’s Active Server Pages (ASP) are Web pages where: • Statements are enclosed within the characters <% . . %>. • ASP statements are processed on the Web server. • Other (HTML) statements are processed by the client Web browser.

41. The ADO Object Model

42. Connection Object • A connection object establishes a connection to a data provider and data source. • Connections have an isolation mode. • Once a connection is created, it can be used to create RecordSet and Command objects.

43. RecordSet Objects • RecordSet objects represent cursors: • They have both CursorType and LockType properties. • RecordSets can be created with SQL statements. • The Fields collection of a RecordSet can be processed to individually manipulate fields. • The Errors collection contains one or more error messages that result from an ADO operation.

44. Command Object • The command object is used to execute stored parameterized queries or stored procedures: • Input data can be sent to the correct ASP using the HTML FORM tag. • Table updates are made using the RecordSet Update method.