Adding Data - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Adding Data

You will need to insert data into tables at some point, so let's look at data inserting using the INSERT statement.

NOTE

In this chapter, you will add, update, and delete rows from tables in the ows data source. The reason you delete any added rows is to ensure that any example code and screen shots later in the book actually look like the way they're supposed to.

Feel free to add more rows if you'd like, but if you don't clean up when you're finished, your screens will look different from the ones shown in the figures. This isn't a problem, just something to bear in mind.

Using the INSERT Statement

You use the INSERT statement to add data to a table. INSERT is usually made up of three parts:

• The table into which you want to insert data, specified with the INTO keyword.

• The column(s) into which you want to insert values. If you specify more than one item, each must be separated by a comma.

• The values to insert, which are specified with the VALUES keyword.

The Directors table contains the list of movie directors working with or for Orange Whip Studios. Directors can't be assigned projects (associated with movies) if they aren't listed in this table, so any new directors must be added immediately.

Appendix G, "Sample Application Data Files" for an explanation of each of the data files and their contents.

Now you're ready to add the new director. The following code contains the SQL INSERT statement:

INSERT INTO Directors(FirstName, LastName)
VALUES('Benjamin', 'FORTA')

Enter this statement into the SQL Query field as seen in Figure 7.1. Feel free to replace my name with your own. When you're finished, click the Execute button to insert the new row. Assuming no problems occur, you should see a confirmation screen like the one in Figure 7.2.

TIP

How can you tell if an INSERT succeeds or fails? Well, no news is good news. If no error is returned, the INSERT has succeeded. If an error has occurred, it will be displayed.

Understanding INSERT

Now that you've successfully inserted a row using the SQL INSERT statement, take a minute to look at the statement's syntax.

The first line of your statement reads:

INSERT INTO Directors(FirstName, LastName)

The text immediately following the INTO keyword is the name of the table into which the new row is being insertedin this case, the Directors table.

Next, the columns being added are specified. The columns are listed within parentheses, and since multiple columns are specified, they are separated by a comma. A row in the Directors table requires both a FirstName and a LastName, so the INSERT statement specifies both columns.

NOTE

When you insert a row into a table, you can provide values for as few or as many columns as you like. The only restriction is that any columns defined as NOT NULL columnsmeaning they can't be left emptymust have values specified. If you don't set a value for a NOT NULL column, the database driver returns an error message and the row is not inserted.

The next line reads:

VALUES('Benjamin', 'FORTA')

A value must be specified for every column listed whenever you insert a row. Values are passed to the VALUES keyword; all values are contained within parentheses, just like their column names. Two columns are specified, so two values are passed to the VALUES keyword.

NOTE

When inserting rows into a table, columns can be specified in any order. But be sure that the order of the values in the VALUES keyword exactly matches the order of the columns after the table name, or you'll insert the wrong data into the columns.

To verify that the new director was added to the table, retrieve the complete list of directors using the following SQL statement:

SELECT * FROM Directors

As explained in Chapter 6, SELECT * means select all columns. As you can see in Figure 7.3, the new row was added to the table. Make a note of the DirectorID, which you'll need later to update or delete this row.