SQL Server / Date Formatting

Too tired to do a full write up. But here are some helpful links if you are trying to do date formatting / masking on SQL Server:

SQL Server Syntax
http://www.oreilly.com/news/sqlnut_1200.html

SQL Server Conversion Codes
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

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.

cfwindow source and cfdiv URL bind strip out <script> tags

This just in. When you use cfwindow or cfdiv to load content dynamically (e.g. thru cfwindow's source attribute, cfdiv's bind attribute (e.g. bind="url:foo.cfm"), or ColdFusion.navigate('foo.cfm')), when the content loads in your window/div, all <script> tags will be stripped from your code.

Consider the following:

<!-------- page.cfm -------->
<cfwindow name="myWindow" source="content.cfm" />

<input type="button" onClick="ColdFusion.Window.show('myWindow')" value="Show the Window">
<!-------- End page.cfm -------->

And the following content page, called by page.cfm

<!-------- content.cfm -------->
<script>

function tellMeSomething (somethingToTell) {

alert('somethingToTell');

}
</script>

<input type="button" onClick=" tellMeSomething('yadda-yadda-yadda'); " value="Tell me Something.">
<!-------- End content.cfm -------->

When page.cfm tells the cfwindow to load conten.cfm, here is the HTML that will be sent to the browser for content.cfm

<div style="overflow: auto; height: 253px; width: 474px;" id="myWindow_body" class="x-dlg-bd">

<input onclick=" tellMeSomething('yadda-yadda-yadda'); " type="button" value="Tell me Something.">
</div>

Notice that the <script tag and its associated function was completely stripped out.

Obviously, this is a problem if the page that you are loading needs to call a JavaScript function. There is a workaround, albeit a very imperfect one. If you put your JavaScript in the calling page (i.e. in the example above, you would put it in page.cfm), it will work.

The problem with the above workaround is that it encourages poor programming practices, and very high coupling between page.cfm and content.cfm.

cfajaxproxy and the head tag

*** UPDATE ***

Ok, so slight modification. It appears that your JavaScript only has to appear after the opening <head> tag. And, this only applies to JavaScript that makes calls against the JavaScript proxy generated by the cfajaxproxy tag (Any other JavaScript can appear anywhere you want). However, if you are going to make JavaScript calls against the proxy generated by the cfajaxproxy tag, then you had better do them after the opening <head> tag.

One other caveat: The above only applies to pages with a <head> tag. If your page doesn't have a <head> tag, then you can put any of your JavaScript anywhere you want. The above only applies to pages with a <head> tag.

It would take too long to explain why the JavaScript behaves this way. However, if anyone wants to know the reason that I believe the JavaScript behaves this way, let me know, and I will post it. It has more to do with where ColdFusion posts its JavaScript on the page than with any quirks in the JavaScript itself.

*** END UPDATE ***

Recent experience has shown that the JavaScript proxy generated by cfajaxproxy will not work if you have any user-generated JavaScript that appears outside of the <head> tag. For example, consider the following pages:

Here is the main page:

<!-------- index.cfm -------->

<cfajaxproxy cfc="component.cfc" jsclassname="component_proxy">

<script>

var js_component_proxy = new component_proxy();

var result = js_component_proxy.a_function();

</script>

<cfinclude template="top.cfm">

Some content

<cfinclude template="bottom.cfm">

<-------- End index.cfm -------->

The "Top" include follows. Notice that it has the HTML header in it.

<!-------- start.cfm -------->

<html>

<head>

Header Content

</head>

<body>

<!-------- End start.cfm -------->

And a nice little bottom.cfm to tie everything up nicely.

<-------- end.cfm -------->

</body>

</html>

<-------- End end.cfm -------->

The above will not work. When you run the JavaScript generated by cfajaxproxy, you will get the following JavaScript error:

window:global: mark_arrived has no properties


However, if you copy the user generated JavaScript into the header area marked "Header Content", suddenly everything works fine.

This was a bit of a problem for us. Our site has a page similar to the "start.cfm" page mentioned here, that in addition to many other things, contains the page header data. This means that cfajaxproxy will not work for us if we use any other JavaScript on the page.

We did find a moderately painless workaround. If anyone is interested, post a comment, and I will post the workaround here.

cfajaxproxy, Application.cfm, and pain

Recent experience has taught me that if you use cfajaxproxy on a page, and your Application.cfm page outputs anything to the screen, you will get a JavaScript error when you try to call any of the JavaScript functions that call proxy methods. So, for instance, consider the following (which gives an error):

<!-------- Application.cfm -------->
<cfapplication name=""blah">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<!-------- End Application.cfm -------->

<!-------- Component.cfc -------->
<cfcomponent>

<cffunction name="test_proxy" access="remote" returntype="string">

<cfreturn "success" />

</cffunction>

</cfcomponent>
<!-------- End Component.cfc -------->

<!-------- index.cfm -------->
<cfajaxproxy
cfc="component"
jsclassname="component_proxy">

<cfoutput>

<script>

var js_component_proxy = new component_proxy();

function js_test_proxy() {
alert(js_component_proxy.test_proxy());
}

</script>

<input type="button" name="a_button" value="Click Me" onclick="js_test_proxy();" />

</cfoutput>
<!-------- End index.cfm -------->

If you run index.cfm?cfdebug, and click the little button, the following error will appear in the little debug window:

window:global: parseJSON

This error took a long time to isolate, and a while to fix. I don't know of anywhere in the livedocs where this is documented. However, if you have any output in your Application.cfm, even a doctype declaration, beware!!!

Incidentally, we did find a moderately difficult fix. If you are interested in knowing how we fixed this problem, post a comment, and I will post the fix.

CF Ajax goodness and SSL

According to Damon Cooper, one of the lead guys on the ColdFusion 8 product, if you request a page using HTTPS, then any of the AJAX calls that the page makes back to the server will use SSL / encrypted connections.


Researched this one for hours. Finally emailed Damon, and he got me a snappy response. Thanks, Damon!

cfexecute

I have been playing with cfexecute for the last