Power of SQL: Indexing and sorting

Coldfusion Muse recently made a couple of posts about clustered indexes on SQL Server. I was commenting off the grid to the Muse that the timely post helped me in solving a data import issue.

To protect the innocent, and make things less prone to narcoleptic episodes, I had the following scenario:
- An Access Database with Categories and Products.
- The First product listed in a category was a "special" product. Problem: There wasn't any information that would indicate that product was the first one.

Example Data:

category_id	product_id	product_name	...
1	2	Red Jumbo Ball	...
1	1	Green Jumbo Ball	...
1	3	Blue Jumbo Ball	...
2	4	Purple People Eater	...
2	6	Yellow Submarine	...
2	5	White Christmas	...

Once imported, I knew the data was at the whim of the database which meant that I needed to "preserve" the order prior to importing the data. I added another column "autonumber" on my products table in Access.

Once imported, I could set the clustered index to the "autoNum" column in SQL and know the sort order was correct! Of course, it never hurts to add an order by to your select statement. There are many factors that can affect sorting, clustered indexes are just one factor.

FFF CSS Menus Over Flex, Flash and Flash Forms

Recently I came across the issue where I needed to change from an old menu design to a CSS drop-down menu. (Similar to the way File, Edit .... Help works).
The main application was Flash based content - which presented me with a problem. The issue is that the CSS content drops behind the flash content.

Fortunately, the solution is easier than I thought.

Add This to your Object tag:

<param name="wmode" value="transparent" />

If you use version detection, make sure you add it in there. It will look something like this:

AC_FL_RunContent(
...
"wmode","transparent",
...

If you still have Flash Forms for ColdFusion laying around, just add the following to your CFFORM tag:
wmode="transparent" (note that this goes in the tag, along with your format="flash" attribute.)

If others know of pitfalls or have a sexier solution, by all means - share. :)

Prism: Bridging the gap ...

I've recently become aware of a desktop application called Prism. This application (6.66MB) in size installs as a desktop app, when you run it, it prompts you for the following information:

After you add the URL of the website, and give it a name you are good to go. From there, you have a shortcut icon that starts that site up every time - just like its a desktop application.

Why is this useful? Let's backup a bit ...
I have long been a fan of Meebo - as well as other sites like google documents. In fact, they are among the FIRST that I startup on any given day. Firfox Browser (with certain "startup" sites), Internet Exploer, Safari, Thunderbird, SQL Enterprise Manager, Query Analyzer, Eclipse ... but this also leads to an eventual problem. Firefox (my browser of choice) gets bloated and needs a restart every two or three days. While I love the fact that FF has so many developer tools, from the ability to add / edit cookies, to being able to inspect and modify the source code of a page using firebug - and even intercepting and tampering with page requests ... (we'll come back to this in a sec)

When I found meebo, I fell in love with it for a couple reasons:
1) I never have to update Trillian (or whatever software) again!
2) It supported everything I used.
3) I could use it at the library or on a friend's computer without hosing up his configuration.
Okay, for those that care, that was three and there are more reasons to like it, but I don't really think you care right now - let's tie this back into Prism.

What I didn't like about Meebo was the fact that when I closed my browser, it knocked me off the grid! It also hindered my development efforts when I was tampering data. (Read more about that when I finish writing the entry) Meebo continuously sends requests back and forth with the server - hence the concept of "instant" messaging. Google Mail or reader is just as bad, even if you don't have chat enabled on it. This is because of the way AJAX works - but this hindered the ability to get meaningful tampering / inspection done.

Moving these "web applications" off as Prism apps is the solution I've been looking for! I can close / reboot my browser (or even let it crash) without being annoyed that its dropping me from the grid, or interrupting that email to my Aunt. I can focus on developing applications without someone "pinging me" and interrupting me by having the tab flash their message (drawing valuable seconds away from my code). Speaking of rebooting the browser, at some point in the future ... I'm planning on featuring FFox and some tools I have found to be useful as a developer.

A few random thoughts about this:
- I don't like that there is only one icon for Prism, and it doesn't use the website icon (if there was one to begin with).
- I don't like the fact that ads pop up in a new prism window, but then again, if I wanted a site to pop up in a new window, this is helpful.
- There is a menu in the bottom-right corner to "Install to Desktop" - this puts the current page as an icon on your desktop, so if you were looking to "control" your kids browsing with this tool - forget it. They will just litter the desktop with thousands (or dozens) of shortcuts.
- This doesn't replace Adobe AIR, which allows for more feature-rich use of apps - like the ability to use the application offline. Prism is an on-the-grid-only application.
- If you setup the site as "https://" - it gives you the padlock in the bottom-right menu, but it doesn't allow you to inspect the security certificate. That's a bummer, but most people won't ever look at one, even though they should.
- If you setup an icon for Gmail on one account and setup an icon for Gmail for another account, they will NOT share the same session.
- Interestingly, if you setup an icon for Gmail and one for Google, they DO share the same session. If anyone knows how to make sure they do not share the same session on the same profile, fire away!

CFML to be Open Source (for Blue Dragon)

Blue Dragon (A CFML server similar to Adobe's ColdFusion Server) has announced a dual-license where the basic CFML based Blue Dragon server will be open source. This is smilar to the way MySQL is! This means that for those who want to "pop the hood" and customize it (like Google did for their database) - they can.

I'll let you read details there, but the first code drop will be released "near" June 2008 - same time as CF United 2008!

There is no word yet as to whether this changes anything for Adobe, but in my mind, choices are a GOOD thing. Since the source code has not been released yet, right now this doesn't mean much (IMHO) other than a PR move for New Atlanta / Blue Dragon. Still, I will certainly be downloading and popping that hood myself when the option comes.

Updated: 03/14/2008 - Clarify what this means for the CF Community.

Power of SQL: Insert from Select

Continuing the discussion of the Power of SQL. I came a situation where I wanted to move data from one table to another (without using DTS). How do you get the data moved over with just ONE SQL statement and no temp tables or complicated programming?

Consider the following example:

INSERT into foo2
(col, col2,col3)
select col,col2,col3
from foo1
where destination='foo2'

I hope that helps!

Excel file with CFcontent

It is extremely easy to give your customers access to an Excel file containing their data.

The key is a nicely formatted HTML table, with correctly placed cfheader/cfcontent tags.
Consider the following example:

<cfsetting showdebugoutput="no">
<cfheader name="Content-Disposition" value="attachment;filename=""My File Name.xls""">
<cfcontent type="application/msexcel;charset=utf-16">
<table>
<tr>
<th>Color</th>
<th>Code</th>
</tr>
<tr>
<td>red</td>
<td>#ff0000</td>
</tr>
<tr>
<td>green</td>
<td>#00ff00</td>
</tr>
<tr>
<td>blue</td>
<td>#0000ff</td>
</tr>
</table>
<cfabort>


If you have any other tips or ideas feel free to share them!

Welcome Home Atlantis

Just a quick note to welcome home the Atlantis crew!

STS-122 Quick Stats...
121st mission
24th flight for Atlantis
5.3Million miles

... priceless!