Run MySQL in Windows

Step 1: Open Command Prompt
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -p

Step 2:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hibernatetutorial |
...

The above command shows all the existing databases.

Step 3:
Create new MySQL user
CREATE USER 'sark'@'localhost' IDENTIFIED BY 'sark';

Step 4:
Grant Permissions.
GRANT ALL PRIVILEGES ON *.* TO 'sark'@'localhost' IDENTIFIED BY 'sark' WITH GRANT OPTION;


Step 5:
To check the existing users.
mysql> select User,Host from mysql.user;
+-----------+--------------+
| User | Host |
+-----------+--------------+
| root | % |
| sark | localhost |
+-----------+--------------+

Step 6: To check the Permissions/privileges.

mysql> show grants for sark@localhost;
+-------------------------------------------------------------------------------
-------------------------------------------------------+
| Grants for sark@localhost
|
+-------------------------------------------------------------------------------
-------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sark'@'localhost' IDENTIFIED B
Y PASSWORD '*A231ED0CF53E6EF33A1A79930EB2A82DB89812F2' |
+-------------------------------------------------------------------------------
-------------------------------------------------------+

Step 6:
Exit from root user and login as 'sark' user;

mysql> exit;
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -usark -psark
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 5.0.24-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step 7:
Create database 'sarkDB'
mysql> CREATE DATABASE sarkDB;

Step 8:
Use Database
mysql> use sarkDB;
Database changed

Step 9:
Create table demo_people.
mysql> CREATE TABLE `demo_people` (
`pid` int(11) NOT NULL,
`name` varchar(20) default NULL,
`phone` int(11) default NULL,
PRIMARY KEY (`pid`)
);
Query OK, 0 rows affected (0.23 sec)

mysql>

AJAX Introduction

AJAX = Asynchronous JavaScript and XML
AJAX is not a new programming language, but a technique for creating better, faster, and more interactive web applications.

With AJAX, your JavaScript can communicate directly with the server, using the JavaScript XMLHttpRequest object. With this object, your JavaScript can trade data with a web server, without reloading the page.

AJAX uses asynchronous data transfer (HTTP requests) between the browser and the web server, allowing web pages to request small bits of information from the server instead of whole pages.

The AJAX technique makes Internet applications smaller, faster and more user-friendly.

AJAX is a browser technology independent of web server software.

AJAX is Based on Web Standards
AJAX is based on the following web standards:

JavaScript
XML
HTML
CSS
The web standards used in AJAX are well defined, and supported by all major browsers. AJAX applications are browser and platform independent.

AJAX is About Better Internet Applications
Web applications have many benefits over desktop applications; they can reach a larger audience, they are easier to install and support, and easier to develop.

However, Internet applications are not always as "rich" and user-friendly as traditional desktop applications.

With AJAX, Internet applications can be made richer and more user-friendly.

AJAX Uses HTTP Requests
In traditional JavaScript coding, if you want to get any information from a database or a file on the server, or send user information to a server, you will have to make an HTML form and GET or POST data to the server. The user will have to click the "Submit" button to send/get the information, wait for the server to respond, then a new page will load with the results.

Because the server returns a new page each time the user submits input, traditional web applications can run slowly and tend to be less user-friendly.

With AJAX, your JavaScript communicates directly with the server, through the JavaScript XMLHttpRequest object

With an HTTP request, a web page can make a request to, and get a response from a web server - without reloading the page. The user will stay on the same page, and he or she will not notice that scripts request pages, or send data to a server in the background.

The XMLHttpRequest Object
By using the XMLHttpRequest object, a web developer can update a page with data from the server after the page has loaded!

AJAX was made popular in 2005 by Google (with Google Suggest).

Google Suggest is using the XMLHttpRequest object to create a very dynamic web interface: When you start typing in Google's search box, a JavaScript sends the letters off to a server and the server returns a list of suggestions.

The XMLHttpRequest object is supported in Internet Explorer 5.0+, Safari 1.2, Mozilla 1.0 / Firefox, Opera 8+, and Netscape 7.


Your First AJAX Application
To understand how AJAX works, we will create a small AJAX application.

First, we are going to create a standard HTML form with two text fields: username and time. The username field will be filled in by the user and the time field will be filled in using AJAX.

The HTML file will be named "testAjax.htm", and it looks like this (notice that the HTML form below has no submit button!):

Name:
Time:

AJAX - Browser Support
The keystone of AJAX is the XMLHttpRequest object.

