Bazy Danych wykład II

Relacyjne Bazy Danych
wykład XII
1
opr. Lech Banachowski, Jan Wierzbicki
ADO - programistyczny interfejs do
bazy danych
Co to są obiekty ADO (ActiveX Data Objects)?
Przy układaniu aplikacji bazodanowej często zachodzi
potrzeba wykonania ciągu operacji na bazie danych bez
interwencji użytkownika. Jedna możliwość to zastosować
ciąg wywołań metody RunSQL na obiekcie DoCmd.
Ma to swoje wady. Nie daje możliwości przetwarzania
danych w trakcie ich odczytywania wiersz po wierszu.
Po drugie, używany jest obiekt DoCmd niedostępny w
innych środowiskach programistycznych.
2
opr. Lech Banachowski, Jan Wierzbicki
Istnieje ogólniejsza i bardziej dopasowana do
przetwarzania danych metoda oparta na tzw.
programistycznym interfejsie wywołań, którego zasady są
zawarte w standardzie języka SQL. Na tym wykładzie
przedstawimy jeden taki interfejs o nazwie ADO (ActiveX
Data Objects) - przygotowany przez firmę Microsoft,
a na następnym wykładzie drugi taki interfejs o nazwie
JDBC - przygotowany przez firmę Sun.
3
opr. Lech Banachowski, Jan Wierzbicki
Model programowania ADO określa grupę obiektów i ich
metod, które umożliwiają uzyskanie dostępu i aktualizację
różnego rodzaju źródeł danych w tym baz danych. Oto jego
zasady:
•Połączenie ze źródłem danych odbywa się przy użyciu obiektu
klasy Connection.
•Określa się instrukcję SQL na źródle danych.
•Wykonuje się instrukcję SQL. Jeśli jest nią SELECT, zapisuje
się wynikowe wiersze w obiekcie klasy RecordSet w celu ich
przejrzenia i aktualizacji.
•Jeśli trzeba, aktualizuje się źródło danych przy pomocy metod
obiektu RecordSet.
•Ewentualnie, następuje wykrycie błędów podczas połączenia i
wykonywania polecenia.
4
opr. Lech Banachowski, Jan Wierzbicki
Podstawowe obiekty modelu ADO
•Connection - korzeń w hierarchii obiektów ADO, używany
przy dokonywaniu połączenia aplikacji ze źródłem danych.
•Recordset - reprezentuje zbiór rekordów przekazywanych
ze źródła danych. Jest używany do przetwarzania
rekordów w bazie danych. Używając tego obiektu można
nawigować po zbiorze rekordów, modyfikować istniejące
rekordy, dodawać nowe rekordy i usuwać wskazane
rekordy. W danej chwili dostęp jest tylko do jednego
rekordu nazywanego bieżącym rekordem.
•Command - reprezentuje instrukcję SQL.
•Error - reprezentuje błąd ADO.
5
opr. Lech Banachowski, Jan Wierzbicki
Obiekty Connection i RecordSet
Uzyskanie połączenia z bieżącą bazą danych MS Access
Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection
Poprzez obiekt cnCurrent i instrukcje języka SQL uzyskujemy
dostęp do wszystkich danych zapisanych w bieżącej bazie
danych MS Access.
Będziemy używać bazy danych MS Access z przykładowymi
tabelami: Klienci o kolumnach IDKlienta i Nazwisko oraz
Towary o kolumnie IDTowaru.
6
opr. Lech Banachowski, Jan Wierzbicki
7
Uzyskanie połączenia z odległą bazą danych przy pomocy
ODBC
Dim cnCurrent As ADODB.Connection
Set cnCurrent = New ADODB.Connection
cnCurrent.ConnectionString =
"DSN=scott;UID=scott;PWD=tiger;"
cnCurrent.Open
Będziemy używać bazy danych Oracle z jej przykładowym
użytkownikiem o identyfikatorze scott i haśle tiger oraz
przykładowej tabeli zakładanej na koncie użytkownika scott o
nazwie Emp. Skorzystamy z kolumn Ename oraz Sal tej
tabeli. Zakładamy, że mamy określone połączenie ODBC z tą
bazą danych DSN=scott. Oczywiście fakt, że jest to baza
danych firmy Oracle nie jest istotny. W taki sam sposób
używalibyśmy bazy danych każdej innej firmy.
opr. Lech Banachowski, Jan Wierzbicki
Deklaracja i utworzenie obiektu Recordset
Dim rsKlienci As ADODB.Recordset
Set rsKlienci = New ADODB.Recordset
8
opr. Lech Banachowski, Jan Wierzbicki
Otwarcie zestawu rekordów dla tabeli Klienci
rsKlienci.Open "Klienci", cnCurrent
Open jest metodą obiektu klasy RecordSet.
Zamknięcie i usunięcie z pamięci obiektów Connection i
Recordset
rsKlienci.Close
cnCurrent.Close
Set rsKlienci = Nothing
Set cnCurrent = Nothing
9
opr. Lech Banachowski, Jan Wierzbicki
Odwołanie do pola rekordu w zestawie rekordów
MsgBox rsKlienci!Nazwisko
Symbol '!' sygnalizuje wybór z kolekcji pól (Fields).
Nawigacja po zbiorze rekordów w zestawie rekordów - metody
obiektu klasy RecordSet:
•MoveFirst
•MoveLast
•MoveNext
•MovePrevious
i właściwości:
•BOF - pozycja bieżącego rekordu jest przed pierwszym
rekordem,
•EOF - pozycja bieżącego rekordu jest po ostatnim rekordzie.
10
opr. Lech Banachowski, Jan Wierzbicki
11
Przeglądanie wszystkich rekordów w pętli:
Do Until rsKlienci.EOF
MsgBox rsKlienci!Nazwisko
rsKlienci.MoveNext
Loop
Otwarcie zestawu rekordów na obiekcie klasy RecordSet
przy pomocy instrukcji SELECT
rsKlienci.Open "SELECT * FROM Klienci", cnCurrent
lub
strSQL = "SELECT * FROM Klienci " & _
"WHERE Nazwisko = '" &
Forms!Klienci!txtNazwisko.Value & "'"
rsKlienci.Open strSQL, cnCurrent
(Zakładamy, że txtNazwisko i txtIDKlienta są polami
tekstowymi na otwartym formularzu MS Access Klienci.)
opr. Lech Banachowski, Jan Wierzbicki
Wykonywanie instrukcji SQL
Instrukcję SQL można wykonać w kodzie VBA w różny sposób.
Oto możliwości:
•opisana na poprzednich wykładach metoda RunSQL obiektu
DoCmd; jej stosowanie jest ograniczone do tabel MS Access (w
tym załączonych z innych baz danych),
•metoda Execute obiektu Connection,
•metoda Execute obiektu Command,
•metoda Open obiektu Recordset.
12
opr. Lech Banachowski, Jan Wierzbicki
Uwaga:
•W przypadku instrukcji SELECT metoda Execute w
obiektach Connection i Command zwraca zestaw rekordów
tylko-do-odczytu.
•Metodę Open obiektu RecordSet można stosować także do
wykonywania instrukcji INSERT, UPDATE i DELETE.
13
opr. Lech Banachowski, Jan Wierzbicki
Oto przykłady stosowania instrukcji UPDATE, INSERT i
DELETE alternatywnie przy pomocy metody Execute
obiektu klasy Connection i przy pomocy metod obiektu
klasy RecordSet:
Dla instrukcji UPDATE:
strSQL = "UPDATE Klienci " SET Nazwisko = '" & _
txtNazwisko.Value & "'" & _
"WHERE IDKlienta = '" & txtIDKlienta.Value & "'"
cnCurrent.Execute strSQL
lub po ustawieniu się na odpowiednim rekordzie w obiekcie
rsKlienci:
rsKlienci!Nazwisko = InputBox("Podaj nazwisko:")
rsKlienci.Update
14
opr. Lech Banachowski, Jan Wierzbicki
Podobnie dla instrukcji INSERT:
strSQL = "INSERT INTO Klienci(IDKlienta, Nazwisko)_
VALUES (' _
& txtIDKlienta.Value & "','" _
& txtNazwisko.Value & "')"
cnCurrent.Execute strSQL
lub
rsKlienci.AddNew
rsKlienci!IDKlienta = txtIDKlienta.Value
rsKlienci!Nazwisko = InputBox("Podaj nazwisko:")
rsKlienci.Update
15
opr. Lech Banachowski, Jan Wierzbicki
Dla DELETE:
strSQL = "DELETE * FROM Produkty " & _
"WHERE IDProduktu = " & txtIDProduktu.Value
cnCurrent.Execute strSQL
lub po ustawieniu się na odpowiednim rekordzie w obiekcie
rsProdukty:
rsProdukty.Delete
rsProdukty.MoveNext
If rsProdukty.EOF Then
rsProdukty.MoveLast
End If
Zwróćmy uwagę, że w ostatnim
przykładzie konieczne jest
przesunięcie wskaźnika bieżącego
rekordu (rsProdukty.MoveNext) za
usunięty rekord. Ponadto w
przypadku wyjścia poza ostatni
rekord, ustawiamy wskaźnik
bieżącego rekordu na ostatnim
rekordzie (rsProdukty.MoveLast).
16
opr. Lech Banachowski, Jan Wierzbicki
Przykład
Załóżmy, że chcemy w tabeli Pracownicy zmienić zawód
Sprzedawca na Księgowy. Najprostsze rozwiązanie to użyć
instrukcji SQL (w metodzie RunSQL lub Execute):
UPDATE Pracownicy
SET Tytuł = "Księgowy"
WHERE Tytuł = "Sprzedawca";
Natomiast używając języka programowania, moglibyśmy napisać
instrukcję iteracji sprowadzającą kolejne rekordy z tabeli
Pracownicy. W przypadku napotkania wartości Sprzedawca
zmienilibyśmy ją na Księgowy. Dostęp do kolejnych rekordów
tabeli (a także rekordów kwerend i formularzy) umożliwia
konstrukcja zestawu rekordów. Stosując ją należy na samym
początku utworzyć zestaw rekordów dla źródła rekordów, a
następnie używając metod MoveFirst i MoveNext obiektu
17
RecordSet przejść po wszystkich rekordach ze źródła
rekordów.
opr. Lech Banachowski,
Jan Wierzbicki
Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection
Dim rsPracownicy As ADODB.Recordset
Set rsPracownicy = New ADODB.Recordset
rsPracownicy.Open "Pracownicy", cnCurrent
rsPracownicy.MoveFirst
Do Until rsPracownicy.EOF
If rsPracownicy!Tytuł = "Sprzedawca" Then
rsPracownicy!Tytuł = "Księgowy"
rsPracownicy.Update 'Zapisanie zmian
End If
rsPracownicy.MoveNext
Loop
rsPracownicy.Close
Set rsPracownicy = Nothing
18
opr. Lech Banachowski, Jan Wierzbicki
19
Uwaga: Nie każde źródło danych umożliwia ten typ
modyfikacji bazy danych!
Jest możliwość przeglądania rekordów zgodnie z
porządkiem określonym przez pewien, uprzednio założony
na tabeli indeks. Gdyby na kolumnie Nazwisko był założony
indeks, moglibyśmy przeglądać rekordy zgodnie z
porządkiem określonym przez ten indeks, kładąc przed
instrukcją
rsPracownicy.MoveFirst
instrukcję:
rsPracownicy.Index = "Nazwisko"
Podstawą użyteczności modelu ADO jest niezależność
modelu programistycznego od źródła danych. Wystarczy
tylko odpowiednio określić napis połączenia
ConnectionString a następnie korzystać z jednolitego
kodu.
opr. Lech Banachowski,
Jan Wierzbicki
Biblioteki ADO można użyć wszędzie tam gdzie można użyć
kodu Visual Basic np. w niezależnym programie klienckim
napisanym w języku Visual Basic lub skrypcie ASP – Active
Server Pages – w aplikacji internetowej.
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=Pubs;UID=sa;PWD=sss;"
cnn.Open
(Pubs jest przykładową bazą danych SQL Server z użytkownikiem
sa.)
20
opr. Lech Banachowski, Jan Wierzbicki
Jeśli w napisie połączenia załączymy parametr Provider
możemy w napisie ConnectionString użyć dodatkowych
parametrów zdefiniowanych przez tego szczególnego
"dostawcę danych".
•Provider = SQLOLEDB oznacza "Microsoft OLE DB
Provider for SQL Server",
•Provider = MSDAORA oznacza "Microsoft OLE DB
Provider for Oracle",
•Domyślne ustawienie to Provider = MSDASQL
oznaczające "Microsoft OLE DB Provider for ODBC".
21
opr. Lech Banachowski, Jan Wierzbicki
Przykład
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Provider = "sqloledb"
cnn.Open "Data Source=srv;Initial Catalog=pubs;","sa",""
22
opr. Lech Banachowski, Jan Wierzbicki
Podnieś zarobki wszystkich pracowników, którzy zarabiają mniej
niż 2000.
Public Sub SalRise()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=scott;UID=scott;PWD=tiger;"
cnn.Open
Dim strSQL As String
strSQL = "UPDATE Emp SET Sal=Sal*1.1 WHERE Sal < 2000"
cnn.Execute strSQL
cnn.Close
Set cnn = Nothing
End Sub
23
opr. Lech Banachowski, Jan Wierzbicki
Wypisz nazwiska wszystkich pracowników.
Public Sub Show_Emps()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=scott;UID=scott;PWD=tiger;"
cnn.Open
24
Dim rsEmps As ADODB.Recordset
Set rsEmps = New ADODB.Recordset
rsEmps.Open "Emp", cnn
rsEmps.MoveFirst
Do Until rsEmps.EOF
MsgBox rsEmps!Ename
rsEmps.MoveNext
Loop
rsEmps.Close
cnn.Close
Set rsEmps = Nothing
Set cnn = Nothing
End Sub
opr. Lech Banachowski, Jan Wierzbicki
Wyznacz pracownika, który zarabia najwięcej. Załącz do wyniku jego
zarobki.
Public Sub EmpHighSal()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=scott;UID=scott;PWD=tiger;"
cnn.Open
Dim rsEmps As ADODB.Recordset
Set rsEmps = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT Ename, Sal FROM Emp WHERE Sal =(SELECT Max(Sal) FROM Emp)"
rsEmps.Open strSQL, cnn
rsEmps.MoveFirst
Do Until rsEmps.EOF
MsgBox rsEmps!Ename & " Sal = " & rsEmps!Sal
rsEmps.MoveNext
Loop
rsEmps.Close
cnn.Close
Set rsEmps = Nothing
Set cnn = Nothing
25 End Sub
opr. Lech Banachowski, Jan Wierzbicki
Transakcje na bazie danych
Transakcja bazy danych to ciąg instrukcji INSERT, DELETE i
UPDATE traktowany jako niepodzielna całość według zasady
albo wszystkie instrukcje są wykonywane albo żadna.
Wymagane jest explicite rozpoczęcie transakcji, ponieważ
domyślnie w ADO każda instrukcja SQL stanowi jednoelementową transakcję kończącą się automatycznym
zatwierdzeniem (auto-commit).
Za pomocą metod obiektu Connection:
BeginTrans – rozpoczyna nową transakcję.
CommitTrans – zapisuje zmiany i kończy aktualną transakcję.
RollbackTrans – kasuje zmiany dokonane w trakcie transakcji
i kończy aktualną transakcję.
26
opr. Lech Banachowski, Jan Wierzbicki
Transakcje mogą być zagnieżdżone.
Składnia (gdzie obiekt oznacza obiekt klasy Connection)
poziom = obiekt.BeginTrans() – zwraca poziom
zagnieżdżenia transakcji
obiekt.BeginTrans
obiekt.CommitTrans
obiekt.RollbackTrans
Ten sam efekt można uzyskać przy pomocy instrukcji SQL
wykonywanych przez metodę Execute:
•BEGIN TRANSACTION
•COMMIT
•ROLLBACK
Uwaga: Nie każdy "dostawca danych" realizuje transakcje!
27
opr. Lech Banachowski, Jan Wierzbicki
Obiekt Command
Obiekt Command jest reprezentacją instrukcji SQL w celu
jej wykonania na źródle danych.
Obiekt Command jest istotny w przypadkach gdy trzeba
powtórnie wykonać tę samą instrukcję bądź, gdy wygodnie
jest w aplikacji używać parametrów (których tutaj nie
będziemy omawiać). Pokazaliśmy uprzednio jak wykonywać
instrukcje SQL przy pomocy metod obiektów Connection i
RecordSet.
28
opr. Lech Banachowski, Jan Wierzbicki
Dim strCnn As String
strCnn = "Provider=sqloledb;Data Source=srv;" & _
"Initial Catalog=pubs;User Id=sa;Password=;"
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open strCnn
Dim cmdChange As ADODB.Command 'Tworzenie obiektu
typu Command:
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn
Dim strSQL As String
strSQL = "UPDATE Titles SET Type = 'Informatyka' " & _
"WHERE Type = 'Computer Science'"
cmdChange.CommandText = strSQL
Tabela Titles z kolumną Type jest
cmdChange.Execute
tabelą w przykładowej bazie danych
29
SQL Server o nazwie
Pubs.
opr. Lech
Banachowski, Jan Wierzbicki
30
Użycie kolekcji Errors i obiektu Error
Każda operacja dotycząca obiektów ADO może spowodować
wystąpienie jednego lub więcej błędów, zgłaszanych przez
dostawcę danych w wyniku wykonywania instrukcji SQL.
Każdy błąd jest reprezentowany przez osobny obiekt klasy
Error w kolekcji Errors. Gdy kolejna instrukcja generuje błędy
- poprzednie obiekty kolekcji Errors są kasowane. Oprócz
błędów zgłaszanych przez dostawcę danych mogą wystąpić
błędy ADO w ramach realizacji kodu w VBA – są one
standardowo zapisywane w obiekcie Err omawianym na
poprzednich wykładach.
Obiekt typu Error zawiera:
1. właściwość Description – tekst opisujący błąd,
2. właściwość Number – numer błędu,
3. właściwość Source – identyfikacja obiektu, który podniósł błąd,
4. właściwości SQLState i NativeError – dostarczające informacji
opr. Lech Banachowski, Jan Wierzbicki
ze źródeł danych SQL.
Zaprogramujemy obsługę błędów dla instrukcji
cmdChange.Execute z ostatniego przykładu.
Dim strCnn As String
strCnn = "Provider=sqloledb;Data Source=srv;" & _
"Initial Catalog=pubs;User Id=sa;Password=;"
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open strCnn
Dim cmdChange As ADODB.Command 'Tworzenie
obiektu typu Command:
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn
Dim strSQL As String
strSQL = "UPDATE Titles SET Type = 'Informatyka' " & _
"WHERE Type = 'Computer Science'"
cmdChange.CommandText = strSQL
31
opr. Lech Banachowski, Jan Wierzbicki
On Error GoTo Err_Execute
cmdChange.Execute
......
Err_Execute: 'Powiadom użytkownika o błędach
pochodzących z wykonania instrukcji SQL.
Dim errLoop As ADODB.Error
If cnn.Errors.Count > 0 Then
For Each errLoop In cnn.Errors
MsgBox "Numer błędu: " & errLoop.Number & vbCr &
errLoop.Description
Next errLoop
End If
Resume Next
.....
32
opr. Lech Banachowski, Jan Wierzbicki
Podsumowanie
Języka programowania przy tworzeniu aplikacji bazy danych
używamy do:
•przetwarzania danych wymagającego iteracji i podejmowania
wyborów;
•sprawdzania poprawności danych, ich poprawiania i diagnostyki
błędów;
•odpowiedniej reakcji na zaistniałe błędy, do ich diagnostyki i
dynamicznego poprawiania;
•współpracy z innymi programami systemu Windows jak i z
odległymi bazami danych;
•wielokrotnego użycia tego samego kodu.
33
W wykładzie 12 przedstawiliśmy metodę programowania dostępu
do baz danych przy użyciu biblioteki ADO opartej na obiektach
Connection, RecordSet, Command i Error oraz na ich
właściwościach i metodach.
opr. Lech Banachowski, Jan Wierzbicki
34
Recordset - obiekt reprezentujący cały zbiór rekordów z tabeli w
bazie danych lub z wyniku zapytania na tabelach bazy danych. W
danej chwili dostęp jest tylko do jednego rekordu nazywanego
bieżącym rekordem.
Connection - obiekt - korzeń w hierarchii obiektów ADO, używany
przy dokonywaniu połączenia między aplikacją i źródłem danych.
Command - obiekt reprezentujący instrukcję SQL do wykonania na
bazie danych.
transakcja - ciąg instrukcji INSERT, DELETE i UPDATE
traktowany jako niepodzielna całość według zasady albo wszystkie
instrukcje są wykonywane albo żadna. Instrukcja COMMIT (metoda
CommitTrans obiektu klasy Connection) kończy transakcję
zatwierdzeniem dokonanych zmian; instrukcja ROLLBACK
(metoda RollbackTrans obiektu klasy Connection) kończy transakcję
wycofaniem dokonanych zmian.
Error - obiekt reprezentujący błąd przy wykonywaniu instrukcji SQL
na bazie danych.
opr. Lech Banachowski, Jan Wierzbicki
Koniec Wykładu XII
35
opr. Lech Banachowski, Jan Wierzbicki