I looked through all of the options but couldn’t find a setting related to the issue. The solution (workaround) turned out to be very easy. Just add 0 to the column in the SELECT list and the values will appear.

For example:

SELECT myBooleanColumn + 0 as myBooleanColumn
FROM myTable

Either a 1 or a 0 will now appear as the value in the results.

From there, I just needed to add the small extra step of Base64 encoding and my query was ready to go.

<cfloop  query="q">
    <cfset base64Data = toBase64(q.my_binary_data) />
    <cfset q["my_binary_data"][q.currentRow] = javaCast("string",'#base64Data#') />
</cfloop>            

This is not the first time I found a useful solution on Ben’s site. If you use ColdFusion, you should definitely check it out. He has some great, easy-to-understand, posts on many useful topics.

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

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!

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