CF DSNS

Hi @weezerboy,

As luck would have it, I also had some difficulty with MySQL only days ago. I wanted to update the MySQL datasource on my ColdFusion 2018 from version 5.x to the current 8.0.19. I should like to share with you the steps that I followed to get it to work.

1) I opened the datasource page of the ColdFusion Administrator and copied the properties (DSN name, database, server, port) of all the MySQL datasources; (Ignore this step if you don't yet have any MySQL datasource)

2) I then stopped ColdFusion, opened the directory C:\ColdFusion2018\cfusion\lib. It contains the MySQL ddriver I wish to replace, namely mysql-connector-java-5.1.48.  I moved the file to a back-up directory outside ColdFusion. (If you have no such driver file, then you should ignore this step)

3) I went to MySQL Community Downloads , chose the "Platform Independent" option for Connector/J 8.0.19 and downloaded the Zip file mysql-connector-java-8.0.19.zip (size: 4.4 MB);

4) After unzipping, I copied the file mysql-connector-java-8.0.19.jar to C:\ColdFusion2018\cfusion\lib.

5) I restarted ColdFusion 2018, and went to the datasource page in the ColdFusion Administrator.

6) For each MySQL datasource, I recreated a new datasource using driver "other" and driver class "com.mysql.cj.jdbc.Driver", as in the following images

As you can see, MySQL is installed on localhost, port 3306. This particular datasource is for the my birds database.

7) I pressed the "Verify All Connections" button (in CF Admin) to verify the datasources, and then ran some queries in CF to confirm.

Thank you, Charlie, Wolf, and Dave.
You each have answered dozens of questions for me over the years.

On this yes I remember now that MSSQL with CF, I need to get the drivers, etc. You are all correct!

Last question on this: Is there a best link for reference on how to set up a CF DSN with MSSQL?

Well, spending a week at this sucks. I'm sorry. I notice you're using SQL Server 2019. I honestly don't know if that's supported in CF 2018. In my experience with other SQL Server versions, you can usually use older versions of CF to talk to newer versions of SQL Server, but not always. You may need to download and use Microsoft's own JDBC driver for that.

Charlie has already mentioned that CF no longer ships with a MySQL driver. So you'll definitely have to download and use that. In both of these cases, you'll have to use "other", then plug in the right JDBC connection string, which will be different for each. There should be guidance for each online, and there's nothing CF-specific about the connection strings.

As for Access, my advice is really just to stop using Access. If you have data in Access that you still want to use, move it into MySQL if at all possible. It's possible to get Access working with CF, but it's a pain and it's not a good thing to do in general.

Finally, you might have basic network connectivity problems between your CF server and the database servers. This is a surprisingly common issue, but you really can't test it reliably from CF. But you can install a regular database client on your CF server, and use that to try to connect. You can download and install SQL Server Management Studio for free. The same is true for several MySQL clients.

Dave Watts, Eidolon LLC

I'm curious, but you don't mention what _versions_ of MySQL and MS-SQL you tried using.  But, as Charlie pointed out, installing the DB servers and keeping the defaults is, IMHO, the most likely culprit.

HTH,

^ _ ^

Without sharing the errors and details of what you tried, we're left just to guess.

With mysql, did you already know of the need to add a mysql driver to cf? It doesn't come with one, due to licensing issues. Did you add it, and restart cf? Was the mysql on the same server as cf? It really should be as trivial as it ever was to get cf working with mysql, once the driver is in place.

As for Access, cf no longer supports it, but people try and try to get it to work. Hopefully you're not really needing it.

But if you're willing to use it and "just need a db", had you tried derby, which is embedded in cf? Just an idea.

Finally, as for sql server, it and mysql are easily the most use dbs with cf. It certainly "works". As for what may have failed, a really common problem is if it (sql server) is left with its various defaults--so anything LIKE cf would fail to connect to it.

For instance, it defaults to ONLY supporting windows Auth. You have to TELL it to allow use of sql logins, then you can use one.

And cf CAN support use of windows Auth, in the form of  "trusted authentication". You don't specify ANY username or pw in the dsn, and assuming the user running cf is defined as a user of the DB, it just works.

But either way, cf won't connect to sql server out of the box because it uses tcp and sql server does not. That needs to be enabled. I have a blog on that, from years ago (as it's not a new issue) which I have updated (as the way to configure sql server itself has changed over the years).

But let's hear more, and either I or others can elaborate.