ColdFusion Muse

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.

A ListFind( ) function for SQL

First let me say that it is usually not a good idea to store lists in Database tables. Many novice web developers falls into the trap of treating the data in the database the way they treat it in their web application. They are used to using lists and list functions in logic loops to qualify users or statements, and they try to do the same thing in the Database. But SQL doesn't have any native list functionality. The equivalent of a "list" to a database is a table with a single row.

A while back I saw a security scheme implemented by storing a list of group ids in a character column of the users table. The code grabbed the groupIds column from the users table on login, then it selected from the "groups" table where group_id IN (1,2,4,8...). This actually works fine when dealing with individual users. But what if you wanted to grab all the users from a particular group? You end up with code that looks like "where groupIds LIKE '%,2,%' Or groupIds like '2,%' or groupIds LIKE '%,2' or groupIs = '2'. Why the 3 statements? Because you have to account for a group id of 20 and a case where 2 is alone or at the beginning or the end. There are other ways around this - none of them pretty (like storing beginning and ending comma's for example). Not to mention this sort of design makes DBA's pull out what little hair they have.

Now, having warned you against being "listy" in your database design, I'm going to show you one way of working with a list that might help in a case like that above. I know, I know, a little knowledge is a dangerous thing. Let's just assume you are forced to deal with a legacy schema that you have no power to change (ha).

Using UNION in your queries

Are you using Union queries yet? If not, you should get up to speed. A union query is an extremely useful method for returning records from different tables in the same recordset. You just have to remember that datatypes of the columns must match in the same order they are referenced. Here's an example: