Coldfusion 10 Update 11 SQL Server 2012 Query.columnList() Geometry Fields Error

[Guest]

When we do a query.columnList on a table that contains a geometry

datatype column it errors with unsupported data type. This does not

seem to be fixed in CF10 - Update 11. Have you heard of this, seen

this or have a solution to this issue?

Super easy to reproduce, just create a table with a geom or geography

column, run a query Select TOP 1 * FROM TABLEWITHGEMO, whatever and

then do the queryname_columnList which normal returns column names

that you can loop through (as you most likely know).

[Carl Von Stetten]

Looks like this is a known issue in CF10: https://bugbase.adobe.com/index.cfm?event=bug&id=3217374

You might want to add your vote to this bug.

-Carl V.

[Gary__F]

This is a problem for me too. But a bug in the bugbase won't let me add my vote. It keeps saying my session has expired and I need to login... repeat 10 times... give up.

The geography data type was added to SQL Server 6 years ago (2008) so CF10 really should have been patched by now. So many apps have maps and these geo coordinates need to be stored and processed.

[Carl Von Stetten]

Try again to add a vote.  I think the bugbase was acting up on the 8th.  I just added a comment to ask Adobe to provide an update.

-Carl V.

[Gary__F]

Thanks Carl. I don't think the bugbase likes IE11. I had to use Chrome. Anyway, I was able to add my vote. I'm surprised how little concern there is from Adobe in supporting a major new datatype. Well, "new" as in 6 years old now. How difficult can it be for their engineers to return the column as a string? That would do nicely.

[Carl Von Stetten]

In fairness, this originally wasn't Adobe's problem directly - it was a problem with the Data Direct drivers for SQL Server which come bundled with ColdFusion.  Data Direct broke something in the version of the drivers that shipped with CF10, and Adobe was waiting for a fix from them.  I do think they dropped the ball on not following up on this, as I would hope that Data Direct has resolved this issue by now.

I don't currently have any databases that contain native spatial data columns (I am currently an Integraph GeoMedia user and spatial data is stored in a BLOB column), but this will affect me when we move to ESRI ArcGIS (which does use native SQL Server spatial data columns).  If you have a database with spatial data, could you download the CF11 beta (there is a version you can simple download, unzip and run a batch file to start) and test it against one of your databases to see if the version of the drivers has been updated in CF11?  If it has, we can politely (or not) ask Adobe to backport the driver to CF10.

-Carl V.

[ChrisGoT]

In fairness... it doesn't mater where they got the drivers it should work. Coldfusion was included with MapGuide GIS platform (Autodesk) back in the day. ESRI and Autodesk along with many other products use GEMO columns... this should be working.

Edit:

How hard would it be for a {catch all} regardless of datatype to return the column name when using columnList()? SQL Server is not a unknown DBMS, we have since switched to C# ASP.NET because of this issue.

[Gary__F]

Chris, a catch all makes perfect sense. It will future proof CF if SQL 2014 has added any new datatypes (which reminds me to test CF11 against it!) or whatever SQL 2016 or the next version of mySQL, etc brings to the table.

It's better for CF to spit out a result set with an unknown datatype converted to a string (or heck, even binary will suffice) than to throw an error and have it completely fail.

Any tips on converting projects to ASP.NET? 😉

[ChrisGoT]

Gary, Tips... drink lots of coffee and enjoy never having to explain the extra cost of $1500 to another client ever again.

[Carl Von Stetten]

@ChrisGoTo,

Yes, Allaire ColdFusion was bundled with early versions of the old AutoDesk MapGuide product (version 5 or earlier) - that is how I first got into ColdFusion development.  However, that Allaire/AutoDesk relationship ended long before MacroMedia and Adobe were involved with ColdFusion.  Therefore, I don't necessarily expect Adobe to know much about Geometry/Geography columns.

@Gary__F,

I have pinged the ColdFusion product manager on Twitter to see if he can respond to this.  I'll send another ping mentioning it's still broken in CF11.

-Carl V.

[Gary__F]

@Carl, thanks. Let me know if he replies. He doesn't follow me on Twitter so I can't get his attention that way. Allaire were clever to bundle CF with other products. If something is free then people are more prepared to take a look and even continue to use it. But who buys boxed software these days? A free "CF Express" download on the Adobe site is the way to go to attract newcomers but I can't see it ever happening.

@Chris, nice reply. I would definately need lots of coffee. I can do ASP.VB but haven't been able to get my head around .NET. Is there a tags version of .NET?

[ChrisGoT]

Gary,

It's easy to pick up C#.NET (Time to let VB go...) and it's Object Oriented web applications, dare I say those words here... All I can do is recommend picking up a couple books and having some fun learning. There is HTML <ASP:TAGS> but then there is the code behind which is nice when creating reusable code. Think of it as one big CFC behind every CFM page. Anyway kind of off topic. Good luck to you!

[Carl Von Stetten]

I heard back from one of the ColdFusion team on Twitter.  They are still *actively* working with Data Direct to get the driver issue resolved.  In the meantime, it was suggested to try downloading and testing Microsoft's JDBC Type 4 driver to see if works as expected.

-Carl V.

[ChrisGoT]

Hey Carl thanks for the info on this topic but "Is this fixed?"???

[Carl Von Stetten]

I'm not an Adobe employee, so I don't have any special insights into what is happening within the ColdFusion team.  If the Bugbase site was working right now, I'd check the status for you.

-Carl V.

[Carl Von Stetten]

Was able to get into the bugbase and check, and it has not been "fixed" yet.  I sent a ping on Twitter to the ColdFusion team to see if there is any progress.

-Carl V.

[ChrisGoT]

Is this working in CF11 with SQL 2014?

[Carl Von Stetten · Accepted solution]

Doubtful.  The issue is with the DataDirect drivers, and I don't believe Adobe has gotten any love from DataDirect on this.

-Carl V.

[ChrisGoT]

I finally figured out a work around. Never use SELECT *, make sure you put the column names in over the *. This works with tables that have a geometry column. Go figure!

Example:

Select * From TABLE

Select id, name, address From TABLE

P.S. I should mention; The original issue getting the column list is not as easy but since we have DB Types stored we can get the columns using the sysobject tables. Work around city. Really breaks columnList() but at least it works for now.

[Gary__F]

Carl, no luck either with the CF11 public beta.

I'm sure I read somewhere that Adobe were going to make an effort to fix loads of outstanding bugs in the CF11 release. Supporting a major datatype in SQL Server would have been a very good starting point.