W wykładzie 12 jest przedstawiona metoda programistycznego dostępu do baz danych. Jest ona niezależna od interfejsu udostępnianego przez MS Access. Umożliwia połączenie z dowolną bazą danych. Jest oparta na predefiniowanych obiektach Connection, Command i RecordSet.
W drugiej części wykładu wspomnimy także o
zastosowaniu ADO w skryptach ASP (ang. Active Server Pages)
będących rozszerzeniami kodu HTML oraz o nowej bibliotece Microsoft do łączenia
z bazami danych - ADO.NET – wersji ADO dla aplikacji
sieciowych Microsoft.
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 obiekt DoCmd jest niedostępny w innych środowiskach programistycznych. Istnieje ogólniejsza i bardziej dopasowana do przetwarzania danych metoda oparta na tzw. programistycznym interfejsie wywołań (ang. call levelinterface), 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:
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.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 RecordSet:
i właściwości:
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 użyciu 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.)
Instrukcję SQL można wykonać w kodzie VBA w różny sposób. Oto możliwości:
Uwaga:
Oto przykłady stosowania instrukcji UPDATE, INSERT i DELETE alternatywnie za pomocą metody Execute obiektu klasy Connection i za pomocą 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 rekordzie (rsProdukty.MoveLast
).
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 RecordSet przejść po wszystkich rekordach ze źródła
rekordów.
Dim cnCurrent As ADODB.Connection
|
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",Provider = MSDASQL
oznaczające "Microsoft OLE DB Provider
for ODBC".Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Provider = "sqloledb"
cnn.Open "Data Source=srv;Initial Catalog=pubs;","sa",""
Przykłady procedur
Zaprezentujemy teraz trzy procedury, w których następuje połączenie poprzez ODBC z odległą bazą danych i wykonanie w niej odpowiednich operacji. Na razie pomijamy jeszcze problem obsługi błędów pochodzących z wykonania instrukcji SQL na bazie danych. Temat ten omówimy za chwilę.
Podnieś zarobki wszystkim pacowników, którzy zarabiają mniej niż 2000
Public Sub SalRise()
|
Wypisz nazwiska wszystkich pracowników
Public Sub Show_Emps()
|
Public Sub EmpHighSal()
Set rsEmps = Nothing
|
Transakcja bazy danych jest 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ę.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 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 za pomocą metod obiektów Connection i RecordSet.
Dim strCnn As String
|
Tabela Titles z kolumną Type jest tabelą w przykładowej
bazie danych SQL Server o nazwie Pubs.
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:
Jako ilustrację zaprogramujemy obsługę błędów dla instrukcji cmdChange.Execute
z ostatniego przykładu.
Dim strCnn As String ..... |
W programie spełniającym funkcję internetowej aplikacji bazodanowej występują instrukcje generowania kodu HTML oraz łączenia się z bazą danych. Najbardziej popularne są tzw. strony serwera będące skryptami języków takich jak PHP, ASP czy JSP. Za chwilę krótko naszkicujemy budowę stron serwera w języku ASP – Active Server Pages - firmy Microsoft. Najpierw przypomnimy podstawowe konstrukcje języka HTML.
W języku HTML definiuje się dokumenty tekstowe uzupełnione o znaczniki określające strukturę dokumentu z punktu widzenia jego prezentacji w oknie przeglądarki internetowej jak np. tytuł, nagłówek czy zawartość strony. Oto przykład
<HTML> <HEAD> <TITLE>Tytuł dokumentu</TITLE> </HEAD> <BODY> Tutaj znajduje się zawartość strony. </BODY> </HTML>Podstawowe znaczniki HTML
Akapit
<P>Tutaj znajduje się zawartość akapitu.</P>
Lista
HTML | Efekt na stronie WWW |
<UL> <LI>Owoce</LI> <LI>Warzywa</LI> <LI>Ryby</LI> <LI>Mięso</LI> <LI>Drób</LI> </UL> |
|
Lista numerowana
HTML | Efekt na stronie WWW |
<OL> <LI>Owoce</LI> <LI>Warzywa</LI> <LI>Ryby</LI> <LI>Mięso</LI> <LI>Drób</LI> </OL> |
|
Tablica
Tablicę definiuje się podając jej strukturę wiersz po
wierszu. Każdy wiersz ujmujemy w znaczniki <TR>...</TR>
natomiast każdy element wiersza w znaczniki
<TD>...</TD>
. Na przykład definicja tablicy:
<TABLE border="1">
<TR><TD>Jeden</TD><TD>One</TD></TR>
<TR><TD>Dwa</TD><TD>Two</TD></TR>
</TABLE
>
daje w wyniku:
Jeden | One |
Dwa | Two |
Atrybut border="1"
określa tabelę z ramką
(domyślnie "0" bez ramki).
Znaczniki formatujące tekst
<B>Nie zapomnij!</B>
Tekst „Nie zapomnij!” ma być wypisany pogrubioną czcionką. Zatem znaczniki <B>
i </B>
mają charakter
prezentacyjny - podobnie jak <I>
i </I>
(italiki)
oraz <U>
i </U>
(podkreślenie).
Odsyłacz
Odsyłacz Odwiedź naszą witrynę definiujemy:
<A href="http://www.pjwstk.edu.pl/">Odwiedź naszą witrynę</A>
Znaczniki <A>
i </A>
definiują odsyłacz (odwołanie
hipertekstowe) do innego dokumentu internetowego. Mają więc charakter metadanych.
Obrazek
Obrazek wstawia się do dokumentu za pomocą pojedynczego znacznika, który nie ma zamykającego odpowiednika.
<IMG SRC=“images/layout.gif” >
Formularz to złożona struktura wyświetlania danych złożona z tekstu, pól tekstowych i przycisków.
HTML | Efekt na stronie WWW |
<FORM METHOD="POST" ACTION="http://xeon/display"> |
Atrybut ACTION elementu FORM określa program w
sieci Internet, który ma być użyty do przetworzenia danych wprowadzonych na
formularzu przez użytkownika.
Język ASP jest bezpośrednim rozszerzeniem języka HTML o fragmenty kodu zapisane w języku Visual Basic. Kod jest umieszczany między znacznikami <% i %>.
Po połączeniu z bazą danych - za pomocą ADO, interpreter załącza dane z bazy danych do dokumentu HTML. W przypadku, gdy razem ze zleceniem zostały otrzymane dane z formularza (lub po prostu zawarte w URL), mogą one być użyte w instrukcjach SQL – zawartych w skrypcie i działających na bazie danych.
Obiekty ASP tworzone przez system
Session("Zestaw_rekordów")
może pamiętać utworzony zestaw rekordów;Request.Form("Nazwisko")
– nazwisko wpisane do pola
Nazwisko formularza;Response.Write("Podaj dane:
")
powoduje dołączenie tekstu do tworzonego dokumentu HTML.Przykład skryptu ASP
Skrypt wyświetlający formularz do wprowadzenia danych osobowych przez użytkownika.
<HTML>
<HEAD>
<TITLE> Dane osobowe </TITLE>
</HEAD>
<BODY>
<CENTER>Proszę wypełnić poniższy formularz:
<FORM METHOD=POST ACTION="r_nowy.asp">
<!-- Pola formularza wycentrowane i wyrównane w tabeli -->
<TABLE>
<TR> <TD>Imię </TD>
<TD> <INPUT TYPE=TEXT NAME="Imie"
SIZE=20></TD></TR>
<TR> <TD> Nazwisko </TD>
<TD> <INPUT TYPE=TEXT NAME="Nazwisko"
SIZE=20> </TD></TR>
<TR> <TD> Telefon </TD>
<TD> <INPUT TYPE=TEXT NAME="Telefon"
SIZE=20> </TD></TR>
<TR> <TD> Adres </TD>
<TD> <INPUT TYPE=TEXT NAME="Adres" SIZE=40> </TD></TR>
</TABLE>
<INPUT TYPE=SUBMIT VALUE="wyslij">
<INPUT TYPE=RESET VALUE="wyczysc">
</FORM>
</CENTER>
</BODY>
</HTML>
Skrypt wprowadzający do bazy danych dane osobowe nadesłane przez użytkownika na formularzu.
<HTML>
<HEAD> <TITLE> Dane osobowe </TITLE> </HEAD>
<BODY>
<CENTER>
<% SET Polaczenie = Server.CreateObject("ADODB.Connection")
Polaczenie.Open("DSN=LocalServer;database=Sklep;UID=Sklep;PWD=xyx#123")
IF (Request.Form("Imie") = "") OR (Request.Form("Nazwisko") = "") THEN
Response.Write("<BR>Jedno z pól nie jest wypełnione<BR>")
ELSE 'Sprawdzenie czy dane o kliencie są już w bazie danych
SET rst = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM Osoba "
SQL = SQL + " WHERE Imie = " & "'" & Request.Form("Imie") & "'"
SQL = SQL + " AND Nazwisko = " & "'" & Request.Form("Nazwisko") & "'"
rst.Open SQL, Polaczenie, 3, 3
IF rst.RecordCount > 0 THEN
Response.Write(" <BR>W bazie jest już klient o takim imieniu i nazwisku.")
ELSE
Response.Write(" <BR>W bazie nie było do tej pory tego klienta.")
SET rstNowy = Server.CreateObject("ADODB.Recordset")
SQLNowy = "INSERT INTO Osoba (Imie, Nazwisko,Adres, Telefon) "
SQLNowy = SQLNowy + " VALUES ('" & Request.Form("Imie")
SQLNowy = SQLNowy + "', '" & Request.Form("Nazwisko")
SQLNowy = SQLNowy + "', '" & Request.Form("Adres")
SQLNowy = SQLNowy + "', '" & Request.Form("Telefon")
SQLNowy = SQLNowy + "')"
rstNowy.Open SQLNowy, Polaczenie, 3, 3
Response.Write(" <BR>Dane zostały wprowadzone do bazy")
END IF
END IF %>
</BODY>
</HTML>
W środowisku ADO.NET do reprezentowania wyniku działania instrukcji SELECT służy obiekt DataSet, którego struktura jest bardziej rozbudowana w porównaniu z odpowiadającym mu w ADO obiektem RecordSet (w szczególności DataSet może zawierać kilka tabel połączonych związkami klucz obcy – klucz główny). Do reprezentowania instrukcji SQL SELECT służy obiekt o nazwie SqlDataAdapter.
Oto jak wygląda ściągnięcie danych z bazy danych do obiektu DataSet:
' ustanowienie połączenia z bazą danych
Dim conn as New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\ASPNET\dane\library.mdb")
' otworzenie połączenia z bazą danych
Dim da As New SqlDataAdapter("select * from Authors", conn)
' wykonanie zapytania i wypełnienie obiektu ds danymi z bazy danych
Dim ds As New DataSet()
da.Fill(ds)
Przykład odczytania danych z bazy danych
Dane z bazy danych zostaną wyświetlone na stronie WWW (ASP.NET) - przykład pochodzi z książki: Chris Payne, ASP.NET dla każdego, Helion, 2002. Dostęp do danych w tabeli jest ułatwiony za pomocą obiektów klasy DataTable (reprezentującego tabelę) oraz DataRow (reprezentującego wiersz).
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load(obj as object, e as eventargs)
dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\ASPNET\dane\banking.mdb")
dim objCmd as new OleDbDataAdapter("select * from tblUżytkownicy", objConn)
dim ds as DataSet = new DataSet()
objCmd.Fill(ds, "tblUżytkownicy")
dim dTable as DataTable = ds.Tables("tblUżytkownicy")
dim AktualneWiersze() as DataRow = dTable.Select(Nothing, _
Nothing, DataViewRowState.CurrentRows)
dim I, J as integer
dim strOutput as string
For I = 0 to AktualneWiersze.Length-1
For J = 0 to dTable.Columns.Count-1
strOutput = strOutput & dTable.Columns(J).ColumnName _
& " = " & AktualneWiersze(I)(J).ToString & "<br>"
next
next
Response.Write(strOutput) ' Wypisz na otwieranej stronie
end sub
</script>
<html>
<body>
<!-- Strona jest tworzona w obiekcie Response!
-->
</body>
</html>
Do wykonywania instrukcji INSERT, UPDATE i DELETE jest używany obiekt klasy OleDbCommand oraz jego metoda ExecuteNonQuery. Przy programowaniu wygodnie jest używać pomocniczej procedury (strSQL to napis instrukcji SQL, Conn - to obiekt klasy OleDbConnection).
sub ExecuteStatement(strSQL as String)
dim objCmd as new OleDbCommand(strSQL, Conn)
try
objCmd.Connection.Open()
objCmd.ExecuteNonQuery()
catch ex as Exception
TekstBłędu = "Wystąpił błąd aktualizacji bazy danych."
end try
objCmd.Connection.Close()
end sub
Zwróćmy uwagę na nowy sposób formułowania wyjątku za
pomocą klauzuli try ... catch ... end try
podobnie w Javie.
Języka programowania przy tworzeniu aplikacji bazy danych używamy do:
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.
Wspomnieliśmy także o zastosowaniu ADO w skryptach ASP będących
rozszerzeniami kodu HTML oraz o nowej bibliotece Microsoft do łączenia z
bazami danych - ADO.NET.
Strona przygotowana przez Lecha Banachowskiego - 06/12/03 .