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

SQL Server Express 2005 Remote ODBC Connections

Just downloaded and installed SQL Server Express 2005 yesterday for my development environment. So far, it looks like a suprisingly functional yet free version of SQL Server. I have been pleasantly suprised. However, there was some heartburn involved with getting it to accept Remote (i.e. from another server) ODBC connections from my development ColdFusion server (non .Net). With the help of google, the following website (and several others), and some patience, I was finally able to get it to work this morning:

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Here is what I learned in a nutshell:

1. By default, SQL Server Express 2005 does not allow TCP/IP connections. You have to turn that on in the Connection Manager. The how-to is listed in the article that I mention above.
   
   
2. By default the SQLBrowser service is turned off. You have to go into Administrative Tools / Services and turn it on. I set it to manual, and then clicked "Start", however you may wish for it to always be running.
   
   
3. Since the computer that I have SQL Server Express 2005 running on is inside a firewalled local network that is not connected to the outside world, I don't have Windows firewall running on the database machine. However, if you do have SQL Server Express 2005 running on a machine with Windows firewall, or any other firewall running on it, you may need to try fiddling with the firewall settings to allow connections to the machine.
   

I think that's all I had to do. If I remember anything else, I will post it here.

Windows XP Home Administrator

Wow. I must be an idiot. I never knew that you could log in as Administrator on Windows XP Home.

To do so, when you come the Windows Home Login Screen (the one with a button for each user, and when you click the button, it gives you a password field) simply press Ctrl-Alt-Delete twice (or so), and you will get the standard login screen with a username and password field. For username put the username, for password put the Administrator password, and you are logged in as Administrator. On some computers, if you don't specify a password, it just leaves it as blank. Amazing.

MS Access: List Table Names

I finally figured out how to query a list of all table names out of MS Access. There is a hidden table called MSysObjects. Many thanks to http://eis.bris.ac.uk/~ccmjs/access_section.html for the information.

To get a list of table names from Access (or form names, or query names, etc.), simply query the MSysObjects table, where type=1. Easy.

CF 7

I got burned today by an (apparently) undocumented change to the way that ColdFusion finds Custom Tags. This took a little bit of stomach acid, and a lot of patience to figure out.

It appears that when ColdFusion 7 tries to invoke a custom tag from the CustomTags folder, it look for the tag in sub-directories to the CustomTags folder before bothering to look in the CustomTags folder itself. So, if you have an old copy of a custom tag in CustomTags\old_tags, and a new copy in CustomTags, it will call the one in old_tags, and ignore the newer one. This is very counter intuitive, and was not the case in older versions of ColdFusion, including CF 5, and CF 6.1.

Furthermore, if you delete files in the sub-directory, ColdFusion doesn't realize it until you restart the server. It just gives an error.

The requested scope application has not been enabled

On our intranet server, I keep the webservices in a seperate folder that is not a part of any application folder tree. The first time that I tried to access an application variable in a web service, I encountered the following error message:

CFCInvocationException:[The requested scope application has not been enabled]

It took me a little while to realize that the reason I was getting this error message was because the webservices folder was in a tree that had no application.cfm, and no . As soon as I added the above it started working perfectly. This may seem obvious, but at the time the problem seemed obscure (About 10 minutes on google got everything sorted out).

Primer on ColdFusion Parsing XML

The ColdFusion documentation, although useful and thorough, is not perfect (yet). The writeups on XML functions lack a simple, easy to follow example of how to parse/manipulate an XML document.

I found the following article useful http://www.findarticles.com/p/articles/mi_m0MLU/is_8_5/ai_106770602. It explains how to parse an RSS feed using ColdFusion. I found that by reading thru the article and observing the sample code, I was able to learn how to parse XML in about 5 minutes.

SQL: Limiting Queried Rows

The Problem
Where I work, we have a few databases with some pretty large tables. Record counts into the hundreds of thousands are a dime a dozen, and you'll even encounter the occasional table with multiple millions or records. Though I dearly love the SQL keyword TOP, sometimes it is inadequate. As a web programmer, how often have I wished that there were a way to tell a SQL Server to only return rows 50 to 100? Doing so would make result pagination a breeze.

