I have ODBC access to remote MySQL DB & it's registered in CF Admin - my local app can't see it

[John_Allred]

I may not have everything installed that I need. I recently lost my entire c: hard drive to a crash. I replaced it and reinstalled everything. But I'm having an issue with accessing a MySQL datasource. It was working fine prior to the crash.

1) I created a datasource in Win10 64-bit ODBC using the MySQL ODBC 8.0 Unicode Driver. It reads the remote MySQL database just fine. I've tried this, also, using the ANSI driver. FWIW, I downloaded and installed the drivers using mysql-connector-odbc-8.0.28-winx64.msi. I don't have the full Oracle application installed.

2) I created a datasource under CF admin (CF2018) using the ODBC Socket. It reads the previous datasource just fine also.

I've set up my localhost site, and it runs without error. But when I try to run code that calls my DSN, I get an error. Datasource XXX could not be found.  

I'm missing something that I did successfully, previously, but I just can't figure it out. Any help is really appreciated. Thanks!

[Charlie Arehart]

Before we may dig into all that, first help us understand why you're using odbc rather than jdbc? I realize it may be solely because it's what you know,  but there is a free Mysql jdbc driver, and Google can show you how to implement it easily in cf.

But as for odbc, to get it to work before, you may have taken an extra step to define a dsn in the windows odbc control panel, specifically the 64-bit one. Again, Google can help find more on that, for people who fought to get MS Access also to work via odbc.

But really, just move to jdbc, which cf has offered since cf6 20 years ago. It's understandable to "go with what you know", but sometimes it's better to light one candle than to curse the darkness. 🙂 

[John_Allred]

Hey, Charlie. 

I continued using ODBC for years, because I remained with Access for years. When I moved to MySQL a few years ago, I stuck with the familiar. I'll try the JDBC route. Thanks!

Can I assume that with JDBC, I can directly access my remote DB without having an in-between connection on my PC, like I do with ODBC?

[BKBK]

I continued using ODBC for years, because I remained with Access for years. When I moved to MySQL a few years ago, I stuck with the familiar. I'll try the JDBC route. Thanks!

By @John_Allred

Understandable. If it ain't broke,...

Can I assume that with JDBC, I can directly access my remote DB without having an in-between connection on my PC, like I do with ODBC?

Yes.

[John_Allred]

New, unfamiliar territory here. I've downloaded the Connector/J 8.0.28 jar file. I copied the mysql-connector-java-8.0.22.jar file to [cfserver]\cfusion\lib. I restarted the server, but I don't see an obvious driver in the list in my cf administrator.

[BKBK]

New, unfamiliar territory here. I've downloaded the Connector/J 8.0.28 jar file. I copied the mysql-connector-java-8.0.22.jar file to [cfserver]\cfusion\lib. I restarted the server, but I don't see an obvious driver in the list in my cf administrator.

By @John_Allred

Strange. I expected one of the options to be just MySQL. MySQL 5 sounds like an old version. On which ColdFusion version are you?

In any case, I would use the MySQL 5 option in the circumstances.. 

[Charlie Arehart · Accepted solution]

John, to clarify, you would have wanted to pick the "other" driver type in that Cf admin dsn page. Again, if you'd Googled even just:

Coldfusion mysql

... the first result is an old but still mostly applicable Adobe technote on the topic:

https://helpx.adobe.com/coldfusion/kb/coldfusion-mx-configuring-mysql-jdbc.html

