ColdFusion Muse

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).

Read More

Data Binding in Oracle

This is a suppliment to the previous blog on data binding without cfqueryparam. I was asked for a syntax example using Oracle. I came up with the following:

Read More

Data Binding Without Using Cfqueryparam

I am always rhapsodizing on the benefits of CFQUERYPARAM. But what if you needed to not use CFQUERYPARAM? Is it possible to get the benefits of the tag without actually needing to USE the tag? Why yes it is! In order to explain let's look under the covers of how an SQL statement is prepared when you use binding. How about a little lesson from Classis ASP?

Read More

Why You should worry about your execution plan

No I'm not talking about dead man walking. I'm talking about your database execution plan. I want to give fair warning to all of you Microsoft haters out there (and you know who you are) that I'm going to use lingo from Microsoft SQL Server 2000. It's a ubiquitous and full-featured database with good documentation regarding this subject. Much of what is said here applies to other databases as well (no doubt using different lingo). So please, feel free to post comments of how Oracle or MySQL or PostgreSQL or Interbase or your flat file - all have a great way of doing this. But please don't post about how your favorite DB is so great and Microsoft is the spawn of Satan. That's not helpful and it's makes me want to poke out my eye with an ice pick! Whew! Now that that's out of the way.

The database execution plan is the series of steps that a database takes to deliver a particular query or task. These steps are cached on the DB server. When you run a query it looks in the cache for a plan that matches. If it finds one, it uses it. If not, it creates a new one. Why is this important? Because the more often your DB Server finds a matching plan in the cache, the better it performs. In fact, it can run significantly faster when it is not tasked with constantly building execution plans from scratch. Here's the rub, much of the query code written in Coldfusion requires the RDBMS to compile a new execution plan. Here's why.

Read More

Funny Coding Errors - Take 2

I was reminded of this very humorous incident recently while chatting with some friends. A very good friend of mine who was a novice web programmer was just beginning to stretch his wings using Coldfuion and SQL. The site he was working on (which shall remain nameless) used an Interbase DB server. My friend Bob (let's call him Bob) was interested in tracking page views for some news stories and articles he was writing. He created a new table with a few columns and wrote to it with each request - logging the page id, news story and IP address. It seemed to work splendidly... at least for a while....

Read More

Using a DSN connection for Connectionless Access

Macromedia added "connectionless" DSN's in CF 5, then took them away again in CFMX. This much lamented feature was useful in certain instances. One of my favorites was for data export. For one of our e-commerce sites suppliers wanted a daily Access file for orders. We created an export process that copied an Access template to a new location (an Access db with the tables needed). Then we created an ODBC connection to it using the "connectionstring" attribute of CFQUERY and ran an insert routine to copy in the orders. The file was then zipped and automatically emailed to the supplier for drop shipment. In the words of Jimmy Neutron's Dad, "Now you gotta admit that's pretty neat!".

In CFMX however the connectionstring attribute is gone. This is because the system no longer interacts directly through ODBC. Instead it uses JDBC as the data access layer. That's a very good thing. We have had great results with speed and reliability through JDBC. It does put a crimp in our data export plan however. Fortunately there's a work-around. It's not perfect, but it works pretty well.

Read More

Changing Database Schemas may require a restart of CFMX

There's a tricky nuance that you must take into account when you make schema changes in MS SQL. When we discovered the following behavior I looked to see if I could find it blogged or documented somewhere. Failing that, I thought someone else out there might benefit from hearing about this issue. You might run into problems if you are dealing with the following conditions:

• CFMX using JDBC drivers to MS SQL
• A view with a "select *" in it for one or more tables
• The need to change the schema of a particular table referenced with the asterisk (*) within the view

Here's what happens (and how to fix it).

Read More

Using GROUP BY in SQL

(reprinted from a previous blog)

Here's the dilemma. Let's say you have a log table where each row is a record of some action taken by a user. Perhaps the user logs in, updates his profile, searches for products and makes purchase. The table has the following fields:

• log_id (int) - a primary key for the table.
• user_id (int) - a foreign key reference to the "users" table.
• action_type (char) - a string indicating "login", "update", "search" or "purchase".
• UpdateTime (smalldatetime) - indicates when the event occurred.

Your boss (we'll call him Ralph) comes to you and says "I'd like to know the last action that each user took on the site before they went away. Can you build me a report like that? Well, you know a couple of ways to do this. One is that you can pull in the whole record set ordered by user and updatetime - then track the last record for each user. Or you might pull in all the users ids and do query looking for the max log_id for that user. Both of these approaches will work, but none of them will pull in the data required in a single query. Never Fear, there is another way - it's tricky, but it works!

Read More