14.1.2- The Structured Query Language (SQL)

<< 14.1.1- Using the Command Object

Chapter14

14.1.3- Using Queries and Stored Procedures >>

The Structured Query Language (SQL)

We've been hinting at the usage of Structured Query Language (usually called SQL) over the last few pages, and right back into Chapter 13 . SQL is the universal language used for programming databases, and we'll introduce SQL at this stage because it's at the heart of our ability to issue commands.

 

If you've ever used the Query By Example (QBE) grid in Microsoft Access, then you've already been using SQL (albeit behind the scenes). Every time you request information from an Access database using the QBE grid, the query is translated into SQL and the result is returned to you as a recordset.

 

As we've already said, all SQL does is provide you with a format for retrieving the information you want from the database.

 

SQL seems to have a reputation as being a difficult language, but don't be put off by it – getting the hang of it is really quite simple. In fact, SQL is a declarative language. The term 'declarative' means that we use SQL to tell the computer what it is we want, and then we let the machine decide how best to achieve the correct result. We, as programmers, never need to see the details; all we see is the result.

 

By contrast, other languages (such as Visual Basic,COBOL,C++ or Java) are procedural languages. These languages are characterized by statements, which tell the computer exactly what to do in a structured step-by-step way.

 

We've already seen something close to a SQL command. For example, we used something very like a SQL statement when we used the Recordset object's Filter method in Chapter 13:

 

objRS.Filter = "Director = 'Quentin Tarantino'"

 

Consider the logic behind this: it's asking the Recordset object to select every record whose Director field contains the exact string Quentin Tarantino (and to hide any other records). Even more appropriately, we could use a SQL query when we are requesting data from the data store in the first place:

 

objRS.Open = "SELECT TitleID, Title FROM AllMovies", strConnect

 

This statement tells the data store to select the values of the TitleID and Title fields for all the records in the AllMovies table, and to pump that data into a new Recordset object. In fact, this command uses two SQL keywords – SELECT and FROM. We'll see more of this SQL syntax as we progress through this chapter – but as you can see, it's not too difficult to read.

SELECT, INSERT, UPDATE and DELETE

There are four main different types of command that you can run against the database using the Command object:

 

• A SELECT command returns a recordset, populated with data from the data store
• An INSERT command adds records into your data store
• An UPDATE command updates portions of existing information within a record in the data store (i.e. not just the record in a recordset)
• A DELETE command removes records from the data store

 

As we said earlier, we've been performing SELECT-type commands in all of the ADO examples so far in this book. For example, in the Command.asp example above we used the following command to 'select' every single record and field from the Movies database table:

 

objCommand.CommandText = "AllMovies"

objCommand.CommandType = adCmdTable

Set objRS = objCommand.Execute

 

In fact, we can use a SQL SELECT statement to perform exactly the same thing:

 

objCommand.CommandText = "SELECT * FROM AllMovies"

objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute

 

Note that the SQL SELECT command is the only one of these four commands that returns a recordset. The other commands are not concerned with fetching data from the data store – instead, they're all concerned with making changes to the data store, and as such they don't return any data. Collectively, the INSERT, UPDATE and DELETE commands are known as action commands or action queries.

 

In the course of this chapter, we'll see all four of these SQL commands in action. We'll deal with SELECT first, and see how it gives us more flexibility for selecting data – so we don't have to select entire tables all the time! Then we'll move on to UPDATE, and later in the chapter we'll look at how to use INSERT and DELETE to create and destroy records.

The SELECT Command

So, a very flexible way to request data from a database table is via SQL's SELECT … FROM … clause. As we've already seen, this clause is phrased in a logical way, that is very readable – we specify what data we want, and which table we want it from, and the results of this query are returned in the form of a table.

 

The act of requesting data from a data store in this way is often referred to as a query. Querying the database doesn't affect the structure or contents of the data store, or the order of the data within the data store. A query simply presents the data to the user in a certain way.

A Simple SELECT Command

