Highlighted

Coldfusion 10 cannot connect to 2016 Microsoft SQL Server

New Here ,
Apr 02, 2020

Copy link to clipboard

Copied

Hello there.

 

I cannot get my datasource connected to a Windows 2016 SQL Server from my CF 10 Enterprise.

 

I've updated to the latest patches.

I think I have to download the MS java db drivers for it to connect, correct?

 

 

 

 

TOPICS
Getting started

Views

1.7K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Coldfusion 10 cannot connect to 2016 Microsoft SQL Server

New Here ,
Apr 02, 2020

Copy link to clipboard

Copied

Hello there.

 

I cannot get my datasource connected to a Windows 2016 SQL Server from my CF 10 Enterprise.

 

I've updated to the latest patches.

I think I have to download the MS java db drivers for it to connect, correct?

 

 

 

 

TOPICS
Getting started

Views

1.7K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 02, 2020 0
Engaged ,
Apr 02, 2020

Copy link to clipboard

Copied

In my opinion, yes.  I recommend installing the official Microsoft JDBC SQL drivers.  I've used them with both CF10 & 2016 and haven't experienced any negative issues.  

 

Here are the articles that helped me get it configured.

https://www.hass.de/content/coldfusion-10-macromedia-database-drivers-leaking-memory

https://www.hass.de/content/coldfusion-10-how-configure-data-sources-microsoft-jdbc-driver-40-sql-se...

We're currently using v7.2.2 w/JRE 11, but I see that v8.2 is the current version. (I'll have to download and test this.):

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 02, 2020 0
Adobe Community Professional ,
Apr 02, 2020

Copy link to clipboard

Copied

Thanks for that, James. Are you confirming you used that ms driver with cf10 and *sql server* 2016? Or did you mean perhaps cf10 and *cf* 2016? If so, what was the sql version used with cf10? 

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 02, 2020 0
Engaged ,
Apr 03, 2020

Copy link to clipboard

Copied

I meant "ColdFusion 10" and "ColdFusion 2016".  (I believe that we may also used the free MSJDBC drivers with "ColdFusion 9".)


We've been able to connect to SQL Server 2008, 2012, 2016 & 2017.  (I just tested 2017.)

 

Charlie, have you explored using the JDBC connector?

 

We've been using it since CF9/10 and found it to perform better/faster.  You can run it along side your regular DSN.  All you need to do is add the JAR to your path, configure a DSN and compare the queries.  We initially configured it so that only developers would use the connector.  I don't recall having to modify any CFML or SQL code when switching.

 

We explored the use of the JDBC Driver because we are required to use the latest versions of Microsoft software and couldn't convince the client to upgrade ColdFusion.  A project required finding the closest locations to a user and we needed to use the SQL geometry data type, but the CF version we used at the time didn't natively support the SQL Server that had the geography feature. We added the JDBC driver and we were able to communicate with the SQL server without any problems.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 03, 2020 0
Adobe Community Professional ,
Apr 03, 2020

Copy link to clipboard

Copied

I don't think you need the Microsoft drivers. They may work better as Jamo indicated, but I think you should still be able to connect to newer versions of SQL Server from the built-in drivers even if they're not listed as supported.

 

What actually happens when you try to connect? What error are you getting, if any?

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 03, 2020 0
Engaged ,
Apr 03, 2020

Copy link to clipboard

Copied

Is there any information available regarding the SQL driver versions that are used in the most recent versions of CF10, 11, 2016, 2018 and 2020?  (I'm not sure how to find this info.)

 

For example, I'm using CF2016 and I see this file: "\cfusion\db\slserver54\DataDirect.url"

and it references "http://www.datadirect-technologies.com"

This website doesn't appear to be online anymore.  DataDirect was acquired in 2003 and has been redirecting requests to datadirect.com since May 2004. Since April 2014, the URL has been redirecting to progress.com... so why it this resource URL so outdated and using a pre-2005 URL in the source library for CF2016?  The sub-directory is titled "slserver54". Does this mean that the DataDirect version for CF2016 is 5.4? (How can this be correctly identified?)

 

If I go to Progress/DataDirect's driver page for Microsoft Server, I see that their current version is 8.0.2.
https://www.progress.com/odbc/release-history/microsoft-sql-server-odbc

The latest 8.0.2 version is certified to work w/SQL 2019 and on Windows Server 2016.

 

The freely available Microsoft JDBC 7.4 & 8.2 drivers both work with MSSQL 2019.  The max JDBC driver for JRE 1.7 (ie, CF9) is the 6.2 or 6.4 driver depending on whether you need to connect to SQL2008 or not.

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matri...

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 03, 2020 0
Adobe Community Professional ,
Apr 04, 2020

Copy link to clipboard

Copied

That directory is for the ODBC Agent/Server stuff. I suspect that stuff hasn't been updated in some time. The JDBC drivers are within \cfusion\lib\adobe_drivers.jar, I think. If you copy that file somewhere else and extract it, you can probably find out what version of JDBC drivers are included.

 

All that said, in my experience older JDBC drivers often still work with newer versions of MS SQL Server, even if they're not listed as "supported". Officially, CF 2018 supports MS SQL Server 2014, 2016 and 2017. CF 2016 supports MS SQL Server 2012, 2014 and 2016. CF 10 supports 2005, 2008 and 2012. But I wouldn't be surprised if you see forward and backward compatibility with SQL Server for general SQL CRUD query use.

 

Finally, while I've seen situations where the Microsoft JDBC drivers work better, I've also seen the opposite. So I would personally hesitate about one being better than the other in all cases.

 

Dave Watts, Eidolon LLC

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 04, 2020 0
Engaged ,
Apr 06, 2020

Copy link to clipboard

Copied

I checked my Windows installation of ColdFusion 2016.0.14.318307 and there isn't any "\cfusion\lib\adobe_drivers.jar" file.

 

Regarding "the opposite" (Microsoft JDBC drivers not working as good), what were the scenarios?  This has not been my experience yet and I'd like to be aware of situations that may not be as performant. Thanks.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 06, 2020 0
Adobe Community Professional ,
Apr 06, 2020

Copy link to clipboard

Copied

It was macromedia_drivers.jar, prior to cf2018. 

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 06, 2020 0
Engaged ,
Apr 09, 2020

Copy link to clipboard

Copied

Any idea if the SQL-related libraries are up-to-date prior to various CF version becoming EOL?  Or do they stick with the same exact library they release with and only update if there's a reported or security problem?

 

As an example, I believe that CFDocument uses a very outdated version of iText and would probably render CSS3/HTML5 better if it were updated.  This causes me to occassionally wonder how well other dependencies are maintained.  The third-party Microsoft JDBC driver can be easily updated. (I just updated to the most recent MSSQLJDBC 8.2.2 driver and can connect to SQL 2019 using CF2016 & JRE8 or 11.)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 09, 2020 0
Adobe Community Professional ,
Apr 10, 2020

Copy link to clipboard

Copied

In my experience - which is not especially recent tbh - these drivers were updated pretty frequently by DataDirect, and those updates would then be available from Adobe (or Macromedia at the time). I don't know how frequently they're updated nowadays. This was kind of pushed by DataDirect, so I don't think Adobe had to do very much except provide a new jar for download. Nowadays, I think the frequency of changes has dropped off a bit, so I don't think they get updated as often. I don't think Microsoft updates their own JDBC drivers that often either, just when they release a new version of SQL Server. So honestly it hasn't been a big deal for me in the last few years.

 

It's going to be hard for me to detail the performance differences between the two driver sets, because I'd usually arrive at these based on load balancing tests, and I didn't really have to know what the application was doing with the drivers. But I can imagine lots of places where they might be different internally: how many queries are stored in the JDBC connection pool, whether or not they're using prepared statements (not really an issue nowadays as ALL queries should be using prepared statements either via CFQUERYPARAM or CFSTOREDPROC with CFPROCPARAM), whether the same query is rerun frequently, etc. In order to solve problems quickly, I tried to avoid getting into low-level analysis with the specific queries. But I do remember testing one application where the MS drivers worked better, and testing another where the DataDirect drivers worked better. I'd also just throw out there that DataDirect can charge money for its drivers for a reason, even though every database vendor offers its own JDBC drivers.

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 10, 2020 0
Adobe Community Professional ,
Apr 10, 2020

Copy link to clipboard

Copied

Adding to what Dave said, I'll address your comments (James) first about CF and library versions, then about itext (and cfdocument).

 

First, about CF library versions and how updated they may be, I will note that I have reported in a blog post in 2016 what they were then: 

https://www.carehart.org/blog/client/index.cfm/2016/2/17/what_are_cf2016_embedded_library_version_nu...

 

I never did such a post for 2018 (I was about to, and lost track). I had gathered the info (at the release of 2018) and documented it in my "hidden gems" talk, available here:

https://www.carehart.org/presentations/Hidden gems in CF2018.pdf

 

Finally, as for itext, I will note two things. It's not CF that uses itext (or controls the version). Instead, it's that the cfdocument tag leverages an embedded tool called icebrowser, and THAT tool uses itext (and controls the version needed). Granted, it's associative math. If CF has not updated icebrowser, it's till on them, and if it's that the icebrowser product is no longer updated by its vendor, then CF is still implicitly guilty.

 

But note also that CF11 implemented a solution that problem, in the new cfhtmltopdf tag (and the associated "PDFG" engine embeded in the "add-on service", running separate from CF itself). That tag (and engine) was intended to greatly improve PDF processing.

 

I know you only brought up cfdocument as an example, but in case somehow a google search leads someone to this discussion, I wanted to make that clarification for them, and for you if it's helpful.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 10, 2020 0
BKBK LATEST
Adobe Community Professional ,
Apr 12, 2020

Copy link to clipboard

Copied

Your being unable to run Windows 2016 SQL Server and CF 10 Enterprise may be a blessing in disguise. As @Dave_Watts says, ColdFusion 10 only has support for MS SQL Server 2005, 2008 and 2012. You can see this for yourself in the ColdFusion 10 Support Matrix.

 

If I were you, I would count myself lucky and stop right here. Better than carrying on to a full-blown application, with SQL Server 2016 and CF seemingly working fine, only to have everything crashing down around your ears later. 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 12, 2020 0