I posted a question to a Cold Fusion Message Board that I frequently visit, and my main man Lance M. (http://www.webasics.net/) came to the rescue by posting three possible solutions that he had read about in a SQL book.

The Solution
While there doesn't really appear to be a way to specify which results to return short of using a stored procedure, there are several possible workarounds. The workaround that I liked best is both simple and elegant. I will post it here. If you are dieing (spelling?) to know the other two, make a comment, and I will post them as well

If you want to get records 1-50 from the fictitious table "author" you would, of course, just do the following:

SELECT TOP 50 *
FROM author
ORDER BY last_name;

However, if you wanted to get records 51-100, you can do this:

---------------------------
SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC;
---------------------------

Presto! What you end up with is records 51-100 (albeit in reverse alphabetical order). This query essentially gets the first hundred authors, reverse alphabetizes them (ORDER BY last_name DESC), and then returns the first 50 from the upside down result. Granted, this method is not as efficient as just being able to specify a STARTROW and an ENDROW, but it is a LOT more efficient than having the database return a coupl'a hundred thousand records, and then just picking out the chunk of 50 that you want.

If you want to re-alphabetize the result, just add the following wrapper:

---------------------------
SELECT *
FROM
(SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC)
ORDER BY last_name ASC;
----------------------------

Very cool, Lance. Thanks for the tip.

Christmas Tree Preservative

I know that Christmas is over. However, I thought that I would post this here anyway. The following recipe is for a Christmas tree preservative. For the last few years, rather than putting regular water in our Christmas tree stand, we have put this mixture in. It seems to keep the Christmas tree fresher for much longer. My in-laws used it last year, and it kept their tree fresh for over a month. This year we used it, and it worked well until we forgot to refill the stand for a few days (about two weeks after we got the tree). We have heard anecdotes about people using this preservative and its tendency to make a tree more fire resistant.

Following is the recipe for the preservative. Disclaimer: I make no gaurantee whatsoever that this preservative will actually work or make your Christmas tree fresher or last longer or more resistant to fire. I just suggest that you try it for yourself and see if it works. If it doesn't, well, sorry. It did for us.

2 C karo syrup
2 oz liquid bleach
2 pinches epsom salt
1/2 tsp borax
2 gal hot water

Mix together in bucket, make fresh cut on tree trunk, soak tree
overnight. Use this solution to refill tree stand as needed.

There you go. Hopefully it will work for you as well as it has for us.

Using ColdFusion Components--Properly

Just read a very interesting article by Ben Forta. In the article, Ben suggests that CFCs should not be allowed to output anything to the screen. He suggests that CFCs should only be allowed to get/set/add/update/delete data and such, but that they should not be allowed to actually output anything to the screen. An interesting way of looking at things.

We have been dabbling with CFCs for a couple of months now, and have been trying to draw that line between what should be done by a CFC, and what should be done by simple CFML/custom tags/etc. Ben's article merits at least a look.

ColdFusion Webservice Issues

ColdFusion has been giving me fits for the last few days. I have been trying to publish a webservice using ColdFusion 6.1. At MAX 2003, they made it seem so simple.

Here are the problems that I encountered, and their solutions. I sure wish that these would have been documented somewhere. Incidentally, I am running ColdFusion MX 6.1, the full developer version, using the builtin webserver, on Windows XP Professional

Problems with changing the returnType
When I first started playing with the cffunction access="remote", everything seemed to be working OK. I did a simple web service with returnType="string", and which returned the following string: "test_string". Everything worked great. However, when I played with changing it to returnType="numeric", and tried to return 123456, I immediately started getting a rather bizarre error that looked something like this:

Could not perform web service invocation "test_function" because AxisFault
faultCode: {http://schemas.xmlsoap.org/soap/envelope/}Server.userException
faultSubcode: faultString: org.xml.sax.SAXException: Bad types (class
java.lang.String -> class [Ljava.lang.Object;) faultActor: faultNode:
faultDetail: {http://xml.apache.org/axis/}stackTrace: blah,blah,blah....

This one took me a day and a half to figure out. Finally after a couple of hours on google, I learned the following. Turns out that when you change a component, you aren't necessarily changing the WSDL. ColdFusion creates the WSDL the first time that a component function is called as a web service, and then caches it away for later use. So, when you change your component, especially if you change the returnType, the WSDL and the way that the web service behaves no longer match. I don't know how long ColdFusion keeps this cache, but it is long enough to generate errors for several hours/days.

What you can do to alleviate this problem is to log into the ColdFusion Administrator, click on "Web Services" (under "Data & Services") , find the webservice that you changed, and click the "refresh" icon listed to the left of it. Doing so will refresh the WSDL so that you don't have to get this rather nasty error any more.

Problems with <cfinvoke>
I was never able to successfully consume the web service using cfinvoke. I don't know if it was a problem with the function that I was calling, or with the way that cfinvoke is implemented, but I never did get it to work right. However, I did have success using the following:

<cfset object_name = createObject("webservice","http://[path]/[component].cfc?wsdl")>
<cfset foo = object_name.test_function()>
<cfdump var="#foo#">

Problem with returnType="array"
I finally got the web service component publishing with returnType="string" and returnType="numeric". However, trying to get it to work with returnType="array" was giving me fits. Then after learning about the Administrator refresh, I finally started getting the following error:

java.lang.NullPointerException

I never actually did find an answer to this one on google, but I was able to figure it out. When ColdFusion creates an array, it is not just memory addresses like a simple array in C++. It is actually creating an array object that the variable name points to. When I was returning the array, it was just returning the pointer. Then after returning the pointer, it would destruct the webservice invocation, thus destructing the array, and the pointer was left pointing to nothing. So, I made the following change to my cfreturn, and now everything works just great (I wish that this would have been documented somewhere in big bold letters!):

Before
<cfreturn variables.test_array>

After
<cfreturn duplicate(variables.test_array)>

Adding the "duplicate" changes the way that ColdFusion returns the variable.

This is a very important concept to understand. When you copy a complex variable from one place to another in ColdFusion, it doesn't actually copy the objects that the variable points to. It only copies the pointer. For example, let variables.test_array be an arbitrary array. Remember from before that variables.test_array isn't the actual array, it is only a pointer to an array. When you make the following assignment:

<cfset array_copy=variables.test_array>

you aren't actually copying the array. You are only copying the pointer to the array. Thus, if you make any changes to variables.array_copy, you also are making changes to variables.test_array; they are the same array or in other words, they are two names for the same thing. This is called a shallow copy.

However, if you want to make an actual seperate copy of the array, you have to use the "duplicate" function. Example:

<cfset array_copy=duplicate(variables.test_array)>

In the above example, you made an seperate copy of the array. This is called a deep copy. It actually goes through, and makes a copy of the array pointed to by variables.test_array, and all objects pointed to by variables.test_array.

CFCInvocationException
For some reason, the in the XML of the WSDL document, the following appears in several places:

CFCInvocationException

What does this mean? Can't say for certain. I think that it is a generic exception name used by ColdFusion to return debugging information in the event of an error.

Mounting a USB Drive / Thumbdrive on Linux

First of all, I am working with Mandrake 9.1, Kernel 2.4.21. this may be a lot easier under 2.6. Who knows. Also, I am using a Sony Microvault USB Thumbdrive. This should probably work with any other thumbdrive. But once again, no guarantees.

1. Turn on computer.


2. Log in.


3. su to root. (I have not tried doing this without su-ing to root, although it may work.)


4. Go to /mnt (is: "cd /mnt").


5. Create a directory called "usb" ("mkdir /usb").


6. Change the directory mode to be 777. ("chmod 777 usb")


7. Go to /etc. ("cd /etc")


8. Open up the file "fstab". ("vi fstab"). I am going to do this using vi. You are welcome to use any program that you wish. Also, be really careful with the fstab file. It is the file that tells your computer how to connect to different file systems, such as your hard drives, and your cd drive. fstab is pretty forgiving of any new stuff that you may add, but don't mess with any text that is already there.


9. Go to the end of the file. (Press the down button until you are on the bottom line. Then press the "End" button on your keyboard.)


10. Tell vi that you want to insert text. (Press the letter "i")


11. Press the "End" button on your keyboard again. You should now be at the end of the last line of the fstab file.


12. Create a new line by pressing the "enter" button on your keyboard.


13. Type in the following, verbatim (leave out the quotation marks):
    
    "/dev/sda mnt/usb ext3,vfat defaults,noauto,users 0 0"
    
    (Make sure that you left out the quotation marks.)


14. Hit the escape button on your keyboard. ("Esc").


15. Press the colon button on your keyboard. (":")


16. Type the following: "sav fstab".
    
    (Leave out the quotation marks.)


17. Tell vi that you want to quit the program. (":q")


18. Type in the following: "mount /mnt/usb".
    
    (Once again, leave out the quotation marks.)


19. Your Sony Microvault is now mounted! If you are using Mandrake 9.1, there is now a picture of a hard drive on your desktop. You can access the microvault thru this icon.

This same procedure should work with other USB Mass Storage drives (not just the Sony Microvault.)

The BYU 100 Hour Board

The BYU Hundred Hour Board is, in my opinion, one of those gems of the internet. Visit their site, ask any question you like, and within 100 hours (usually) they will post the question and answer to their website. Depending on the nature of the question, the answers are often well-researched, usually with contributions from more than one member of the 100 Hour Board staff, and are frequently witty and clever.

They have archives going back to 2003, although the board has been around since before 1996.

Here are some of my favorites:

• What can I do with a pound of powdered sugar?
• What happens to milk when it goes into your stomach?
• How many stairs are there on [BYU] campus, both inside and outside the buildings?