In its simplest form, the SELECT command will simply return an entire table, or specific columns and rows of a table. Such a query would take the following form:

 

SELECT <field_name(s)> FROM <table_name>

 

So, to extract the list of movie titles from the Movies table, we'd write a SELECT command that queries the database for the Title field of every record in the AllMovies database table, like this:

 

SELECT Title FROM AllMovies

 

We're not limited to querying the data store for one field at a time – we can ask for as many fields as we like (so long as each field we request is a field that belongs to the specified table!). Querying for more than one field is simple – we just list each field name, separated with commas. The following command queries the Cast table for a list of records of characters (held in the CastRole field), along with the actors who portrayed those characters (the CastName field):

 

SELECT CastRole, CastName FROM Cast

 

What if we want to query the database for the data contained in all of the fields in a table? Well, you can do it by using the wildcard * in your query, like this:

 

SELECT * FROM AllMovies

 

This returns the data in every field of record in the AllMovies table. The * symbol is effectively a shorthand which means 'all fields'.

 

Of course, the amount of time it takes to complete a query is related to the amount of data you ask for. So, if you ask for all fields then don't be surprised if your query takes a long time to complete! It's much better to write commands that only query those fields that you're actually going to use. Only ask for all fields if you really do need the data in all of those fields.

Conditional SELECT Commands

As we've seen, we can reduce the amount of data requested by querying for the required fields only. In fact, there's another way that we can reduce the size of our queries – by being specific about which database records we're interested in. For this, we can use a slightly more advanced version of the SQL statement, which has the following form:

 

SELECT <field_name(s)> FROM <table_name> WHERE <condition>

 

This is just an extension of the original SELECT … FROM … clause – we've appended a WHERE … subclause at the end. And again, you'll see that it's very easy to read. The WHERE … subclause allows us to specify what kind of records we're interested in. For example, if we wanted a list of all the films in the AllMovies table that were directed by Quentin Tarantino, we could specify that we're only interested in those specific records:

 

SELECT Title FROM AllMovies WHERE Director = 'Quentin Tarantino'

 

You might notice that the Director Name is surrounded by single quotes. This is because the name is a text string. You might be wondering why we don't use double quotes – after all when using variants, that's what we would do normally. You must consider that, here, the whole SQL statement will be appear in double quotes, which are used to denote where the SQL statement starts and ends. So, in place of double quotes in a SQL statement, we use single quotes instead.

 

The <condition> part of this SQL command is actually something we've seen before. It works in the same way as the Criteria parameter of the Recordset object's Find method and Filter property:

 

strCriteria = "Director = 'Quentin Tarantino'" ' set criteria

objRS.Find strCriteria ' apply criteria

More Complex Conditional SELECT Commands

Now let's go one stage further, and create a SQL command that will return a list containing the movies directed by Quentin Tarantino plus the movies directed by Francis Coppola:

 

SELECT Title FROM AllMovies WHERE Director = 'Francis Coppola'

OR Director = 'Quentin Tarantino'

 

Here, we've just extended the <condition> clause to take in the films of both directors. Let's actually see how you could run this last statement in an ASP script.

1.    You guessed it – we're using the Movies2000 database and the strConnect connection string again! So you need Movie2000.mdb or Movie2000.mdf set up as described in Chapter 12 ; and the DataStore.asp SSI file placed in the \inetpub\wwwroot\BegASPFiles folder, as explained in Chapter 12.

