Getting dates to appear correctly in an HTML cfgrid

A little while ago, I posted this to my blog, however I didn't give much context, I just dumped a pair of links to fairly dense articles. What do these links tell you? They tell you how to have SQL Server format things for you (in this case dates), similar to using the dateFormat function in ColdFusion. This can come in very handy when working with cfgrids.

Although the Adobe livedocs say that you can mask / format values that go into a grid, I haven't had much success with getting it to work; especially in the case of HTML grids.

A great work-around is just to use the SQL Server CONVERT function. How? If you plug a date/time/timestamp column name into CONVERT, and tell it what you want to convert it to, it will change the data-type of the date/time column (just in the resulting recordset, not in the actual table) to the data-type of your choice, and format the date for you to one of many predefined formats. Lots of examples of this and other techniques here:

http://www.oreilly.com/news/sqlnut_1200.html

For a list of predefined formats, and some more (not very useful) examples, look here:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

So, if you need to use a date/time column in a cfgrid, just preformat it in the actual query like so:

SELECT CONVERT(VARCHAR(11), date_column, 102) AS formatted_date
FROM table
...etc.

I hope that makes more sense.

SQL Server, Nulls, and SET CONCAT_NULL_YIELDS_NULL

When you try to concatenate string columns in SQL Server, if any of the columns have null as a value, by default the result of the broader concatenation will be null. You can get around this by entering the following:

SET CONCAT_NULL_YIELDS_NULL OFF

For example, consider the following query:

SELECT last_name + ', ' + first_name + ' ' + middle_name AS full_name
FROM person
WHERE person_id = 12345

If person 12345 doesn't have a middle name, and person 12345's middle_name is set to null in the database, then the entire concatenation will resolve to null, and full_name will result in an null / empty string.

However, if you do this:

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT last_name + ', ' + first_name + ' ' + middle_name AS full_name
FROM person
WHERE person_id = 12345

Then the result will be something like:

-----------------------
Doe, John
-----------------------

By the way, this solution works just fine in ColdFusion too. For example:

<cfquery name=""get_name" datasource="ds">
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT last_name + ', ' + first_name + ' ' + middle_name AS full_name
FROM person
WHERE person_id = 12345
</cfquery>

This is especially useful when using cfgrid. CFgrid won't allow you to compose columns that represent more than one database field. For instance, you would have to use one cfgridcolumn for first_name, and a second for last_name. You can't just have both in one column called "Full Name". Unless, that is, you use the above example to create the full_name column thru the database.

Multiple LEFT JOINs in MS Access

I had forgotten about this one. In Microsoft Access, if you want to do more than one LEFT JOIN, you have to use parenthesis in the FROM clause. So, for example, instead of just plain old:

SELECT a.columna, b.columnb, c.columnc
FROM tablea AS a LEFT JOIN tableb AS b ON a.id = b.id LEFT JOIN tablec AS c ON a.id = c.id

you would have to do the following:

SELECT a.columna, b.columnb, c.columnc
FROM ((tablea AS a) LEFT JOIN tableb AS b ON a.id = b.id) LEFT JOIN tablec AS c ON a.id = c.id

Otherwise, you get a "Missing Operator" error. Stupid Access.

--Update 10/29/2009--
Incidentally, this should work with other sorts of joins as well.

SQL Server Express 2005 Remote ODBC Connections

Just downloaded and installed SQL Server Express 2005 yesterday for my development environment. So far, it looks like a suprisingly functional yet free version of SQL Server. I have been pleasantly suprised. However, there was some heartburn involved with getting it to accept Remote (i.e. from another server) ODBC connections from my development ColdFusion server (non .Net). With the help of google, the following website (and several others), and some patience, I was finally able to get it to work this morning:

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Here is what I learned in a nutshell:

1. By default, SQL Server Express 2005 does not allow TCP/IP connections. You have to turn that on in the Connection Manager. The how-to is listed in the article that I mention above.
   
   
2. By default the SQLBrowser service is turned off. You have to go into Administrative Tools / Services and turn it on. I set it to manual, and then clicked "Start", however you may wish for it to always be running.
   
   
3. Since the computer that I have SQL Server Express 2005 running on is inside a firewalled local network that is not connected to the outside world, I don't have Windows firewall running on the database machine. However, if you do have SQL Server Express 2005 running on a machine with Windows firewall, or any other firewall running on it, you may need to try fiddling with the firewall settings to allow connections to the machine.
   

I think that's all I had to do. If I remember anything else, I will post it here.

SQL: Limiting Queried Rows

The Problem
Where I work, we have a few databases with some pretty large tables. Record counts into the hundreds of thousands are a dime a dozen, and you'll even encounter the occasional table with multiple millions or records. Though I dearly love the SQL keyword TOP, sometimes it is inadequate. As a web programmer, how often have I wished that there were a way to tell a SQL Server to only return rows 50 to 100? Doing so would make result pagination a breeze.

I posted a question to a Cold Fusion Message Board that I frequently visit, and my main man Lance M. (http://www.webasics.net/) came to the rescue by posting three possible solutions that he had read about in a SQL book.

The Solution
While there doesn't really appear to be a way to specify which results to return short of using a stored procedure, there are several possible workarounds. The workaround that I liked best is both simple and elegant. I will post it here. If you are dieing (spelling?) to know the other two, make a comment, and I will post them as well

If you want to get records 1-50 from the fictitious table "author" you would, of course, just do the following:

SELECT TOP 50 *
FROM author
ORDER BY last_name;

However, if you wanted to get records 51-100, you can do this:

---------------------------
SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC;
---------------------------

Presto! What you end up with is records 51-100 (albeit in reverse alphabetical order). This query essentially gets the first hundred authors, reverse alphabetizes them (ORDER BY last_name DESC), and then returns the first 50 from the upside down result. Granted, this method is not as efficient as just being able to specify a STARTROW and an ENDROW, but it is a LOT more efficient than having the database return a coupl'a hundred thousand records, and then just picking out the chunk of 50 that you want.

If you want to re-alphabetize the result, just add the following wrapper:

---------------------------
SELECT *
FROM
(SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC)
ORDER BY last_name ASC;
----------------------------

Very cool, Lance. Thanks for the tip.