Page One

Campus Computing News

Holiday Hours

UNT Internet Services in Transition

Academic Mainframe Shutdown Proposed

Lab-of-the-Month: SOVA

Using ColdFusion: Making a Connection to a Database

Today's Cartoon

RSS Matters

SAS Corner

The Network Connection

Link of the Month

WWW@UNT.EDU

Short Courses

IRC News

Staff Activities

    

Using ColdFusion:
Making a Connection to a Database

By Shannon Eric Peevey, UNT Central Web Support

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">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; First, we need to create

two text-boxes:</p>

<p align="left">&nbsp;&nbsp;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

1.&nbsp; one is named NAME</p>

<p align="left">&nbsp;&nbsp;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

2.&nbsp; the second is named TELEPHONE</p>

<p align="left">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

(HINT:&nbsp; Use the code from our first application to do this</p>

<p align="left">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

3.&nbsp; set FORM ACTION = &quot;firstFormInsert2.cfm&quot;</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:&nbsp; (make sure that it is

centered on the page.)</p>

<DIV ALIGN=CENTER>

<P><INPUT TYPE="submit" VALUE="Submit"></P>

<p align="left">&nbsp;</p>

<p align="left">&nbsp;</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&nbsp;</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">&nbsp;&nbsp;&nbsp; 1.&nbsp; create a table with two columns and

two rows.</p>

<p align="left">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - label the first

row:&nbsp; NAME, TELEPHONE</p>

<p align="left">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - add variables to

the second row</p>

<p align="left">&nbsp;&nbsp;&nbsp; 2.&nbsp; write a &lt;CFQUERY&gt; that inserts

all of the data from the previous page into the&nbsp;</p>

<p align="left">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; database.</p>

<p align="left">&nbsp;&nbsp;&nbsp; 3.&nbsp; place your &lt;CFOUTPUT&gt; tags in

the appropriate places.</p>

<p align="left">&nbsp;&nbsp;&nbsp; 4.&nbsp; Enjoy!!!</p>

<p align="left">&nbsp;</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?!&nbsp;&nbsp;&nbsp; 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.