2.    Start your code editor, and create a new file (which we'll call SQLSelect.asp). Type in the following code:

 

<%

Option Explicit

Dim strConnect

%>

<!-- #include file="DataStore.asp" -->

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Using SQL's SELECT Command and the ADO Command Object</TITLE>

</HEAD>

<BODY>

 

<%

Dim objCommand, objRS

Set objCommand = Server.CreateObject("ADODB.Command")

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "SELECT Title, Director FROM AllMovies " & _

"WHERE Director LIKE 'Quentin Tarantino' " & _

" OR Director LIKE 'Francis Coppola'"

objCommand.CommandType = adCmdText

 

Set objRS = objCommand.Execute

Set objCommand = Nothing

 

While Not objRS.EOF

Response.Write objRS("Title") & " was directed by " & objRS("Director") & "<BR>"

objRS.MoveNext

Wend

 

objRS.Close

Set objRS = Nothing

%>

</BODY>

</HTML>

 

3.    Save SQLSelect.asp into the \inetpub\wwwroot\BegASPFiles directory.

4.    Navigate to the SQLSelect.asp page, in your browser:

How It Works

We start by declaring variants for the Command and Recordset objects, and creating an instance of the Command object:

 

Dim objCommand, objRS

Set objCommand = Server.CreateObject("ADODB.Command")

 

Note that we don't need to create a Recordset object using Server.CreateObject – we'll get a real Recordset object shortly, when we use the Execute method.

 

Now we can set the Command object up ready to execute the command. This involves assigning values to the ActiveConnection, CommandText and CommandType properties:

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "SELECT Title, Director FROM AllMovies " & _

"WHERE Director LIKE 'Quentin Tarantino' " & _

" OR Director LIKE 'Francis Coppola'"

objCommand.CommandType = adCmdText

 

For the connection, we're using the strConnect connection string, defined in DataStore.asp. For the command text, we're using a more complex SQL command now – of the form SELECT…FROM…WHERE…. We want to retrieve a recordset containing the Title and Director fields of the AllMovies table; but we're only asking for those movies directed by Tarantino or Coppola, so we're using the WHERE clause to reflect that. The query is in the form of a text string, so the command type is adCmdText.

 

There's an interesting technicality regarding the WHERE clause here – note that we've used the LIKE keyword (an expression parser on character fields) in place of the equals sign (=). You can try the text-based condition Director = 'Quentin Tarantino' instead; but if you're using SQL Server (with OLE-DB or ODBC providers), you'll find that it causes a syntax error. SQL Server doesn't allow you to use text, ntext or image data types in the WHERE clause except with the LIKE keyword. Not all providers are as fussy as this (Access isn't) – but it's something to be aware of. We'll use the LIKE keyword because it's more widely accepted.

 

Now we run the command using the Execute method, and store the results in the Recordset object (here, note that we're using the Set statement because we're assigning an instance of an object to the objRS variable):

 

Set objRS = objCommand.Execute

 

Once we've got our recordset we don't need to keep the Command object any longer. So we'll get rid of it:

 

Set objCommand = Nothing

Lastly, we display the contents of our recordset, using the EOF property to control the While…Wend loop as we've seen in previous examples:

 

While Not objRS.EOF

Response.Write objRS("Title") & " was directed by " & objRS("Director") & "<BR>"

objRS.MoveNext

Wend

Selecting Information from More than One Table

So far, we've been writing queries that request data from just a single database table. However, you have probably noticed that our Movies2000 database comprises sixteen different tables – with names like Movies, Cast, ProductionCompanies, ReleaseDates. Each table contains different types of data (all relating to different aspects of the cinematic recordings listed within).

 

In fact, the AllMovies table is an amalgamation of some of the data contained in the other fifteen tables in the database. We included it for convenience, in order to simplify some of the preceding examples. In a real-life situation, amalgamated tables such as AllMovies are often discouraged.

 

By contrast, multi-table databases are common, and there are a number of reasons for designing databases in this way. The most obvious reason is that these tables contain many different types of data – a single table containing all these different types of data would have more than 20 different fields, which would be rather unmanageable, as you'll see if you try to view the entire contents of the AllMovies table in one go.

 

There are other reasons. Single-table data storage can appear disorganized, with apparently unrelated fields stored in adjacent columns. Also, they can be very inefficient – potentially, a single table database can end up with lots of repeated data. We first encountered these issues in the early sections of Chapter 12. Let's briefly consider how these issues relate to our Movies2000 database.

 

