10 Easy Steps to set up Model-Glue 2: Unity on a Shared Host

Date April 8, 2009

Once I had it working, I was astounded by the simplicity. The only pre-requisite is that your server is running ColdFusion 8 or above. Here are the steps:

Note that the following steps assume your site is already created

  1. Download ColdSpring.
  2. Unzip ColdSpring, rename the unzipped folder to “coldspring” and copy it into your site root.
  3. Download Model-Glue.
  4. Unzip the Model-Glue archive into a temp folder.
  5. Copy the Model-Glue folder into your site root.
  6. Copy the contents of the /modelglueapplicationtemplate directory to your site root. Do NOT copy the folder itself.
  7. From your site root, open /config/ColdSpring.xml and delete the two occurrences of “/modelglueapplicationtemplate” on lines 12 and 13. This should result in your viewMappings being “/views” and your generatedViewMapping being “/views/generated”
  8. From your site root, open /config/ModelGlue.xml and delete “modelglueapplicationtemplate.” from line 3 making it “controller.Controller” instead of “modelglueapplicationtemplate.controller.Controller”.
  9. Delete Application.cfm from your site root and replace it with the Application.cfc in this zip file.
  10. Open Application.cfc and change the application name from yourAppNameHere to your application name. If you don’t know what to use, use your domain minus the dot, i.e., “myDomainCOM”.

Providing your server is running ColdFusion 8 or above, you should now have a working Model-Glue site! To test it, open the site in your browser and you should see a message stating “Model-Glue is up and running!”

-rG

Category Posted in coldFusion, modelGlue Comments No Comments »

Use cf_sql_timestamp for MS SQL and MySQL date types

Date 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!

Category Posted in coldFusion Comments 4 Comments »

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

Date 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

Category Posted in sql Comments 3 Comments »

« Previous Entries
Next Entries »