1 / 72

IT 390 Business Database Administration

Unit 9: Database Access Standards. IT 390 Business Database Administration. Objectives. Identify and classify ODBC, OLE DB, and ASP standards. Introduce database access standards. Explain ODBC, OLE DB and ASP standards. Define and apply basic XML and ADO .NET Explain XML and ADO.NET.

adeola
Download Presentation

IT 390 Business Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Unit 9: Database Access Standards IT 390 Business Database Administration

  2. Objectives • Identify and classify ODBC, OLE DB, and ASP standards. • Introduce database access standards. • Explain ODBC, OLE DB and ASP standards. • Define and apply basic XML and ADO .NET • Explain XML and ADO.NET

  3. ODBC Introduction • You need interfaces to enable applications interact and exchange data with databases. • ODBC is a Microsoft specification for creating a database application programming interface (API). The ODBC API is implemented in the form of drivers.

  4. The ODBC works as an interface between an RDBMS and Application Program. This setup works in a three-tier architecture. SQL Server 2000 connects to development modules using specific API drivers. More about ODBC

  5. The components of ODBC architecture enable the ODBC API to communicate with a database. These components interact with each other to make the database accessible to the user. ODBC Architecture

  6. API ODBC SQL Server 2000 ODBC API ODBC Visual Overview

  7. Enables applications to communicate with different databases Facilitates SQL grammar conversion Enables a single application to use different drivers Enables applications to interact with different types of databases Allows the use of enhanced DBMS features Enables applications to achieve a three-layered architecture Functions of ODBC

  8. Components of ODBC There are four components of the ODBC architecture that interact with each other to to make the required output available to the user. • Application: Applications are the programs that call the ODBC API to communicate with the DBMS.

  9. Components of ODBC Driver manager: It is the component that enables communication between an application and a driver by monitoring the transactions between them. A driver manager also performs basic error- handling tasks after a connection is established between the application and the driver.

  10. Components of ODBC (cont.) • Drivers: Drivers are software components that provide functions in the ODBC API. • Data source: A data source stores all the information that is necessary to connect to a database or a DBMS that is stored on the same computer as the client computer or on another computer on the network.

  11. ODBC is NOT Microsoft exclusively

  12. Setting Up an ODBC Data Source • You use the ODBC Data Source Administrator dialog box to create a DSN. The ODBC Data Source Administrator function is available in Control Panel in the Windows 98 operating system. In Windows 2000, the ODBC Data Source Administrator function is available under Administrative Tools. • To set up an ODBC Data Source: • Double-click the Data Sources icon in the Control Panel.

  13. Setting Up an ODBC Data Source (cont.) Then, on the ODBC Data Source Administrator screen, click the System DSN tab.

  14. Setting Up an ODBC Data Source (cont.) In the System DSN tabbed page, click the Add button.

  15. Setting Up an ODBC Data Source (cont.) Clicking the Add button displays the Create New Data Source dialog box. In this dialog box, select the SQL Server option, and click the Finish button.

  16. Setting Up an ODBC Data Source (cont.) The Create a New Data Source to SQL Server wizard appears. In the first screen of the Create New Data Source Wizard, specify the name for a data source, a description for the data source, and the name of the SQL Server that you want the DSN to connect.

  17. Setting Up an ODBC Data Source (cont.) On the next screen, click the With SQL Server authentication using a login ID and password entered by the user option. Then, specify a login ID and password.

  18. Setting Up an ODBC Data Source (cont.) After specifying the login ID and password for SQL authentication, select the Change the default database to option, and select the name of the database from the drop-down list. Accept the default selections for other options, and then click the Next button.

  19. Setting Up an ODBC Data Source (cont.) On the next screen of the wizard, accept all the default selections, and then click the Finish button.

  20. Setting Up an ODBC Data Source (cont.) You can see the data source name included in the list of other data sources on the System DSN tabbed page.

  21. Using an ODBC Data Source To connect to the database, you need a front-end application. In addition, you need to call the following functions in the code of the front-end application. • Call the SQLAllocEnv or SQLAllocHandle function to get an environment handle. • Call the SQLAllocConnect function. • Call the SQLConnect function. • Call the SQLExecDirect function to execute the SQL statements. • Call the SQLFreestmt function to close the connection.

  22. The functions of ODBC are: 1) Enables applications to communicate with only SQL Server 2000. 2) Enables a single application to use different drivers. State whether True or False

  23. 1) False 2) True Solution (True or False)

  24. Choose the correct ODBC components from the following list: Application File system Driver manager Driver Operating system Data source Class Activity

  25. Application Driver manager Driver Data source Solution

  26. OLE DB and its Components • OLE DB consists of three components: • Data providers • Data consumers • Service components

  27. An ADO object model uses object-oriented programming techniques to communicate with the underlying data source. ADO creates a layer between the applications and the OLE DB provider. • The ADO object model consists of nine objects and four collections.

  28. The ADO Object Model (cont.) The nine objects are: • Connection • Error • Command • Recordset • Record • Parameter • Field • Property • Stream

  29. The ADO Object Model (cont.) The four collections are: • Fields • Properties • Parameters • Errors

  30. Accessing Databases by Using ADO The generic steps to access databases using ADO are: • Set up reference to an ADO Connection object. • Define the connection string to be used when opening the connection object. The connection string can be defined in two ways, with a DSN or without a DSN. • Open the connection object by using the Open method of the connection object. • Use the state property of the connection object to check whether or not a connection is established successfully.

  31. Accessing Databases Using ADO (cont.) • Execute SQL statements after the connection is established. • Create a recordset object and execute it by using the Open method of the recordset object. • Close the recordset object. In addition, close the connection object by calling the Close method of the connection object.

  32. 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. Active Data Objects (ADO)

  33. 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. Invoking ADO from Active Server Pages

  34. The ADO Object Model

  35. 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. Connection Object

  36. 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. RecordSet Objects

  37. 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. Command Object

  38. ADO Constants:Isolation Levels

  39. ADO Constants:Cursor Levels

  40. ADO Constants:Lock Types

  41. <% Dim objConn Set objConn = Server.CreateObject (“ADODB.connection”) objConn.IsolationLevel = adXactReadCommitted ‘ use ADOVBS objConn.Open “ViewRidgeSS”, %> <!--#include virtual =“ADOExamples/ADOVBS.inc --> <% objConn.Open “DSN=ViewRidgeOracle2;UID=DK1;PWD=Sesame” %> Connection Object:ASP Code

  42. <% Dim objRecordSet, varSql varSQL = “SELECT * FROM ARTIST” Set objRecordSet = Server.CreateObject(“ADODB.Recordset”) objRecordSet.CursorTye = adOpenStatic objRecordSet.LockType = adLockReadOnly objRecordSet.Open varSQL, objConn %> RecordSet Object:ASP Code

  43. <% Dim varI, varNumCols, objField varNumCols = objRecordSet.Fields.Count For varI = 0 to varNumCols - 1 Set objField = objRecordSet.Fields(varI) ‘ objField.Name now has the name of the field ‘ objField.Value now has the value of the field ‘ can do something with them here Next >% Fields Collection:ASP Code

  44. <% Dim varI, varErrorCount, objError On Error Resume Next varErrorCount = objConn.Errors.Count If varErrorCount > 0 Then For varI = 0 to varErrorCount - 1 Set objError = objConn.Errors(varI) ‘ objError.Description contains ‘ a description of the error Next End If >% Errors Collection:ASP Code

  45. <% Dim objCommand, objParam, objRs ‘Create the Command object, connect it to objConn and set its format Set objCommand = Server.CreateObject(“ADODB.command”) Set objCommand.ActiveConnection = objConn objCommand.CommandText=“{call FindArtist (?)}” ‘Set up the parameter with the necessary value Set objParam = objCommand.CreateParameter (“Nationality”, adChar, adParamInput, 25) objCommand.Parameters.Append objParam objParam.Value = “Spanish” ‘Fire the Stored Proc Set objRs = objCommand.Execute >% Command Object:ASP Code

  46. ADO Example: Reading a TableArtist.asp

  47. ADO Example: Reading a TableThe Artist.asp Results

  48. Identify the objects in the ADO Model: Connection Error Recordset File Attribute Stream Class Activity

More Related