Different browsers use different methods to create the XMLHttpRequest object.

Internet Explorer uses an ActiveXObject, while other browsers uses the built-in JavaScript object called XMLHttpRequest.

To create this object, and deal with different browsers, we are going to use a "try and catch" statement. You can read more about the try and catch statement in our JavaScript tutorial.

Let's update our "testAjax.htm" file with the JavaScript that creates the XMLHttpRequest object:

Name:
Time:

Example explained: First create a variable xmlHttp to hold the XMLHttpRequest object.

Then try to create the object with XMLHttp=new XMLHttpRequest(). This is for the Firefox, Opera, and Safari browsers. If that fails, try xmlHttp=new ActiveXObject("Msxml2.XMLHTTP") which is for Internet Explorer 6.0+, if that also fails, try xmlHttp=new ActiveXObject("Microsoft.XMLHTTP") which is for Internet Explorer 5.5+

If none of the three methods work, the user has a very outdated browser, and he or she will get an alert stating that the browser doesn't support AJAX.

Note: The browser-specific code above is long and quite complex. However, this is the code you can use every time you need to create an XMLHttpRequest object, so you can just copy and paste it whenever you need it. The code above is compatible with all the popular browsers: Internet Explorer, Opera, Firefox, and Safari.

The next chapter shows how to use the XMLHttpRequest object to communicate with the server.

AJAX - More About the XMLHttpRequest Object
Before sending data to the server, we have to explain three important properties of the XMLHttpRequest object.

The onreadystatechange Property
After a request to the server, we need a function that can receive the data that is returned by the server.

The onreadystatechange property stores the function that will process the response from a server. The following code defines an empty function and sets the onreadystatechange property at the same time:

The readyState Property
The readyState property holds the status of the server's response. Each time the readyState changes, the onreadystatechange function will be executed.

Here are the possible values for the readyState property:
State Description
0 The request is not initialized
1 The request has been set up
2 The request has been sent
3 The request is in process
4 The request is complete

We are going to add an If statement to the onreadystatechange function to test if our response is complete (this means that we can get our data):

xmlHttp.onreadystatechange=function()
{
if(xmlHttp.readyState==4)
{
// Get the data from the server's response
}
}

The responseText Property
The data sent back from the server can be retrieved with the responseText property.

In our code, we will set the value of our "time" input field equal to responseText:

xmlHttp.onreadystatechange=function()
{
if(xmlHttp.readyState==4)
{
document.myForm.time.value=xmlHttp.responseText;
}
}

AJAX - Sending a Request to the Server
To send off a request to the server, we use the open() method and the send() method.

The open() method takes three arguments. The first argument defines which method to use when sending the request (GET or POST). The second argument specifies the URL of the server-side script. The third argument specifies that the request should be handled asynchronously. The send() method sends the request off to the server. If we assume that the HTML and ASP file are in the same directory, the code would be:

xmlHttp.open("GET","time.asp",true);
xmlHttp.send(null);

Now we must decide when the AJAX function should be executed. We will let the function run "behind the scenes" when the user types something in the username text field:

Name: onkeyup="ajaxFunction();" name="username" />
Time:

Our updated AJAX-ready "testAjax.htm" file now looks like this:

Name: onkeyup="ajaxFunction();" name="username" />
Time:

AJAX - The Server-Side ASP Script
Now we are going to create the script that displays the current server time.

The responseText property (explained in the previous chapter) will store the data returned from the server. Here we want to send back the current time. The code in "time.asp" looks like this:

<%
response.expires=-1
response.write(time)
%>

Note: The Expires property sets how long (in minutes) a page will be cached on a browser before it expires. If a user returns to the same page before it expires, the cached version is displayed. Response.Expires=-1 indicates that the page will never be cached.

MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN

First, some sample data:
Mr Brown, Person number 1, has a phone number 01225 708225
Miss Smith, Person number 2, has a phone number 01225 899360
Mr Pullen, Person number 3, has a phone number 01380 724040
and also:
Person number 1 is selling property number 1 - Old House Farm
Person number 3 is selling property number 2 - The Willows
Person number 3 is (also) selling property number 3 - Tall Trees
Person number 3 is (also) selling property number 4 - The Melksham Florist
Person number 4 is selling property number 5 - Dun Roamin.



mysql> select * from demo_people;
+------------+--------------+------+
| name | phone | pid |
+------------+--------------+------+
| Mr Brown | 01225 708225 | 1 |
| Miss Smith | 01225 899360 | 2 |
| Mr Pullen | 01380 724040 | 3 |
+------------+--------------+------+
3 rows in set (0.00 sec)

mysql> select * from demo_property;
+------+------+----------------------+
| pid | spid | selling |
+------+------+----------------------+
| 1 | 1 | Old House Farm |
| 3 | 2 | The Willows |
| 3 | 3 | Tall Trees |
| 3 | 4 | The Melksham Florist |
| 4 | 5 | Dun Roamin |
+------+------+----------------------+
5 rows in set (0.00 sec)

mysql>



If I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:



mysql> select name, phone, selling
from demo_people join demo_property
on demo_people.pid = demo_property.pid;
+-----------+--------------+----------------------+
| name | phone | selling |
+-----------+--------------+----------------------+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

mysql>



If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every PERSON gets a mention:



mysql> select name, phone, selling
from demo_people left join demo_property
on demo_people.pid = demo_property.pid;
+------------+--------------+----------------------+
| name | phone | selling |
+------------+--------------+----------------------+
| Mr Brown | 01225 708225 | Old House Farm |
| Miss Smith | 01225 899360 | NULL |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+------------+--------------+----------------------+
5 rows in set (0.00 sec)

mysql>



If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join - im my example, that means that each property gets a mention even if we don't have seller details:



mysql> select name, phone, selling
from demo_people right join demo_property
on demo_people.pid = demo_property.pid;
+-----------+--------------+----------------------+
| name | phone | selling |
+-----------+--------------+----------------------+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL | NULL | Dun Roamin |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

mysql>



An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatability and doesn't add an extra capabilities.

Java API for XML Processing

The Java API for XML Processing (JAXP) is for processing XML data using applications written in the Java programming language. JAXP leverages the parser standards Simple API for XML Parsing (SAX) and Document Object Model (DOM) so that you can choose to parse your data as a stream of events or to build an object representation of it. JAXP also supports the Extensible Stylesheet Language Transformations (XSLT) standard, giving you control over the presentation of the data and enabling you to convert the data to other XML documents or to other formats, such as HTML. JAXP also provides namespace support, allowing you to work with DTDs that might otherwise have naming conflicts.

Designed to be flexible, JAXP allows you to use any XML-compliant parser from within your application. It does this with what is called a pluggability layer, which lets you plug in an implementation of the SAX or DOM API. The pluggability layer also allows you to plug in an XSL processor, letting you control how your XML data is displayed.

The basic outline of the SAX parsing APIs are shown in Figure 4-1. To start the process, an instance of the SAXParserFactory class is used to generate an instance of the parser.



The main JAXP APIs are defined in the javax.xml.parsers package. That package contains vendor-neutral factory classes--SAXParserFactory, DocumentBuilderFactory, and TransformerFactory--which give you a SAXParser, a DocumentBuilder, and an XSLT transformer, respectively. DocumentBuilder, in turn, creates a DOM-compliant Document object.

The factory APIs let you plug in an XML implementation offered by another vendor without changing your source code. The implementation you get depends on the setting of the javax.xml.parsers.SAXParserFactory, javax.xml.parsers.DocumentBuilderFactory, and javax.xml.transform.TransformerFactory system properties, using System.setProperties() in the code, in an Ant build script, or -DpropertyName="..." on the command line. The default values (unless overridden at runtime on the command line or in the code) point to Sun's implementation.

The SAX and DOM APIs are defined by the XML-DEV group and by the W3C, respectively. The libraries that define those APIs are as follows:

javax.xml.parsers: The JAXP APIs, which provide a common interface for different vendors' SAX and DOM parsers
org.w3c.dom: Defines the Document class (a DOM) as well as classes for all the components of a DOM
org.xml.sax: Defines the basic SAX APIs
javax.xml.transform: Defines the XSLT APIs that let you transform XML into other forms
The Simple API for XML (SAX) is the event-driven, serial-access mechanism that does element-by-element processing. The API for this level reads and writes XML to a data repository or the web. For server-side and high-performance applications, you will want to fully understand this level. But for many applications, a minimal understanding will suffice.

The DOM API is generally an easier API to use. It provides a familiar tree structure of objects. You can use the DOM API to manipulate the hierarchy of application objects it encapsulates. The DOM API is ideal for interactive applications because the entire object model is present in memory, where it can be accessed and manipulated by the user.

