DATABASE CONNECTIVITY

DATABASE CONNECTIVITY INTRODUCTION to SQL  What is SQL?  SQL stands for Structured Query Language. It is pronounced either as 'sequel' or simply as the letters 'S-Q-L'. SQL is a language that has been specially designed for communicating and interacting with database. It is governed by the ANSI (American National Standards Institute) standards committee and is often referred to as ANSI SQL. Some DBMS (database management system) vendors have added their own statements or instructions to the language to provide extra functionality, but we will be concentrating on standard ANSI SQL which should work across most major databases without any modifications.  As a language, SQL is relatively simple yet deceptively powerful. The simple statements used disguise a very powerful language that allows you to perform many varied and complex database operations. Theory of Inference Engines  What is an inference engine?  An inference engine is the processing part of an expert system, a system that will derive answers and conclusions from various facts and rules stored in its database. These facts and rules are known as the knowledge base and the whole idea is based on artificial intelligence methods and techniques. The knowledge base may be very large. Hence the reason why the inference rules are needed to help deduce answers from the vast amounts of information. Building an inference engine could be thought of as trying to emulate the human ability to evaluate information and come to reasonable conclusions. Examples:  1. I am a man. 2. All men are mortal.  Therefore I am mortal! 1. Sarah is either happy or angry. 2. Sarah is not happy.  Therefore we can infer that Sarah is angry! Types of inferencing  There are two different types of inferencing that we will consider. These are forward chaining, or data driven as it is also know, or backward chaining, also known as goal driven. Forward chaining  Forward chaining inferencing refers to when a conclusion is reached based on an event. For example, a boy runs in front of your car so you put your foot on the brake. The idea here is that new information causes us to examine a specific rule without having to consider other rules not relevant to the situation. Backward chaining  Backward chaining inferencing refers to the types of situations where you are trying to explain what caused a present situation that has been observed. For example, if the car does not start then you would have to deduce why this is the case. The idea here is that since the cause of the situation is unknown or unclear, then questions and tests have to be used to come to a reasonable explanation as to the cause of the situation. In general, forward chaining is a more direct approach to use as it is less complex and involves less variables.   DELETE  The DELETE statement is relatively simple to understand but can be used to delete one or more rows from one or more tables. As for the UPDATE statement, it is important to remember not to omit the WHERE clause when using this statement, otherwise all data in a table can be deleted.  The DELETE statement takes the following form:  DELETE FROM tablename1, tablename2 WHERE condition1, condition2  An example of DELETE in action is as follows:  Example:  DELETE FROM tblAddressBook WHERE Age andlt; 21 This example will delete all rows in the table where the age is less than 21. If the WHERE clause had been omitted then all rows would have been deleted from the table. DELETE cannot be used to delete columns, only entire rows. To delete all values from a column use the UPDATE statemen. Basic operations There are four basic operations that we will learn about. SELECT - retrieves data  INSERT - adds data  UPDATE - edits existing data DELETE - removes data  Basic operators The following operators can be used in SQL: Operator Description = Equality <> Non-equality != Non-equality < Less than <= Less than or equal to !< Not less than > Greater than >= Greater than or equal to !>Not greater than BETWEEN Between two specified values IS NULL Is a NULL value INSERT The INSERT statement is used to add rows to a table. There are two variations on how the INSERT statement can be used. The first way allows you to add one new row to the table using a set of specified values. The second way uses a SELECT query and allows you to add multiple rows to a table. This variation is known as INSERT SELECT. Inserting a single row To insert a row into a table you must specify the table name and a list of values to insert into the table, as follows: Example: INSERT INTO tblAddressBook VALUES ('121', 'Stephen Smith', '24', '45 Great Eastern Road, Glasgow', 'G1A 1BA', '0123 456 7890', 'stephen@123abc.co.uk', NULL) This example will insert a single row into a table. The values to store are provided in the VALUES clause, and a value must be provided for every field. If there is no value to enter for a field, NULL should be entered, as has been done above for the MobilePhone field. This method of using INSERT is relatively simple but in general should be avoided. This is because the order of the values you have entered must match the order of the fields (also referred to as columns) in the table. If the order of the columns changes then it will break your SQL code. Therefore, the following syntax should be used, where both the field names and the values for those fields are specified: SELECT The SELECT statement is used to retrieve data from a database, and is probably the statement you will use most. Minimum requirements The simplest of SELECT statement consists of a minimum of two parts (called clauses): SELECT field1, field2... FROM table1, table2... The SELECT clause determines what you want to retrieve, and the FROM clause determines where you want to retrieve it from. The following example demonstrates these clauses in use: Example: SELECT Name, Address, PhoneNumber FROM tblAddressBook At the moment only the Name, Address and PhoneNumber fields will be retrieved from the Address Book table. There may be other fields such as Age, PostCode, EmailAddress, MobileNumber that we are interested in. If we wish to retrieve all fields for this table we can use the asterisk (*) wildcard character in place of the list of field names. The above example becomes:  Example: SELECT * FROM tblAddressBook Filtering data  Up to now we have been retrieving all of the records from the Address Book table. Normally we would only want a subset of these records, for example we may be interested in just those records where the person's age is greater than 25. The WHERE clause is used to achieve this and looks as follows: WHERE condition1, condition2... The example now becomes:  Example:  SELECT * FROM tblAddressBook WHERE Age andgt; 25 Sorting data  Finally, the order the data is retrieved in is important. With a list of names, it would be nice if we could view them in alphabetical order. This is where the ORDER BY clause can be used: ORDER BY field1[ASC|DESC], field2[ASC|DESC]... The ASC|DESC part of the clause is optional and is used for returning the sorted data in either ascending or descending order respectively. By default the sort order is ascending. Our completed example is as follows: Example: SELECT * FROM tblAddressBook WHERE Age andgt; 25 ORDER BY Name ASC  UPDATE The UPDATE statement allows you to modify existing data in a database. There are three clauses to the basic UPDATE statement: UPDATE tablename SET field1=value1, field2=value2... WHERE condition1, condition2 The WHERE clause is optional, but should not be omitted unless you deliberately intend to update all rows in a table. The WHERE clause determines which rows in the table should be updated, while the SET clause specifies which columns should be updated and the values to set them to. The UPDATE clause specifies the table to be updated. Only one table can be updated at a time. The following example demonstrates how to use the UPDATE statement: Example: UPDATE tblAddressBook SET Address = '23 London Street, London', PostCode = 'L99 9ZZ' PhoneNumber = '0976 543 210' WHERE Name = 'Stephen Smith' In this example, Stephen Smith's address and phone number are updated. If the WHERE clause had been omitted, then all rows in the table would have had their address and phone number updated to the above values. In the SET clause, the new values for the fields can also be specified by looking up fields from other tables, or from an expression. For example, to update the age column the following expression can be used in the SET clause. Example: UPDATE tblAddressBook SET Age = Age + 1 WHERE Submitting information In order to use a database with your web site, you must be able to connect to and interact with the database from your web pages. In order to interact with the database, a user will often use a web form to submit information to a web application, which will process that information, reading and writing to the database as needed. There are different ways in which you can submit information from a web form and these are discussed below.  GET and POST When submitting a form on a web site, two of the most common methods for submitting the form are GET and POST. The method specified affects the way in which the information specified in the form is transmitted to the web server. When information is submitted from a form, it is submitted as name=value pairs, separated by an ampersand (&) character. Spaces are replaced by the addition (+) symbol Using a simple form as an example, with just three fields, the information would be submitted as follows: name=Billy+Brown&age=27&location=glasgow If GET is specified as the method, then the browser will append the data to the actual URL of the receiving page. Using the same example, the GET method would transfer the information as follows:  GET http://www.somewebsite.com/guestbook.asp?name=Billy+Brown&age=27&location=Glasgow If POST is used as the method instead, then the data would be transferred after the URL and some other header information. The POST method would alert the web server to the fact that information is to follow. Therefore the information would be transferred now as:  POST http://www.somewebsite.com/guestbook.asp ....[further header information] name=Billy+Brown&age=27&location=Glasgow  GET is the default form action, but POST is probably the better method to use as the information is hidden from the user and is therefore not as easily accessible by the user. Mailto An alternative to using GET and POST to submit your web form to a web application or ASP page is to use the mailto action. Mailto will use the e-mail program of the user to create an e-mail which should contain the information specified in the fields of the web form. Using mailto is not totally reliable however and may just create a blank e-mail addressed to the intended recipient, so it should only be used with caution. You should also realise that this method does not allow the user to interact directly with a database as the information is sent to an e-mail address and not a web application. Web applications  There are various types of web application that can be used to parse and process the information submitted by a web form. Two of the most common are CGI applications and scripts, and Active Server Pages (ASP). Whichever method you choose to use, to interact with databases a connection will have to be established between the application and the database, and this will be discussed in more detail later on. CGI applications  CGI stands for Common Gateway Interface. When a form is submitted for a CGI application, a connection is opened with the CGI program on the web server. The program can be written in a variety of ways and languages, and the programmer can use and manipulate the data in whatever way they desire. For example, the application could be a .exe compiled application written using Borland Delphi. Once processing is complete, a resulting page is sent back to the browser. Active Server Pages  Active Server Pages work differently from CGI applications because the code is stored in the same text file that is used to display the web page. When the server realises that it is dealing with an ASP file, it processes the ASP code before sending the resulting page to the browser. Therefore, ASP is independent of browser implementation, unlike JavaScript, which is processed by the browser. ODBC One of the most common methods to access databases is to use ODBC (Open DataBase Connectivity). If you have direct physical access to the web server, you can register a database in the System registry by assigning a data source name to it using the ODBC Administrator from the Control Panel in Windows, as shown in the diagram below. In many cases however you will not have such access to the web server. Instead, the service provider may provide you with a control panel interface that allows you to set-up a data source name. Once you have created a data source name it can then be used in your scripts and programs to reference and access the database. A simple advantage of doing this, instead of referencing a database by specifying its full path, is that the database name or location can be changed and no change needs to be made to your code. The only change that needs to be made is to the data source details. JDBC JDBC (Java Database Connectivity) enables developers to have standard SQL access from their Java applets and Java applications to databases regardless of the database product. JDBC is part of the Java Development Kit (JDK) and defines an application programming interface (API) for Java. In order to use JSBC, you are required to have the JDK 1.1, a JDBC driver and your database. It can sometimes be difficult to find a JDBC driver, but once found JDBC provides you with a uniform interface to databases. Example To illustrate the principles that have been discussed, we will create a working example of some of the basic building blocks of an e-commerce system, using an Access database and Active Server Pages. Step 1 - Create the database The first step is to create the database. Using Access, create a new blank database, and save it with the name "stock.mdb" in an appropriate location. Step 2 - Create the tables, add the necessary fields and add some sample data Create four new tables for the database. Select Design View when prompted each time. The first table is for the stock. As shown in the diagram below, add StockID, Description, Price, and NumberInStock fields to the table. Right click on the StockID field name and select this field to be the Primary Key. Save the table with the name tblStock. Now open the table in Datasheet view and add some sample data to the table, as illustrated in the diagram below. You do not need to enter a value for the StockID field as this is an AutoIncrement field. The second table is for customers and should be called tblCustomer. Add the following fields to this table: CustomerID, Username, Password, Name, Address, and Email. Remember to add the appropriate types for each of the fields as well. You can also create some sample customers if you wish. Thirdly, create a table for orders called tblOrder. This table should contain the following fields: OrderID, CustomerID, DateOfOrder. Note that we have included a foreign key to create a relationship between the tables. Leave this table blank. Finally, create a table for items that will comprise an order. This table should be called tblPurchase and contain the following fields: PurchaseID, OrderID, StockID, Quantity. Step 3 - Register the database in the system registry Your next step is to register the database in the System registry by assigning a data source name to it using the ODBC Administrator from the Control Panel in Windows. Use the name dbMyShop for your data source name. Alternatively, if you are using a remote web server, you may have to use an online control panel to assign the data source name if you do not have direct access to the web server. You should upload the database to the web server using FTP (File Transfer Protocol). Step 4 - Create an ASP page to list the products The next step is to create a web page, formatted however you like, but which includes a section for listing the products in the database. The products in the database will be accessed using ASP code contained within this web page. Therefore, the page should be saved with the .asp extension. Save the file with the name "default.asp". The following ASP code will display the stock items: <% set conn = server.createobject ("adodb.connection") conn.open "dbMyShop" set rs = conn.execute("select * from tblStock order by Price desc") do until (rs.EOF) set StockID = rs("StockID") set Description = rs("Description") set Price = rs("Price") set NumberInStock = rs("NumberInStock") response.write "<FONT size=""3"" color=""black""><B>" & Description & "</B></FONT>" response.write "Price: £" & Price & ". " response.write "Number in stock: " & NumberInStock & "" response.write "Order this item. Quantity: <INPUT type=""text"" name=""" & StockID & """ size=""2""> maxlength=""2"" value=""0""" rs.MoveNext loop rs.close set rs = nothing conn.close %> The following lines may need some explanation: These two lines create a connection to the database, using the data source name we specified earlier: set conn = server.createobject ("adodb.connection") conn.open "dbMyShop" This line creates a variable called rs which points to a record set from the database that is created using a simple SQL select query: set rs = conn.execute("select * from tblStock order by Price desc") Values from the database are then read from the recordset that has been created: set StockID = rs("StockID") set Description = rs("Description") set Price = rs("Price") set NumberInStock = rs("NumberInStock") These lines are written to the web page and form part of the response of the page to the form submission: response.write "<FONT size=""3"" color=""black""><B>" & Description & "</B></FONT>" response.write "Price: £" & Price & ". " response.write "Number in stock: " & NumberInStock & "" response.write "Order this item. Quantity: <INPUT type=""text"" name=""" & StockID & """ size=""2""> maxlength=""2"" value=""0""" The do loop continues until all of the records have been accessed, and then the recordset and connection are closed. The Form You may notice within this code that we have included an INPUT tag, which in this case creates a text field to allow the user to specify how many of the particular item they wish to order. The whole ASP script is within a FORM tag, which has the following format. FORM details: <FORM method="post" action="placeorder.asp" name="frmMyShop"> ....ASP code.... <INPUT type="submit" value="Place order"> </FORM> Post has been used as the method of submission for this form. The form is submitted to another ASP page, which in this case is placeorder.asp. Step 5 - Completing the purchase The placeorder.asp page has a number of jobs. First of all, it will store the details of the items that have been ordered by the customer in hidden fields on this new page. For the sake of simplicity we are not using a shopping basket system. Such a system could use more tables to store details of the items in the current user's basket or could use cookies to store the user's basket. The hidden fields we are using store the StockID and the quantity that the user has ordered. The details of the order should be displayed on screen along with a calculation showing the total cost of the order including VAT, if applicable. Finally, this page should contain fields which allow the user to enter their personal details, such as name and address, as well as their method of payment. This could be directly by credit card using a secure third party online payment system. Once these details have been entered, and the user is happy with their order, they can place their order. When they do this, the details of the order along with the customer's personal details are entered into the database, and the order is placed. This can be achieved using similar ASP code to that used above, except that INSERT and UPDATE SQL statements would be used instead of SELECT. Our e-commerce web application may at this point generate an email to the sales department of the company to notify them that an order has been placed. Generating a notification e-mail (using the JMail ASP component): <% Set JMail = Server.CreateObject("JMail.SMTPMail") JMail.ServerAddress = "smtp.somewebsite.com" JMail.SenderName = "E-commerce web site" JMail.Sender = "webmaster@somewebsite.com" JMail.AddRecipientEx "sales@somewebsite.com", "Sales" JMail.Subject = "An order has been placed" JMail.Body = "............DETAILS OF ORDER..........." JMail.Execute %>  FOR MORE INFORMATION CLICK HERE