Relacyjne Bazy Danych
wykład XII
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.
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.
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
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.
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.
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.
Deklaracja i utworzenie obiektu Recordset Dim rsKlienci As ADODB.Recordset
Set rsKlienci = New ADODB.Recordset
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
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.
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.)
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.
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.
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
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
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
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
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
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.
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.)
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".
Przykład
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection cnn.Provider = "sqloledb"
cnn.Open "Data Source=srv;Initial Catalog=pubs;","sa",""
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
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
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
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
End Sub
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 jedno-
elementową 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ę.
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!
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.
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
cmdChange.Execute Tabela Titles z kolumną Type jest
tabelą w przykładowej bazie danych
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,
31 opr. Lech Banachowski, Jan Wierzbicki
