Adam Tuttle

REALLY?! CFSpreadsheet Throws Exception When Column Name Contains a Comma

I don't even know where to start with this one. So let's just look at some code, shall we?

<cfspreadsheet action="read" src="someFile.xlsx" headerrow="1" />

Pretty simple, innocuous code, right?

Now, in your spreadsheet, create a column name with at least 1 comma in it. In my case: "Committees, Chapters, & Networks Selected"

If you're getting data from non-technical people, you're likely to get stuff like this — and really, there's no good reason not to support this behavior.

What does CF do with this?

[Table (rows 3 columns Username, Password, <snip>, Committees, Chapters, & Networks Selected, Education, <snip> ] is not indexable by COMMITTEES

It's pretty obvious what's going on here. Someone had the bright idea to use a comma-delimited list of column names when converting the imported spreadsheet file into a Query object; and my single column name "Committees, Chapters, & Networks Selected" now appears to be 3 distinct columns, and it can't find the data for them.

I am simultaneously gobsmacked and awestruck.

Raised 3825042.

This also happens to remind me of a bug I filed back when CFSpreadsheet was in beta before the release of CF8 (785,511 bug reports ago), that the header row should not be included in the resulting data. At the time the answer I got from the engineering team was that it was "functioning as designed." But if there's anything I think we should have learned from (the late) Adam Cameron, it would be not to take crappy responses like that lying down. So I think it's time to put the spotlight back on that one.

I found it in the bug database (3039531) but it's marked as "withdrawn" as a duplicate of ... something. The bug number of the supposed original is provided, but it's not found. I'm going to try to draw attention to this existing bug instead of submitting a new one.

Update: Rupesh was kind enough to point out, on that older bug, that an attribute named "excludeHeaderRow" was added to resolve this second bug. I seem to recall hearing about this before, now that it's mentioned, but I obviously had long forgotten it. So at least that one's resolved.

Please vote accordingly, if you agree.

Published 2014-09-16 @ 08:20 in ColdFusion

My 9/11 Story

I don't think I've ever shared this before. It's not a particularly riveting, eye-opening, heart-warming, or otherwise exciting story, but it's mine. And I want it recorded for the sake of history, so here goes.

I was at Towson University on September 11th 2001, the day that the Twin Towers and Pentagon were attacked. I lived in the on-campus dorms. I rolled out of bed at the customary 10-minutes-until-class mark, ate whatever Top Ramen was left on the counter from dinner the night before, as breakfast —washing it down with warm, flat Mountain Dew, no doubt— and brushed my teeth. I had no idea what was already going on.

I had no habit of turning on the TV in the morning, for any reason. I walked everywhere on campus, and I could tell just by looking out my window whether or not I should bring an umbrella. So I had no idea what I was walking into when I knocked on my neighbor's door to pick her up and walk to class.

She opened the door and probably said something like, "Can you believe this?!" — my memory of the details has already started to fade. At this point, only the first tower had been struck. I am pretty sure the news media was all-but confirming it was terrorists. They didn't have any footage of the first tower being struck, but a few "first hand" accounts from people on the street and live footage of the plume of smoke the size of my home town coming from the side of a building were pretty newsworthy.

We decided to skip Spanish class that morning.

As we sat with the dorm room door propped open, watching the news, a small crowd gathered. Maybe 15 people at its peak, probably half the people that lived on our floor. We were all in shock. Then the second tower was hit, live on-air, and we really, really weren't going to go to Spanish class. We heard through the grape vine that the test scheduled for that morning would not be postponed and no make-up would be allowed, so we took our zero's.

Then the news came in that the Pentagon had been attacked, and we all started freaking out; as one is prone to do when terrorists are attacking one's country repeatedly in the same day. Where would it end?

By lunch time, we decided that we were all going to go give blood. Maybe that would be a helpful thing we could do from so far away. So we gathered as many people as we could and walked 30 minutes to the local hospital. By this time, I had worked up the courage to actually give blood. I didn't tell anyone there, but I was petrified of needles. As a young boy, I had to be held down by several nurses and my mom any time the pediatrician wanted to give me a shot. Petrified. By this time in my young-adulthood, I still couldn't look and very much did not like needles (who does?), but I was able to get a shot or two. I was going to do it. I felt compelled.

The hospital turned us away. They said they had enough blood, and that it couldn't be shipped up to New York or D.C. to help there. We were heartbroken, and I was stuck somewhere between relieved and disappointed. And glad that none of my friends found out how scared I was to give blood. Very glad.

The rest of the day was a blur. The news of United 93 came in. There were meals in the dining hall, which was eerily quiet. Everyone was afraid Baltimore would be hit too. Most kids there were from the Baltimore area.

I remember waking up the next morning and turning on the news to make sure the attacks hadn't continued. Thankfully they hadn't, but there was news and footage of aerial attacks somewhere in the Middle East overnight. (Iraq? Iran? Afghanistan? Another detail lost to the fog of time...)

And then life went on. Courses resumed. Finals were taken. Some people graduated that year. I'm pretty sure that my best friend dropped out of school and joined the Air Force (for unrelated reasons) that summer. I think I got a B in that Spanish class, too.

That day changed the course of American history, and for me it was an awakening into what life was like after school. As the kids say, "Shit gets real."

My campus wasn't one that saw a lot of political protests, but personally I started to really notice what the rest of the world was like. I came out of my Privileged American Student bubble a little bit. I started to care about elections and which countries we sent aide to (and why). I didn't turn into an activist; I just had my eyes opened.

This is a strange, dysfunctional space ship we're riding and I wish we could find a way to get along.

Published 2014-09-11 @ 08:53 in Off Topic

GitHub Tip for Your First Pull Request

There is a mistake that I see a lot of people new to GitHub, or new to making Pull Requests, making. It's something I find myself explaining often in people's first PR threads; so I thought I would clean it up a little bit and share it here for the benefit of a wider audience.

In Git, unlike SVN and other centralized Version Control Systems, branches are easy, fast, and awesome. You should be using the heck out of them. And, if you're not, then that can come back to bite you when you get around to sending pull requests. I'll illustrate the problem with an example.

Let's say you want to contribute something to CFDocs. So you fork the repository, clone your fork to your local machine, and edit the code. You're only planning on doing this one little thing, so why bother making a branch?

You make your change, everything looks great, so you open a pull request. You select that you want to merge in the changes from your master branch into the original repo's master branch:

Pull Request Screen Shot

You submit your pull request, and happily go on about your business. All is right with the world.

As it happens, it takes Pete a few days to get to your Pull Request, but in the meantime, you've become inspired at how easy it was to do that, so you want to help more!

You make more changes, push them up to your repository, and then... oops. Because you still have an open Pull Request against your master branch, pushing new commits to that branch updates the Pull Request and adds those commits. Oops indeed! These new commits have nothing to do with the original PR and really should not be included.

The solution to this problem is branches.

If you create a branch for each feature, or for each discrete pull-request that you want to submit, then they will stay in their separate silos. This is particularly important if the project author asks you to make changes (add tests, change formatting, etc). Then you can update the code in one branch without affecting the other; and as luck would have it, these changes are automatically merged into the PR when you push them up to your fork on GitHub.

Here's a more appropriate workflow:

  1. Fork the repo to your account
  2. Clone the repo to your local machine: git clone (replace the account and repo names, of course)
  3. Create a branch to do your work in, and name it after what you're working on: git checkout -B ormExecuteQueryImprovements
  4. Make your changes, and commit them to your local clone
  5. Push your changes, in their branch, back up to GitHub: git push -u origin ormExecuteQueryImprovements
  6. Then, open your PR and select this new branch as the HEAD of the PR.
  7. Profit

I hope this saves you the headache of adding commits to the wrong Pull Request and helps you enjoy your experience with Git and GitHub a little bit more.

Published 2014-09-10 @ 10:14 in Git

HQL Keywords

I was recently bitten by the fact that apparently "Member" is a reserved keyword in Hibernate's HQL. I was further disgusted to find that, apparently, no canonical list of HQL keywords exists. All we have to go on is references to individual keywords in the documentation ("after the keyword...") and complaints of people that have accidentally run into them.

I'm not the first to run into this issue and hope to raise awareness by blogging about it, but I have been known to read that blog from time to time and this still bit me, so I figured it was worth reverberating it a bit.

As far as I can find, all of the words on this list are reserved keywords, either as reported by someone that ran into an issue using one as a table/entity name, or by references to them as keywords in the documentation.

  • Member
  • Event
  • Class
  • State
  • Extends ("when using the extends keyword")
  • From ("the FROM keyword is optional")
  • Versioned ("adding the VERSIONED keyword")
  • Update ("after the UPDATE keyword")
  • As ("the AS keyword is optional")
  • With ("the hql WITH keyword")
  • Join ("the JOIN keyword")
  • Distinct ("the DISTINCT keyword")
  • All ("the ALL keyword")
  • True
  • False ("the keywords TRUE and FALSE")
  • Connect ("the CONNECT keyword")

Presumably, additional SQL-ish words like SELECT, WHERE, GROUP, ORDER, LIKE, etc are also reserved (or at least troublesome). I would avoid any of the above for Entity/Table names, if I were you.

In my case, I had a table and entity named Member, which is a pretty logical thing to do when you're modeling an organization with Members.

Since my system has been running fine with an entity named Member for a while now, and it would be a real pain to go back and change everything, I tried to find the easiest possible solution. Here's what I changed:

  • Renamed Member.cfc (entity) to xMember.cfc
  • Updated entity relationships from cfc="Member" to cfc="xMember"
  • Updated any HQL references from from Memberto from xMember
  • Updated any EntityLoad("Member") references to EntityLoad("xMember")

So far, so good.

Published 2014-09-08 @ 08:30 in ColdFusion Hibernate