Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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 email@example.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.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104776 Viewers
56 Video lessons · 116661 Viewers
71 Video lessons · 85848 Viewers
131 Video lessons · 41073 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.