rabidGadfly

Use cf_sql_timestamp for MS SQL and MySQL date types

Posted at March 10, 2009

Hopefully, if you’re using ColdFusion, you’re already using cfqueryparam in your queries for better performance and security against SQL Injection attacks. If you’re not, you should read this.

When using cfqueryparam you specify a value and an SQL type. Dates can be assigned two different types, cf_sql_date and cf_sql_timestamp. cf_sql_date is intended to be used for date types in DB2 and Informix. If you’re using MS SQL Server, you should use cf_sql_timestamp.

cf_sql_timestamp maps to the datetime and smalldatetime data types in MS SQL Server. Although cf_sql_date works in many instances, it does not work in all instances. I ran into this issue when evaluating whether a date equaled a certain date value. For example:

SELECT *
FROM myTable
WHERE create_dt = <cfqueryparam type="cf_sql_date" value="2099-12-31  23:59:59" />

When I ran the query in SQL Query Analyzer (minus the cfqueryparam of course), I received the expected results. When I ran it inside a ColdFusion page I received an empty result set. Once I changed to timestamp, I received my results.

SELECT *
FROM myTable
WHERE create_dt = <cfqueryparam type="cf_sql_timestamp" value="2099-12-31  23:59:59" />

I haven’t tested the code with mySQL, and mySQL isn’t listed in the livedocs cross-reference table so I don’t know what kind of effect can be seen there, though I’d be interested to find out.

UPDATE: I got two great tips from comments. According to sebastiaan cf_sql_timestamp should be used with MySQL as well as MS SQL Server. And Ben Nadel adds that cf_sql_longvarchar should be used for text fields. Great info. Thank you both!

coldFusion

How to Create Delimited Lists of Related Data in Your MS SQL Queries

Posted at January 7, 2009

A project I’m working on requires me to return asset records along with a comma-delimited list of related keywords. The keywords are stored in their own table and there is a one-to-many relationship from assets to keywords.

The following code returns what I’m looking for, but only for one record:

DECLARE @KeywordList varchar(1000)

SELECT @KeywordList = COALESCE(@KeywordList + ', ', '') + k.keyword
FROM assets a
JOIN keywords k ON a.asset_guid = k.asset_guid
WHERE a.asset_guid = 'xxxxxxxx-xxxx-xxxx-xxxx-023ECD4EBB4C'

SELECT @KeywordList

The problem was that I needed to return the results in a column for every record in the result set, and that code won’t work in a subquery.

My solution was to create a SQL user defined function.

Before proceeding, I should note that the method I’m posting should only be used if absolutely necessary. In addition to running your main query, you will also be running an additional query for every returned record, which can get pretty expensive.

But if you have a need as I did, this method should do the trick. Te following function accepts an asset GUID and returns the associated keywords in a comma-delimited list:

CREATE FUNCTION getDelimitedKeywords
   (@asset_guid uniqueidentifier )
RETURNS varchar(1000)
AS
BEGIN
   DECLARE @KeywordList varchar(1000)
   SELECT @KeywordList = COALESCE(@KeywordList + ', ', '') + keyword
   FROM keywords
   WHERE asset_guid = @asset_guid

   RETURN ( @KeywordList )
END

Now I can easily include the list in every row of my query by calling the function in my SELECT:

SELECT asset_name, dbo.getDelimitedKeywords(asset_guid) as Keywords
FROM assets

I didn’t research how to achieve the same results in mySQL, but it’s probably pretty easy to do using the GROUP_CONCAT function.

-rG

sql

Making Alpha Work on AS3 Dynamic Text Fields

Posted at October 14, 2008

Getting the alpha property to work with coded TextFields is another AS3 technique that you would expect to be a little easier, or at least more intuitive, than it is. Initially, you may expect something like this to render the text invisible:

var myTextField:TextField = new TextField();
myTextField.text = "Doesn't Work";
addChild(myTextField);
myTextField.alpha = .5;

But regardless of the alpha property, the code above will be visible. The reason is that I haven’t specified a font. This means that it will be rendered by the operating system and Flash Player will have no control over it.

With this new knowledge you may figure that setting the font will solve the problem, so you write something like this:

var myTextField:TextField = new TextField();
var myTextFormat:TextFormat = new TextFormat();
myTextFormat.font = "Arial";
myTextField.text = "Still doesn't work";
myTextField.setTextFormat(myTextFormat);
addChild(myTextField);
myTextField.alpha = .5;

But it still doesn’t work. Why? Well, I really have no idea!

Luckily, I ran across a comment left on Adobe Livedocs by Haiducii7 who suggested that by changing the blendMode to something other than NORMAL, alpha will work. The code now, looks something like this:

var myTextField:TextField = new TextField();
var myTextFormat:TextFormat = new TextFormat();
myTextFormat.font = "Arial";
myTextField.blendMode = BlendMode.LAYER;
myTextField.text = "It works!";
myTextField.setTextFormat(myTextFormat);
addChild(myTextField);
myTextField.alpha = .5;

And it worked! According to Flash Help, BlendMode.LAYER “forces the creation of a transparency group for the display object.” I guess that makes sense, but there’s nothing in the documentation that outlines all of the steps needed to make this happen.

Not even Colin Moock’s Essential ActionScript 3.0, a book I’ve treasured in multiple versions, mentions the necessity of BlendMode.

Definitely a tip worth earmarking.

-rG