I was eating with another speaker at cf.Objective() (I am resolved to find a way to work my having spoken at a conference into all future anecdotes) and an interesting SQL question came up: How to delete everything except the first 1000 records. To my mind, this brought up a general class of problems in SQL. Which is, returning results that exclude the result of a query.
With that in mind, let's look at a few of those.
Most of us have deal with many-to-many relationships from time to time. This is generally easy. What happens, though, when the client wants some items to be available to all members of the related table?
For example, I might have some articles that are only available to users with certain permissions.
If that were the case, then I might have an articles2permissions table. Then I might pass in a comma-delimited list of permissions when retrieving articles:
One of my sites has started getting sluggish lately. I thought it was just that the traffic was increasing and so I should optimize my code. Then, in the last couple of weeks, I had a 3AM scheduled task crash a few times. This weekend, I had a simple query with one row and one column take several minutes to execute even from the SQL Server Query Analyzer. This is getting weird!
I was trying to create a trigger in Oracle last night with cfquery and it wouldn't work. Oracle kept reporting that the trigger was invalid. If I copied the same exact SQL into Oracle directly, it would work. When I would look at the invalid trigger in Oracle and copy the SQL from there into Oracle, it would fix the trigger, but still I couldn't get it to work from there.
In talking to a friend of mine yesterday, I realized that a great many programmers are not aware of one of the nicest features of SQL, the ability to insert the results of a query into a table. This is really handy for copying data from an old format to a newer format or if you have a need to denormalize part of a database.
When I read Ben Forta's announcement (and follow up) that ColdFusion 8 would ship with support for the Derby database, my first thought was "I should add support for that in DataMgr". This proved to be a good introduction to Derby.
I decided that I would get my local copy of my demonstration site running on Derby.
I have found that many people set up the primary keys for join tables in a manner that I find undesirable. This leaves open the possibility of some sneaky bugs in their programs.
I'll start by covering what I mean by a join table (as opposed to what I will call a data table).
Most basic data is stored in data tables. For example a "users" table is a data table, as is a "groups" table. Each row in a "users" table represents one user, as each row in a "groups" table would represent a group.
Ben Forta is asking for input on his next CFWACK book. The discussion in the comments reminded of the old CFML Reference that I keep with me. It is one of a few books that I always have near my computer when I travel.
These books taken together weigh less than a pound and take up less space than your average computer book. They have still managed to be tremendously useful when I am working on the road (especially if I am trying to get some work done with no internet connection).
Anyone else have suggestions for books that they don't leave home without?
I was recently asked how to show a record for the current user before the record for other users - in one query. Here is my solution.
A recent discussion on CF-Talk implied cfqueryparam as the culprit in a query problem. It seems, however that the problem only happens when using cfqueryparam in conjuction with "select *".
In the discussion, Ben Nadel pointed out reasons he recommends listing out columns instead of using of "select *". His words:
