Phillip's ColdFusion Blog - SQL

The Persistant SQL UDF

Wed, 19 Aug 2009 05:07:00 -0700

I came across a very strange and unexpected problem with a SQL Server 2005 UDF yesterday that continues to puzzle me. First, the cast of players:

ColdFusion MX 7
SQL Server 2005
A table-valued function
Puzzled developers

In case you are not familiar with table-valued functions, they are functions in SQL that return a table, as opposed to the more traditional scalar value. We had to change the data type for one of the columns in the returned table from decimal to text to fix a bug. Nothing a simple ALTER FUNCTION statement could not handle. Script, run, test, same error, what? Maybe something is cached on the SQL Server side. Let's try dropping and recreating the function. Script, run, test, same error, ??? Something very odd was happening. Only after restarting the ColdFusion Application Server service did the error go away. I am still searching for an answer. Surely ColdFusion does not cache database object information...?

Version Control for Database Objects?

Mon, 27 Jul 2009 07:56:40 -0700

I have used a variety of version control methods and products over the years. In the beginning, I (infrequently) added a digit or date to the end of the file name, an effort that was largely ineffective and highly subject to user error. :-) Then, corresponding with a job change, I was introduced to version control software in the shape of CS-RCS from ComponentSoftware, based on GNU RCS. After a couple of years, the development team made the shift to Subversion, my personal favorite. To be complete, I will throw in a year of pain using Visual SourceSafe. [More]

Finding All Triggers in SQL Server

Tue, 14 Jul 2009 12:30:00 -0700

Database triggers are sometimes difficult to track down. I needed to locate a list of all of the triggers in a database to check them for performance issues, and, when I turned to the INFORMATION_SCHEMA views, discovered there is not one for triggers. Oh well, back to interrogating the system tables. Triggers are located in the sys.triggers table, so the following query delivered the information I needed:


SELECT
	o.name AS parent_object,
	t.name AS trigger_name,
	c.text AS trigger_def
FROM sys.sysobjects o 
	INNER JOIN sys.triggers t 
		ON t.parent_id = o.id
	INNER JOIN sys.syscomments c
		ON c.id = t.object_id

Finding a Random Row

Tue, 30 Sep 2008 13:20:00 -0700

Every now and then I get a requirement to do something to a random table record. The most common is to retrieve a random record for display. Other times it is part of a data scrambling routine. So how is this done? Most database platforms have a function that generates a unique identifier. SQL Server has NEWID(), which generates a GUID:


SELECT NEWID()

Example Output: 0B7869E5-1428-4CA9-9D5A-CDA0E9756DC2

MySQL has RAND(), which generates a random floating-point value:


SELECT RAND();

Example Output: 0.93845168309142

Using these functions, retrieving a random row from a table becomes as simple as: SQL Server


SELECT TOP 1 *
FROM mytable
ORDER BY NEWID()

MySQL


SELECT *
FROM mytable
ORDER BY RAND()
LIMIT 1;

So what is really going on behind the scenes? The key to the randomness is the ORDER BY clause. As each row in the table is evaluated, it is assigned a unique value via the NEWID() or RAND() function. The value changes for each row and each time the query is executed.

Coding Standards

Thu, 26 Jun 2008 09:36:00 -0700

I admit, I am a code diva. I want to see and write clean, clear, prettified code. I firmly believe that all programmers (and teams) should have, and more importantly follow, a set of coding standards. A few of my personal CF quirks are:

Tabs not spaces for code indentation.
Functions are camel-cased, e.g., querySetCell and arrayToList.
Variables are camel-cased and descriptive, e.g., currentRecord and dateOfBirth.
Component names are capitalized, e.g., User and UserService.
SQL keywords are upper case, e.g., SELECT username FROM users.
Use implicit boolean function results, e.g., <cfif structKeyExists(...)>.
Use structKeyExists over isDefined whenever possible.
And so on.

The important concept behind coding standards is not necessarily the nitty-gritty details of what is good and what is bad, but the discipline it provides your coding efforts. Consistency in coding is more important than the (endless) debates about which code formatting template is more readable or which function is better. If you cannot read your own code because you never structure it the same way twice then you have only yourself to blame.

Building a Date Table with ColdFusion

Thu, 24 Apr 2008 13:56:00 -0700

If you have ever worked with a data warehouse, or a complex, date-sensitive application, then you have probably come across a date dimension table. Simply put, a date dimension table is a lookup table that contains one record per date. Each record has information about that date, such as the day of the week, the quarter it falls in, and so on. Many DBAs come armed with a SQL script that creates and populates a date dimension table. If you are not a SQL adept, why not use the power of ColdFusion? Each record in a date dimension table describes a date, so our table will have the following columns:

date - the full date
year - the date's year
month - the date's month number
month_desc - the month's name
day - the date's day
quarter - the date's quarter within the year
day_of_week - the date's day of week number
day_of_week_desc - the day of week's name
day_of_year - the date's day number within the year
week_of_year - the date's week number within the year

In SQL Server:


CREATE TABLE days (
	[days_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[date] smalldatetime NOT NULL,
	[year] tinyint NOT NULL,
	[month] tinyint NOT NULL,
	[month_desc] varchar(10) NOT NULL,
	[day] tinyint NOT NULL,
	[quarter] tinyint NOT NULL,
	[day_of_week] tinyint NOT NULL,
	[day_of_week_desc] varchar(10) NOT NULL,
	[day_of_year] tinyint NOT NULL,
	[week_of_year] tinyint NOT NULL
)

Now that the table is created, it needs data. Populating the data is simply a big loop using the many different CF functions for splitting and formatting dates. In this case, I will generate a script that can then be run in a database query tool, such as Query Analyzer:






	
		INSERT INTO days (date, year, month, month_desc, day, quarter, day_of_week, day_of_week_desc, day_of_year, week_of_year)
		VALUES (
			'#createODBCDate(currentDate)#',
			#year(currentDate)#,
			#month(currentDate)#,
			'#monthAsString(month(currentDate))#',
			#day(currentDate)#,
			#quarter(currentDate)#,
			#dayOfWeek(currentDate)#,
			'#dayOfWeekAsString(dayOfWeek(currentDate))#',
			#dayOfYear(currentDate)#,
			#week(currentDate)#
		)

In the above example, the date dimension table will contain one record for each day between January 1, 2000 and December 31, 2008. In the real world you will probably use a longer timespan, depending on your needs.