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.
Read MoreWhy 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 experthost.com to godaddy.com 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.
Read MoreMore 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.
Read MoreTop 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:
SELECT TOP 1 fname, lname,
'Portal' AS sourceTable,
dateAdded
FROM portalUsers
WHERE unApproved = 1
UNION
SELECT TOP 1 fname, lname,
'Intranet' AS sourceTable,
dateAdded
FROM intranetUsers
WHERE Approved = 0
ORDER BY dateAdded
</cfquery>
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:
Read MoreGreat 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!
Read MoreMultiple 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:
SELECT * FROM table
WHERE (x, y) IN
(SELECT a, b FROM otherTable)
</CFQUERY>
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.
Sites to Watch
Aggregators
RSS
