ColdFusion Muse

Radder Rad With Cfquery and Cut and Paste

When I first heard of RAD my immediate thought was the wonderful folks of Virginia and the Cumberland Gap - where I met my wife (a nurse from Minnesota, what are the odds). In the blue green mountains of Appalachia, everyone knows about Rad. It's the opposite of Blue. If you mix a little yeller into it you get arnge. When I started studying IT and Technology. It didn't take me long to learn that RAD stood for "Rapid Application Development". Now if you've been using Coldfusion for any length of time you will know that "RAD" is a word often used in to describe the usability and accessibility of the language. Here one reason why....

Listen Here

Breaking Up with Lists - When you are Ready for a Real Relationship

Every blue moon someone asks, "How do I determine if a list contains a value using SQL?" This question belies a misunderstanding of how a relational database is supposed to work. You can get at a value in a list using a UDF (see this previous post). But just because something can be done does not mean that it should be done.

Data Migration and Coldfusion

If you've been working with Coldfusion very long, chances are you've written a data import script. There are many tools that allow you to migrate data from one database platform or schema to another, and I'm well aware that "guru dogma" states that Coldfusion is not the best tool for things like long running tasks that can be performed by the database. I'm also a big advocate for letting the database do its job. So it may surprise you to learn that I believe Coldfusion is actually a pretty good choice in many cases - especially if you have to do anything tricky with the data. Take looping for example:

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.

Getting DB Meta Data From Access

Sometimes it's useful to be able to query a database for information about itself. For example, I have a survey application that allows a user to send a survey to a "population" of users. The user has multiple SQL database containing multiple possible recipients. He also receives ad-hoc leads from marketing that are temporarily "dumped" into his database. In other words, he knows the data is in there but he doesn't know the table name and he doesn't know the columns inside the table from which to draw the name and email. In MS SQL you can select from the "sys" tables or us the stored procedures "sp_tables" and "sp_columns" to get a list of all the tables, columns and data types. Wouldn't it be nice to do the same in access? After all, access is what often used as portable transport for transferring leads around - right?

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

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: