ColdFusion Muse

Cfqueryparam Fails When Using "WHERE EXISTS"

I have always been an advocate of Cfqueryparam. Binding your variables innoculates you against SQL injection attack, often results in speed improvements and lessens the load on your database server. It may even help with the laundry (that's the word on the street). This morning I ran across an error that is produced by the correct use of cfquery param. It has to do with using the clause "WHERE EXISTS" in your query. Here's the query in question.


Why You Should Join AA (Access Anonymous)

I'm an Access hater too - so the idea of giving tips on it galls me just a bit. Recently, however, I had the opportunity to help a fellow with his Access driven Coldfusion site. This poor soul had moved his Coldfusion site from to to save a few bucks. Saving money on hosting is almost never worth it in my opinion - but I won't go there for this post. After much heartache he got the Access DSN installed using the godaddy control panel. But, uh oh... the new Access driver is the one with Unicode support (at least that's what I surmise without talking to the perky but useless godaddy support). That means that lots of things are now broken.


More CF and DTS Troubleshooting

For those of you that have made use of my blog post on DTS and Coldfusion and the subsequent blog on Troubleshooting DTS and Coldfusion, I have a 2 more useful tips. One has to do with a way to trap errors on the server and get information about what's going on on the client - the second tip has to do with protocol selection and conflict.


Top and the UNION Query

A "UNION" query can be very useful. Take sorting for example. If I want to select 2 types of data from the same table using different conditions and order by my conditions union is a great tool. See my previous post on Using UNION in your queries. If you also like using TOP you may run into an unexpected behavior when using UNION. Let's say you had 2 tables, portalUsers and intranetUsers and you wanted to get the most recent entries in each that were unapproved. If you try something like this:

<cfquery name="myquery" datasource="mydsn">
      SELECT    TOP 1 fname, lname,
            'Portal' AS sourceTable,
      FROM    portalUsers
      WHERE   unApproved = 1
      SELECT TOP 1 fname, lname,
            'Intranet' AS sourceTable,
      FROM   intranetUsers
      WHERE   Approved = 0
   ORDER BY dateAdded      
You might expect to get 2 records equal to the most recently added portal user and the most recently added intranet user - right? You could take the first record and you would be good to go. Actually, unless the "portalUsers" table has a clustered index ordered by dateAdded, this is not the case.


Inserting and Updating in a Single Query Statement

This is a tip about something I do regularly. No, I'm not talking about forgetting to put down the seat. I'm talking about coding. Let's say you have an application that collects user data in a wizard-like format using a few steps. You allow folks to go back and forth between steps and edit what they've done. How do you handle the insert on that first step without creating duplicates when users return to that step? There are several approaches to this problem:


Great Tip - Capturing "PRINT" output from T-SQL

When I write stored procedures I often throw in a few print statements to help me understand what is going on. For example, if I have a routine that loops through table A and updates table B based on some conditions, I might include a print statement that said something like PRINT 'Condition Met' or PRINT 'Condition Not Met'. I could also output the values of a column or 2 to indicate why a condition was or was not met. That's a neat trick in Query analyzer and it makes debugging stored procedures easier. When you move the procedure to CF the PRINT output is lost or ignored. That is usually exactly what you want. But what if you did want to see the PRINT output? Is there a Coldfusion way to do id? Thanks to this great tip from Shlomy Gantz you can!


Multiple Columns in a Sub Select??

Selecting more than 1 column in a sub-select is possible. I learned this tip from Jochem van Dieten (who knows more about SQL than anyone I know) on a recent CF-Talk thread. According to Jochem you can use the following Syntax:

<Cfquery ... >
WHERE (x, y) IN
   (SELECT a, b FROM otherTable)
...Now ya gotta admit that's pretty neat!

Note: This syntax will not work on every db server. It will not work on MS SQL server for example. See the notes below for more detail

Getting the Auto Number Field in MS Access

As a follow up to this Previous Post on using the @@Identify Field and it's related function "scope_identity()", it appears that it is possible to do this same thing in Access. Charlie Arehart has posted a great article with some examples titled "Select @@Identity Works in Access. Worth Exploring. Check it out.

You Should Use an Alias for Those Sub Query Tables!

Here's a rule of thumb for you. When you are writing a query that includes a sub query always use an alias for the table and qualify the columns - or use the full column name if you are so inclined. If you don't you may very well end up with unexpected results. Consider this query:


Radder Rad With Cfquery and Cut and Paste

When I first heard of RAD my immediate thought was the wonderful folks of Virginia and the Cumberland Gap - where I met my wife (a nurse from Minnesota, what are the odds). In the blue green mountains of Appalachia, everyone knows about Rad. It's the opposite of Blue. If you mix a little yeller into it you get arnge. When I started studying IT and Technology. It didn't take me long to learn that RAD stood for "Rapid Application Development". Now if you've been using Coldfusion for any length of time you will know that "RAD" is a word often used in to describe the usability and accessibility of the language. Here one reason why....

Listen Here


Breaking Up with Lists - When you are Ready for a Real Relationship

Every blue moon someone asks, "How do I determine if a list contains a value using SQL?" This question belies a misunderstanding of how a relational database is supposed to work. You can get at a value in a list using a UDF (see this previous post). But just because something can be done does not mean that it should be done.


Data Migration and Coldfusion

If you've been working with Coldfusion very long, chances are you've written a data import script. There are many tools that allow you to migrate data from one database platform or schema to another, and I'm well aware that "guru dogma" states that Coldfusion is not the best tool for things like long running tasks that can be performed by the database. I'm also a big advocate for letting the database do its job. So it may surprise you to learn that I believe Coldfusion is actually a pretty good choice in many cases - especially if you have to do anything tricky with the data. Take looping for example:


Using LIKE Wildcards in Your Queries

I often comment that learning the ins and outs of your database platform can do just as much for your productivity as learning advanced Coldfusion coding techniques. Here's another example. Suppose you have a "LIKE" expression in your query that checks against a stored character value. You use the expression to allow a user to do a search against a character column in the database. If the column in question contains an underscore you might end up scratching your head at the results.


MSDE - the Little DB Engine That Could

You may already know about MSDE. It's a full-featured SQL server with a restriction for file size (2Gig or 4Gig) and number of connections (25). It's a great choice if you have a home business, a small intranet or a dev server and you don't want to shell out 5 grand per CPU for the full SQL server. Getting it correctly installed for use with a Web Application is another matter.


Getting DB Meta Data From Access

Sometimes it's useful to be able to query a database for information about itself. For example, I have a survey application that allows a user to send a survey to a "population" of users. The user has multiple SQL database containing multiple possible recipients. He also receives ad-hoc leads from marketing that are temporarily "dumped" into his database. In other words, he knows the data is in there but he doesn't know the table name and he doesn't know the columns inside the table from which to draw the name and email. In MS SQL you can select from the "sys" tables or us the stored procedures "sp_tables" and "sp_columns" to get a list of all the tables, columns and data types. Wouldn't it be nice to do the same in access? After all, access is what often used as portable transport for transferring leads around - right?


More Entries

Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.