I ran into an interesting issue today. To make a long story a little less uninteresting, I was basically trying to go from an array of ORM entities to an Excel dump of the data in those entities. Obviously, the easiest way to accomplish this is to first convert the array of entities to a query, and then pass the query result set off to the built-in CF Excel manipulation functions.

Easy enough. With one little line of code, you can very simply convert ORM entities to a ColdFusion query…just like so:

// get the orm entities
ormstuff = EntityLoad( "Art" );
// convert the orm goodness to a CF query
anicequery = EntityToQuery( ormstuff );

Cake, right? If I were to hand this query off to CF’s spreadsheet functions, I could easily prepare and export a nice Excel file full of the data I just converted. But of course, nothing is that simple…and that’s where the potential bug announced itself.

A Dark Foreboding

You see, instead of simply dumping all the data for all the columns of the query-which-was-once-an-entity, I wanted to pare down the list of columns that would be output in the Excel export. Since I already had a query handy, I figured passing a list of column names to a Query-of-Queries would make short work of this. However, the moment I tried this…

BOOM!

I received the following error:

Query Of Queries runtime error. Unsupported SQL type java.sql.Types.UNKNOWN

The Weirdness Begins

So what happened? Turns out that one of my columns (a BIT column on the database) was being converted to an “UNKNOWN” data type in the query result. You can see this easily by viewing the metadata of the query object (notice the “ISSOLD” column):

writedump( getMetaData( anicequery ) );

 

 

 

 

 

 

 

 

 

 

 

 

The Weirdness Grows

As strange as this is, it gets weirder.

  • If I do a non-entity query of the database and the dump the result, the data type for “ISSOLD” is not UNKNOWN, but the correct data type.
  • If I pass a “*” in the Q-of-Q instead of a list containing the offending column’s name, no error occurs
  • CAST() the column, you say? Yep, tried that. No dice.
  • Changing the “type” and “ormtype” attributes of the BIT column’s properties in the ORM mapping don’t help. I tried a number of combinations, but could not get the “TypeName” to ever NOT be “UNKNOWN”
    • Ok, that’s not 100% true. if I leave those attributes off, the TypeName is set to “JAVA_OBJECT”, and the QofQ is able to execute without error. Of course, this is only for MySQL…in SQL Server, I couldn’t get it to change, even with the same settings as what “worked” for MySQL. Ugh.

The Weirdness Ends

After several unfruitful experiments and dozens of minutes Googling to no avail, I put an end to the madness by simply writing my own version of EntitytoQuery(). It’s ugly and still a work in progress, but it at least by-passes the issue I encountered.

If you’re interested in seeing this issue in action, check out the demo pages I put up (each page has a dump of the code for the entities):

I’ve also submitted a bug for this, so if you think it’s crazy, feel free to vote!