• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

Contributor ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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!

TOPICS
Database access

Views

368

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
community guidelines

correct answers 1 Correct answer

Community Expert , Jan 29, 2022 Jan 29, 2022

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

...

Votes

Translate

Translate
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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. 🙂 


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
Contributor ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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?

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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.

Votes

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
community guidelines
Contributor ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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.

jdbc.png

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

 

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.. 

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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.


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
Contributor ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

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.

Votes

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
community guidelines
Community Expert ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

 

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.

Votes

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
community guidelines
Community Expert ,
Jan 31, 2022 Jan 31, 2022

Copy link to clipboard

Copied

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.


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
Contributor ,
Feb 02, 2022 Feb 02, 2022

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Community Expert ,
Feb 03, 2022 Feb 03, 2022

Copy link to clipboard

Copied

LATEST

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. " 


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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 ?

Votes

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
community guidelines
Contributor ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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. 

 

Votes

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
community guidelines
Contributor ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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.

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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. 🙂

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

BKBK_0-1643498554125.png

 

 

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

BKBK_0-1643499114847.png

 

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

BKBK_0-1643499779250.png

 

Votes

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
community guidelines
Community Expert ,
Jan 29, 2022 Jan 29, 2022

Copy link to clipboard

Copied

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">

 

Votes

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
community guidelines
Contributor ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

BK,

I followed your directions. I created the datasource:

John_Allred_0-1643546840539.png

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

John_Allred_1-1643546905423.png

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.

Votes

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
community guidelines
Community Expert ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

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)? 

Votes

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
community guidelines
Community Expert ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

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). 


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
Contributor ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

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.

Votes

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
community guidelines
Community Expert ,
Jan 30, 2022 Jan 30, 2022

Copy link to clipboard

Copied

Cheers, @John_Allred .

Votes

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
community guidelines
Resources
Documentation