Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using SQL editing and the Query Viewer

From: ColdFusion Builder Essential Training

Video: Using SQL editing and the Query Viewer

Because connecting to a database is such an integral part of our day-to-day lives as developers, the CFBuilder has added some nice features to the IDE for dealing with your database and SQL query specifically. I already showed you the RDS Dataview earlier in this course, but now I am going to show you the SQL Editor and some of the differences between the two. To continue on with our Login application, we need to add a query to actually validate a user's username and password. So, on my login.cfm page, which we were working on in the last video, I am going to go ahead and add an if IF statement here to determine when I want to actually check the username and password.

Using SQL editing and the Query Viewer

Because connecting to a database is such an integral part of our day-to-day lives as developers, the CFBuilder has added some nice features to the IDE for dealing with your database and SQL query specifically. I already showed you the RDS Dataview earlier in this course, but now I am going to show you the SQL Editor and some of the differences between the two. To continue on with our Login application, we need to add a query to actually validate a user's username and password. So, on my login.cfm page, which we were working on in the last video, I am going to go ahead and add an if IF statement here to determine when I want to actually check the username and password.

So, we have our cfif. Now I am going to write my query. So, cfquery name = rsValidation. And then we need a datasource. Now, I normally have my datasource in a variable that's stored in the application CFC, so I can use it anywhere in the application, but in this case, I am just going to drop in the datasource name directly into the cfquery tag. I can do that by just grabbing my datasource in the RDS Data view and dragging it in between the datasource attribute quotes.

There is my cfartgallery datasource. I will close my cfquery, and then I can start writing some SQL code. So, there is two ways I can write the SQL code. The first way is I can right-click anywhere inside of my cfquery and choose SQL Editor. This pops up a small window, where I can actually just start writing my code. We will do SELECT * FROM, and when I press the Spacebar, I actually get a list of all of the tables inside of the cfartgallery data source.

In this case, we are going to be checking against the GALLERYADMIN table. I will press Enter, type WHERE EMAIL, space, equals, we will just put an empty string for now. AND PASSWORD =. Actually, I got that wrong, didn't I? It's ADMINPASSWORD, so I will press Enter, take out my bad column over here. There we go. = empty string. So, now I can execute that query and the SQL Editor will switch to the Query Result tab.

As you can see, I didn't get any results. So, let's go back to the SQL Editor and I will take out my WHERE clause. I will just cut it to my Clipboard and choose Execute again. Now I can see all of the users in the database. We have admin@demodata.com, whose password is demo. Let's go back to the SQL Editor and I will undo and get my WHERE clauses back. Now I can click OK and CFBuilder adds the SQL code in between my cfquery tags. I will press Tab to indent that.

And then we will replace our quotes here with our actual cfqueryparams. But what are the datatypes for my columns? If I go back over here to the RDS Data view, I will expand Tables, GALLERYADMIN. If I scroll over here to the left, I can see that the EMAIL is a character field and the ADMINPASSWORD is also a character field. So, we will go back here, cfsqltype is going to be character, and value is going to be FORM.username, and we will do the same for the PASSWORD.

And now I have my query ready to go to actually check whether or not my user authenticated properly. So, we will set cfset Session. LoggedIn = rsValidation.recordcount GT 0. So, that's one way to edit your SQL queries. If I want to edit the SQL query further, I can click inside of the cfquery tag, right-click and choose SQL Editor, and my query comes back up.

But because it has ColdFusion code in it, if I click Execute, I will get a SQL syntax error and I don't get any results for that query. If I go back to the SQL Editor and then remove code here so that the query actually works, then when I click OK, that code is removed from my cfquery tag. So, this is one reason I don't like using the SQL Editor. It's a modal dialog. I can't do anything else while I am working in it. And I don't have a whole lot of control over what I can add or remove without affecting the page below it.

So, let's click Cancel and I will show you the way I like to work. Let's take this query. I will copy it to my Clipboard. And inside of the RDS Dataview, I will click the Query Viewer, which opens up the RDS Query Viewer in a completely separate tab inside of my editor. This means that I can be working over here in login.cfm, go over here to the Query Viewer and check my work, copy code, back to login.cfm, and I can go back and forth all day long as I work on my queries. So, let's paste my query here, and we will remove my WHERE clauses and execute the query.

Now I get my results down here at the bottom. I can check and make sure what my demo e-mail and passwords are. Then I can switch back over to the login.cfm and test my page. Now, one downside of using the RDS Query Viewer is that you don't get any code hints. If I press Spacebar in here, I don't get table names or column names, but if you have access to the RDS Data view here, you can always just click and drag and drag your table names directly into the RDS Query Viewer. That saves you a lot of typing and ensures that you don't misspell one of your columns.

If you are anything like me, a third or more of your time is spent dealing with databases. The ability to preview your SQL directly inside the IDE, meaning you get rid of another tool that you have to switch back and forth between all day long, really makes a difference in how quickly you can get your work done.

Show transcript

This video is part of

Image for ColdFusion Builder Essential Training
ColdFusion Builder Essential Training

40 video lessons · 3867 viewers

Daniel Short
Author

 
Expand all | Collapse all
  1. 7m 48s
    1. Welcome
      59s
    2. Installing ColdFusion Builder
      2m 20s
    3. Installing ColdFusion Builder as a plug-in
      3m 18s
    4. Using the exercise files
      1m 11s
  2. 17m 42s
    1. Workspace overview
      2m 25s
    2. Organizing your workspace
      6m 19s
    3. Adding and selecting workspaces
      2m 53s
    4. Installing and managing plug-ins
      6m 5s
  3. 12m 12s
    1. Creating projects
      2m 11s
    2. Importing projects
      2m 48s
    3. Creating project working sets
      2m 23s
    4. Managing projects
      2m 22s
    5. Linking folders
      2m 28s
  4. 23m 2s
    1. Configuring and managing ColdFusion servers
      6m 26s
    2. Using the Services Browser
      4m 15s
    3. Using RDS Dataview and Fileview
      4m 52s
    4. Using FTP
      7m 29s
  5. 18m 10s
    1. Creating editor profiles
      2m 6s
    2. Using Code Assist
      5m 43s
    3. Setting code coloring
      4m 5s
    4. Using shortcut keys
      3m 5s
    5. Working with outlines and syntax checking
      3m 11s
  6. 37m 40s
    1. Using code completion
      7m 25s
    2. Using snippets
      9m 23s
    3. Using SQL editing and the Query Viewer
      5m 55s
    4. Working with components
      7m 27s
    5. Working with variable mapping
      2m 14s
    6. Navigating code and documents
      5m 16s
  7. 13m 12s
    1. Getting set up
      3m 50s
    2. Using the ColdFusion debugger
      5m 37s
    3. Watching logs with Tailview
      3m 45s
  8. 13m 8s
    1. Installing extensions
      4m 5s
    2. Using the CFC Generator extension for ColdFusion 8
      4m 52s
    3. Using the CFC Generator extension for ColdFusion ORM
      4m 11s
  9. 34m 5s
    1. Building a simple contact form extension
      3m 42s
    2. Creating user interfaces
      8m 55s
    3. Packaging the extension
      3m 55s
    4. Building the handler
      8m 10s
    5. Using the Builder extension
      9m 23s
  10. 25s
    1. Goodbye
      25s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed ColdFusion Builder Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.