On the other hand, constructing the DOM requires reading the entire XML structure and holding the object tree in memory, so it is much more CPU- and memory-intensive. For that reason, the SAX API tends to be preferred for server-side applications and data filters that do not require an in-memory representation of the data.

Finally, the XSLT APIs defined in javax.xml.transform let you write XML data to a file or convert it into other forms. And, as you'll see in the XSLT section of this tutorial, you can even use it in conjunction with the SAX APIs to convert legacy data to XML.


The parser wraps a SAXReader object. When the parser's parse() method is invoked, the reader invokes one of several callback methods implemented in the application. Those methods are defined by the interfaces ContentHandler, ErrorHandler, DTDHandler, and EntityResolver.

Here is a summary of the key SAX APIs:

SAXParserFactory

A SAXParserFactory object creates an instance of the parser determined by the system property, javax.xml.parsers.SAXParserFactory.

SAXParser

The SAXParser interface defines several kinds of parse() methods. In general, you pass an XML data source and a DefaultHandler object to the parser, which processes the XML and invokes the appropriate methods in the handler object.

SAXReader

The SAXParser wraps a SAXReader. Typically, you don't care about that, but every once in a while you need to get hold of it using SAXParser's getXMLReader() so that you can configure it. It is the SAXReader that carries on the conversation with the SAX event handlers you define.

DefaultHandler

Not shown in the diagram, a DefaultHandler implements the ContentHandler, ErrorHandler, DTDHandler, and EntityResolver interfaces (with null methods), so you can override only the ones you're interested in.

ContentHandler

Methods such as startDocument, endDocument, startElement, and endElement are invoked when an XML tag is recognized. This interface also defines the methods characters and processingInstruction, which are invoked when the parser encounters the text in an XML element or an inline processing instruction, respectively.

ErrorHandler

Methods error, fatalError, and warning are invoked in response to various parsing errors. The default error handler throws an exception for fatal errors and ignores other errors (including validation errors). That's one reason you need to know something about the SAX parser, even if you are using the DOM. Sometimes, the application may be able to recover from a validation error. Other times, it may need to generate an exception. To ensure the correct handling, you'll need to supply your own error handler to the parser.

DTDHandler

Defines methods you will generally never be called upon to use. Used when processing a DTD to recognize and act on declarations for an unparsed entity.

EntityResolver

The resolveEntity method is invoked when the parser must identify data identified by a URI. In most cases, a URI is simply a URL, which specifies the location of a document, but in some cases the document may be identified by a URN--a public identifier, or name, that is unique in the web space. The public identifier may be specified in addition to the URL. The EntityResolver can then use the public identifier instead of the URL to find the document--for example, to access a local copy of the document if one exists.

A typical application implements most of the ContentHandler methods, at a minimum. Because the default implementations of the interfaces ignore all inputs except for fatal errors, a robust implementation may also want to implement the ErrorHandler methods.

The SAX Packages
The SAX parser is defined in the packages listed in Table 4-1.

Table 4-1 SAX Packages Package Description
org.xml.sax Defines the SAX interfaces. The name org.xml is the package prefix that was settled on by the group that defined the SAX API.
org.xml.sax.ext Defines SAX extensions that are used for doing more sophisticated SAX processing--for example, to process a document type definition (DTD) or to see the detailed syntax for a file.
org.xml.sax.helpers Contains helper classes that make it easier to use SAX--for example, by defining a default handler that has null methods for all the interfaces, so that you only need to override the ones you actually want to implement.
javax.xml.parsers Defines the SAXParserFactory class, which returns the SAXParser. Also defines exception classes for reporting errors.

The Document Object Model APIs
Figure 4-2 shows the DOM APIs in action.