If you're a cinema expert, then you'll know that the film Pulp Fiction was directed by Quentin Tarantino, featured Amanda Plummer, John Travolta and Samuel L. Jackson (in the roles of Honey Bunny, Vincent Vega and Jules), and had a release certificate 'R' in the United States and '18' in the United Kingdom. If we stored this information within a single table in the database, we'd have a set of records like this:

 

Movie	Director	CastName	CastRole	USA Cert	UK Cert
Pulp Fiction	Quentin Tarantino	Amanda Plummer	Honey Bunny	R	18
Pulp Fiction	Quentin Tarantino	John Travolta	Vincent Vega	R	18
Pulp Fiction	Quentin Tarantino	Samuel L. Jackson	Jules	R	18

 

There are two problems with storing the data like this. First, there's a lot of unrelated data all contained in the same table – it just doesn't demonstrate good organization. Second (and possibly more importantly), there's a lot of repetition. Therefore, in Movies2000, we have dissected this data into four related tables like this:

 

MovieID	Title		MovieID	Director
67	Pulp Fiction		67	Quentin Tarantino

 

MovieID	CastName	CastRole		MovieID	USA Cert	UK Cert
67	Amanda Plummer	Honey Bunny		67	R	18
67	John Travolta	Vincent Vega
67	Samuel L. Jacksona	Jules

 

When you consider that there are 330 films in our database, and 22 different fields, you can see how a set of tables is easier to use and vastly more efficient than a single table. In this case, we just use each movie's unique identifier, as defined in the Movies table, to indicate to which movie the data relates. The unique identifier for Pulp Fiction is 67.

 

The consequence of this is that we need to be able to write a command that can query data from two or more database tables at the same time. Fortunately, SQL provides us with a number of ways of doing this – by way of a table join. SQL even provides a keyword, JOIN, just for the purpose.

 

In fact, there are many different types of join in SQL, but we are only going to cover one of them – an inner join. This allows us to write a command that queries two or more tables at the same time, returning the results of the query as a single recordset.

 

So, let's put aside the AllMovies table, which we've used in the past, and show how we can write an example that pairs up movies with their directors using only the much smaller Movies and Directors tables of our database.

In order to do this, we'll write a new command that queries the two tables and returns a single recordset. We'll create a new example by simply adapting the code from SQLSelect.asp.

 

1.    Create a copy of the file SQLSelect.asp – call the copy SQLSelectInnerJoin.asp.

2.    Instead of selecting all the data from the AllMovies table, we'll select the text from a JOIN of smaller tables. So open up SQLSelectInnerJoin.asp, and change the following highlighted lines:

 

...

<%

Dim objCommand, objRS

Set objCommand = Server.CreateObject("ADODB.Command")

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "SELECT Movies.Title, Director.DirectorName " & _

"FROM Movies INNER JOIN Director ON Movies.MovieID= Director.MovieID " & _

"WHERE (Director.DirectorName LIKE 'Quentin Tarantino' " & _

" OR Director.DirectorName LIKE 'Francis Coppola') "

objCommand.CommandType = adCmdText

 

Set objRS = objCommand.Execute

Set objCommand = Nothing

 

While Not objRS.EOF

Response.Write objRS("Title") & " was directed by " & _

objRS("DirectorName") & "<BR>"

objRS.MoveNext

Wend

objRS.Close

Set objRS = Nothing

%>
...

 

3.    Save these changes. Now use your browser to view the page:

How It Works

By amending only one line in our script, we've completely changed the whole query. Let's have a look at that query line again:

 

objCommand.CommandText = "SELECT Movies.Title, Director.DirectorName " & _

"FROM Movies INNER JOIN Director ON Movies.MovieID = Director.MovieID " & _

"WHERE (Director.DirectorName LIKE 'Quentin Tarantino' " & _

" OR Director.DirectorName LIKE 'Francis Coppola') "

 