And it shows the driver type, the needed jdbc url, how to get the driver jar, where to put it (that's old: it should be cfusion/lib, not the wwwroot/WEB-INF/lib it indicates), and more. 

(While under some situations you can get the "Mysql" cf admin driver "type" to work also or instead, you should always be able to use the "other" driver. )

And to be clear, the dsn you setup on your local machine has zero interaction with any that you may setup on your remote cf admin. Your local cf admin will be configured to point to your remote Mysql server, not to cf. And whether that connection will work depends on your Mysql server being configured to allow access to it from your local machine. It sounds like you had that working in the past. 

Hope this all helps get you going.

[John_Allred]

This was the solution, Charlie. Getting mysql-connector-java-8.0.28.jar into the \lib folder did the trick. I guess I've joined the 21st century now, ha! Thanks.

[BKBK]

Getting mysql-connector-java-8.0.28.jar into the \lib folder did the trick. 

By @John_Allred

Oh. From what I read, you had already done that, without success:

 I've downloaded the Connector/J 8.0.28 jar file. I copied the mysql-connector-java-8.0.22.jar file to [cfserver]\cfusion\lib

By @John_Allred

In any case, I am glad to hear you're now set up and ready to rock and roll.

[Charlie Arehart]

Yep, I suspect instead it was the switch to an "other" DSN that really was the solution, right John? Just to close this loop? Anyway, glad it's solved and hope it may help others in the future.

[John_Allred]

Actually, Charlie, it worked with the MySQL 5 driver, rather than "other."

[Charlie Arehart]

Fair enough. I had said also, "While under some situations you can get the "Mysql" cf admin driver "type" to work also or instead, you should always be able to use the "other" driver. " 

[BKBK]

Open the datasources page of the ColdFusion Administrator. Press the button to verify all connections. 

Does every connection verify with an OK? Does the name of the datasource that you use in your code match exactly (including case) the name of one of the datasources on this page ?

[John_Allred]

Thanks for the follow up, BK. 

The call to the datasource from my code, as shown in the error, is all lowercase, so I created the DSN in the CF administrator as lowercase, also. However, the DSN on my remote server is camel case. It is all hooked up to my remote db, and tests OK. But I'm still getting an error when I open a page that calls the DSN.

"Datasource xxx could not be found."

I used the MySQL 5 driver, as I did not see anything indicating JDBC. 

[John_Allred]

I rechecked the actual code in my cfm. The DSN called is using camel case to match the name on the remote server, so I'm thinking case is not the issue.

[BKBK]

Oh, I think I missed something. Are we dealing here with 2 servers, one local and one remote? I thought we were dealing with just one, the one on which the CF Administrator is located.

In any case, let's experiment further with the MySQL 5 driver. 🙂

[BKBK]

[BKBK]

[BKBK]

[BKBK]

Now dump the details of the datasources. We can then see which ones are using JDBC.

In particular, we can see which type of driver the newly created MySQL datasource (testdb) uses

<!--- Login into Coldfusion Administrator. --->
 <cfset  createObject("component","cfide.adminapi.administrator").login("your_cf_admin_password")>

<!--- Instantiate the data source object. --->
 <cfset  datasourceObject = createObject("component","cfide.adminapi.datasource")>

 <!--- Get a structure containing all the data sources --->
 <cfset datasources = datasourceObject.getDatasources()>
 <cfdump var="#datasources#" label="All available data sources">

[John_Allred]

BK,

I followed your directions. I created the datasource:

Then I ran the code you supplied to check it. The new datasource, AptMaps, does not show up.

My datasource, created under CF admin, is pointing directly to the datasource on my remote server and checks OK there.

On a hunch, I stopped and started my CF server under Windows services, but that didn't cause the datasource to appear in the list above.

[BKBK]

I can synchronize with you on every step you've followed, but one. I was thinking of just 1 server, whereas you have been talking about 2 servers. In fact, I mentioned earlier:

Oh, I think I missed something. Are we dealing here with 2 servers, one local and one remote? I thought we were dealing with just one, the one on which the CF Administrator is located.

BKBK

So: 

• Could you explain what's with the server/remove server? Why two servers? How are they connected. What roles do they play - in order words, how do they work together - as far as ColdFusion is concerned? 
• Could it be that you are configuring the MySQL DSN on one server (server 1), whereas ColdFusion is installed on a different server (server 2)? 

[Charlie Arehart]

Guys, please see also what I said above in another reply thread yesterday. I ask similar clarifying questions about the servers, and also offer a different possible solution (though the server clarification should come first). 

[John_Allred]

BK,

I've been doing my datasources similarly for years. When I was using Access, I swear to god I used to create a DB on the local machine and, of course, create a DSN for it. Then I would copy that DB to my remote server and create a DSN there. I would update the DB locally and then upload it to the remote server to overwrite it.

Then my ISP said they just couldn't support Access anymore, so I moved to MySQL. I continued using the ODBC client on my PC. I was able to point it to the remote DSN, but I also had to create a DSN under Coldfusion that pointed to the local Windows DSN. That process no longer worked for me after my recent system failure/rebuild. And that's why I posed the question at the top of this.

I got a neat routine from you for checking on my datasources, but Charlie got me over to JDBC, and everything is humming along now.

I want to thank both of you. You have always been incredibly generous with your time and expertise.

[BKBK]

Cheers, @John_Allred .