You use the javax.xml.parsers.DocumentBuilderFactory class to get a DocumentBuilder instance, and you use that instance to produce a Document object that conforms to the DOM specification. The builder you get, in fact, is determined by the system property javax.xml.parsers.DocumentBuilderFactory, which selects the factory implementation that is used to produce the builder. (The platform's default value can be overridden from the command line.)

You can also use the DocumentBuilder newDocument() method to create an empty Document that implements the org.w3c.dom.Document interface. Alternatively, you can use one of the builder's parse methods to create a Document from existing XML data. The result is a DOM tree like that shown in Figure 4-2.


--------------------------------------------------------------------------------

Note: Although they are called objects, the entries in the DOM tree are actually fairly low-level data structures. For example, consider this structure: blue. There is an element node for the color tag, and under that there is a text node that contains the data, blue! This issue will be explored at length in the DOM section of the tutorial, but developers who are expecting objects are usually surprised to find that invoking getNodeValue() on the element node returns nothing! For a truly object-oriented tree, see the JDOM API at http://www.jdom.org.


--------------------------------------------------------------------------------

The DOM Packages
The Document Object Model implementation is defined in the packages listed in Table 4-2.

Table 4-2 DOM Packages Package Description
org.w3c.dom Defines the DOM programming interfaces for XML (and, optionally, HTML) documents, as specified by the W3C.
javax.xml.parsers Defines the DocumentBuilderFactory class and the DocumentBuilder class, which returns an object that implements the W3C Document interface. The factory that is used to create the builder is determined by the javax.xml.parsers system property, which can be set from the command line or overridden when invoking the new Instance method. This package also defines the ParserConfigurationException class for reporting errors.

The Extensible Stylesheet Language Transformations APIs
Figure 4-3 shows the XSLT APIs in action.

A TransformerFactory object is instantiated and used to create a Transformer. The source object is the input to the transformation process. A source object can be created from a SAX reader, from a DOM, or from an input stream.

Similarly, the result object is the result of the transformation process. That object can be a SAX event handler, a DOM, or an output stream.

When the transformer is created, it can be created from a set of transformation instructions, in which case the specified transformations are carried out. If it is created without any specific instructions, then the transformer object simply copies the source to the result.

The XSLT Packages
The XSLT APIs are defined in the packages shown in Table 4-3.



Table 4-3 XSLT Packages Package Description
javax.xml.transform Defines the TransformerFactory and Transformer classes, which you use to get an object capable of doing transformations. After creating a transformer object, you invoke its transform() method, providing it with an input (source) and output (result).
javax.xml.transform.dom Classes to create input (source) and output (result) objects from a DOM.
javax.xml.transform.sax Classes to create input (source) objects from a SAX parser and output (result) objects from a SAX event handler.
javax.xml.transform.stream Classes to create input (source) objects and output (result) objects from an I/O stream.

Simple API for XML (SAX), an event-driven, serial-access mechanism for accessing XML documents. This protocol is frequently used by servlets and network-oriented programs that need to transmit and receive XML documents, because it's the fastest and least memory-intensive mechanism that is currently available for dealing with XML documents, other than StAX.

SAX is an event-driven model (you provide the callback methods, and the parser invokes them as it reads the XML data), and that makes it harder to visualize. Finally, you can't "back up" to an earlier part of the document, or rearrange it, any more than you can back up a serial data stream or rearrange characters you have read from that stream.

For those reasons, developers who are writing a user-oriented application that displays an XML document and possibly modifies it will want to use the DOM mechanism

even if you plan to build DOM applications exclusively, there are several important reasons for familiarizing yourself with the SAX model:

Same Error Handling: The same kinds of exceptions are generated by the SAX and DOM APIs, so the error handling code is virtually identical.
Handling Validation Errors: By default, the specifications require that validation errors (which you'll learn more about in this part of the tutorial) are ignored. If you want to throw an exception in the event of a validation error (and you probably do), then you need to understand how SAX error handling works.
Converting Existing Data: There is a mechanism we can use to convert an existing data set to XML. However, taking advantage of that mechanism requires an understanding of the SAX model.

SAX is fast and efficient, but its event model makes it most useful for such state-independent filtering. For example, a SAX parser calls one method in your application when an element tag is encountered and calls a different method when text is found. If the processing you're doing is state-independent (meaning that it does not depend on the elements have come before), then SAX works fine.

On the other hand, for state-dependent processing, where the program needs to do one thing with the data under element A but something different with the data under element B, then a pull parser such as the Streaming API for XML (StAX) would be a better choice. With a pull parser, you get the next node, whatever it happens to be, at any point in the code that you ask for it. So it's easy to vary the way you process text (for example), because you can process it multiple places in the program.

SAX requires much less memory than DOM, because SAX does not construct an internal representation (tree structure) of the XML data, as a DOM does. Instead, SAX simply sends data to the application as it is read; your application can then do whatever it wants to do with the data it sees.

Pull parsers and the SAX API both act like a serial I/O stream. You see the data as it streams in, but you can't go back to an earlier position or leap ahead to a different position. In general, such parsers work well when you simply want to read data and have the application act on it.
But when you need to modify an XML structure--especially when you need to modify it interactively--an in-memory structure makes more sense. DOM is one such model.