I use fairly vanilla SQL most of the time so I was shocked to read this post on the PostgreSQL list when I ran into some slowness with a NOT IN query. While my experience is with Postgres, apparently this works with most databases.
If you have a NOT IN with a subquery that returns […]

Yesterday I started a post talking about PostgreSQL 8.3 RC2 and this morning I come in to find the official 8.3 release in my inbox! There are quite a few interesting new features, a few of which are specifically of interest to ColdFusion developers:

UUID datatype
ANSI-standard SQL/XML support
ENUM datatype

There is a page about UUID support […]

If you skim the Transfer docs, like I do, you might miss the fact that specifying a relationship (onetomany, manytoone, manytomany) automatically creates support for the column you reference. This can cause maddening errors like the following:
Error Executing Database Query.
ERROR: column “dtype” specified more than once
transfer\com\sql\QueryExecution.cfc (108)
transfer\com\sql\TransferInserter.cfc (371)
transfer\com\sql\TransferInserter.cfc (132)
transfer\com\sql\TransferInserter.cfc (49)
transfer\com\sql\SQLManager.cfc (61)
transfer\com\Transfer.cfc (197)
transfer\com\Transfer.cfc (177)
If […]

The SE-PostgreSQL team released their first full version today based on Postgres 8.2.4. Apparently it’s not a holiday in Japan where the primary contributors live.
SE-PostgreSQL is an interesting effort to combine the policies and controls of SE-Linux into the database engine to extend the access controls of the OS. It’s […]

Based on my geocoded zip code database research, I wound up purchasing the ZipList Can-USA GeoCode database. Here’s what I did to get their 848,000-record CSV file into a PostgreSQL database table. First, create a table to hold the data:
CREATE TABLE tblLookupZipCode (
vchCity varchar(33) DEFAULT ” NOT NULL
,booDST boolean DEFAULT false NOT […]

I’m preparing to migrate my application to a framework like Model-Glue. Because I have 70 tables and a large API already, I’m afraid of the cutting and pasting required to migrate my code to a Bean/DAO/Gateway structure. Brian Rinaldi’s Illudium PU-36 Code Generator would solve my problem except it didn’t support Postgres 8.x […]

Quick tip - if you use PostgreSQL, you should look into the INTERVAL data type. It’s a great way of expressing periods of time without using actual dates and makes things like calendaring and recurring events very easy.
Unfortunately, ColdFusion’s CFQUERYPARAM doesn’t have support for the INTERVAL type so I had to hack around to […]

I’m in a quandry. I am building a reserved number system that will let a manager keep track of which competitor is entitled to a favorite number. These organizations run different types of events so this number manager must provide some flexibility. A schema with example data follows:

Participant
Event Type
Bicycle
Class
Grid
#

Brian

12

Joe
X-Country

12

Brian
X-Country
Klein
Pro
Red
12

Heidi
X-Country
Specialized
Pro
Blue
12

Mary

1002

Frank
Downhill

777

What IS OK:

Because this […]

On MotorsportReg.com, we store vehicle numbers as a string because every so often someone has a letter as part of their number. The problem with storing numbers as strings is that they sort poorly. Instead of “1, 2, 3… 10, 20, 30…” you are presented with “1, 10, 2, 20, 3, 30…”. […]

I’m doing a bit of consulting for a local health services organization. The environment is the predictable “LAMP” stack: Linux, Apache, MySQL and PHP. I’m about a half day into writing the PHP and remembering all of the reasons why I use ColdFusion: you have to roll your own solutions (or find somebody […]