There's a lot in this command but when we break it up you'll see that it's quite simple to understand. The first thing to note is that the command still has the form SELECT…FROM…WHERE… – it's just that the table (in the FROM clause) is a little more complex. Let's look at the FROM clause first.

The FROM clause begins by saying that we'll create an INNER JOIN of the Movies and Director tables. Each record in that table is created by joining a record from the Movies table to a record of the Director table, where the MovieID field of the first is equal to the MovieID of the second:

 

... FROM Movies INNER JOIN Director ON Movies.MovieID = Director.MovieID ...

 

Here, we need to use the 'dot notation' to indicate the table to which each field belongs – so Movies.MovieID refers to the MovieID field of the Movies table. Here's what the inner join does:

 

From the resulting table, we'll select the fields corresponding to the Title field of the Movies table and the DirectorName field of the Director table:

 

SELECT Movies.Title, Director.DirectorName ...

 

However, we don't want every record. We only want those records that correspond to the films of Tarantino and Coppola:

 

... WHERE (Director.DirectorName LIKE 'Quentin Tarantino'

OR Director.DirectorName LIKE 'Francis Coppola')

 

Having created the join, and indicated the required fields and conditions, the resulting data is put into a single recordset. Then it's just a case of using the default field names to access the values from the recordset. The default field names are taken from the field names of the database tables:

 

While Not objRS.EOF

Response.Write objRS("Title") & " was directed by " & & _

objRS("DirectorName") & "<BR>"

objRS.MoveNext

The UPDATE Command

Let's move on from the SELECT command, to consider the first of the three action commands that we listed earlier in this chapter. The UPDATE command allows you to update the data contained within the data store. This opens the door to a whole new concept for us – making changes to the data store itself. Before now, we have only been concerned with reading the data.

 

SQL's UPDATE statement is used as follows:

 

UPDATE <table> SET <field_name> <operator> <value>

 

For example, if you were so inclined you could go through the AllMovies table and change every Director's name to your own:

 

UPDATE AllMovies SET Director = 'Chris Ullman'

 

Of course, it wouldn't be very easy to undo a change like this, so we don't advise that you try it!

 

The above line can be quite brutal, because it makes the requested change to every single record. Fortunately, the UPDATE command also allows us to specify the exact condition under which records in the table will get changed. The format for this is:

 

UPDATE <table> SET <field_name> = <value> WHERE <condition>

 

So you could go ahead and try the following:

 

UPDATE AllMovies SET Director = 'Chris Ullman' WHERE Title LIKE 'Pulp Fiction'

 

OK, once again we've changed the value in the Director field of the AllMovies table, but this time only for any record whose title is Pulp Fiction. As it happens, there's only one record of this description in the AllMovies table – and the Director field of that one record is updated from Quentin Tarantino to Chris Ullman. As in the earlier examples, we've used the LIKE keyword in place of the equality operator (=), though it provides the same functionality.

 

