| |
1. Dostęp do danych.
W tym rozdziale zajmiemy się wykorzystaniem baz danych do przechowywania informacji zebranych na stronie WWW. Zostanie zaprezentowana metoda połączenia się z bazą danych poprzez ADO (ActiveX Data Object), które zapewnia połączenie ASP z bazą danych. ASP umożliwia połączenie się z każdą bazą danych mającą dostęp do OLE DB (Object Linking and Embedding Database) lub ODBC (Open Database Connectivity). Przykłady będą zawierały połączenia z bazami danych utworzonych w MS Access oraz SQL Server.
ADO jest zbiorem interfejsów (poziom aplikacji), które umożliwiają dostęp do danych OLE DB przy użyciu dowolnego języka programowania, także VBScript. ADO jest bardzo łatwe w użyciu, zapewnia szybki dostęp do danych przy niewielkich zapotrzebowaniach pamięci.
OLE DB jest zbiorem interfejsów na poziomie systemu, umożliwiających dostęp do różnych danych i źródeł danych umieszczonych w różnych miejscach.
ODBC stanowi interfejs umożliwiający dostęp aplikacjom do danych, tj. bazy danych, pliki tekstowe, itd. Poprzez ODBC można podłączyć się do baz danych MS Access, MS SQL Server, dBase, Oracle, DB2 i wielu innych.
ADO posiada wiele obiektów, które umożliwiają podłączenie do bazy danych i operowanie na danych w nich zawartych. Oto główne z nich:
| Connection |
- |
stanowi połączenie z źródłem danych |
| Recordset |
- |
zawiera rekordy zwrócone po wykonaniu zapytania na bazie danych |
| Field |
- |
zawiera dane z pojedynczej kolumny i informacje na temat tych danych; obiekt Recordset zawiera grupę Fields, która z kolei zawiera wszystkie obiekty Field |
| Property |
- |
|
| Error |
- |
zawiera rozszerzoną informację o zwracanych błędach; wszystkie błędy dostępu do bazy danych mogą być dostępne przez grupę Errors |
| Command |
- |
umożliwia definiowanie specjalnych komend, które są wykorzystywane do wykonania operacji na bazie danych kilka razy wraz ze zmianą parametrów |
| Parameter |
- |
jest to pojedynczy parametr lub argument skojarzony z obiektem Command bazującym na sparametryzowanym zapytaniu lub procedurze przechowywanej |
Procedury przechowywane są wykorzystywane w bazach SQL Server. Pobierają one argumenty i wykonują polecenia bezpośrednio na serwerze SQL.
Najczęściej używanymi obiektami w skryptach są: Connection, Recordset i Field. Relacje między nimi są następujące:
Obiekt Recordset można stworzyć poprzez wykonanie metody Execute na obiekcie Connection. Odwrotnie, właściwość ActiveConnection obiektu Recordset wskazuje połączenie do którego bieżący obiekt Recordset należy. Obiekt Recordset posiada grupę Fields, która zawiera wszystkie obiekty Field obiektu Recordset. Obiekt Field reprezentuje pojedynczą kolumnę danych. Można użyć domyślnej właściwości Value w celu ustawienia lub zwrócenia danych z bieżącego rekordu.
2. Dostęp do bazy danych.
W celu pobrania danych z bazy należy:
| 1. |
Otworzyć połączenie z bazą danych. |
| 2. |
Stworzyć obiekt umożliwiający dostęp do pól danych każdego rekordu. |
| 3. |
Pobrać dane ze stworzonego obiektu. |
| 4. |
Zamknąć obiekt oraz połączenie z bazą danych. |
Poniższy przykład pokazuje jak można to zrealizować:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20: |
|
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
Dim objConn, objRS, strQuery
Dim strConnection
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=Northwind;Database=Northwind;"
strConnection = strConnection & "UID=sa;PWD=;"
objConn.Open strConnection
strQuery = "SELECT ProductName, UnitPrice, FROM Products "
strQuery = strQuery & "ORDER BY ProductName"
Set objRS = objConn.Execute(strQuery)
%>
<HTML>
<BODY>
<p>Wszystkie produkty znajdujące się w tabeli Products,
posortowane według nazwy produktu:</p>
<%
While Not objRS.EOF |
21:
22:
23: |
|
|
Response.Write objRS("ProductName") & " ("
Response.Write FormatCurrency(objRS("InitPrice")) & ")<BR>"
objRS.MoveNext |
24:
25:
26:
27:
28:
29:
30:
31: |
|
Wend
objRS.Close
objConn.Close
Set objRS = Notning
Set objConn = Nothing
%>
</BODY>
</HTML> |
Linia 4 nakazuje przeglądarce aby nie przechowywała (nie keszowała) tej strony. Przy braku tej linii, przeglądarka przechowywałaby stronę i użytkownik, w razie zmiany zawartości bazy danych, widziałby przestarzałe dane. Linie 5-6 zawierają wszystkie deklaracje zmiennych. ObjConn i objRS są obiektami Connection i Recordset. Zmienna strQuery zawiera zapytanie SQL, które spowoduje pobranie wszystkich produktów z tabeli Products. StrConnection zawiera łańcuch połączenia, który określa DSN (źródło danych ODBC) jako źródło danych. W linii 7 utworzony został obiekt Connection i przechowywany jest w zmiennej objConn. Ponieważ skrypt zakłada fizyczne połączenie do źródła danych, to musi je opisać. Opis połączenia (łańcuch połączenia) znajduje się w liniach 8-9 w zmiennej strConnection. Łańcuch połączenia wygląda następująco:
| "DSN=Northwind;Database=Northwind;UID=sa;PWD=;" |
Łańcuch połączenia zawiera serię argumentów wraz z ich wartościami oddzielonymi średnikami. W przykładzie użyto następujących argumentów:
| DSN |
określa nazwę źródła danych ODBC połączenia |
| Database |
określa bazę danych, z którą zostanie nawiązane połączenie; chociaż ten parametr jest opcjonalny, dobrze jest go umieścić |
| UID |
zawiera nazwę użytkownika (identyfikacja) logującego się do bazy danych, w celu określenia uprawnień do dokonywania na bazie określonych operacji |
| PWD |
zawiera hasło użytkownika logującego się do bazy danych; UID i PWD to jedna nierozłączna para, która nadaje prawa dostępu do bazy danych |
Istnieje możliwiość dostępu do baz danych MS Access bez użycia ODBC. Poniżej znajduje się odpowiednik linii 8-9 dla takiego przypadku:
| strConnection = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(vsciezka) & ";" |
gdzie vsciezka to wirtualna ścieżka do pliku bazy danych MS Access (*.mdb). Metoda MapPath obiektu Server zwarca rzeczywistą (fizyczną) ścieżkę do pliku.
W linii 10 zostało otwarte połączenie do bazy danych poprzez użycie łańcucha połączenia znajdującego się w strConnection. W linii 11 i 12 do zmiennej strQuery zapisane zostało zapytanie skierowane do bazy danych i wykonane w linii 13 poprzez wykorzystanie metody Execute. Rezultat zapytanie przechowywany jest w obiekcie Recordset, tj. objRS. Zapytanie SQL jest wykonywane przez sterownik ODBC lub bazę danych, nie przez Active Server Pages. Obiekt objRS zawiera wszystkie wiersze, które zostały zwrócone z bazy danych jako rezultat wykonania zapytania SQL. Następnym krokiem jest wyświetlenie rezultatu na ekranie przeglądarki. W liniach 15-18 zostały wysłane znaczniki HTML wraz z informacją na temat wyświetlonych danych.
W liniach 20-24 następuje wysłanie do przeglądarki wszystkich wierszy rezultatu zapytania SQL. Jest to zrealizowane przy pomocy pętli While. Po otwarciu zbioru otrzymanych rekordów (wykonaniu zapytania) aktywnym rekordem jest rekord pierwszy. Jeżeli rekord istnieje to wartością zwróconą po wywołaniu właściwości EOF (end of file) będzie FALSE. W naszym przykładzie przed warunkiem znajduje się operator Not, który zmienia wartość wykonanego warunku na przeciwny, czyli jeśli rekord będzie istniał (objRS.EOF = FALSE), to wykona się pętla While.
Dane z pól bazy uzyskiwane są poprzez odczytanie właściwości Value z obiektu Field z grupy Fields obiektu Recordset. Komenda taka powinna w naszym przypadku wyglądać następująco:
| objRS.Fields.Item("ProductName").Value |
jednak VBScript pozwala nam taką komendę skrócić do postaci:
Funkcja FormatCurrency(wartość) powoduje dodanie symbolu waluty oraz poprawnego zapisu wartości jako wartości walutowej według ustawień lokalnych serwera lub skryptu.
Linia 23 nakazuje przejście do następnego rekordu. Należy o niej pamiętać, gdyż jej brak może doprowadzić do wyraźnego pogorszenia wydajności serwera WWW do momentu upływu czasu wykonywania skryptu.
Kiedy skrypt wyświetli całą zawartość pól rekordów i zakończy wykonywanie pętli, obiekty Recordset (objRS) oraz Connection (objConn) zostają zamknięte (linie 25-26). W celu zwolnienia zasobów systemu, obiekty Recordset (objRS) oraz Connection (objConn) zostały zamknięte po ich użyciu. W celu usunięcia obiektów całkowicie z pamięci została przyporządkowana im wartość Nothing (linie 27-28).
3. Pobieranie danych z bazy danych.
Przykład z poprzedniego podrozdziału (podrozdział 6.2), pokazał w jaki sposób można pobrać dane z bazy przy pomocy metody Execute obiektu Connection. Teraz zostanie pokazany jeszcze inny sposób pobierania danych z bazy. Do tego celu wykorzystana zostanie metoda Open obiektu Recordset. Oto przykład:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30: |
|
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
Dim objConn, objRS, strQ
Dim strConnection
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Data Source=Northwind;"
strConnection = strConnection & "User ID=sa;Password=;"
objConn.Open strConnection
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = objConn
strQ = "SELECT Customers.CompanyName, "
strQ = strQ & "COUNT(Orders.OrderID) AS NumOrders "
strQ = strQ & "FROM Customers INNER JOIN Orders ON "
strQ = strQ & "Customers.CustomerID = Orders.CustomerID "
strQ = strQ & "GROUP BY Customers.CompanyName "
strQ = strQ & "HAVING COUNT(Orders.OrderID) > 7 "
strQ = strQ & "ORDER BY COUNT(Orders.OrderID)"
objRS.Open strQ
%>
<HTML>
<BODY>
<p>Lista nazwy firm wszystkich klientów, którzy mają po
ponad 7 zamówień wraz z ich ilością zamówień.
Lista jest posortowana rosnąco według ilości zamówień.</p>
<%
While Not objRS.EOF |
31:
32:
33: |
|
|
Response.Write objRS("CompanyName") & ": "
Response.Write objRS("NumOrders") & " zamówień<BR>"
objRS.MoveNext |
34:
35:
36:
37:
38:
39:
40:
41: |
|
Wend
objRS.Close
objConn.Close
Set objRS = Notning
Set objConn = Nothing
%>
</BODY>
</HTML> |
Aby przykład mógł poprawnie działać należy mieć ustawione (skonfigurowane) źródło danych ODBC o nazwie Northwind do bazy Northwind, które w tej części nie będzie omawione. Przejdę od razu do omówienia przykładu. Linie 1-11 były omawiane w poprzednich rozdziałach, więc myślę, że nie trzeba do tego wracać. Mała różnica jest może w przypadku łańcucha połączenia, gdzie:
- Data Source jest równoważne zapisowi DNS z poprzedniego przykładu;
- User ID jest równoważne zapisowi UID z poprzedniego przykładu;
- Password jest równoważne zapisowi PWD z poprzedniego przykładu.
Nowością jest linia 13, w której jest tworzony obiekt Recordset nazwany objRS przy pomocy metody Server.CreateObject z parametrem ADODB.Recordset. Kiedy został stworzony obiekt Connection (objConn) oraz Recordset (objRS) należałoby umożliwić obiektowi Recordset wykorzystać połączenie zdefiniowane w obiekcie Connection. Linia 14 wykonuje to zadanie, przez co obiekt Connection został przypisany do właściwości ActiveConnection obiektu Recordset (objRS). Teraz wszystkie operacje tj. odczyt rekordów, poprawianie, wstawianie oraz kasowanie, wykonywane przy użyciu objRS będą korzystały z połączenia określonego przez objConn. Linie 15-21 przypisują zmiennej strQ łańcuch odpowiadający zapytaniu SQL. Zapytanie SQL powoduje wybranie z bazy rekordów z pola CompanyName z tabeli Customers oraz liczbę OrderID (nazwaną NumOrders) z tabeli Orders. Pola OrderID są przypisane do każdej firmy poprzez użycie składni INNER JOIN na tabelach Customers i Orders. Stworzone połączenie mówi, że pola CustomerID w obu tabelach (Customers, Orders) są ze sobą powiązane. Składnia wygląda następująco:
SELECT Customers.CompanyName, Orders.OrderID AS NumOrders
FROM Customers INNER JOIN Orders ON |
|
|
Customers.CustomerID = Orders.CustomerID |
Powyższe zapytanie zwraca wszystkie wiersze bazy danych z połączonych tabel Customers i Orders. W przykładzie zliczane są pola OrderID dla każdego klienta. Aby zastosować to do powyższego zapytania trzeba użyć funkcji sumującej Count(pole). Funkcja zlicza ilość wszystkich OrderID, które są przydzielone do jednego klienta (firmy). Należy także pogrupować rekordy, których pola Customers.CompanyName są takie same (ten sam klient). Teraz dopiero funkcja COUNT(Orders.OrderID) zwróci liczbę OrderID przyporządkowaną jednemu klientowi (firmie). W celu uwzględnienia rekordów, których ilość zamówień będzie większa niż 7, tzn. wartość COUNT(Orders.OrderID) będzie większa od 7, należy użyć członu HAVING. Człon HAVING jest użyty dla określenia warunku dla składni GROUP BY. Nie można użyć członu WHERE, gdyż nie objąłby on członu GROUP BY. Człon ORDER BY sortuje rekordy wynikowe, w naszym przypadku sortowanie jest rosnące (domyślne) według ilości wystąpień pól Orders.OrderID.
W linii 22 użyta została metoda Open obiektu Recordset w połączeniu z zapytaniem SQL, które znajduje się w zmiennej strQ. Powoduje ona wykonanie się zapytania SQL na bazie danych. W liniach 24-28 znajduje się część zwykłego HTML, którą nie trzeba chyba omawiać. W liniach 30-34 znajduje się pętla While, która podobnie jak w przykładzie z poprzedniego podrozdziału (podrozdzał 6.2) powoduje wysłanie rezultatu wykonania zapytania SQL do przeglądarki. Po wysłaniu wszystkich rekordów (zakończeniu wykonywania pętli While) następuje zamknięcie objRS i objConn oraz zostają zwolnione ich zasoby na serwerze.
Pobieranie wielu zbiorów danych
Czasami zapytanie SQL zwraca więcej niż jeden zbiór rekordów i składa się z dwóch innych zapytań, np.:
SELECT MAX(UnitPrice) FROM Products
SELECT COUNT(*) FROM Orders |
MS Access nie obsługuje takich złożonych zapytań SQL, więc poniższy przykład będzie dotyczył tylko MS SQL Server.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15: |
|
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
Dim objConn, objRS, strQ
Dim strConnection
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Data Source=Northwind;"
strConnection = strConnection & "User ID=sa;Password=;"
objConn.Open strConnection
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = objConn
If objConn.Properties("DBMS Name") <> "Microsoft SQL Server" Then |
16:
17:
18: |
|
|
Response.Write "<p>Ten skrypt może być użyty tylko "
Response.Write "do korzystania z bazy MS SQL Server.</p>"
Response.End |
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32: |
|
End If
strQ = "SELECT MAX(UnitPrice) AS "
strQ = strQ & "'Cena maksymalna' FROM Products;"
strQ = strQ & "SELECT COUNT(*) AS 'Liczba zamowien' "
strQ = strQ & "FROM Orders"
objRS.Open strQ
%>
<HTML>
<BODY>
<p>Tutaj jest maksymalna cena produktu oraz
liczba zamówień znajdujących się w bazie Northwind.</p>
<%
Do Until objRS Is Nothing |
| 33: |
|
|
Do While Not objRS.EOF |
34:
35: |
|
|
|
Response.Write objRS(0).Name & ": " & objRS(0) & "<BR>"
objRS.MoveNext |
36:
37: |
|
|
Loop
Set objRS = objRS.NextRecordset |
38:
39:
40:
41:
42:
43: |
|
Loop
objConn.close
Set objConn = Nothing
%>
</BODY>
</HTML> |
Linie 1-14 są zrozumiałe, gdyż były wcześniej omawiane. W liniach 15-19 skrypt sprawdza czy połączenie opisane w objConn jest połączeniem bo bazy SQL Server poprzez sprawdzenie czy właściwość DBMS Name połączenia jest równa "Microsoft SQL Server". Jeżeli baza danych jest inna niż SQL Server to zostaje wyświetlony komunikat umieszczony w liniach 16-17 i skrypt kończy swoje działanie na linii 18. W liniach 21-24 następuje przypisanie zmiennej strQ łańcucha będącego zapytaniem SQL. W linii 25 następuje wykonanie zapytania na bazie danych. Linie 27-30 zawierają znaczniki HTML i tekst wysłany do przeglądarki. Linie 32-38 zawierają dwie zagnieżdżone pętle Do. Zewnętrzna pętla jest wykonywana do chwili aż zbiór rekordów w objRS będzie wartością Nothing, co będzie wskazywało, że nie ma więcej zbiorów rekordów określonych przez objRS. W pętli wewnętrznej (linie 33-36) pobierane są krok po kroku wszystkie wiersze bieżącego zbioru rekordów oraz wysyłana jest nazwa i wartość pierwszego pola, którego indeks jest równy zero, do przeglądarki użytkownika (klienta). Kiedy wewnętrzna pętla dojdzie do końca bieżącego zbioru rekordów, jest zakończana i następuje przejście (linia 37) do następnego zbioru rekordów. Kiedy bieżący zbiór rekordów będzie ostatnim zbiorem, metoda NextRecordset ustali wartość objRS na wartość Nothing, co pozwoli zewnętrznej pętli Do zakończyć jej wykonywanie. W liniach 39-40 następuje zamknięcie objConn i zwolnienie jej zasobów.
4. Umieszczanie danych w tabeli.
Dynamiczne strony WWW pobierają dane z bazy danych i przekazują je użytkownikowi oraz umożliwiają pobranie informacji od użytkownika i umieszenie ich w bazie danych. Umieszczenie informacji w bazie danych można zrealizować na wiele sposobów, oto dwa spośród nich:
| - |
wykonanie zapytania SQL zawierającego komendę INSERT, |
| - |
otwarcie objektu Recordset, dołączenie rekordu za pomocą metody AddNew i uzupełnienie pól rekordu i zapisanie rekordów do bazy danych poprzez użycie metody Update. |
Użycie komendy INSERT w zapytaniu SQL
Poniżej zostanie przedstawiony skrypt zapisujący rekord do tabeli Shippers bazy Northwind, używając zapytania SQL z komendą INSERT. Następnie zostaną pobrane wszystkie rekordy z tabeli Shippers i wyświetlone w oknie przeglądarki. Za każdym razem, kiedy skrypt będzie uruchomiony, do tabeli Shippers zostanie dopisany nowy rekord z wartościami Kiosk przy Rotundzie w polu CompanyName i (075) 75 112 233 w polu Phone.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
Dim objConn, objRS, strQ, strConn, strOutout
set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Data Source=Northwind;User ID=sa;Password=;"
objConn.Open strConn
strQ = "INSERT INTO Shippers (CompanyName, Phone) VALUES "
strQ = strQ & "('Kiosk przy Rotundzie', '(075) 75 112 233')"
objConn.Execute strQ
Set objRS = objConn.Execute("SELECT * FROM Shippers")
%>
<HTML><BODY>
<p>Wszyscy spedytorzy:</p>
<%
While Not objRS.EOF |
21:
22:
23: |
|
strOutput = objRS("CompanyName") & " " & objRS("Phone")
Response.Write Server.HTMLEncode(strOutput) & "<BR>"
objRS.MoveNext |
24:
25:
26:
27:
28:
29: |
Wend
objRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
%>
</BODY></HTML> |
Linie 1-9 są zrozumiałe, więc nie będą omawiane. W liniach 11-12 następuje przypisanie do zmiennej strQ łańcucha będącego zapytaniem SQL. Zapytanie ma postać:
INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Kiosk przy Rotundzie', '(075) 75 112 233') |
Komenda dodaje nowy rekord do tabeli Shippers i umieszcza wartości z listy Values, tj. "Kiosk przy Rotundzie" i "(075) 75 112 233", w polach CompanyName i Phone.
W linii 13 następuje wykonanie zapytania SQL na bazie danych. W liniach 15 i 20-24 pobierane są wszystkie rekordy z tabeli Shippers (tak jak jest to opisane w zapytaniu w linii 15) i wyświetlane w oknie przeglądarki. Przed zapisaniem zawartości zmiennej strOutput, w której znajdują się zawartości pól CompanyName i Phone, jest ona konwertowana na tekst zrozumiały dla przeglądarki (dokument HTML). Linia 26 zamyka obiekty Recordset (objRS) i Connection (objConn), a następnie w linii 27 zwalnia ich zasoby.
Skrypt zapisujący do bazy cały czas te same wartości nie jest zbyt użytecznym. Bardziej użytecznym będzie skrypt umieszczający dane z wypełnionego i wysłanego przez użytkownika formularza.
Użycie metody AddNew
Użycie metody AddNew ma wiele zalet, np. nie trzeba śledzić wszystkich apostrofów, których niepoprawna kolejność może wywołać błędy w wykonywanym skrypcie. Wadą metody AddNew jest to, że w porównaniu ze składnią INSERT zapytania SQL zajmuje ona więcej kodu. Poza tym przed dodaniem danych do tabeli poprzez użycie metody AddNew należy najpierw stworzyć obiekt Recordset. Poniższy przykład pokazuje sposób użycia metody AddNew.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn, objRS, strConn, strOut
Dim strCN, strPh, lngShipperID
set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Data Source=Northwind;User ID=sa;Password=;"
objConn.Open strConn
set objRS = Server.CreateObject("ADODB.Recordset")
If (Request.ServerVariables("CONTENT_LENGTH") > 0) Then |
17:
18:
19: |
|
strCN = Trim(Request.Form("Nazwa"))
strCN = Left(strCN, 40)
If Len(strCN) > 0 Then |
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30: |
|
|
objRS.CursorLocation = adUseServer
objRS.CursorType = adOpenKeyset
objRS.LockType = adLockOptimistic
objRS.Open "Shippers", objConn, , , adCmdTable
objRS.AddNew
objRS("CompanyName") = strCN
strPh = Trim(Request.Form("Telefon"))
strPh = Left(strPh, 24)
If Len(strPh) > 0 Then |
| 31: |
|
|
|
objRS("Phone") = strPh |
32:
33:
34:
35:
36:
37:
38:
39: |
|
|
End If
objRS.Update
lngShipperID = objRS("ShipperID")
Responase.Write "<p>Twój nowy rekord posiada ID "
Response.Write lngShipperID & ".</p>"
objRS.Close |
| 40: |
|
Else |
| 41: |
|
|
Response.Write "<p>Proszę wypełnić formularz!</p>" |
| 42: |
|
End If |
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53: |
End If
objRS.Cursor Location = adUseClient
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockReadOnly
objRS.Open "Shippers", objConn, , , adCmdTable
%>
<HTML><BODY>
<TABLE><TR><TD>ID</TD><TD>Firma</TD><TD>Telefon</TD></TR>
<%
While Not objRS.EOF |
54:
55:
56:
57: |
|
strOut = "<TR><TD>" & objRS("ShipperID") & "</TD><TD>"
strOut = strOut & objRS("CompanyName") & "</TD><TD>" & objRS("Phone")
Response.Write strOut & "</TD></TR>"
objRS.MoveNext |
58:
59:
60:
61:
62:
63:
64: |
Wend
objRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
%>
</TABLE>
<FORM ACTION="<%= Request.ServerVariables("SCRIPT_NAME") %>" |
| 65: |
|
|
METHOD="POST"> |
66:
67:
68:
69:
70: |
Nazwa firmy: <INPUT TYPE="TEXT" NAME="Nazwa"><BR>
Telefon: <INPUT TYPE="TEXT" NAME="Telefon"><BR>
<INPUT TYPE="SUBMIT">
</FORM>
</BODY></HTML> |
Pierwsza część, zawierająca linie 1-14, dołącza stałe ADO, deklaruje zmienne, tworzy połączenie do bazy Northwind poprzez ODBC, tworzy obiekt Recordset. W linii 6 znajduje się dyrektywa dołączająca plik adovbs.inc do skryptu. Plik adovbs.inc powinien znajdować sięw katalogu X:Program FilesCommon FilesSystemado, gdzie X: jest napędem, na którym zainstalowany jest system. W pliku znajdują się deklaracje wszystkich stałych ADO, z których niektóre zostały użyte w skrypcie. Następna część (linie 16-43) sprawdza czy jakieś informacje zostały wysłane do skryptu (serwera). Jeśli tak zostaje wykonana część zawarta w liniach 17-42. Jeśli jakieś informacje zostały wysłane, tzn. użytkownik wypełnił formularz (linie 64-69) i go wysłał, skrypt odczyta zawartość pól Nazwa i Telefon i zapisze te wartości do tabeli Shippers bazy danych. Pole ShipperID w tabeli Shippers jest polem którego nie można edytować, jest to pole typu autonumerycznego. W nim znajduje się unikalny numer rekordu w tabeli Shippers. Zawartość tego pola może zostać odczytana dzięki przypisaniu CursorLocation wartości adUseServer. W linii 17 następuje przypisanie zawartości pola Nazwa wypełnionego formularza, po uprzednim usunięciu wszystkich odstępów (klawiszy spacji) poprzedzających nazwę (funkcja Trim()). Funkcja Left(ciag, limit) pozwala wyciąć z łańcucha ciag taką ilość znaków jaką określa limit. Linia 18 jest pewnego rodzaju ograniczeniem na maksymalną ilość wprowadzonych znaków do pola tabeli. W linii 19 znajduje się warunek sprawdzający czy pole wypełnionego formularza Nazwa nie zostało pozostawione puste. Jeśli w polu były jakieś informacje zostaje wykonana część znajdująca się w liniach 20-39, w przeciwnym wypadku wykona się linia 41, nakazująca wypełnić formularz. Instrukcja (linia 20):
| objRS.CursorLocation = adUseServer |
nakazuje dokonywanie zmian w bazie danych. Kursor musi znajdować się w bazie danych, gdyż jest potrzeba pobrania wartości z pola autonumerycznego ShipperID. W linii 21 typ kursora zostaje określony wartością adOpenKeyset. To przypisanie jest niezbędne ponieważ typ kursora adOpenKetyset sprawia, że będzie można pobrać wartość z pola autonumerycznego po dodaniu rekordu do bazy danych. Instrukcja
| objRS.LockType = adLockOptimistic |
określa sposób zapisu danych do bazy. Wartość adLockOptimistic wskazuje na zapis rekordu do bazy po wywołaniu metody Update. Takie rozwiązanie jest wskazane jeśli do bazy zapisywany jest jednorazowo jeden rekord. Jeśli trzeba byłoby zapisać większą ilość rekordów do bazy, co odbywałoby się poprzez użycie metody UpdateBatch, należałoby zamiast wartości adLockOptimistic użyć wartości adLockBatchOptimistic. Jeśli zaś istniałaby potrzeba zapisu do bazy po każdej zmianie zawartości rekordu należałoby użyć wartości adLockPessimistic, zaś jeśli nie ma potrzeby zapisywania zawartości rekordu do bazy można użyć wartości adLockReadOnly. W linii 24 następuje otwarcie rekordu:
| objRS.Open "Shippers", objConn, , , adCmdTable |
Pierwszy parametr ("Shippers") określa dane źródłowe, tabelę. Drugi parametr (objConn) określa połączenie do bazy danych. Piąty parametr (adCmdTable) wskazuje, że źródłem danych będzie tabela. Parametr trzeci i czwarty są tutaj zbędne, gdyż określają one wartości CursorType i LockType, które zostały już określone w liniach 21-22. Nowy rekord zostaje utworzony w momencie wywołania metody AddNew (linia 25). W liniach 26 i 31 zawartość pól zostaje uzupełniona. W linii 28 zostają usunięte wszystkie odstępy (klawisze spacji) poprzedzające numer telefonu (funkcja Trim()), jeśli takie występują, następnie numer jest ograniczany co do długości, tj. 24 znaki. Jeśli długość ciągu w polu Telefon w formularzu jest większa od zera następuje przypisanie zawartości pola do pola bazy danych Phone. Linia 34 powoduje zapisanie wszystkich zmian rekordu do bazy danych, przy użyciu metody Update. W wyniku tego następuje wygenerowanie wartości w polu autonumerycznym bazy ShipperID i przypisanie do zmiennej lngShipperID (linia 35). Jest to możliwe ze względu na to, że kursor znajduje się jeszcze na pozycji dodanego przed chwilą rekordu. Linie 36-37 wysyłają do przeglądarki tekst powiadamiający użytkownika o jego numerze ID. W linii 39 następuje zamknięcie obiektu Recordset. Następna część skryptu wyświetla zawartość tabeli Shippers (linie 45-61). Instrukcja
| objRS.CursorLocation = adUseClient |
powoduje, że kursor jest znajduje się po stronie klienta, nie w bazie danych. Ponieważ kursor będzie przemieszczał się tylko w jednym kierunku (odczytanie danych od początku do końca), właściwości CursorType została przypisana wartość adForwardOnly. Właściwość LockType posiada wartość adLockReadOnly, ponieważ nie ma potrzeby dokonywania zmian w zawartości rekordów w bazie danych. Linie 51-52 to zwykły HTML. W linii 52 tworzona jest tabela. W liniach 54-56 następuje wypisanie zawartości rekordu bieżącego do nowoutworzonej rubryki tabeli w przeglądarce. W linii 57 następuje przejście do następnego rekordu. W liniach 60-61 następuje zamknięcie obiektów Recordset i Connection i zwolnienie ich zasobów. Linie 63 i 65-70 to zwykły HTML. W linii 63 następuje zamknięcie tabeli. Linie 64-69 zawierają formularz, do którego użytkownik może wprowadzić dane w celu wprowadzenia ich do bazy danych. W linii 64 wartość atrybutu ACTION formularza określona jest przez zmienną SCRIPT_NAME, która zawiera adres WWW bieżącego skryptu ASP.
Podsumowując, używając metody AddNew w celu dodania rekordu do tabeli z ponad 1000 rekordów, nie trzeba otwierać całej tabeli, a to mogłoby dość znacznie obciążyć zasoby serwera WWW i bazy danych.
Załączanie dużych tekstów i binarnych pól danych
Jeśli w polu bazy danych znajduje się obiekt dużego rozmiaru (często oznaczany jako BLOB, binary large object). Aby pobrać dane z takiego pola w tabeli należy użyć metody GetChunk, zaś w celu dodania zawartości pola do tabeli należy użyć metody AppendChunk. Odczytanie zwykłego pola z tabeli bazy danych odbywa się poprzez komendę:
| zmienna = Recordset("NazwaPola") |
zaś odczytanie zawartości pola BLOB składa się z dwóch części:
rozmiar = Recordset("NazwaPola").ActualSize
wartosc = Recordset("NazwaPola").GetChunk(rozmiar) |
Jak widać metoda GetChunk potrzebuje parametru określającego rozmiar (w bajtach lub znakach) pola typu BLOB. Wartość określającą rozmiar zawartości pola zwraca właściwość ActualSize.
W celu zapisania danych do zwykłego pola w tabeli danych należy wpisać:
| Recordset("NazwaPola") = wartosc |
zaś do pola typu BLOB należy wpisać:
| Recordset("NazwaPola").AppendChunk wartosc |
Poniżej znajduje się przykładowy fragment przedstawiający odczyt zawartość pola typu BLOB.
wRozmiar = RozmiarCzesci
Rozmiar = Recordset("NazwaPola").ActualSize
Offset = 0
Do While Offset < Rozmiar |
|
Wartosc = Recordset("NazwaPola").Getchunk(wRozmiar)
warBLOB = warBLOB & Wartosc
Offset = Offset + wRozmiar |
| Loop |
Poniżej znajduje się przykładowy fragment skryptu zapisującego dane do pola typu BLOB.
wRozmiar = RozmiarCzesci
Rozmiar = LenB(warBLOB)
Offset = 0
Do While Offset < Rozmiar |
|
Wartosc = LeftB(RightB(warBLOB, Rozmiar - Offset), wRozmiar)
Recordset("NazwaPola").AppendChunk Wartosc
Offset = Offset + wRozmiar |
| Loop |
Odczyt i zapis, dla przykładu 200KB pola w przypadku 1000 użytkowników, zużyje 200MB zasobów serwera. Jak widać z tymi polami trzeba działać dość ostrożnie, tzn. nie należy ich nadużywać.
5. Modyfikowanie tabel danych.
W tej części zostanie pokazane jak zmieniać zawartość pól w istniejących już rekordach oraz w jaki sposób usuwać rekordy z bazy danych.
Uzupełnienie pól istniejących rekordów można realizować na dwa sposoby:
| - |
poprzez użycie komendy UPDATE w zapytaniu SQL, |
| - |
poprzez uaktualnienie obiektu Recordset. |
Użycie komendy UPDATE w zaytaniu SQL
Składnia zapytania wygląda następująco:
UPDATE NazwaTebeli SET NazwaKolumny1 = Wartosc1, NazwaKolumny2 = Wartosc2
WHERE Warunek |
W zapytaniu można użyć wiele par NazwaKolumny = Wartosc.
Zaletą tego rozwiązania jest możliwość zmiany wartości w wielu kolumnach rekordu. Poza tym użycie komendy UPDATE w zapytaniu SQL w mniejszym stopniu zużywa zasoby serwera WWW niż użycie metody Update na obiekcie Recordset ponieważ jest to wykonane wyłącznie na serwerze bazy danych i nie potrzeba tworzyć i przechowywać żadnego obiektu. Komenda UPDATE zapytania SQL jest wywoływana w skrypcie podobnie jak inne, czyli poprzez zastosowanie metody Execute na obiekcie Connection z parametrem w postaci zapytania SQL. Wygląda to następująco:
| Connection.Execute skladnia_UPDATE |
Jeśli jest potrzeba zmiany zawartości określonych pól rekordów, można posłużyć się członem WHERE, np.:
UPDATE Towar SET Cena = 1700
WHERE Nazwa = 'Telewizor' AND Producent='SONY' |
Uaktualnianie jednej kolumny obiektu Recordset
Druga metoda uaktualnienia zawartości bazy danych jest realizowana poprzez otwarcie rekordu i dokonanie zmian w kolumnie (polu). W celu dokonania uaktualnienia trzeba będzie wywołać metodę Update lub UpdateBatch. Należy pamiętać, że jeśli używa się tych metod trzeba ustalić odpowiednią wartość właściwości LockType (adLockOptimistic lub adLockBatchOptimistic). Poniższy przykład przedstawia sposób uaktualnienia jednego pola w rekordzie.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn, objRS, strOut
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Data Source=Northwind;User ID=sa;Password=;"
%>
<HTML><BODY>
<p>Skrypt zmienia numer telefonu spedytora "United Package":</p>
<%
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseServer
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockOptimistic
objRS.Open "SELECT * FROM Shippers WHERE CompanyName ='United Package'", objConn, , , adCmdTable |
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42: |
objRS("Phone") = "(503) 112-233"
Response.Write "<p>Numer telefonu zmieniony na " & objRS("Phone") & ".</p>"
objRS.Update
Response.Write "<p>Rekord uaktualniony.</p>"
objRS("Phone") = "(503) 332-211"
Response.Write "<p>Numer telefonu zmieniony na " & objRS("Phone") & ".</p>"
objRS.CancelUpdate
Response.Write "<p>Uaktualnionie przerwane.</p>"
Response.Write "<p>Numer telefonu teraz jest " & objRS("Phone") & ".</p>"
objRS.Requery
%>
<BR><p>Kolumna pobrana z bazy danych:</p>
<%
strOut = objRS("CompanyName") & " " & objRS("Phone")
Response.Write Server.HTMLEncode(strOut) & "<BR>"
objRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
%>
</BODY></HTML> |
W celu uaktualnienia jednego pola należy przed otwarciem rekordu ustalić wartość właściwości LockType na adLockOptimistic, a po zmianie zawartości pola (kolumny) wywołać metodę Update. Nowymi elementami w powyższym skrypcie są linie 24, 28 i 32. Pozostała część skryptu była już wcześniej omawiana. Linia 24, tj.:
uaktualnia pole rekordu w bazie danych mimo, że w linii 22 zawartość pola Phone została już zmieniona. Jednak linia 22 zmienia zawartość pola rekordu tylko na serwerze WWW, a nie w bazie danych. Zapisanie do bazy danych odbywa się właśnie poprzez wywołanie metody Update tegoż rekordu. Linia 28
sprawia, że zawartość pola Phone zostaje niezmieniona, tzn. że przypisanie znajdujące się dwie linie wyżej jest traktowane jak by go nie było. W linii 34, tj.:
cała zawartość rekordu jest odświeżana i zawartość pól pobierana jest z bazy danych. Metoda Requery jest równoważna wywołaniu kolejno metody Close i metody Open.
Uaktualnianie grupy kolumn obiektu Recordset
Istnieje możliwość przechowania wielu zmian jednego lub wielu rekordów lokalnie przed przekazaniem ich do bazy danych. Oto przykład:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn, objRS, strOut
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Data Source=Northwind;User ID=sa;Password=;"
%>
<HTML><BODY>
<p>Skrypt zmienia numer telefonu spedytorów "United Package" oraz "Speedy Express".</p>
<%
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseServer
objRS.CursorType = adOpenKeyset
objRS.LockType = adLockBatchOptimistic
objRS.Open "SELECT * FROM Shippers WHERE Phone LIKE '(503)%'", objConn, , , adCmdTable |
22:
23: |
Response.Write "<p>Kolumny zwrócone przez składnię SELECT: " & objRS.RecordCount & "</p>"
While Not(objRS.EOF) |
| 24: |
|
Select Case objRS("CompanyName") |
| 25: |
|
|
Case "United Package" |
| 26: |
|
|
|
objRS("Phone") = "(503) 123-456" |
| 27: |
|
|
Case "Speedy Express" |
| 28: |
|
|
|
objRS("Phone") = "(503) 987-654" |
29:
30: |
|
End Select
objRS.MoveNext |
31:
32:
33:
34:
35:
36:
37:
38: |
Wend
objRS.UpdateBatch
objRS.Requery
%>
<BR><p>Wszystkie kolumny pobrane z tabeli Shippers:</p>
<%
While Not(objRS.EOF) |
39:
40:
41: |
|
strOut = objRS("CompanyName") & " " & objRS("Phone")
Response.Write Server.HTMLEncode(strOut) & "<BR>"
objRS.MoveNext |
42:
43:
44:
45:
46:
47: |
Wend
objRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
%>
</BODY></HTML> |
W skrypcie przypisano właściwości CursorLocation wartość stałej adUseServer oraz właściwości LockType wartość adOpenKeyset. Jeśli LockType przypisanoby wartość taką, jak w poprzednim przykładzie, tj. adOpenForwardOnly, podczas wykonywania skryptu mogłyby wystąpić błędy. Należy pamiętać, że w przypadku użycia metody UpdateBatch kursor przemieści się do tyłu (na sam początek) po uaktualnieniu pierwszej całej kolumny z grupy. W wyniku wykonania się zapytania SQL, znajdującego się w linii 21, zostaną zwrócone wszystkie rekordy, których wartość w polu Phone będzie zaczynała się od "(503)". W linii 22 zostanie wyświetlona ilość zwróconych kolumn, którą można odczytać z właściwości RecordCount obiektu Recordset. Właściwość RecordCount można wykorzystać tylko w przypadku, gdy właściwość CursorType będzie posiadała wartość adOpenKeyset lub adOpenStatic, ponieważ w pozostałych przypadkach właściwość RecordCount zwróci błędną wartość. Użycie właściwości RecordCount razem z przypisaniem CursorLocation wartości adUseServer może spowodować zmniejszenie osiągów co do szybkości wykonania skryptu. Kiedy użyjemy jej z wartością adUseClient przypisaną do CursorLocation, wtedy użycie RecordCount nie będzie miało wpływu na szybkość, gdyż wszystkie informacje są przechowywane na serwerze WWW i nie trzeba będzie odwoływać się do bazy danych.
Pętla While (23-31) przemieszcza się po wszystkich kolumnach rekordów i zmienia wartości pól Phone (numery telefonu) w zależności od zawartości pola CompanyName. Wszystkie zmiany są przechowywane na serwerze WWW i nie są wprowadzane do bazy. Pętlę można zastąpić zapisem:
objRS.Filter = "CompanyName = 'United Package'"
objRS("Phone") = "(503) 123-456"
objRS.Filter = "CompanyName = 'Speedy Express'"
objRS("Phone") = "(503) 987-654"
objRS.Filter = "" |
Opis powyższych komend będzie omawiany za chwilę. Po zakończeniu dokonywania zmian (zakończeniu wykonywania pętli While), zostają zapisane wszystkie zmiany do bazy danych po wywołaniu metody UpdateBatch (linia 32). Wynik aktualizacji danych jest wyświetlany na ekranie przeglądarki. Wykonuje to pętla While znajdujące się w liniach 38-42.
Główna różnica pomiędzy użyciem metod Update i UpdateBatch jest to, że w przypadku metody UpdateBatch należy ustalić wartość CursorLock na adLockBatchOptimistic i wartość adOpenKeyset zamiast adOpenStatic dla własności CursorType.
Właściwość Filter służy do selekcji (filtracji) rekordów z bieżącej grupy rekordów. Kiedy właściwość Filter jest ustawiona, kursor przemieszcza się na pierwszą pozycję, spełniającą warunek filtracji.
Przykład użycia metody w przypadku jeśli jest potrzeba filtracji rekordów których pole Telefon będzie zaczynało się od ciągu "(503)", a pole Nazwa rozpoczynało się od ciągu "Sklep", właściwość Filter powinna przyjąć następującą wartość:
| Recordset.Filter = "Telefon LIKE '(503)%' AND Nazwa LIKE 'Sklep%'" |
Wyłączenie filtracji wykonuje się w następujący sposób:
czyli właściwość Filter przyjmuje wartość łańcucha o zerowej długości.
Usuwanie danych z bazy
Ważną operacją na bazie danych jest także usuwanie rekordów (danych). Podobnie jak w poprzednich przypadkach istnieją dwa rozwiązania:
| - |
poprzez użycie komendy DELETE w zapytaniu SQL, zalety: |
|
* |
łatwy sposób usuwania danych spełniających określone kryteria w bazie danych |
|
* |
zużywa tylko zasoby serwera WWW |
|
* |
nie ma potrzeby tworzenia i otwierania obiektu Recordset |
| - |
poprzez użycie metody Delete, zalety: |
|
* |
możliwość natychmiastowego usunięcia rekordu podczas przeglądania rekordów |
|
* |
możliwość usunięcia wszystkich rekordów zawartych w obiekcie Recordset, spełniających kryteria znajdujące się we właściwości Filter |
Użycie komendy DELETE w zapytaniu SQL
Składnia zapytania SQL zawierającego komendę DELETE wygląda następująco:
| DELETE FROM NazwaTabeli WHERE Warunek |
na przykład:
| DELETE FROM Pracownicy WHERE Data < #03/02/66# |
|
- w przypadku MS Access |
| DELETE FROM Pracownicy WHERE Data < '03/02/66' |
|
- w przypadku SQL Server |
Zapytanie SQL jest wykonywane po wywołaniu metody Execute obiektu Connection.
Użycie metody Delete
Podczas przemieszczania się poprzez grupę rekordów (obiekt Recordset), można usunąć bieżący rekord lub wszystkie rekordy poprzez wywołanie metody Delete. Na przykład, przemieszczając się po obiekcie objRS wewnątrz pętli While, usunięte zostaną wszystkie rekordy, które w polu Imie zawierają wartość "Jan":
| While Not(objRS.EOF) |
|
If objRS("Imie") = "Jan" Then |
|
|
objRS.Delete |
|
End If
objRS.MoveNext |
| Wend |
Jeśli istnieje potrzeba usunięcia wszystkich rekordów z obiektu Recordset, można użyć następującej instrukcji:
| Recordset.Delete adAffectGroup |
co jest równoważne zapisowi
6. Stronicowanie zbiorów rekordów.
Wiadomo już jak można dopisywać, uaktualniać i kasować rekordy z bazy danych. Znane już są możliwości i różnice wykorzystania zapytań SQL oraz tworzenia obiektów Recordset i wywoływania ich metod. Teraz skrypt może pobierać tysiące rekordów a nawet jeszcze więcej, lecz co z wysłaniem tak dużej ilości do klienta (przeglądarki)? Wysyłanie tak dużej ilości danych do klienta (przeglądarki), to nie najlepszy pomysł. Należałoby znaleźć lepsze rozwiązanie. Takim rozwiązaniem będzie technika zwana stronicowaniem (ang. paging). Polega ona na tym, że wysyłana jest tylko część rekordów do klienta (przeglądarki), po czym klient (użytkownik) może zażądać kolejnej części danych, itd. Takie rozwiązanie zużyje mniejszą ilość zasobów serwera i skróci czas ładowania się strony.
Przykładowy skrypt zostanie podzielony na siedem części, tj.:
| a) |
deklaracja - w tej części nastąpi zdeklarowanie i ustawienie wartości zmiennych i stałych; |
| b) |
określenie numeru wyświetlonej strony - skrypt określi numer wcześniej wyświetlanej strony i wybór użytkownika co do przemieszczenia się na kolejną stronę, tzn. skąd się tutaj dostał i co wybrał; |
| c) |
przygotowanie rekordów do stronicowania - ustawienie parametrów obiektu Recordset w celu wykorzystania metod i właściwości potrzebnych do stronicowania; |
| d) |
otwarcie rekordów - otwarcie rekordów (obiektu Recordset) i wykonanie zapytania SQL w celu pobrania odpowiednich danych z bazy; |
| e) |
przemieszczanie się kursora po rekordach - ustawienie pozycji kursora na rekordach, które powinny być wyświetlone na bieżącej stronie WWW; |
| f) |
wysłanie danych z bazy do klienta - wysłanie danych do klienta; |
| g) |
stworzenie elementów umożliwiających nawigację - utworzenie elementów (przycisków) umożliwiających użytkownikowi nawigację, stronicowanie. |
a) deklaracja
1:
2:
3:
4:
5:
6:
7:
8:
9:
10: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Const rozmiarStrony = 10
Dim biezacaStrona, objConn, objRS, strQ
Dim razemStron, pozycjaR |
Linia 4 informuje przeglądarkę aby nie przechowywała strony WWW. Jest to dość ważne, ponieważ zawartość strony będzie się zmieniać za każdym razem, kiedy użytkownik przejdzie do kolejnej grupy rekordów. Ilość rekordów znajdujących się na jednej stronie jest określona przez zmienną rozmiarStrony i zawiera wartość 10 (linia 8). Zmienna biezacaStrona zawiera numer aktualnie wyświetlanej strony (grupy rekordów). objConn i objRS zawierają obiekty Connection i Recordset. Zmienna razemStron zawiera ilość wszystkich stron, zaś zmienna pozycjaR zawiera numer porządkowy rekordu na bieżącej stronie.
b) określenie numeru wyświetlonej strony
| 11: |
If Request.ServerVariables("CONTENT_LENGTH") = 0 Then |
| 12: |
|
biezacaStrona = 1 |
| 13: |
Else |
14:
15: |
|
biezacaStrona = CInt(Request.Form("BiezacaStrona"))
Select Case Request.Form("Akcja") |
| 16: |
|
|
Case "Poprzednia" |
| 17: |
|
|
|
biezacaStrona = biezacaStrona - 1 |
| 18: |
|
|
Case "Nastepna" |
| 19: |
|
|
|
biezacaStrona = biazacaStrona + 1 |
| 20: |
|
End Select |
| 21: |
End If |
Jeżeli skrypt nie otrzyma danych z wypełnionego i wysłanego formularza (linia 11), bieżący numer strony jest ustawiany na wartość równą 1 (linia 12). Jeżeli użytkownik kliknie na jeden z przycisków nawigacyjnych skrypt odczyta numer poprzedniej strony z przesłanych informacji z formularza (linia 14). Pobrany numer strony jest zwiększany (linia 17) w przypadku, gdy użytkownik kliknął na przycisk Nastepna, a zmniejszany (linia 19), jeśli kliknął na Poprzednia.
c) przygotowanie rekordów do stronicowania
22:
23:
24:
25:
26:
27: |
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Data Source=northwind;User ID=sa;Password=;"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.CacheSize = rozmiarStrony |
W tej części skrypt tworzy obiekt Recordset (linia 24) i przygotowuje go w taki sposób, aby dostarczyć metod i właściwości niezbędnych do dokonania stronicowania. W liniach 22-23 tworzone jest i ustalane połączenie z bazą danych. Linia 25 to przyporządkowanie właściwości CursorLocation wartości adUseClient. Nie jest to obowiązkowe, ale jest zalecane w celu zmniejszenia obciążenia bazy danych. W następnej linii właściwość CursorType przyjmuje wartość stałej adOpenStatic. Przypisanie zmieni wartość domyślną właściwości z adOpenForwardOnly, która nie pozwoliłaby odczytać liczby stron w zbiorze rekordów poprzez użycie właściwości PageCount. Kolejna właściwość to CacheSize (linia 27), przyjmuje ona wartość równą ilości rekordów na każdej stronie. Ustawienie tej właściwości poprawia osiągi, szczególnie samego skryptu ASP, ponieważ od tego czasu czytanie i przechowywanie wszystkich rekordów określonej strony odbywa się od razu.
d) otwarcie rekordów
28:
29:
30:
31:
32:
33: |
strQ = "SELECT Customers.CompanyName, Orders.OrderDate "
strQ = strQ & "FROM Orders INNER JOIN Customers ON "
strQ = strQ & "Orders.CustomerID = Customers.CustomerID "
strQ = strQ & "ORDER BY Orders.OrderDate, "
strQ = strQ & "Customers.CompanyName"
objRS.Open strQ, objConn, , , adCmdText |
W tej części do zmiennej strQ przypisywana jest zawartość zapytania SQL, która wygląda następująco:
SELECT Customers.CompanyName, Orders.OrderDate
FROM Orders INNER JOIN Customers |
|
ON Orders.CustomerID = Customers.CustomerID |
| ORDER BY Orders.OrderDate, Customers.CompanyName |
Wywołanie zapytania następuje w linii 33, w której tworzony jest obiekt Recordset. Zapytanie pobiera zawartość kolumny CompanyName z tabeli Customers oraz OrderDate z tabeli Orders. Obie tabele, tzn. Customers i Orders, są ze sobą powiązane poprzez pola CustomerID. Wynik będzie posortowany rosnąco po polach Orders.OrderDate i Customers.CompanyName.
e) przemieszczanie się kursora po rekordach
34:
35: |
objRS.PageSize = rozmiarStrony
If Not(objRS.EOF) Then objRS.AbsolutePage = biezacaStrona |
W linii 34 znajduje się przypisanie właściwości PageSize ilości rekordów na jednej stronie (rozmiarStrony). W tym momencie, kiedy skrypt podzielony jest na strony o rozmiarze określonym przez rozmiarStrony, może on przejść do bieżącej strony poprzez przypisanie wartości bieżącej strony (zmienna biezacaStrona) do właściwości AbsolutePage. Skrypt nie może przejść do jakiejkolwiek strony, gdy obiekt Recordset jest pusty, gdyż w takim przypadku zostanie wygenerowany błąd. Z tego powodu w linii 35 znajduje się instrukcja warunkowa If.
f) wysłanie danych z bazy do klienta
36:
37:
38:
39:
40:
41: |
razemStron = objRS.PageCount
%>
<HTML><BODY>
<B>Klient: data zamówienia</B><P>
<%
For pozycjaR = 1 to objRS.PageSize |
| 42: |
|
Response.Write Server.HTMLEncode(objRS("CompanyName") & ": " & objRS("OrderDate")) & "<BR>" |
43:
44: |
|
objRS.MoveNext
If objRS.EOF Then Exit For |
45:
46:
47:
48:
49:
50: |
Next
objRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
%>
<BR>
Strona <%= biezacaStrona %> z <%= razemStron %><P> |
Ponieważ zbiór rekordów jest pobrany i kursor ustawiony jest na odpowiedniej stronie, skrypt może przekazać wszystkie rekordy z bieżącej strony do przeglądarki. W linii 36 ilość wszystkich stron, zwrócona przez wywołanie metody PageCount obiektu Recordset, jest przekazana do zmiennej razemStron. Linie 38-39 to zwykły tekst HTML. Pętla For (linie 41-45) wysyłają wszystkie rekordy z bieżącej strony do klienta (przeglądarki). W linii 44 znajduje się komenda nakazująca, w razie wystąpienia mniejszej ilości rekordów na bieżącej stronie niż określona przez zmienną rozmiarStrony, opuszczenie pętli For. W liniach 46-47 następuje zamknięcie i zwolnienie zasobów obiektów Recordset i Connection. Linia 50 informuje użytkownika o numerze strony w porównaniu do ilości wszystkich możliwych do wyświetlenia.
g) stworzenie elementów umożliwiających nawigację
| 51: |
<FORM ACTION="<%= Request.ServerVariables("SCRIPT_NAME") %> METHOD="POST"> |
| 52: |
<INPUT TYPE="Hidden" NAME="BiezacaStrona" VALUE="<%= biezacaStrona %>"> |
53:
54:
55:
56:
57:
58:
59:
60:
61: |
<%
If biezacaStrona > 1 Then %>
<INPUT TYPE="Submit" NAME="Akcja" VALUE="Poprzednia">
<% End If
If biezacaStrona <> razemStron Then %>
<INPUT TYPE="Submit" NAME="Akcja" VALUE="Nastepna">
<% End If %>
</FORM>
</BODY></HTML> |
Funkcją formularza WWW jest dostarczenie użytkownikowi elementów nawigacyjnych, dzięki którym użytkownik będzie mógł przeglądać rekordy (dane), np. 10 kolejnych rekordów, itd. W linii 51 otwierany jest formularz i określane są jego atrybuty. Linia 52 zawiera ukryty element formularza, który przechowuje numer bieżącej strony. W linii 54 sprawdzany jest numer bieżącej strony. Jeśli wartość jest większa od 1 to wyświetlany jest przycisk Poprzednia, w przeciwnym przypadku, tj. kiedy użytkownik znajduje się na pierwszej stronie, przycisk Poprzednia ze zrozumiałych powodów nie pokazuje się. Podobnie rzecz się ma w linii 57, tyle tylko, że sprawdzany jest warunek, czy bieżąca strona nie jest stroną ostatnią. Jeśli nie jest, wyświetlany jest przycisk Następna. Linia 60 zamyka formularz, zaś linia 61 zamyka cały dokument HTML.
Przyspieszenie operacji na bazie danych na serwerze WWW
Otwarcie połączenia z bazą danych jest związane z użyciem zasobów serwera WWW i serwera bazy danych oraz zmniejszeniem wydajności serwera WWW. Należy starać się aby aplikacje internetowe miały jak najmniejszy wpływ na zmniejszenie wydajności serwera WWW. Należałoby w związku z tym jak najszybciej zamykać połączenie z bazą i zwolnić zasoby. Nie ma potrzeby utrzymywania połączenia z bazą, kiedy jest ono użyte dla zbioru rekordów, których właściwość CursorLocation ustawiona została na wartość stałej adUseClient. Istnieje możliwość zamknięcia połączenia z bazą danych bez zamykania połączenia z obiektem Recordset w tym samym momencie. Poniżej znajduje się przykład takiego rozwiązania.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn, objRS, strQ, strOut
Set objConn = ServerCreateObject("ADODB.Connection")
objConn.Open "Data Source=Northwind;User ID=sa;Password=;"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockReadOnly
strQ = "SELECT CompanyName, Country "
strQ = strQ & "FROM Suppliers"
objRS.Open strQ, objConn, , , adCmdText
Set objRS.ActiveConnection = Nothing
objConn.Close
Set objConn = Nothing
Response.Write "<HTML><BODY>"
Do While (Not objRS.EOF) |
25:
26:
27: |
|
strOut = objRS("CompanyName") & ", " & objRS("Country")
Response.Write Server.HTMLEncode(strOut) & "<BR>"
objRS.MoveNext |
28:
29:
30:
31:
32: |
Loop
Response.Write "</BODY></HTML>"
objRS.Close
Set objRS = Nothing
%> |
Ponieważ w powyższym skrypcie następuje rozłączenie połączenia z bazą i obiektem Recordset, należy przyporządkować odpowiednie wartości ich właściwościom. Właściwość CursorLocation musi przyjąć wartość stałej adUseClient, ponieważ tylko w takim przypadku będzie można dokonywać operacji na obiekcie Recordset przy zamknięciu obiektu Connection. CursorType powinna przyjąć wartość adOpenStatic, ponieważ nie ma możliwości użycia wartości adOpenDynamic, kiedy połączenie jest usunięte. Wartość właściwości LockType jest ustawiona tylko na odczyt (adLockReadOnly), gdyż nie można dokonywać zmian w bazie danych, kiedy rekordy nie zostały przyłączone do konkretnej bazy.
W skrypcie po ustaleniu połączenia z bazą danych (linie 9-10), następuje utworzenie obiektu Recordset (objRS) (linia 11), pobranie danych (zawartość kolumn CompanyName oraz Country z tabeli Suppliers) (linie 17-19), po czym zostaje usunięte połączenie z bazą danych i zwolnienie zasobów na serwerze związanych z obiektem Connection (linie 20-22). W liniach 23-29 jest tworzony dokument HTML i wysyłany do przeglądarki użytkownika. W liniach 30-31 następuje zamknięcie obiektu Recordset i zwolnienie jego zasobów.
Gdyby w powyższym przypadku zaistniała potrzeba zmiany zawartości bazy danych, należałoby ponownie utworzyć obiekt Connection i połączyć go z obiektem Recordset. Przykład:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn, objRS, strQ, strOut, strConn
Set objConn = ServerCreateObject("ADODB.Connection")
strConn = "Data Source=Northwind;User ID=sa;Password=;"
objConn.Open strConn
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockOptimistic
strQ = "SELECT SupplierID, CompanyName, Address, City, Country "
strQ = strQ & "FROM Suppliers"
objRS.Open strQ, objConn, , , adCmdText
Set objRS.ActiveConnection = Nothing
objConn.Close
Set objConn = Nothing
Response.Write "<HTML><BODY>"
Do While (Not objRS.EOF) |
26:
27:
28: |
|
strOut = objRS("CompanyName") & ", " & objRS("Country")
Response.Write Server.HTMLEncode(strOut) & "<BR>"
objRS.MoveNext |
29:
30:
31:
32:
33:
34:
35:
36: |
Loop
Response.Write "<P>Przygotowanie do poprawienia rekordu:</P>"
Set objConn = ServerCreateObject("ADODB.Connection")
objConn.Open strConn
Set objRS.ActiveConnection = objConn
objRS.Filter = "CompanyName = 'Kiost przy Rotundzie'"
If objRS.EOF Then |
37:
38:
39:
40:
41:
42:
43: |
|
objRS.AddNew
objRS("CompanyName") = "Kiost przy Rotundzie"
objRS("Address") = "Targowa 1"
objRS("City") = "Nowe Miasto"
objRS("Country") = "Polska"
objRS.Update
Response.Write "<P>Dodano nowy rekord.</P>" |
| 44: |
Else |
45:
46:
47: |
|
objRS("Address") = "Rynek 18"
Response.Write "<P>Rekord uaktualniony.</P>"
objRS.Update |
48:
49:
50:
51:
52:
53:
54:
55: |
End If
Set objRS.ActiveConnection = Nothing
objConn.Close
Set objConn = Nothing
Response.Write "</BODY></HTML>"
objRS.Close
Set objRS = Nothing
%> |
7. Wykorzystanie procedur przechowywanych.
W tej części zostanie zaprezentowany sposób wywoływania procedur przechowywanych (MS SQL Server) za pomocą skryptów ASP poprzez ADO. Nie będą tutaj omawiane zasady tworzenia i działania procedur przechowywanych, lecz same skrypty ASP.
Użycie parametrów wejściowych
Procedury przechowywane zachowują się podobnie jak funkcje w VBScript, można dostarczać danych wejściowych, które są użyte do wygenerowania określonego rezultatu wyjściowego. Dla przykładu poniżej zostanie przedstawiony skrypt, który wywoła procedurę ZamowieniaKlienta umieszczoną w bazie Northwind z parametrem wejściowym podającym ID klienta jako LORAN oraz pobierze rekordy wynikowe. Procedura przechowywana wygląda następująco:
| CREATE PROCEDURE ZamowieniaKlienta |
|
@IDKlienta nchar(5) |
| AS |
| SELECT |
OrderID, |
|
OrderDate,
RequiredDate,
ShippedDate |
FROM Orders
WHERE CustomerID = @IDKlienta
ORDER BY OrderID |
zaś skrypt:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn, objRS
Dim strConn, strOut
Dim objCommand, objParameter
Const strParameter = "LORAN"
Function HTMLEncode(strA) |
| 14: |
|
If isNull(strA) Then |
| 15: |
|
|
HTMLEncode = "" |
| 16: |
|
Else |
| 17: |
|
|
HTMLEncode = Server.HTMLEncode(strA) |
| 18: |
|
End If |
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31: |
End Function
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "DSN=Northwind;Database=Northwind;"
strConn = strConn & "UID=sa;PWD=;"
objConn.Open strConn
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.CommandText = "ZamowieniaKlienta"
objCommand.CommandType = adCmdStoredProc
Set objCommand.ActiveConnection = objConn
Set objParameter = objCommand.CreateParameter("@IDKlienta", adWChar, adParamInput, 5, Left(strParameter, 5)) |
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43: |
objCommand.Parameters.Append objParameter
Set objRS = objCommand.Execute()
%>
<HTML>
<BODY>
Dane wyjściowe wykonania procedury przechowywanej
CustOrdersOrders, użytej z wartością parametru
<%= objCommand.Parameters("@IDKlienta") %>
<BR><BR>
<TABLE CELLSPACING="2" CELLPADDING="2">
<TR> |
44:
45:
46:
47: |
|
<TH>ID zamówienia</TH>
<TH>Data zamówienia</TH>
<TH>Data wymagana</TH>
<TH>Data wysyłki</TH> |
48:
49:
50: |
</TR>
<%
While Not objRS.EOF |
51:
52:
53:
54:
55:
56:
57:
58: |
|
strOut = "<TR>"
strOut = strOut & "<TD>" & HTMLEncode(objRS("OrderID")) & "</TD>"
strOut = strOut & "<TD>" & HTMLEncode(objRS("OrderDate")) & "</TD>"
strOut = strOut & "<TD>" & HTMLEncode(objRS("RequiredDate")) & "</TD>"
strOut = strOut & "<TD>" & HTMLEncode(objRS("ShippedDate")) & "</TD>"
strOut = strOut & "</TR>"
Response.Write strOut
objRS.MoveNext |
59:
60:
61:
62:
63:
64:
65:
66:
67:
68: |
Wend
ObjRS.Close : objConn.Close
Set objRS = Nothing : Set objConn = Nothing
Set objParam = Nothing
Set objCommand = Nothing
%>
</TABLE>
</BODY>
</HTML> |
W linii 11, zmiennej strParameter zostaje przypisana wartość parametru wejściowego dla procedury przechowywanej ZamowieniaKlienta. Wartość ta mogła by być również wartością wprowadzoną przez użytkownika. W liniach 13-19 znajduje się funkcja, która zwraca łańcuch przetworzony na kod HTML. Jak wiadomo w przypadku, gdy wartością parametru funkcji Server.HTMLEncode() będzie Null, wystąpi błąd. Aby się przed tym zabezpieczyć stworzona została właśnie funkcja HTMLEncode, która w przypadku wartości Null zwraca łańcuch o zerowej długości. Połączenie z bazą danych jest zrealizowane w liniach 21-24. W linii 26 utworzony został nowy obiekt Command.
Obiekt Command został stworzony w celu wywołania procedury przechowywanej wraz z jej parametrami. W linii 27 właściwości CommandText przyporządkowany został łańcuch zawierający nazwę procedury przechowywanej. W kolejnej linii, tj. 28, właściwości CommandType została przyporządkowana wartość stałej adCmdStoredProc. Informuje ona obiekt Command, że wartość przechowywana we właściwości CommandText to nazwa procedutry przechowywanej. Obiekt Command został przyporządkowany do połączenia (obiektu Connection) w linii 29. Ponieważ procedura posiada parametr wejściowy, skrypt musi także przypisać ten parametr do obiektu Command. Będzie on dostarczał określoną wartość procedurze przechowywanej. Zanim parametr będzie użyty należy najpierw go utworzyć. Jest to zrobione w linii 31 przez wpisanie komendy:
| Set objParameter = objCommand.CreateParameter("@IDKlienta", adWChar, adParamInput, 5, Left(strParameter, 5)) |
Powyższa komenda tworzy parametr o nazwie @CustomerID. Składnia komendy tworzącej parametr obiektu Command jest następująca:
| Set Parametr = Command.CreateParameter(Nazwa, Typ, kierunekDanych, Rozmiar, Wartosc) |
Nazwa to dowolny łańcuch określający nazwę tworzonego parametru.
Typ określa rodzaj danych przechowywanych w parametrze.
KierunekDanych określa czy parametr jest użyty dla danych wejściowych, czy dla danych wyjściowych, czy dla obu.
Rozmiar określa długość danych, zaś Wartosc przechowuje wartość, która powinna być dostarczona do procedury przechowywanej.
Po utworzeniu parametru należy go jeszcze przyporządkować obiektowi Command. Zostało to zrobione w linii 32. Teraz obiekt Command jest gotowy do użycia i skrypt może wywołać jego metodę Execute(), linia 34. Zwrócone rekordy są przechowywane w obiekcie Recordset (objRS). W liniach 36-59 skrypt wysyła do przeglądarki użytkownika informację zawierającą rekordy zawarte w tabeli HTML. Wszystkie wartości rekordów są przetworzone na kod HTML przy pomocy funkcji HTMLEncode() zdefiniowanej w liniach 13-19. Na końcu, w liniach 61-64, następuje zamknięcie obiektów i zwolnienie wszystkich wykorzystywanych przez nie zasobów.
Jeśli zdarzy się, że procedurze przechowywanej trzeba dostarczyć większą ilość parametrów, należy to zrobić w następujący sposób:
Set objParameter = objCommand.CreateParameter("Parametr1", adWChar, adParamInput, 8, Left(strA, 8))
objCommand.Parameters.Append objParameter
Set objParameter = objCommand.CreateParameter("Parametr1", adInteger, adParamInput, 4, CLong(intA))
objCommand.Parameters.Append objParameter |
Ważne jest aby formaty danych przesyłanych ze skryptu do bazy SQL były takie same. Poniżej została przedstawiona tabela z nazwami odpowiadających sobie zmiennych.
| Visual Basic |
Typy danych SQL Server |
Stałe ADO |
| Long |
int |
adInteger |
| Integer |
smallint |
adSmallInt |
| Byte |
tinyint |
adTinyInt |
| Boolean |
bit |
adBoolean |
| Double |
float |
adDouble |
| Single |
real |
adSingle |
| Currency |
money |
adCurrency |
| Date |
datetime |
adDate |
| String (poniżej 8000 znaków) |
varchar |
adVarChar |
| String (ponad 8000 znaków) |
text |
adLongVarChar |
Czwarty parametr w metodzie CreateParameter określa długość danej, czyli ilość bajtów przez nią wykorzystywanych. Poniższa tabela przedstawia najczęściej używane typy danych, ich długość w bajtach oraz stałe wartości, które określa te dane w ADO.
| Typy danych SQL Server |
Długość danych |
Wartość stała ADO |
| int |
4 |
3 |
| smallint |
2 |
2 |
| tinyint |
1 |
16 |
| bit |
1 |
11 |
| float |
8 |
5 |
| real |
4 |
4 |
| money |
8 |
6 |
| datetime |
8 |
7 |
| varchar |
1-8000 |
200 |
| text |
16 |
201 |
Użycie parametrów wyjściowych
Procedury przechowywane mogą również zwracać wartości w postaci parametrów wyjściowych. Parametry wyjściowe są często używane kiedy jest potrzeba wyciągnięcia z bazy pojedynczej informacji, zamiast wielu rekordów. Poniżej znajduje się przykład procedury przechowywanej nazwanej OstatniaWysylkaUSA.
| CREATE PROCEDURE OstatniaWysylkaUSA |
|
@DataWysylki datetime OUTPUT |
| AS |
| CREATE |
@DataWysylki = MAX(ShippedDate) FROM Orders |
|
WHERE ShipCountry = 'USA' |
Parametr wyjściowy w VBScript jest tworzony podobnie jak w przypadku parametru wejściowego, np.:
Set objParameter = objCommand.CreateParameter("Output", adVarChar, adParamOutput, 25)
objCommand.Parameters.Append objParameter |
Po wykonaniu się procedury przechowywanej, wartość parametru wyjściowego może być dostępna poprzez grupę Parameters:
| StrLastDate = objParameter.Parameters("Output").Value |
Kody zwrotne
Kody zwrotne swoim działaniem są podobne do parametrów wyjściowych z trzema tylko różnicami:
| - |
kody zwrotne mogą zwracać tylko wartości całkowite SQL odpowiadające danym typu Long w VBScript |
| - |
kody zwrotne nie muszą być deklarowane w procedurze przechowywanej; wartość kodu jest zwracana poprzez użycie instrukcji RETURN |
| - |
kiedy wykorzystuje się procedurę posiadającą kod zwrotny można stworzyć tylko jeden parametr w skrypcie ASP, który przechowuje wartość kodu zwrotnego (w każdej procedurze kod zwrotny jest tylko jeden) |
Poniżej przedstawiona zostanie przykładowa procedura przechowywana, której parametr wyjściowy będzie zawierał ostatnią datę wysyłki z tabeli Orders, w której pole ShipCountry będzie zawierało tekst USA, oraz w której kod zwrotny będzie równy 1 w przypadku, gdy wystąpią jakieś zamówienia i pole ShipCountry zawierać będzie tekst USA lub 0 jeśli nie wystąpią żadne.
| CREATE PROCEDURE OstatniaWysylkaUSA2 |
|
@DataWysylki datetime OUTPUT |
| AS |
| SELECT |
@DataWysylki = MAX(ShippedDate) FROM Orders
WHERE ShipCountry = 'USA' |
| IF @DataWysylki IS NULL |
|
RETURN 0 |
| ELSE |
|
RETURN 1 |
Tak utworzoną procedurę przechowywaną można wykorzystać w skrypcie ASP w celu przedstawienia parametru wyjściowego oraz kodu zwrotnego w oknie przeglądarki. Oto skrypt:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13: |
<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc"-->
<%
Dim objConn
Dim strConn, strOut
Dim objCommand, objParameter
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=sqloledb;User ID=sa;Password=;Initial Catalog=Northwind;Data Source=serwer_nr_15" |
14:
15:
16:
17:
18:
19:
20:
21: |
objConn.Open strConn
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.CommandText = "OstatniaWysylkaUSA2"
objCommand.CommandType=adCmdStoredProc
Set objCommand.ActiveConnection = objConn
Set objParameter = objCommand.CreateParameter("KodZwrotny", adInteger, adParamReturnValue) |
22:
23:
24: |
objCommand.Parameters.Append objParameter
Set objParameter = objCommand.CreateParameter("@DataWysylki", adDate, adParamOutput, 8) |
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48: |
objCommand.Parameters.Append objParameter
objCommand.Execute
%>
<HTML>
<BODY>
<P>Procedura przechowywana zwróciła następujące dane:</P>
<TABLE>
<TR>
<TD>Kod zwrotny: </TD>
<TD><%= objCommand.Parameters("KodZwrotny") %></TD>
</TR>
<TR>
<TD>Data ostatniej wysyłki:</TD>
<TD><%= objCommand.Parameters("@DataWysylki") %></TD>
</TR>
</TABLE>
<%
objConn.Close
Set objConn = Nothing
Set objParameter = Nothing : Set objCommand = Nothing
%>
</BODY>
</HTML> |
W skrypcie w linii 13 wykorzystano sterownik OLE DB dla SQL Server (Provider=sqloledb). Łańcuch połączenia strConn wskazuje, że użyty został komputer o nazwie serwer_nr_15 jako SQL Server. Aby skrypt był wykonywalny (użyteczny) należy zmienić tą nazwę na odpowiednią nazwę komputera zawierającego SQL Server.
Kod zwrotny został stworzony i dołączony do obiektu Command w liniach 21-22, zaś parametr wyjściowy w liniach 24-25. Polecenie w linii 27 wywołuje metodę Execute obiektu Command. W wyniku tego zwracane są nie grupy rekordów, lecz tylko pojedyncze informacje. Teraz parametr wyjściowy oraz kod zwrotny dostępne są poprzez zbiór Parameters, linie 35 i 39.
8. Różnice zapytań SQL w MS Access i w ASP.
Czasami zdarza się, że chcemy wykorzystać zapytanie SQL stworzone pod MS Access na naszej stronie internetowej wykorzystującej pliki *.asp. Próba wykonania zapytania w pliku *.asp kończy się błędem składni zapytania SQL mimo, że w programie MS Access działa bez zarzutów. Gdzie tkwi błąd? Różnice w składni zapytania SQL tworzonego w MS Access oraz w ASP/ADO przedstawia poniższa tabelka oraz przykłady.
| MS Access |
ASP/ADO |
| * |
% |
| ? |
_ |
Przykłady:
SELECT * FROM Customer WHERE City LIKE "N*"
Działa bezbłędnie w MS Access, nie działa w ASP.
SELECT * FROM Customer WHERE City LIKE "N%"
Działa bezbłędnie w ASP.
SELECT * FROM Customer WHERE Pharse LIKE "N?w"
Działa bezbłędnie w MS Access, nie działa w ASP.
SELECT * FROM Customer WHERE Pharse LIKE "N_w"
Działa bezbłędnie w ASP. | |
