Download 192 Data management for programmers and more Assignments Software Development in PDF only on Docsity!
Database Development for the
World-Wide-Web
Introduction
The Internet is a well-established fact of life, and while a lot of the Internet’s content comes in the form of ‘static’ web pages composed of text files in HTML format and images, our interest is in the methods that we can use to publish data from database tables on web pages. Web sites that derive their content from database files are often referred to as ‘dynamic’ web sites because if any of the data in the source database is changed (new data added, existing data deleted or updated), the next person to access the web site will see these changes immediately. Dynamic web sites are created by producing programs or scripts that generate the web pages on demand. While this may initially seem like a very wasteful use of computer processing power since there may be long periods during which the data does not change and yet the web server continues to slavishly re-generate web pages that it has generated many times before, the process of publishing this ‘live’ data on the web is only marginally less efficient that that required to copy existing files of HTML text stored on a disk. In these tutorials, you will discover the basic mechanisms for creating dynamic web sites and by following the included practical sessions, you will gain practice in the techniques necessary to implement dynamic, data-driven web sites. Practical Issues If you intend to follow the practical sessions throughout these tutorials, you will need only a few tools to allow you to do so. The first and most important tool is a Web Server: don’t panic, this does not mean you need to go out a purchase a big, industrial strength computer and start negotiating with an Internet Service Provider to provide you with a T2 connection to the Internet. In fact, web servers tend to come free with many computer operating systems, and operate perfectly well for executing web pages with active content for delivery to a browser on the local machine. If you have a computer with a copy of Windows 98, Millennium, 2000 or XP, you will almost certainly have the software necessary to set up a web server. For the practical exercises described here, you won’t even need an internet connection. If you have Windows 95, you can download a copy of Personal Web Server from the Microsoft web site (although of course you do need a connection to the web to do this). If you are running a computer installed with Linux, or an Apple Macintosh, you will also have web server software either already installed or easily available, although it is most likely that the practical sessions described here will not be compatible with your specific system – you will have to access one of the web-sites specific to your own computers, operating systems and web servers to find similar support material.
Web Sites and Web Pages
The Request-Response structure A Website is a collection of files stored on a computer that is connected to the Internet and organised in a way that allows them to be accessed by any other computer that is connected to the internet and running a Web Browser. The World-wide-web was invented by Tim Berners-Lee, a physicist at Cern, the international atomic research facility located near Geneva in Switzerland. It was devised as a way for computers connected to the Internet to share documents easily, and is based on the idea of Hypertext – textual documents which are interlinked by a system of document references within them. Berners-Lee devised the Hyper-Text Transport Protocol (HTTP) and the Hyper-Text Mark-up Language (HTML) that became the core format for every website. HTTP is a simple system which is understood by web servers. When a request for a document arrives at a web server, the server responds by sending the document back to the address that issued the request – a very simple client-server arrangement not unlike the way database servers respond to SQL. From the client end, the request incorporates the address of the web server and the name and location (actually, a virtual location) of the document on the server. When this request is issued, a large database distributed across the internet (the Domain Name Service or DNS) is used to work out the actual Internet address of the server (a numeric address that locates it uniquely among all of the networked computers in the world) and the request is directed there. Figure 1: Request-Response. The Internet Address of a web page or media file is issued at a browser and received by the web server. The HTML text of the web page is returned to the browser. Typically, the request comes from a Web Browser running on some computer, and is for a specific web page or resource, for example, www.BigBusiness.com/Documents/SomeDocument.html. In this case, the DNS would look up the Internet address for www.BigBusiness.com and pass the request for Documents/SomeDocument.html to it. The very clever part (alluded to by the word Hypertext in the acronym HTML) is that the document can contain text marked up in a Request Response Web Browser Web Server
‘plugged-in’ to a web server; e.g. JavaScript, PHP, Python and VBScript. We will use the latter in the practical sessions, since this language is similar to the Macro language used in Microsoft Access, and is the standard scripting language for Microsoft web servers. Figure 2: Active Server pages can generate a ‘live’ response to a web page request Script can also be created to execute in the client’s browser. This client-side script is an efficient way of doing some of the work, but has limitations that make it not particularly useful for our purposes. For a start, you can not publish data from a database that you host on the server by executing a script on the client’s computer; apart from it being an absurd idea (the database is at your end of an internet connection – not the client’s), the browser would not allow such a thing on the basis of security, your web server would need to have been set up to trust scripts run from an anonymous source (a security nightmare), and there would need to be software on either side of the connection capable of maintaining the direct access the client has to your database. For these and other reasons, client-side script is never used to do much more than alter the way that information is presented in the browser, ‘jazz-up’ interactions between the user and the page on the browser or validate information that the user is required to send to the server. Even then, we can never be sure that the browser accessing a web page is capable of allowing script to run, or that if the browser is capable of executing scripts, that the facility has not been disabled. All in all, client-side scripting is a non-starter for publishing data. Generating a page from a web server If we have a computer with a web-server running on it, for example Microsoft’s Internet Information Server (IIS), we can create a website by simply adding files that contains some HTML text to a directory that the web server is set up to ‘publish’. For example, the following simple file, placed in a folder that was configured to be published by the web server, would be enough to display a static message on a client’s browser:
Static Web Publishing Test Page
Hello HTML
Listing 1: Hello.html – this is a static html file, which would simply be The Internet Web Server Active Server Page Database Request Response
sent from the server unchanged. The text in listing 1 is HTML. Everything enclosed in angled brackets <> is treated by the web browser as ‘mark-up’, special code to indicate something to the browser. The only pieces of non-mark-up text in this listing are the page title (enclosed in ... tags), and some text to be displayed in a heading style (…). This would result in the following display on a web browser (Internet Explorer, or IE) running on any computer connected to the internet: Figure 3: The Output from Hello.html Note that the web address (shown above the page in IE’s address bar) is given as http://paddington/DreamHome/Hello.html. The Web Server is hosted on a PC that I call Paddington (it is a laptop and I carry it around in a small suitcase – you figure it out), and I have set up a website in it called DreamHome (after the example database in the book). The file was saved to the folder that this website is hosted in with the name Hello.html. Of course, this is a static web page. To make a dynamic page, we need to change two things. First, the page needs to be named with an ASP file extension instead of HTML, so that IIS recognises it as a file containing script that is to be executed, and the page also need to have some script embedded in it. <%@ LANGUAGE = VBScript %>
Dynamic Web Publishing Test Page
Hello HTML: the time is <%=Time%>
Listing 2: Hello.asp – this page contains script code embedded in the html to generate ‘live’ results
Figure 5: A small active web site, with a mixture of static (htm) pages, acti9ve (asp) pages and application pages (index.htm and Global.asa). The pages link (arrows) to each other to provide navigation through the site and to organize necessary services (e.g. login and credit checking). An ASP-based site typically interacts with many users, effectively doing so concurrently so that at any one time, there may be many different users all currently interacting with any page within the site. The sequence of pages accessed by each user is regarded as a session, and it is useful to be able to track any one user through all of the pages that they visit, either to gather statistics on the use of the site, or simply to save users from having to repeatedly send the same information to the site from page to page. Session state Tracking users through a site brings up one problem that is central to the way that Active Server Page websites are designed. Each request for an ASP page will execute some script. In sequence these ASP pages may, for example, allow a user to log-in to the site (to provide them with personalized information, or simply to prevent unauthorized access to restricted facilities), allow them to browse through several pages, perhaps selecting products to add to a shopping cart, and then to continue to a check-out page where the sale can be completed by the user entering credit card and delivery details (see figure 5). The problem is simply that the web application has no ‘memory’ that goes beyond a single page, so there is no simple way to know what stage in a sequence a client is on. Good website design demands this, because there is no compulsion for a user who has, say, added some items to a shopping cart to complete the process by going through the checkout page, or even to stay at the same website. You might think that a way around this would be to keep track of the user as they browse from page to page of the site, gathering information as they go and keeping this information ready for when the next page request arrives. However, as designers of a website, we have no knowledge of, or way of controlling the number of simultaneous users of the site. A site could at any time have zero, 100 or 1000000 current visitors, and if the web server was required to actively track each of these, it could take up a lot of time and memory (or none). For the site to remain efficient regardless of how many current visitors there are, it is necessary for the server to NOT be aware of the sequence of page requests for each and every visitor. However, for it to keep track of the current stage that each visitor to the site is at, the website must have some way of retaining their current status. Well designed web-sites are built to be ‘stateless’ – that is, there is no specific storage of data between sequential visits to active server pages. This makes is difficult to track where in a sequence a user of a site is – have they just logged in or are they proceeding to the checkout?
ASP allows a number of techniques to be used to track a user through a site, from the deployment of special ‘Session’ objects which are embedded in each page returned to a client so that they can be retrieved on the next request, to the use of ‘Cookies’ – strings that can be stored in the client’s computer from page to page (or even session to session). We can also use a couple of web programmers’ tricks, such as storing session data in variables hidden in web forms to be passed back and forth between server and client. All of these techniques involve embedding session data in the response to a page request, with the page organised so that the same data will be returned if a link on that page is used. Figure 6: Session state is maintained by sending data back and forth between the server and the browser embedded in the request-response data. The envelope here depicts this session data. All of the methods used to store data between ASP pages are there to save having to store the data at the server, where they could take up valuable space that would limit the number of simultaneous clients that could be serviced. The result is that well-designed web sites are inherently ‘scalable’, meaning that the software imposes no limit on the number of clients that the site can deal with at any one time. Of course there are limits imposed by the hardware the site is running on, the speed of the internet connection that the server has and other factors, but not the ASP website design. Application state ASP applications place demands on a web server, and it would be unwise to create a site that subsequently hogged space on the server whether it was servicing any clients or not. Managing session state goes a long way to reducing the load on a server, but a server could easily fill up with inactive ASP sites because each site must maintain a certain amount of information simply to function. A website typically keeps track of such things as the number of current users, page statistics (how many times each page has been visited) and database connections that can be used by a number of simultaneous sessions. Note that while session status multiplies up by the number of current users, application status is effectively one lot of stored data per application (or active website). i.e. the problem of maintaining application data is not anywhere near as bad. Even so, an application consumes some resources, and so it would be useful if we could cut-back on it (or even remove it completely) if there were no requests coming in for pages within it. Global.asa, one of the code files that are automatically associated with a web application, can contain code to perform application housekeeping. A realistic example of a suitable Global.asa file for a website is given later in listing 13. Web Server Response Next Request The World- Wide- Web
does not allow the creation of more complex data management pages. In these tutorials, we’ll use ASP to provide as much flexibility as we can. IIS – Installation Issues If you have a computer that runs either Windows 2000 or XP professional, you are likely to have Internet Information Server installed already. If it is not installed, you will need to run the Windows Setup program from the CD-ROM that your operating system was supplied on, and select the option to install IIS – currently this option appears on the Internet section of the Tools page of the setup program. Different versions of the Windows operating system do not include a copy of IIS. Windows XP home does not include a web server at all, although Windows 98 and Millennium (ME) come with a copy of Personal Web Server (PWS), which is effectively a cut-down version of IIS that has all of the capabilities that we need for developing websites. Consult the manuals or help files of the CD to find out how to locate and install the web server with these operating systems. Once you have a web server installed, you will need to create web sites on your computer to place your ASP code in. In IIS and PWS, creating a website is a simple matter. To create a new site in IIS on a Windows XP machine:
- Select StartAdministrative ToolsInternet Information Services
- In the tree view on the left of the IIS display, right-click on Default Web Site, and select New…Virtual Directory from the context menu that pops up
- Follow the directions of the New Virtual Direction Wizard to set up a site within your web server. The steps are: a. Provide a name for the virtual directory – this will become the root name of the site your are creating (e.g. Dreamhome) b. Type or (more likely) browse to a folder that will house the files on the virtual directory. In a standard IIS installation, it is expected that you will place the files in a folder off C:\Inetpub\wwwroot. You can place the files in a different folder or on a different drive if you wish. c. Select the required settings for file access for your web page files from the Internet. The default is Read and Run Scripts, and this is a very sensible default. You are best to have a very good reason for choosing other settings for this, since you may be allowing hackers to gain access to your hard disk if your website is every published across a broadband connection d. Press the Finish button at the last stage of the wizard. You now have a root folder for a named website.
- At this stage it is useful to test the website. Drop a very simple HTML file or ASP file into the root folder you created (e.g. in C:\Inetpub\wwwroot\MySite) – listing 1 or 2 (later) are ideal for this. You should now be able to access these files via the web server as follows: a. Run Internet Explorer
b. Enter http://MyMachine/MySite/Hello.asp into the address bar and press enter. Of course, you should replace MyuMachine with your actual PC’s name, and MySite with the name you gave the virtual directory. Alternatively, you can use http://localhost/MySite/Hello.asp since the name localhost is reserved to refer to the web server that resides on the local computer (the one that you are running the browser on). Note that the localhost name will only work from the local computer, and browsing from any other machine will require the actual server name. The result (with Hello.asp from listing 2) should be a page that displays a message and the current time. This last part is important – if the time is not displayed, you have typed up the ASP file with an error in it, or your server is not executing the script properly. Fix this now, before you go on to try to create any other active web pages.
ADO
Before we move on to examine the ways we can move data between web pages and a database, a quick recap of ActiveX Data Objects, or ADO, will be useful. ADO is a database access layer developed by Microsoft using ActiveX. ActiveX is no more than a marketing term used by Microsoft to describe their component-based object technology COM, or Component Object Model. The term ActiveX has generally been unpopular with developers, and even Microsoft employees have been known to explain that the ‘X’ is silent in Active(X) Data Objects. ADO is a simple API (Application Programmers Interface) for accessing databases. It is essentially a software component library hosting the small number of components that are necessary to retrieve data from and update data in databases. There are three essential types of object you will need to learn to use to access data: Connection objects: a connection is a software ‘object’ that provides an interconnection between a piece of software and a database. It has been designed so that it is easy to access data from almost any type of database, including Microsoft Access, Paradox, dBase, Oracle, SQL Server, and even structured text files and the data stores used by email servers. Typically a connection object is deployed in a script to create a connection to a particular database and send commands to it Command objects: some of the commands that are sent to a database are more complex than a simple connection object (which has a job of its own to do maintaining a database connection) can handle easily. Typically, when a command contains a number of associated pieces of information (parameters), a command object is used to package and dispatch it via a connection. There is no real need to use command objects for most database operations, but it can make some of them easier to handle Recordset objects: one thing we often want to do is to retrieve data from a database. Using ADO, this data is almost always returned in the form of a Recordset, which acts like a table of data held in computer memory. A Recordset can contain a whole table of data, but more typically, Recordsets are used to collect the results of a query – a single record from a table or joined tables, several selected
A Command object has a set of Parameters (each indicating a piece of information that can be sent to or retrieved from a query) and some Properties (the type of command, its name and whether it will return a Recordset or not) This small set of objects is enough to enable us to do whatever we wish to an existing database. The objects contained in ADO are interrelated so that they collaborate on performing data access tasks for us. A typical sequence of operations we might perform to retrieve some data for display from a database might be something like:
- Connect to a database
- Open the database (making the data in it accessible)
- Send a SQL query to the database connection defining the set of information we want
- Using the Recordset that is returned from the connection as the result of the query, step through the current record in it, displaying the contents of each field in the record
- Try to move to the next row in the Recordset
- If the next row is not empty, return to step 4
- Close the Recordset
- Close the database connection This sequence of steps indicates something about the structure that relates the objects in ADO. A connection is capable of creating a Recordset, and a Recordset contains a sequence of records (rows), each of which may have a number of individual Fields (attributes). The relationships between these objects is structural, and is sometimes referred to as an Object Model – the way in which objects are interconnected to form a coherent and useful assembly. The way we step through the operations is typical of the flow of instructions in an ASP script
- a step by step sequence of small tasks that incrementally perform a larger overall task. As we step through such a sequence, the various ‘objects’ in ASP are made to interact so that they collaborate on some overall task (for example, in steps 3 and 4, we send a SQL query to a connection, which responds by giving us back a Recordset full of data). In this way, the object model, rather than any individual object, is responsible for performing some task. Database Neutrality One important feature of ADO is that it tries not to make distinctions between different types of database. The ADO code to retrieve data from an Oracle server is exactly the same as the code we would use to retrieve the data from an Access database with a similar data structure. It is impossible for ADO to be 100% neutral in this respect (some database systems provide advanced features that not all of them do), but as much as possible, ADO hides the idiosyncrasies of the underlying databases, which is a very good reason to use it. By providing for transparent interaction with any of a range of database types, ADO makes it possible to change the underlying database without ADO code needing to be radically re-
written. For example, I may start with a small website that caters for a small number of visitors and use an Access database as the underlying database. If my website became very popular over time, it might be sensible to transfer the data to a SQL Server database, since this provides for much higher data throughput. Using ADO, only minimal changes would be needed to make the transition from Access to ADO (provided I did not immediately need to make use of some of the more industrial-strength features of SQL server, since for these, it would almost certainly be necessary to change the ADO code significantly).
IIS Server Objects
IIS (and Personal Web Server) make a number of standard objects available to ASP code. Typically these objects perform complex functions such as extracting useful information from web requests, packaging up web responses to send back to the clients’ browsers, and maintaining the state of clients’ sessions and the application itself. It also provides a simple mechanism for deploying other objects that are not part of IIS, such as ADO connections and commands, on request. IIS separates out ASP code from html code as it sends a response back to a request. While plain HTML is passed directly into the response, ASP code is executed by one of the available script engines attached to IIS. The script engine interprets the ASP code (e.g. VBScript), and returns a result to IIS, which then passes this on into the response. ASP script is lines of script code within any script tags <% and %> in an ASP file. A typical script statement might evaluate an expression (e.g. 2+2) and calculate its result (e.g. 4). An ASP file may contain any number of blocks of script interspersed between blocks of HTML, and in each case, IIS will direct HTML straight back into the response but will have ASP code executed by the script engine. Within the ASP statements, there will usually be references to a number of objects that IIS is aware of. These objects perform standard functions that would be too complex or too mundane to write script code for. Objects in IIS are responsible for representing information about the page request (the Request object), sending and controlling the sending of results back into the response (the Response object), managing various aspects of the server engine itself (the Server object) and keeping track of information that is used by the entire ASP application (the Application object) and by individual sessions (the Session object). Other objects collect data from a client’s browser (Form), store data on a client’s PC (Cookies) and allow script to access information specific to the server (ServerVariables). Server The Server object is used mostly to create other, non-native (to ASP) ob jects. For example, to create an ADO Connection object, we would use a statement like: Set conn = Server.CreateObject(“ADODB.Connection”) where “ADODB.Connection” is the name used to identify database connection objects to the Windows registry. We can also get the server to do other things that are not specific to any one website on the server, such as: Server.ScriptTimeOut = 90
The code Sub Application_OnStart() defines the entry point to a subroutine (a block of code that is executed as a sequence of instructions) that will execute automatically when a site starts up. Session A session object can be maintained for each and every current user of the site. This allows us to store a small amount of information for each user between pages – for example, we can establish who a user is when they first log in to the site by making them visit a log-in page, which would allow us to authenticate users of a restricted site, or provide us with an identity that we can use to record purchases in a shopping cart style website. Once someone has logged in for the first time and we have collected some data on their identity (typically in a database Recordset), we would not want to ask them to log in again for each page they visit, so we could store the user’s login-identity in a session variable: Session(“UserID”) = RS(“UserID”) In the above statement, RS is the database Recordset containing user data, and UserID is the name of the field in the current record that stores the user’s identification (typically a customer number, or possibly a nickname or email address). Now, in each subsequent page- visit, the user’s ID will be accessible as: Session(“UserID”) Since we can create and use as many session variables as we like, we could continue to define more variables to store, for example, user’s name and other details, details of current purchases in a shopping cart, and so on. However, since each session variable will require a block of information to be passed back and forth between the web server and the browser each time an ASP page is accessed, the result of too many session variables can be to slow down the web accesses and make the site appear more sluggish. For good website design, it is better to minimize the amount of data being passed back and forth between browser and server – typically we would store the bulk of the data in a database and just use session variables to pass key values back and forth, so that we could always identify the database entries that stored the information we needed. Request The Request object is used in ASP script to gain access to information that comes from the browser. It has four properties that give this access: QueryString : simple interactions with an ASP page (GET operations) can contain further information in the (modified) URL in the form of Name=Value pairs. The QueryString property allows these to be accessed. Form : this property allows you to retrieve values entered into and POSTed from a form on the requesting web page. Each control on a HTML form is represented in the form object, and the collection of Form values can amount to a significant quantity of information sent from a browser to a website
ServerVariables : Using this property, you can determine the type of browser that sent the request (e.g. Internet Explorer 5.0, Netscape) and other information (e.g. the visitor’s IP address) Cookies : this property allows you to access data that you previously stored in the user’s computer; these are typically small configuration files that allow you to recognise a browser that had previously visited the site, and are typically used to allow you to ‘personalize’ the pages you send back. QueryString There are two types of request that can be sent from a user’s browser. A GET operation (the simplest one) can contain up to 255 characters worth of data appended to the URL used to access your site. For example, a HTML file might contain a number of different hyperlinks which all lead to the same ASP page. To identify which link was used, the hyperlinks could include a different Name/Value pair in each link. For example, Figure 8: A hyperlink with a query string (link is displayed in the status bar). Note the four links at the bottom of the page (First, Previous, Next and Last), and the URL shown in the status bar, which is the URL that will be used if the mouse is clicked when the pointer is over the ‘previous’ link, as shown. This would be coded into the html page as a set of page-links as shown in bold below:
DreamHome Estate Agents
Information: Select a page...
**First Previous Next Last**
Listing 4: HTML code with hrefs defining links with query strings.
Join our Mailing List
Please enter the details below (do not omit any items marked *):
* First Name:
* Last Name:
Telephone:
* Street Address:
* City :
* PostCode:
Email:
Where do you want to find a house:
Preferred Rental Type: House Flat
Maximum Monthly Rent: £
Listing 5: HTML code to produce the form shown in figure 9 All of the controls on the above form are encoded in the web form as , or tags. At the receiving ASP page, the information can be extracted using Request.Form(“”), where represents whatever tag name has been given to each of the controls on the form. For example, Request.Form(“cboRegion”) will refer to the selection made in the drop-down box labelled “Where do you want to find a house”, and will return one of the specified Where they are allowed by the browser, Cookies provide the ideal way to track the clients of your active web-site. A Cookie is a small amount of data that is distinguished by it being stored (legally) on the PC that is used to access a web site. Cookies are retrieved as members of the Cookies property of the Request object, and set as members of the Cookies property of the Response object. Because Cookies are stored ‘client-side’, they do not take up any space on the server, and so are the ideal scalable storage mechanism – it does not matter how many clients your web site has since they all store (at least some of) their own data. A small amount of data left in a cookie on the client’s PC can be retrieved by a statement list: LastVisitDate = Request.Cookies(“LastVisited”) The data would have been left on the site by assigning a value to a cookie in the Response object’s Cookies collection. Storing small amounts of data like this can do no harm to a client’s computer and can be used to enhance the way they use your site. For example, you can personalize a page so that it is able to greet the client by name, remind them of their current status or guide them to new information that might have been added since the last time they visited the site. However, the Internet is no longer a medium that you can trust (whether you are a client browsing web-sites or a host of a web site); some clients may be malicious and try to extract sensitive information from your site, some websites act in highly suspect ways leaving small bits of software that can track your web page usage or even relay all your keystrokes back to the site (a serious security problem if you do Internet Banking, for example). Since cookies can store data that other websites might misuse, some people turn off the facility for their browser to store them, and since we can not every guarantee that a client will allow cookies, it is best not to assume that any client does. Response The response object is there to allow you to direct information back to the browser that issued a request. It provides ways to control how the information is sent to the browser (whether it is sent in small chunks as and when the ASP page issues its Response.Write statements, or collated into larger blocks of data to save information ‘dribbling’ back to the browser if a request may take some time to process) and allows data to be sent to the browser directly (Response.Write) or from another file (Reponse.Redirect). Mainly, the Response object is used to ‘write’ data back to the browser, whether this is literal text (Response.Write “Hello Client”), the result of Script functions (Response.Write Date), data retrieved from an ADO data session (Response.Write RS(“ClientName”), or a mixture of all of these: Response.Write “Hello “ & _ RS(“ClientName”) & “ we’ve not heard from you since “ & _ Response.Cookies(“LastDate” (Note that in VBScript, a statement, which normally must appear in a single line of code, can be broken to run over several lines, by inserting line continuation sequences [a space followed by an underscore] between elements.)