When you perform an update, you'll probably want to know whether it succeeded and how many records it changed – and in the following example we'll demonstrate how to do that too (you might recall, from our discussions at the beginning of the chapter, that it's the RecordsAffected parameter of the Execute method that allows us to do this).

We'll perform two commands in this example. First, we'll find all the records corresponding to films directed by Quentin Tarantino (there are two in this database), and we're going to change the value of the Director field from Quentin Tarantino to some fictional value. The object of this exercise is just to demonstrate how to update records, and how to determine how many of the table's records have been affected.

 

Then, in order to restore the accuracy of the database, we'll change these values back from our fictional value to Quentin Tarantino.

 

1.    We're going to use the Movies2000 database and the strConnect connection string again, so you'll need the appropriate database files and SSI included (as we have for the other examples in this chapter).

2.    Open up your favorite ASP editor, and create the following page (called SQLUpdate.asp):

 

<%

Option Explicit

Dim strConnect

%>

<!-- #include file="DataStore.asp" -->

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Using SQL's UPDATE Command and the ADO Command Object</TITLE>

</HEAD>

<BODY>

<%

Dim objComm, intNoOfRecords

Set objComm = Server.CreateObject("ADODB.Command")

 

Response.Write "<B>Harry the Ham directs Tarantino films? </B><BR>"

objComm.ActiveConnection = strConnect

objComm.CommandText="UPDATE AllMovies SET Director = 'Harry the Ham' " & _

"WHERE Director LIKE 'Quentin Tarantino'"

objComm.CommandType=adCmdText

objComm.Execute intNoOfRecords

Response.Write "This UPDATE command has affected " & _

intNoOfRecords & " records<BR><BR>"

 

Response.Write "<B>Tarantino returned to his rightful place as director:</B> <BR>"

objComm.CommandText="UPDATE AllMovies SET Director = 'Quentin Tarantino' " & _

"WHERE Director LIKE 'Harry the Ham'"

objComm.Execute intNoOfRecords

Response.Write "This UPDATE command has affected " & _

intNoOfRecords & " records<BR><BR>"

Set objComm = Nothing

%>

</BODY>

</HTML>

 

3.    Save it as SQLUpdate.asp in the usual place – \inetpub\wwwroot\BegASPFiles.

4.    Now view the page in your browser:

 

 

The results indicate that for the first command, two records were affected – and the two records for the two Tarantino films in the AllMovies table were updated, with the imposter Harry the Ham instated as director of these films. The second command locates the records that claim that Harry the Ham is director, and reinstates Quentin Tarantino as director. Okay, let's look through the code.

How It Works

We're using a command that does not return a Recordset object; in fact, we don't need a recordset at all in this example, so we don't create one. All the changes that we make are made directly, to records that exist the database.

 

However, we are aiming to return the number of records that have been updated, so we need to define a variable which will hold that total, called intNoOfRecords. We also create the Command object:

 

Dim objComm, intNoOfRecords

Set objComm = Server.CreateObject("ADODB.Command")

 

Next we set all the properties for the first of our commands. The ActiveConnection property takes the value of the connection string, strConnect. The CommandText property takes the value of the UPDATE command. And the CommandType property is adCmdText, to reflect the fact that the command is a SQL statement:

 

objComm.ActiveConnection = strConnect

objComm.CommandText="UPDATE AllMovies SET Director = 'Harry the Ham' " & _

"WHERE Director LIKE 'Quentin Tarantino'"

objComm.CommandType=adCmdText

 

The command text itself says that it will locate every record in the AllMovies table whose Director field holds the value Quentin Tarantino, and it will change that value to Harry the Ham.

 

Now we execute the command, and return the number of records affected within the variable intNoOfRecords:

 

objComm.Execute intNoOfRecords

To prove that intNoOfRecords really does hold the number of affected records, we'll use it in our report of the command's effect:

 

Response.Write "This UPDATE command has affected " & _

intNoOfRecords & " records<BR><BR>"

 

This gives us a good way to double-check that the command performed the actions that we expected it to perform. In this case, we know that there are two Tarantino films in the AllMovies table, because we've seen them in an earlier example. So if this variable contains a value other than 2, we know that something has gone wrong. If the command has failed in any way, then intNoOfRecords won't hold a value. In this case, we've checked by printing the value out, but you might do something subtler, like this:

 

If intNoOfRecords = 2 Then

Server.Execute Successful.asp

Else

Server.Execute SomethingWentWrong.asp

End If

 

Next we set about changing those two records back. The ActiveConnection and CommandType properties are already set, so we only need to change the CommandText property before executing this second command:

 

objComm.CommandText="UPDATE AllMovies SET Director = 'Quentin Tarantino' " & _

"WHERE Director LIKE 'Harry the Ham'"

objComm.Execute intNoOfRecords

 

Again, we can display the number of affected records:

 

Response.Write "This UPDATE command has affected " & _

intNoOfRecords & " records<BR><BR>"

<< 14.1.1- Using the Command Object

Chapter14

14.1.3- Using Queries and Stored Procedures >>