Using
ColdFusion:
Making a Connection to a Database
Shannon will be teaching two Short
Courses on ColdFusion this semester. Check the Short
Course schedule
for dates, times, and locations. -- Ed.
Hello, again!! It
has been a hectic month here at Central Web Support, and
we can’t seem to get caught up. I hope that it is
not as hectic for all of you out there, as it is getting
close to the holiday season.(I am already in the
Christmas mood for some reason… Maybe it’s the
sudden appearance of Christmas decorations at most major
stores. ;-) )
Well, To Get Started…
This month’s article is going
to deal with connecting to a database. As I mentioned in
the previous articles, we connect to a database by using
a DataSource Name, or DSN, which is essentially a gateway
between the database and the rest of the world. Before we
can try working this example out, you will need to
contact me at: speeves@unt.edu,
and ask to have the DSN created. The information that you
will need to provide me with is: the location of the
database, (the location needs to be on the web server,
preferably in the _private directory), the name of the
database, and the name that you would like the DSN
called. (For example, location:
http://web2.unt.edu/beginningclass/_private/test.mdb,
name of database: test.mdb, name of DSN: test_connect.)
When you contact me with this information, I will set up
the DSN, and email the confirmation information back to
you. You will then be able to connect to the database at
will.
In last month’s
article, we set up a form and then returned the input
data to the next screen. In this month’s article, we
are going to modify the application that we had last
month to include output from a database. The first page
of our application will be:
<html>
<head>
<title>This is my First
ColdFusion</title>
</head>
<body>
<h1
align="center">This Page Will Insert
Data Into</h1>
<h1
align="center">My Database!!!</h1>
<p
align="left">This page will help you to
get acquainted with the power and
ease</p>
<p
align="left">of ColdFusion.</p>
<p
align="left">
First, we
need to create
two text-boxes:</p>
<p
align="left">
1. one is named
NAME</p>
<p
align="left">
2. the second is named
TELEPHONE</p>
<p
align="left">
(HINT: Use the code
from our first application to do this</p>
<p
align="left">
3. set FORM ACTION =
"firstFormInsert2.cfm"</p>
<p
align="left">Create them below this
line:</p>
<p
align="left">__________________________________________________________________</p>
<FORM
ACTION="firstFormInsert2.cfm"
METHOD="post">
<P><FONT
SIZE=3><B> Please enter your name here:
<INPUT TYPE=TEXT
NAME="NAME"
SIZE=25></B></FONT></P>
<P><FONT
SIZE=3><B> Please enter your telephone
here:
<INPUT TYPE=TEXT
NAME="TELEPHONE"
SIZE=25></B></FONT></P>
<p
align="left">__________________________________________________________________</p>
<p
align="left">Create a submit button
here: (make sure that it is
centered on the page.)</p>
<DIV ALIGN=CENTER>
<P><INPUT
TYPE="submit"
VALUE="Submit"></P>
<p
align="left"> </p>
<p
align="left"> </p>
</div>
</form>
</body>
</html>
If you remember from last month, we
only need to change the <FORM> attributes to post
the information to a destination page. This destination
page will be firstFormInsert2.cfm:
<html>
<head>
<title>The data that was
passed from insert Page 1</title>
</head>
<body>
<h1
align="center">The data that was
Inserted into </h1>
<h1
align="center">Your Database in Table
Display:</h1>
<p
align="left">The following page will
query your database and return the data
in a table.</p>
<p
align="left">
1. create a table with two columns and
two rows.</p>
<p
align="left">
- label the first
row: NAME,
TELEPHONE</p>
<p
align="left">
- add variables to
the second row</p>
<p
align="left">
2. write a <CFQUERY> that
inserts
all of the data from the
previous page into the </p>
<p
align="left">
database.</p>
<p
align="left">
3. place your <CFOUTPUT> tags
in
the appropriate
places.</p>
<p
align="left">
4. Enjoy!!!</p>
<p
align="left"> </p>
<p
align="left">This is the data that was
pulled from the database:</p>
<p
align="left">__________________________________________________________________</p>
<div
align="center">
<center>
<table border="0"
width="100%">
<tr>
<td width="50%"
align="center"><b>NAME</b></td>
<td width="50%"
align="center"><b>TELEPHONE</b></td>
</tr>
</table>
</center>
</div>
<p
align="left">__________________________________________________________________</p>
<p
align="left">Did it
work?! If not, ask me
and I will be
glad to help you. :-)
Speeves</p>
<p
align="left"><a
href="firstFormInsert.cfm">return to the
first insert page</a></p>
</body>
</html>
If you copy the html for these two
pages into your html editor, we can then get started
writing the ColdFusion code that will insert the data
from firstFormInsert.cfm into our database, test.mdb, and
then querying this same database and returning the
appropriate values to the screen on firstFormInsert2.cfm.
Let’s Start Coding…
As I’ve mentioned earlier, the
only changes that are necessary to firstFormInsert.cfm,
are the changes to the <FORM> attributes. (If you
copied the html from this document, the changes are
already made for you.) If you are writing your own html,
then you will need to write the <FORM> tag to look
like this:
<FORM
ACTION="firstFormInsert2.cfm"
METHOD="post">
This is the only change that is
necessary to the first page.
On the second page, we are going to
add the meat to the ColdFusion code. The page,
firstFormInsert2.cfm, is the page that actually inserts
all of the data into the database, and then pulls the
data out so that we can view it. We will insert the code
using the <CFQUERY> tag. Here is a syntax diagram
for the <CFQUERY> tag:
<CFQUERY
NAME="query_name"
DATASOURCE="ds_name">
SQL statements
</CFQUERY>
Note that there are more attributes
than are seen here in the diagram, but for the scope of
this example, these are the only attributes that are
necessary. As you can see, the attributes are NAME and
DATASOURCE. The NAME attribute allows you to name the
query, so that you can access the query from other places
in the application. (Think of the NAME attribute as the
variable name of the query.) The DATASOURCE attribute
tells the ColdFusion server which DSN to use to connect
to the database that we want to connect to. If this
attribute is misspelled or incorrect in any way, then
your query will return an error and your application will
not work… (Please be sure that your DATASOURCE
attribute is the same as the DSN that I set up for you.
(This is why it is important to double-check the
information that I return to you after I have set up the
DSN.
If I made a mistake, you could spend
hours trying to debug your program and never figure out
the error.)) After you have opened the query, you will
need to write the SQL statements that will perform the
desired functions for your application.
As an aside, I want to mention that
you should always do some pre-coding thought and
diagramming before you start to write code “by the
seat of your pants”. It is not as noticeable in a
program that is as small and simple as this, but it will
become very noticeable when your applications start to
become larger. (Even though it may seem to be more work
now, it is good to get into the habit, and will actually
save you time in the future.)
Returning to our regularly scheduled
programming, ;-) , what action do we need to perform
before we can pull information from the database?
That’s right, we need to INSERT that information
into the database. The INSERT statement will look like
this:
INSERT INTO [tablename]
(column1, column2, etc)
VALUES
(‘string_data’,
number, etc)
We INSERT the data into a table,
[tablename], with the columns named, column1 and column2,
the VALUES, string_data, and a number. (I am afraid that
SQL is not the focus of this article, so for a good
tutorial, check out: http://www.baycongroup.com/tocsql.htm.
For the scope of this article, I will only do a cursory
explanation of the SQL statements that are being used in
this application.)
After this brief explanation of the
<CFQUERY> statements, and the INSERT statement, we
will write our first <CFQUERY> within the
<BODY> tag of our html document. It will look like
this:
<CFQUERY
NAME=”insert_data”
DATASOURCE=”test_source”>
INSERT
INTO name_table (NAME, TELEPHONE)
VALUES
(‘Shannon’,
555-1212)
</CFQUERY>
Now, this works fine, if you want to
keep entering the same data into the database table over
and over again, but this is not what we are looking to
do. We are looking to exploit the power of ColdFusion and
turn it to our own sinister will. Therefore, we will need
to replace the VALUES in the preceding statement with the
FORM values from firstInsertForm.cfm. To do this, you
will use the variable names that are associated with the
two forms on firstInsertForm.cfm, NAME and TELEPHONE. Our
query will then need to be modified to look like this:
<CFQUERY
NAME=”insert_data”
DATASOURCE=”test_source”>
INSERT
INTO name_table (NAME, TELEPHONE)
VALUES
('#Form.NAME#',
'#Form.TELEPHONE#')
</CFQUERY>
This modified query will take the
data entered into the forms on firstInsertForm.cfm and
insert that information into the table
“name_table” using the DSN
“test_source”, that I have set up for you.
Now… How do you know if the
information was entered into the database? You could
easily open the database, and see for yourself… But
wouldn’t it be easier to write another query that
will pull the information from the table itself? That
way, you don’t have to copy the database to your
computer, etc, etc. To SELECT all of the information from
the desired database table, you will use the same
<CFQUERY> and change the SQL statements, to perform
the SELECT “function” instead of the INSERT
“function”.
<CFQUERY
NAME=”select_data”
DATASOURCE=”test_source”>
SELECT
* FROM name_table
</CFQUERY>
The SQL statement
“SELECTS” all of the information from the
database table, “name_table”. (The * is
considered a meta-character with the meaning,
“everything”, or “all”.) You will now
add this query underneath the INSERT query, and then add
the <CFOUTPUT> tags to the html, which in this case
is in the form of a table.
<div
align="center">
<center>
<table
border="0" width="100%">
<tr>
<td width="50%"
align="center"><b>NAME</b></td>
<td width="50%"
align="center"><b>TELEPHONE</b></td>
</tr>
<!---Start the output
from the database--->
<CFOUTPUT
QUERY="select_data">
<tr>
<td width="50%"
align="center">#NAME#</td>
<td width="50%"
align="center">#TELEPHONE#</td>
</tr>
</CFOUTPUT>
<!---Finish output--->
</table>
</center>
</div>
This is will output all of the
information from your database into a tabular format,
which makes the returned values very readable.
- One note, you don’t want to include your
headings in the <CFOUTPUT>, because your
output will repeat for every row of data in your
database, giving you something like:
NAME
TELEPHONE
Speevey
555-1234
NAME TELEPHONE
Mary
555-4321
Etc, etc…
(Of course, if you want your
output to look like that, you can go ahead and do it.
The sky is the limit!!)
- Note number two. We are adding an attribute to
our <CFOUTPUT> tag that we didn’t see
last month. The QUERY attribute. The QUERY
attribute points our <CFOUTPUT> to the
correct <CFQUERY>. (In this case, the
“select_data” query.)
- And finally, note number three. The output
variables must have the same name as the database
tables’ columns. That is the way that the
ColdFusion server knows how to pull the data from
the database, and where to output the data to the
screen.
In Conclusion…
In this month’s article, we
have written two pages of html, and turned them into
ColdFusion dynamic dynamo. We are accepting input from
two forms on the first page, then inserting the data into
a database and reviewing our database information on the
second page. This is very easy using ColdFusion, and
opens up the doors to the real power of the RAD
application.
Now that you have this information,
and have tested the examples, try different SQL
statements to see what they do. If you are unsure about
what SQL is, or want to know more, check out my
mini-course: Introduction to SQL, and also check-out the
url that I mentioned earlier in the article. There is a
lot of information in those two places. Also, I am
offering another short course this semester called
“Introduction to ColdFusion” in which we cover
many of the same concepts that we have been covering in
these articles. (For more information on the short
courses offered by the Computing Center, check out http://www.unt.edu/training/shortcrs.htm
)
Next month, we are going to cover
the <CFQUERY> in more depth, and look at how to
deal with some of the small quirks that html throws at us
in our ColdFusion application creation.
Until then…
For a completed example of the
application that I just created, hit http://web2.unt.edu/speeves/firstdbapp.
|