ColdFusion Muse

Handling NULLs in MS SQL

The blog on Waterswing.com has a great entry on handling nulls in MS SQL. If you ever wondered when to use "= NULL" (if ever) and when to use "IS NULL" you should check it out. Also note the excellent tip by Barney in the comments on using the <=> operator in conjunction with CFQUERYPARAM allowing you to avoid a cumbersome CFIF statement.

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.

Selecting a Random Row Using MS SQL

It's often a requirement to choose a random record from a table. This is pretty easy to do in Coldfusion. Select all the rows and do a RandRange(1,query.recordcount) to get a row number. That's great if you have a few rows or even a few hundred. You can cache the query and serve up random rows at lightening speed. But what if you had a few hundred thousand rows?

MS SQL and Instance Names - DTS Package Gotcha

I recently had to alter a large DTS package for a customer. He sent me an IP address and a username and password with access to the server and permissions to the databases and package. My first step was to open my little "client network utility" and add an alias for the database server, let's call it "marksDtsClient". Then I went and registered "marksDTSClient" in Enterprise manager so I could work with the package. I was able to open the package designer and see all the steps as well as the SQL in the execution modules. It's when I went to save changes I ran into a big "uh oh". I received a "server not found" type error.

The Identity Field Part III - Mia Culpa

Ok Ok - @@Identity is not as good as SCOPE_IDENTITY(). I'm sorry to have written a post that was "pro-@@identity" (even though it was rife with warnings). For anyone listening please be advised. There is never a case where @@Identity is needed and SCOPE_IDENTITY() cannot be used instead - at least not that I can think of. You can quote me on that. Now let's all remain friends.

(follow up to the previous post)

The Identity Field part II - a Gotcha

This is a follow up to the post titled "Getting the ID field after an Insert". I recently saw someone struggle with this because of an easily made syntax error. Consider this code:

Follow up post on doing this in MS Access.

Getting the ID field after an Insert

About every 2 months or so someone asks about reliably returning the primary key record from an insert query. The problem they are trying to solve usually has to do with additional inserts into related tables. For example, if you are adding a new user and you want to set group permissions as well - but group permissions require inserting into another table. One way to do it is to do an insert, then do a second query that pulls back the "max(id)" and uses another qualifier - like an email address. This requires 2 connections to the database, but it is a very common method. If you are using SQL server and your primary key is an "identity" type field then you have another option. You can insert and get back the identity value in the same query. This is preferred because of SQL treats a single query statement as an implicit transaction - meaning you are assured of data integrity, and that you will return the right value. Here's the way it works.