CodeGeex

As we discussed back in our first CF_101 discussion, ColdFusion is all about causing HTML to dynamically change based on data provided to it. And while it is possible to supply this data manually each and every time you need it (like through forms, for example) it is far more reasonable to be able to store that data somewhere for later use. Large collections of stored data are called Databases, and they are incredibly useful for keeping data neat, organized, and easy to look up in the future. And in order to do this data lookup, we have to turn a scripting language called SQL.

SQL (most commonly pronounced "sequel") is the language of choice when it comes to communicating with most popular databases nowadays. While there are some minor variances between the two more commonly used flavors, MSSQL and MySQL, they are very similar for the most part - I will note differences as we come across them. But for the moment, we need to address the most common of database interactions, a group of procedures that we web developers lovingly refer to as CRUD.

You like CRUD, right?

Knee deep in the CRUD

CRUD stands for the four basic operations that we use SQL for:

• Create data by inserting it into the database
• Read the data so that we can display it or make decisions based on it
• Update the data by changing what is stored
• Delete the data when we are sure we will never use it again

Most applications we will write in the future will use all four of those operations at some point or another. Reading is mostly used when we want to display something out to the published HTML page, while Creation and Updating are more Admin side things. Deletion, on the other hand, should be rarely used because once you delete a record, there is no getting it back unless you have a backup or want to spend a small fortune on data recovery.

Now that we know what the four core operations are, lets get into some detail about how we would use them. Going forward, we are going to assume that we already have a table in the database, we will call it "testData" and say it looks a little something like this:

Not the most interesting or in-depth of tables, but it will satisfy our needs at the moment. It certainly shows the basic structure of a table: You have a set of vertical Columns, and horizontal Rows (also called Records) that each contain a set of column data. One more thing we need to know before we proceed, though, is that each column in a database is assigned to hold a specific "type" of data. In the case of our test table, we are using columns that represent the four most common types, in order:

• Varchar = A string consisting of a variable number of characters.
• Integer = A positive whole number.
• Bit = A flag representing one of two binary states: True or False, On or Off. Also known as a Boolean value.
• Decimal = A decimal number.

Ok, now that we have established the data we are working with, its time to talk about the first SQL operation, Creation, which is accomplished using the SQL command INSERT.

Create using INSERT

In order to use an INSERT statement, you have to declare what table you are inserting into, what columns you are cramming data in, and what values you want each column to have. Here is a very simple INSERT statement that would add a new record into our testData table:

INSERT INTO testData(
	stringCol,
	intCol,
	boolCol,
	decCol
	)
VALUES(
	'Do or do not, there is no try',
	42,
	0,
	4.17
	)

In that statement, we are creating a new Row in the database table, and inserting in the appropriate data. So now our table looks like this:

Something to take note of, you will notice that when in inserted the string into the stringCol, I surrounded it with single quotes - this is what lets SQL know that it is in fact a proper varchar string. Leave the quotes off or try to use double quotes (") and SQL will throw an error.

Another thing to notice is that I did not specify a value for testPK- in this scenario, the table would have been created with what is referred to as an auto-numbering primary key. We will talk about primary and foreign keys another time.

Read using SELECT

Well, we now know how to add new data into our table. We can start storing data away like a squirrel storing nuts for the winter. Unfortunately, springtime has rolled around for DataVille and we have no idea how to retrieve those nuts! This brings us to our next SQL operation, called SELECT. SELECT is a powerhouse of a function, and where the real "meat" of what SQL can do lives, so at this point I'm only going to lightly touch on its functionality and come back to the more advanced stuff later.

A SELECT query is composed of very similar set of requirements as an INSERT operation: You declare what columns you want, from what table, and optionally what conditions they need to meet. The most basic of SELECT statements could look like this:

SELECT *
FROM testData

In this example, I used a shortcut called a "wildcard", in this case an asterisk (*), that basically means "anything" - combined with not specifying any data conditions, this will return the entire table. While this shortcut is handy while learning or testing, its usually best to not use the * and instead specify each column you want, both for code  readability and performance reasons.

But lets say I dont want the entire table (and that Im not being lazy), how would I go about pulling up a specific piece of data? What if I wanted to retrieve the stringCol for every record that has an intCol greater than 50? Well a query like this would do the trick:

SELECT stringCol
FROM testData
WHERE intCol >= 50

That query, if read out loud, would read "Select column 'stringCol' from the table 'testData' where the column 'intCol' is greater than or equal to fifty" and the result of this query would look something like this:

Since I specified only stringCol in my SELECT statement, SQL did not return the other columns. That is great, because to answer the query I put forth I didn't really need the testPK, boolCol or decCol, and the intCol was only referenced in the WHERE clause, so it didn't need to be returned. This saves a lot of system overhead in bigger queries that address large tables, and is commonly considered a "best practice" - only ask for what data you are going to use!

Update using, well, UPDATE

We now know how to INSERT fresh new data records, and how to SELECT a basic set of records back from the database. Sometimes, however, you need to change an existing record's data, in one, some or all of its columns. While technically you could delete and re-insert a new records, that would be dangerous, inefficient and actually a bit disruptive to the database's internal behind-the-scenes methods of keeping the data structure tidy. No, a far better way would be to use the UPDATE command.

Looking back over my previous examples, I see a major Geek faux pa in my data - while two of my sample record sets use Monty Python references, the one I inserted used a Star Wars reference! I am definitely crossing the proverbial streams here (there I go again!) so I will have to fix this with the following UPDATE query:

UPDATE testData
SET stringCol = 'This is an ex-parrot!'
WHERE testPK = 3

You will notice that once again I have used a WHERE clause in order to specify exactly the data I want to effect. In this case, I only wanted to effect the record that had the primary key of 3 and nothing else. This is very important, as leaving out the WHERE clause or writing it improperly could leave you overwriting the stringCol column in all your records! That would be bad.

Delete using DELETE (at your own risk)

The fourth and final operation is one that I highly recommend you not use. DELETE is powerful and dangerous in ways your new-to-SQL mind can not yet imagine. In fact, whever possible I suggest you use some preplanning and setup a non-destructive delete system in advance, but that is a topic for an entire separate post of its own.

For now, I'm going to show you the DELETE syntax, but once again ask you to not use it. Ever.

DELETE FROM testData
WHERE testPK = 3

Its very simple, as you can see, and that is a large part of its allure - but even a passing tryst can leave chaos and devastation in its wake. So on that happy note, I put this post to rest and let you off into the world, hopefully a little wiser in the ways of SQL and what you can do with it. In a future post, we will discuss what it can do for you, and its symbiotic relationship with ColdFusion.