ColdFusion 9’s MySQL Drivers and Aggregate Functions

I found out that ColdFusion 9 ships with MySQL drivers that don’t play nice with aggregate functions like SUM…

I was running a fairly complex query just fine in (Sequel Pro for Mac (probably the best MySQL client out there for Mac). When I added this complex query to a ColdFusion app I was working on — BOOM — ByteArray objects cannot be converted to strings.

The data I was working with was decimal / integer data. CFDump showed that they really were byte arrays.

Well, it seems that the latest and greatest MySQL drivers that ship with CF9 aren’t working so hot for Adobe. After a little research, I noted that there were three workarounds I found on the web, none of which were great:

1. Revert your MySQL drivers back two or so versions
2. Use CAST or CONVERT to encode to Latin (yes, Latin) encoding on the SQL side
3. Use CF function “toString” on the ColdFusion side

I choose not to go messing around with old versions of drivers; but this solution sounded like it might work. However, if you’re in the process of upgrading servers to CF9 and you don’t want to change the SQL or the CF of the underlying application — this might be the best option.

Since I had 200 lines of SQL with a lot of aggregate “fun”, updating the SQL wasn’t an attractive option. I tested this workaround anyway but couldn’t get to stop throwing those ByteArray errors. I suppose that (with enough time) I may have gotten it to work with CAST(someColumn AS decimal), but I would’ve had a bunch of SQL to update so, on to “curtain number three”.

Wrapping the output value from the query with “toString” did the trick for me. There were only a dozen places this change was needed in the code and –since it was new code and all “behind the scenes” stuff– it was the least impact/risk.

All in all, a bit disappointing to find that shipped drivers aren’t working with ColdFusion (or vice-versa).

Macromates TextMate Even More Useful

So, I’ve been using TextMate (http://www.macromates.com) on the Mac for quite a while now and I am continually amazed at the depth of this editor. In fact, this post was created and posted using TextMate.

Windows people don’t despair – there is a compatible editor for you called ‘E Text Editor’ (http://www.e-texteditor.com/) and it supports many/most of the TextMate functionality.

Most recently I’ve started doing some ColdFusion coding using the CFTextMate 3.0 bundle (http://www.cftextmate.com) and I’ve been blown away by the speed and performance of this editor over Eclipse (with CFEclipse).

One thing I noticed with CF is that the # isn’t on the list of “Typing Pairs” – the feature that lets you type in one # character and have the editor “autocomplete” the wrapping (i.e. #variable#).

It was SIMPLE to add that, I just went into the Bundle Editor and selected the SOURCE bundle and added ( ‘#’, ‘#’ ) into the ‘smartTypingPairs’ list – make sure you add an appropriate comma wherever you add your new entry. I’m sure there is some way to edit the CF bundle to support this – but really, this solved the problem quickly and easily.

I’ll keep adding to this post as I document the more useful “getting started” things

Some quick tips –
APPLE + ‘/’ – is the default comment (line)
SHIFT + CRTL + ‘B’ – creates a nice comment banner
SHIFT + CRTL + < = Inserts an “open/close” using the current word that is to the left of the cursor

Evernote can be your Digital Photographic Memory

Evernote makes it possible to have the digital equivalent of a photographic memory with you at all times (and on any computer) and its FREE.

Evernote (www.evernote.com) has native applications for Mac, Windows, iPhone/iPod Touch, BlackBerry, Palm Pre, Windows Mobile, and a web-based version that allows you to CAPTURE, MODIFY (mostly) and RETRIEVE information when and where you need it.

Capture – getting data into Evernote can be through typing, web clipping, audio recording, PDF files or images.  Premium users can add other file types.  Once something is captured, it will be available on ALL your devices and computers in seconds after that note is “synced”.

The image functionality can take advantage of the camera on your phone (or webcam) to capture images straight into Evernote.   Once notes with images are “synced”, they are magically indexed with optical character recognition (OCR) and can be searched just like text!

Overall the OCR is really good.  I’ve been using it to capture whiteboard notes, business cards, return addresses, interesting websites (web clipping), and more.  I used it when I was recently shopping for a washing machine; recording the pricing at different stores and taking images of the sale flyers/advertisements.  It was really nice to be able take a snapshot and add a tag and keep walking.

Focus and contrast is always important for OCR and this is no different, older iPhone cameras (without autofocus) may have issues without some type of close-up lens like the Griffin Clarifi (www.griffintechnology.com/products/clarifi).

Modify – editing a note is pretty straightforward on the web, PC or Mac versions.  Editing on the mobile side can be a little challenging on anything other than text-only notes.  Notes with images, audio, or rich text can only be added to (not edited) on the iPhone or BlackBerry applications (not sure about the Palm Pre and Windows Mobile versions; but the documentation seems to lean that way).

Retrieval – getting data OUT of Evernote is a joy.  If you are hyper-organized you can setup multiple “notebooks” to store notes in and use tags to categorize your notes globally.  But, if you do nothing more than the basic capture – you will still be pleasantly surprised at the speed and accuracy of the searches, synchronization, and various ‘views’ — especially on the mobile devices.  I’ve gotten very accurate results with the search — even “artsy” text and small print has been recognized.  Sideways text does seem to pose a problem.  Searches work off the index and the tags – but not the notebook names – making it very easy to search.

I’ve been a big fan of Evernote for a while.  I started using it to keep some important notes synced between my Mac and a couple of PCs.  Now I have thousands of notes, plus links to web sites, whiteboard images, business cards, and more — available on my computers and mobile devices.  It really does make a difference.

The free account has ads (which are very unobtrusive) and is limited to 40mb transfer per month.  That sounds low – but here is the reality; I have 18,000 typed notes, 360 camera phone images, 240 web clips, 34 audio notes, and 9 hi res photos. I only hit 15% this month.

For power users, $5 per month gets rid of the ads, increases the monthly limits (from 40mb to 500mb), adds SSL security & document sharing tools, and allows for any file-type (i.e. videos, Word, Excel) as attachments.  If you collaborate or are working with sensitive information – this might really be worth it.