Bài giảng Hệ quản trị cơ sở dữ liệu - Nguyễn Vượng Thịnh (Phần 2)
Tóm tắt Bài giảng Hệ quản trị cơ sở dữ liệu - Nguyễn Vượng Thịnh (Phần 2): ...ẠO BẢNG BẰNG LỆNH SELECT INTO. Lệnh Select Into truy vấn dữ liệu, dữ liệu được đưa vào một bảng mới. Nếu thuộc tính select into/bulkcopy được đặt có thể tạo bảng cố định, nếu thuộc tính không được đặt ta có thể tạo bảng tạm thời. SELECT Shippers.*, Link.Address, Link.City, Link.Region, Link.Po...stance có tên seattle1, user có tên sa, mật khẩu MyPass. + OPENROWSET với OLE DB Provider for ODBC. USE pubs GO SELECT a.* FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass', pubs.dbo.authors) AS a ORDER BY a.au_lname, a.au_fname GO + Microsoft OLE DB Pr...J] Ví dụ gán quyền thao tác cho role và user: USE pubs GO GRANT SELECT ON authors TO public GO GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom GO Gán quyền thao tác đối tượng. Là việc gán quyền cho các user hoặc role có quyền thao tác với các đối tượng của SQL Server. Ví dụ...
trình phải thực hiện các thao tác sau: + Kết nối đến nguồn dữ liệu (data source). + Gửi câu lệnh SQL đến nguồn dữ liệu. + Xử lý kết quả nhận được từ câu lệnh đã gửi. + Xử lý các lỗi và thông báo. + Ngắt kết nối đến nguồn dữ liệu. Đối với một số ứng dụng phức tạp sử dụng ADO có thể sử dụng một số thao tác sau: + Sử dụng con trỏ (cursor) để điều khiển vị trí trong tập kết quả. + Thực hiện thủ tục lưu trữ trên Server. + Thực hiện hàm tự định nghĩa trên Server. + Quản lý các phép truy vấn mà có nhiều tập kết quả. 215 + Yêu cầu kết thúc hoặc lặp lại một phiên giao dịch. + Quản lý các thao tác với kiểu dữ liệu lớn (text, image). + Thực hiện các thao tác với XML sử dụng phép truy vấn XPath. Kết nối đến SQL Server. Để kết nối đến SQL Server, các công việc cơ bản cần thực hiện như sau: + Cấu hình kết nối. + Thiết lập hoặc ngắt kết nối đến nguồn dữ liệu. + Xác định OLE DB provider. + Thực hiện truy vấn. + Quản lý các phiên làm việc trên kết nối. Khi sử dụng SQLOLEDB ta phải thực hiện đặt các thuộc tính sau cho kết nối: + Initial Catalog: Xác định CSDL. + Data Source: Xác định tên Server. + Integrated Security: Xác định chế độ xác thực, nếu là SSPI chế độ xác thực là Windows Authentication, hoặc xác định User ID, Password của chế độ xác thực SQL Server Authentication. Ví dụ thực hiện kết nối đến SQL Server đặt từng thuộc tính tiêng biệt từ Visual Basic: ' Initialize variables. Dim cn As New ADODB.Connection . . . Dim ServerName As String, DatabaseName As String, _ UserName As String, Password As String ' Put text box values into connection variables. ServerName = txtServerName.Text DatabaseName = txtDatabaseName.Text 216 UserName = txtUserName.Text Password = txtPassword.Text ' Specify the OLE DB provider. cn.Provider = "sqloledb" ' Set SQLOLEDB connection properties. cn.Properties("Data Source").Value = ServerName cn.Properties("Initial Catalog").Value = DatabaseName ' Decision code for login authorization type: ' Windows NT or SQL Server authentication. If optWinNTAuth.Value = True Then cn.Properties("Integrated Security").Value = "SSPI" Else cn.Properties("User ID").Value = UserName cn.Properties("Password").Value = Password End If ' Open the database. cn.Open Ví dụ kết nối đến SQL Server sử dụng chuỗi kết nối: ' Initialize variables. Dim cn As New ADODB.Connection Dim provStr As String ' Specify the OLE DB provider. cn.Provider = "sqloledb" ' Specify connection string on Open method. ProvStr = "Server=MyServer;Database=northwind;Trusted_Connection= yes" cn.Open provStr Ví dụ kết nối sử dụng ODBC: 217 Dim cn As New ADODB.Connection cn.ConnectionTimeout = 100 ' DSN connection. You can use variables for the parameters. cn.Open "MyDataSource", "sa", "MyPassword" ' Alternative syntax follows: ' cn.Open "DSN=DataSourceName;UID=sa;PWD=Password;" cn.Close Ví dụ kết nối xác định Driver của SQL Server: Dim cn As New ADODB.Connection ' Connection to SQL Server without using ODBC data source. cn.Open "Driver={SQL Server};Server=Server1;Uid=SA;Pwd=;Database=northwind" cn.Close Thực hiện truy vấn. Thực hiện truy vấn sử dụng đối tượng Command. cmd.Execute(NumRecords, Parameters, Options) Đối tượng Command có thể thực hiện nhiều kiểu câu lệnh (Select, Update, Insert, Delete, Create, Drop), đối với lệnh Select kết quả thực hiện là một recordset. Set rs = cmd.Execute(NumRecords, Parameters, Options) Kiểu lệnh thực hiện trong Command được xác định theo option của lệnh, gồm một số kiểu sau: Tên kiểu Mô tả adCmdFile Tên file chứa đối tượng recordset 218 adCmdStoreProc Stored procedure adCmdTable Tên bảng adCmdTableDirect Tên bảng mà các cột được truy vấn adCmdText Câu lệnh SQL adCmdUnknown Chưa xác định adCmdUnspecified Chưa xác định tham số cho lệnh Thực hiện truy vấn thông qua đối tượng connection. Dim cn As New ADODB.Connection . . . Dim rs As New ADODB.Recordset cmd1 = txtQuery.Text Set rs = cn.Execute(cmd1) Thực hiện truy vấn có sử dụng tham số. Khi thực hiện các thủ tục có tham số truyền vào các ứng dụng phải truyền tham số, trong phần này sẽ giới thiệu một ví dụ sử dụng đối tượng parameter. Tạo thủ tục: USE NORTHWIND GO drop proc myADOParaProc GO CREATE PROC myADOParaProc @categoryid int(4) AS SELECT * FROM products WHERE categoryid = @categoryid GO Sử dụng đối tượng parameter truyền tham số là số nguyên xác định categoryID: Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim prm As ADODB.Parameter 219 Dim fld As ADODB.Field Dim provStr As String ' Connect using the SQLOLEDB provider. cn.Provider = "sqloledb" ' Specify connection string on Open method. provStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes" cn.Open provStr ' Set up a command object for the stored procedure. Set cmd.ActiveConnection = cn cmd.CommandText = "myADOParaProc" cmd.CommandType = adCmdStoredProc cmd.CommandTimeout = 15 ' Set up a new parameter for the stored procedure. Set prm = Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7) Cmd.Parameters.Append prm ' Create a recordset by executing the command. Set rs = cmd.Execute Set Flds = rs.Fields ' Print the values for all rows in the result set. While (Not rs.EOF) For Each fld in Flds Debug.Print fld.Value Next Debug.Print "" rs.MoveNext Wend ' Close recordset and connection. rs.Close cn.Close Đối tượng Recordset. Sử dụng đối tượng Recordset lưu trữ kết quả của lệnh Select. 220 Dim cn As New ADODB.Connection Dim rs As ADODB.Recordset . . . cmd1 = txtQuery.Text Set rs = New ADODB.Recordset rs.Open cmd1, cn rs.MoveFirst . . . ' Code to loop through result set(s) Đối tượng Field. Sử dụng đối tượng field là các cột của Recordset, thông qua nó ta có thể láy giá trị, thuộc tính của cột. Dim rs As New ADODB.Recordset Dim fld As ADODB.Field Dim cn As ADODB.Connection Dim cmdText As String cn.Provider = "sqloledb" cn.Properties("Data Source").Value = "MyServerName" cn.Properties("Initial Catalog").Value = "northwind" cn.Properties("Integrated Security").Value = "SSPI" cn.Open cmdText = "select * from authors" rs.Open cmdText, cn Set Flds = rs.Fields Dim TotalCount As Integer TotalCount = Flds.Count For Each fld In Flds Debug.Print fld.Name Debug.Print fld.Type Debug.Print fld.Value Next rs.Close Sử dụng con trỏ. 221 Khi sử dụng đối tượng Recordset của ADO, ta có thể sử dụng nhiều kiểu con trỏ khác nhau xác định kiểu khóa, điều khiển vị trí,... Dim rs As New ADODB.Recordset . . . rs.Open "SELECT * FROM titles", , adOpenDynamic, adLockOptimistic rs.Close COn trỏ nói trên gồm những thuộc tính cơ bản sau: CursorType, CursorLocation, LockType, CacheSize. Thuộc tính Mô tả CursorType - adOpenForwardOnly: Ngầm định Xác định kiểu con trỏ được sử dụng: - adOpenForwardOnly: Chỉ đọc, chỉ có thể cập nhật dữ liệu trên hàng dữ liệu hiện thời. - adOpenStatic: Trạng thái tĩnh, khi mở kiểu này hệ thống sẽ cung cấp một ảnh dữ liệu (snapshot), dữ liệu thay đổi trên bảng cơ sở sẽ không được thể hiện trên snapshot dạng này. - adOpenKeyset: Theo vị trí tùy chọn, khi di chuyển hàng cập nhật con trỏ kiểu này sẽ chiếu đến hàng dữ liệu cơ sở, hàng dữ liệu được khó và bạn có thể cập nhật, lấy dữ liệu từ hàng cơ sở. - adOpenDynamic: Động, con trỏ kiểu này gần giống keyset cursor , nhưng con trỏ kiểu này phản ảnh những thay đổi trên bảng cơ sở. CursorLocation -adUseServer: Ngầm định. - adUseClient: Nếu đặt là ta chỉ có thể mở ở trạng thái tĩnh. LockType -adLockReadOnly: Ngầm định. Xác định kiểu khóa trong quá trình cập nhật dữ liệu (adLockPessimistic, adLockOptimistic, adLockBatchOptimistic). CacheSize Ngầm định: 1 Xác định số hàng đặt trong bộ đệm hoặc đọc trong một thời điểm. Các phương thức dịch chuyển hàng dữ liệu. 222 Khi sử dụng đối tượng Recordset bạn có thể dịch chuyển vị trí của hàng dữ liệu bằng các phương thức MoveFirst, MoveLast, MoveNext, MovePrevious. Đánh dấu vị trí theo phương thức Bookmark, phương thức clone để tạo một bản sao recordset. Quản lý phiên làm việc. Trong phần câu lệnh T-SQL ta đã xem xét việc điều khiển một hpiên làm việc (transaction), tuy nhiên ta có thể sử dụng đối tượng connection của ADO để điều khiển trực tiếp phiên làm việc như trong kịch bản lệnh nói trên bằng việc sử dụng các phương thức BeginTrans, CommitTrans, RollbackTrans. Xét ví dụ sau: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset . . . ' Open connection. cn.Open ' Open titles table. rs.Open "SELECT * FROM titles", Cn, adOpenDynamic, adLockPessimistic . . . ' Begin the transaction. rs.MoveFirst cn.BeginTrans ' User loops through the recordset making changes. . . . ' Ask if the user wants to commit all the changes made. If MsgBox("Save all changes?", vbYesNo) = vbYes Then cn.CommitTrans Else cn.RollbackTrans End If Thực hiện các lệnh DDL. Để thực hiện các lệnh DDL như CREATE TABLE, DROP TABLE, ALTER TABLE. Bạn có thể sử dụng đối tượng command của ADO, xét ví dụ sau: Dim Cn As New ADODB.Connection 223 Dim Cmd As New ADODB.Command ' If the ADOTestTable does not exist, go to AdoError. On Error GoTo AdoError ' Connect using the SQLOLEDB provider. cn.Provider = "sqloledb" cn.Properties("Data Source").Value = "MyServerName" cn.Properties("Initial Catalog").Value = "northwind" cn.Properties("Integrated Security").Value = "SSPI" cn.Open ' Set up command object. Set Cmd.ActiveConnection = Cn Cmd.CommandText = "DROP TABLE ADOTestTable" Cmd.CommandType = adCmdText Cmd.Execute Done: Cmd.CommandText = "SET NOCOUNT ON" Cmd.Execute Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))" Cmd.Execute Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')" Cmd.Execute Cn.Close Exit Sub AdoError: Dim errLoop As Error Dim strError As String ' Enumerate Errors collection and display properties of ' each Error object. Set Errs1 = Cn.Errors For Each errLoop In Errs1 Debug.Print errLoop.SQLState Debug.Print errLoop.NativeError Debug.Print errLoop.Description 224 Next GoTo Done End Sub Quản lý dữ liệu kiểu lớn – Text, image. Dữ liệu kiểu text, ntext, image là kiểu dữ liệu phức tạp, việc quản lý, khai thác không được thực hiện thông thường, ADO hỗ trợ các phương thức riêng để thực hiện. Thay vì độc, cập nhật dữ liệu trực tiếp thì dữ liệu kiểu này được thao tác theo đoạn (chunk) bằng cách sử dụng các phương thức AppendChunk, GetChunk. Trước khi thực hiện bạn phải đặt tham số bằng cách thực hiện lệnh sau: EXEC sp_dboption 'pubs', 'Select into/bulkcopy', 'True' Xét ví dụ sau trên CSDL Pubs: - Copy bảng pub_info sang bảng mới USE pubs SELECT * INTO pub_info_x FROM pub_info GO - Thực hiện chèn dữ liệu vào bảng: Public Sub AppendChunkX() Dim cn As ADODB.Connection Dim rstPubInfo As ADODB.Recordset Dim strCn As String Dim strPubID As String Dim strPRInfo As String Dim lngOffset As Long Dim lngLogoSize As Long Dim varLogo As Variant Dim varChunk As Variant Const conChunkSize = 100 225 ' Open a connection. Set cn = New ADODB.Connection strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;" cn.Provider = "sqloledb" cn.Open strCn 'Open the pub_info_x table. Set rstPubInfo = New ADODB.Recordset rstPubInfo.CursorType = adOpenDynamic rstPubInfo.LockType = adLockOptimistic rstPubInfo.Open "pub_info_x", cn, , , adCmdTable 'Prompt for a logo to copy. strMsg = "Available logos are : " & vbCr & vbCr Do While Not rstPubInfo.EOF strMsg = strMsg & rstPubInfo!pub_id & vbCr & _ Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr rstPubInfo.MoveNext Loop strMsg = strMsg & "Enter the ID of a logo to copy:" strPubID = InputBox(strMsg) ' Copy the logo to a variable in chunks. rstPubInfo.Filter = "pub_id = '" & strPubID & "'" lngLogoSize = rstPubInfo!logo.ActualSize Do While lngOffset < lngLogoSize varChunk = rstPubInfo!logo.GetChunk(conChunkSize) varLogo = varLogo & varChunk lngOffset = lngOffset + conChunkSize Loop ' Get data from the user. strPubID = Trim(InputBox("Enter a new pub ID:")) strPRInfo = Trim(InputBox("Enter descriptive text:")) 226 ' Add a new record, copying the logo in chunks. rstPubInfo.AddNew rstPubInfo!pub_id = strPubID rstPubInfo!pr_info = strPRInfo lngOffset = 0 ' Reset offset. Do While lngOffset < lngLogoSize varChunk = LeftB(RightB(varLogo, lngLogoSize - _ lngOffset),conChunkSize) rstPubInfo!logo.AppendChunk varChunk lngOffset = lngOffset + conChunkSize Loop rstPubInfo.Update ' Show the newly added data. MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _ "Description: " & rstPubInfo!pr_info & vbCr & _ "Logo size: " & rstPubInfo!logo.ActualSize rstPubInfo.Close cn.Close End Sub Kết nối từ ASP. Trong ác ví dụ saiu đây thực hiện làm việc với SQL Server từ ASP, sử dụng ngôn ngữ lập trình VBScript, để làm được ví dụ này bạn đọc phải có kiến thức về thiết kế Web site (HTML). Thiết kế form kết nối: Login SQL Server example <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <!-- 227 .style1 { font-size: 24px; font-weight: bold; } --> Login SQL Server User name Password <input name="txtPassword" type="password" id="txtPassword"> Server name <input name="txtServer" type="text" id="txtServer"> <input name="cmdLogin" type="button" id="cmdLogin" value="Login"> sub cmdLogin_Onclick 228 frmlogin.method="post" frmlogin.action="connect.asp" frmlogin.submit end sub Tập tin connect.asp: Là tập tin được gọi từ form login.asp, thực hiện nhận tham số của form login.asp, kết nối đến SQL Server. <% dim username, password, servername, txt username=request.Form("txtUser") password = request.Form("txtPassword") servername=request.Form("txtServer") txt= "Provider=SQLOLEDB; " txt=txt & " Data Source=" & servername & ";" txt=txt & " Initial Catalog=pubs; " txt=txt & " User ID=" & username & ";" txt=txt & " PWD=" & password 229 Set cn=Server.CreateObject("ADODB.Connection") cn.Open txt %> Kết nối thành công <% cn.close %> Liệt kê danh sách. Để liệt kê danh sách (có thể lấy dữ liệu bằng cách truy vấn trực tiếp hoặc thông qua khung nhìn - view) trước hết phải tạo một recordset lưu trữ kết quả truy vấn, từ recordset ta có thể lấy dữ liệu và đặt vào vị trí tương ứng cần thiết. + Khai báo Recordset: Set rs=Server.CreateObject("ADODB.Recordset") rs.ActiveConnection =cn 230 rs.Source ="Select * from Authors" rs.Open + Lấy giá trị: Rs.fields(“au_id”) + Đóng Recordset: Rs.close + Ví dụ liệt kê danh sách bằng cách truy vấn trực tiếp: Danh sach <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <!-- .style5 { font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: 14px; } .style6 {font-family: Arial, Helvetica, sans-serif} .style7 { font-size: 14px; font-weight: bold; } .style9 {font-family: Arial, Helvetica, sans-serif; font-size: 14px; } .style12 {font-size: 12px} --> <% dim username, password, servername, txt username="sa" 231 password = "" servername="TDCong" txt= "Provider=SQLOLEDB; " txt=txt & " Data Source=" & servername & ";" txt=txt & " Initial Catalog=pubs; " txt=txt & " User ID=" & username & ";" txt=txt & " PWD=" & password Set cn=Server.CreateObject("ADODB.Connection") cn.Open txt Set rs=Server.CreateObject("ADODB.Recordset") rs.ActiveConnection =cn rs.Source ="Select * from Authors" rs.Open %> LIST OF AUTHORS <div align="center" class="style5">No <div align="center" class="style6 style7">au_id <div align="center" class="style9">au_lname <div align="center" class="style9">au_fname <div align="center" class="style9">phone <div align="center" class="style9">address <div align="center" class="style9">city <% i=0 do while not rs.eof and not rs.bof i=i+1 if i mod 20 then %> 232 <span class="style12"> <span class="style12"></t d> <span class="style12"> <span class="style12"> <span class="style12"> <span class="style12"> <span class="style12"> <% else %> <span class="style12"> <span class="style12"></t d> <span class="style12"> <span class="style12"> <span class="style12"> <span class="style12"> <span class="style12"> <% end if rs.movenext loop %> 233 <% rs.close %> KẾT NỐI VỚI SQL SERVER BẰNG SQL-DMO. SQL DMO viết tắt của cụm từ SQL Distributed Management Objects, sử dụng thư viện liên kết động (dll) để kết nối đến SQL Server. SQL DMO thực hiện liên kết nhúng (OLE Automation ), các đối tượng SQL Server được thực hiện nhúng các đối tượng của SQL Server vào ứng dụng, khai thác các đối tượng thông qua thuộc tính, sự kiện và các phương thức làm việc của nó. 234 SQL DMO hỗ trợ phát triển ứng dụng từ ngôn ngữ lập trình Visual Basic, C++, khi đóng gói các thư viện liên kết động sẽ được đóng gói cùng, cài dặt ứng dụng thư viện sẽ được cài đặt trong Windows, nên khi chạy ứng dụng bạn không cần thiết lập môi trường Client Connectivity. Các tập tin cơ bản cho SQL DMO: sqldmo.dll, sqldmo80.hlp, sqldmo.rll, sqldmo.h (C++), sqldmoid.h (C++), sqldmo.sql. Trong phần này sẽ giới thiệu kỹ thuật thiết kế ứng dụng từ Visual Basic 6.0. Khai báo thư viện trong project. - Vào menu Project -> References - Chọn Microsoft SQL DMO Object Library -> Ok Khai báo đối tượng. Sau khi thực hiện khai báo thư viện trong project, ta có thể khai báo biến kiểu đối tượng (object) hoặc kiểu đối tượng của SQL DMO. Ví dụ khai báo biến kiểu SQL Server: Dim oSQLServer As SQLDMO.SQLServer Kết nối đến SQL Server. 235 Để kết nối đến SQL Server ta sử dụng phương thức kết nối của đối tượng SQL Server, có 3 tham số Servername, LoginName, Pasword. Dim oSQLServer As SQLDMO.SQLServer Set oSQLServer = New SQLDMO.SQLServer oSQLServer.Connect "ServerName", "LoginName", "Pasword" Thực hiện lại kết nối: Trong nhiều trường hợp bạn muốn ngắt kết nối hiện tại và thực hiện lại kết nối để lấy trạng thái SQL Server hiện thời (tương tự động tác làm tươi – Reresh). oSQLServer.DisConnect oSQLServer.ReConnect Làm việc với các đối tượng. SQL DMO tạo đối tượng kế thừa từ những đối tượng con của nó, ví dụ SQL Server kế thừa từ các đối tượng Database <- Table <- Column, Xác định biến với CSDL: Dim oDatabase as new SQLDMO.Database Set oDatabase = oSQLServer.Databases("Northwind") Lấy danh sách tên các CSDL vào hộp thoại: Dim nDatabase as Integer For nDatabase = 1 to oSQLServer.Databases.Count Combo1.AddItem oSQLServer.Databases(nDatabase).Name Next nDatabase Các đối tượng đều được kế thà từ các đối tượng con, các đối tượng con tạo thành một tập hợp, tập hợp nói trên có thể thực hiện các phương thức Add, Remove,với từng đối tượng. Ví dụ remove bảng khỏi CSDL: oServer.Databases("Northwind").Tables.Remove("Orders", "anne") Thực hiện lệnh SQL: 236 Các đối tượng (SQL Server, Database) có thể thực hiện các lệnh SQL thông qua các phương thức ExecuteImmediate và ExecuteWithResults. Ví dụ thực hiện lệnh thao tác: oSQLServer.ExecuteImmediate “Create Database Example” Ví dụ thực hiện lệnh truy vấn: Dim rs As QueryResults Set rs = oDatabase.ExecuteWithResults("Select * from Authors") Ví dụ lấy dữ liệu từ một truy vấn: For i = 1 To rs.Rows For j = 1 To rs.Columns MsgBox rs.GetColumnString(i, j) Next j Next i Các phương thức thực hiện kết nối có thể hỗ trợ theo từng ngôn ngữ lập trình, hỗ trợ nhiều trong việc lập trình từ Visual Basic, ASP, C, C++. Bạn có thể tham khảo các ví dụ trong thư mục Sample của SQL Server. Các ví dụ sẽ đề cập nhiều ngôn ngữ lập trình, nhiều sự kiện khác nhau.
File đính kèm:
- bai_giang_he_quan_tri_co_so_du_lieu_nguyen_vuong_thinh_phan.pdf