Skip to main content
John_Allred
John_Allred
Inspiring
January 29, 2022
Answered

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

  • January 29, 2022
  • 2 replies
  • 1045 views

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!

This topic has been closed for replies.
Correct answer Charlie Arehart

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.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
January 29, 2022

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
John_AllredAuthor
Inspiring
January 29, 2022

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. 

 

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
January 29, 2022

 


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

 

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
January 29, 2022

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)
    John_Allred
    John_AllredAuthor
    Inspiring
    January 29, 2022

    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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 29, 2022

    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.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices