Not much hair left to pull out here...
I have two CF sites, each with a MySQL database, on an ISP's server. I'm managing them on a local instance of Coldfusion 18 (on 64-bit Win10) via localhost.
Sometime back, I got the first of these set up to access its datasource on the ISP with no problem. Using the same settings (I think), I can't get my second site to recognize its datasource. I placed the files on a subdomain and the datasource for my queries works fine there. It's just the access from localhost.
I set up both datasources through Coldfusion Admin the same way, using ODBC Socket. Both datasources test OK there. Neither site's application.cfc affects the datasources.
Is there some setting for the local sites that could produce a problem? Because I can't find anything amiss anywhere else.
I'm not sure what the problem is, but I'd recommend using a JDBC driver instead of ODBC Socket. I'll bet that will also take care of this problem too, and things will generally work better.
Dave Watts, Eidolon LLC
Any idea where I would create a JDBC to make it available to me in CF Admin? Plus, remember, the original datasource is on a remote server.
Here are my choices through ODBC Administrator on my local PC
Since it's MySQL, you'd choose one of the MySQL drivers. I'm pretty sure they're both JDBC. One is commercial (DataDirect) and is probably better than the vendor-provided one (MySQL 5). When you set this up, you can point it to a remote server as part of the process.
Dave Watts, Eidolon LLC
Nil desperandum! You're almost there. 🙂
Looks like you have already installed the MySQL ODBC drivers. Good.
The installation steps you would already have followed are:
> Control Panel\All Control Panel Items\Administrative Tools
> Data Sources (ODBC)
> Click on tab 'System DSN'. Click on 'Add'.
You would then have arrived at the 'Create New data Source' interface shown in your picture.
> Select the ANSI version of the driver (as you had done in the picture)
> Press 'Finish'. You will get the following interface:
> Replace my settings with yours. Then test the connection by pressing on the 'Test' button. If everything went well, you should see:
> Press on the OK buttons to close these 2 interfaces.
You should then see that the datasource you've just configured - in my case, BKBK_MySQL_ODBC_Data_Source - is now listed in the 'System DSN' tab.
Press OK to close the Windows interfaces.
> Open the ColdFusion Administrator and navigate to the Data Sources page. Use the 'Add' button to create a new ODBC data source as follows:
> When you press 'Add', you get an interface similar to the following:
> Fill in the details and press 'Submit'
Thanks so much for your in-depth reply.
Here's the crux of my problem. I went through each of the steps you described. A year or two back, I created the DSN, AptMaps, and it is available to my localhost site(s). More recently, I created the DSN, OleSouth, using all the same steps—the ones you laid out above. As you can see, its status shows as "OK" in Coldfusion server, just as the other DSN does. However, this DSN is NOT available to my localhost site(s). Both databases and datasources reside on my ISP's servers. Both are defined in my local windows ODBC app and in my local Coldfusion server. One works. The other doesn't.
I have no doubt there is a simple setting that I've overlooked, but I just can't find it.
I followed the same steps, creating a MySQL ODBC driver on Windows and using it to register an ODBC datasource in the ColdFusion Administrator. I also got hit in the face by an error. 😞
I have logged a bug report. If you, too, think it's a bug then please vote to have it quickly fixed.
I'm one of those who knows as much as he needs to know to get what he needs to get done, done.
Following your post on the bug tracker, I don't know where in CF Administrator to enter the code. Never done that before.
<!--- Log in to Coldfusion Administrator. ---> <cfset createObject("component","cfide.adminapi.administrator").login("my_cfAdmin_password")> <!--- Instantiate the data source object. ---> <cfset datasourceObject = createObject("component","cfide.adminapi.datasource")> <!--- Get a structure containing all the ODBC data sources ---> <cfset OdbcDatasources = datasourceObject.getODBCDatasources()> <cfdump var="#OdbcDatasources#" label="All available ODBC data sources"> When I run this, the list includes the MySQL ODBC datasource mentioned in the above bug report.
Could you direct me? Thanks!
Can you please try with MySQL JDBC jar which you can download from the MySQL site and use the JDBC connection string to connect it.
Priyank graciously spent a little time with me yesterday and again this morning to sus out the problem. He correctly diagnosed it as creating my datasource under the wrong Coldfusion server instance. I had created it under the top level 8500 instance, when I should have done so under the instance my application was running under.
THANKS, SO MUCH, PRIYANK!
More specific to the previous comments, I'll suggest you use an "other" driver type in the CF Admin DSN page, not mysql. CF no longer provides a MySQL driver. You have to get it yourself (it's free).
Sadly, none of the top 10 results of googling: coldfusion mysql tell you this. (Adobe, please create a modern resource and strive to get it to show up first!) About the 14th one currently does explain the process, and though it's from CF11 timeframe, the steps (if not the specific mysql version info) are right:
Let us know if you get going.
There's another suggestion that might or might not be relevant. But I shall just put it out there:
- Using a text editor, open - as Administrator - the file C:\Windows\System32\drivers\etc\hosts
- Edit it as suggested below, and save.
I am glad to hear you've solved the problem. You say,
"[Priyank] correctly diagnosed it as creating my datasource under the wrong Coldfusion server instance. I had created it under the top level 8500 instance, when I should have done so under the instance my application was running under."
But there were 2 possible driver options: MySQL ODBC and MySQL Connector/J Jar. Which one did he help you with?
For testing I removed the ODBC connection and created a JDBC connection using the MySQL jdbc driver and having the same issue "datasource could not be found" then I noticed that it is pointing to different instance. I created the DB using JDBC on that particulat instance and it worked.
Before this call, John was able to verify the ODBC connection for the same DB however, it was not working because he added in the wrong instance.
If he adds the ODBC connection now and use in his application that will also work.