jameskane
asked on
Converting a Coldfusion/ms-access application to a Coldfusion/mysql application.
I have been using Coldfusion for several years - with ms access database. I am now converting one of my applications to run with an SQL version of the database. Its my understanding that it is possible to achive this by 'simply' pointing the current database source within my coldfusion environment to the SQL database. I am having difficulty in pointing the data source to the mysql database. Below are notes on what I have done so far. Would appreciate any advice / pointers to info which will help me.
So far :
1. converted the ms access database to mysql - placing it on my local XAMPP2 server. From there I exported it to the application folder within the coldfusion wwroot environment - giving it the name "thirdoffice.sql"
2. created a new data source "thirdoffice" within the coldfusion administrator - pointing to the thirdoffice.sql database (see the datasource1.JPG image below)
The datasource name "thirdoffice" is the same name as that used in the original coldfusion/access application.
However, I have not been able to successfully register the new datasource - getting an error message as shown in the attached "datasource2.jpg" below.
I have
datasource1.jpg
datasource2.jpg
So far :
1. converted the ms access database to mysql - placing it on my local XAMPP2 server. From there I exported it to the application folder within the coldfusion wwroot environment - giving it the name "thirdoffice.sql"
2. created a new data source "thirdoffice" within the coldfusion administrator - pointing to the thirdoffice.sql database (see the datasource1.JPG image below)
The datasource name "thirdoffice" is the same name as that used in the original coldfusion/access application.
However, I have not been able to successfully register the new datasource - getting an error message as shown in the attached "datasource2.jpg" below.
I have
datasource1.jpg
datasource2.jpg
> "thirdoffice.sql"
Hm... on second thought, what conversion tool did you use and how is the .sql file supposed to be used? I suspect you may need to create a MySQL db first, then run the script in that db, but that's just a guess.
Hm... on second thought, what conversion tool did you use and how is the .sql file supposed to be used? I suspect you may need to create a MySQL db first, then run the script in that db, but that's just a guess.
ASKER
Thanks for taking the time _agxs_.
Well, I think I'm ok with the mysql db. Here's what I've got :
I have been using XAMPP2 to manage my mysql databases - used for developing Python version of this same application which I am working with now. Its taking longer to produce the python version and I need to quickly move away from ms access as Coldfusion just does not want apparently to support it any more. In the python case as now, I used Intelligent convertor to convert the access database and pointed it to XAMPP2. You will see the database folder in the attached image. As this worked perfectly for the Python program, I am assuming that it works here - anyhow, all the data was correctly exported in this case.
So clearly I did install mysql db when setting up the xampp2 server. I also used the EXPORT functionality (again, see attached image) to export thirdoffice to a folder. It turned up there as a .SQL file - which I have been trying to use with coldfusion - stupidly thinking that it would work in the same way as it did for access.
Well, I think I'm ok with the mysql db. Here's what I've got :
I have been using XAMPP2 to manage my mysql databases - used for developing Python version of this same application which I am working with now. Its taking longer to produce the python version and I need to quickly move away from ms access as Coldfusion just does not want apparently to support it any more. In the python case as now, I used Intelligent convertor to convert the access database and pointed it to XAMPP2. You will see the database folder in the attached image. As this worked perfectly for the Python program, I am assuming that it works here - anyhow, all the data was correctly exported in this case.
So clearly I did install mysql db when setting up the xampp2 server. I also used the EXPORT functionality (again, see attached image) to export thirdoffice to a folder. It turned up there as a .SQL file - which I have been trying to use with coldfusion - stupidly thinking that it would work in the same way as it did for access.
Doesn't look like any images were attached to the last post :)
ASKER
So the db does exist on the same server as CF? If yes, try using "thirdoffice" for the database name and "localhost" or "127.0.01" for the server ip
ASKER
Well the database does exist on the xxampp server which supports sql databases. However, I would think that it can only by used by the Coldfusion server if it can be registered as a db source. Unfortunately this is my problem - I have not been able to create that db source for coldfusion. Given that, it does not exist for coldfusion ?
ASKER
just tried to register the database with coldfusion. See attached images
coldfusionregistration.jpg
coldfusionerrormessage.jpg
coldfusionregistration.jpg
coldfusionerrormessage.jpg
IIRC phpMyAdmin is just a tool used to simplify db administration. MySQL is totally separate. So I don't think it should make any difference.
"Admin" isn't usually a default username for MySQL. Can you check the CF log files to get the full trace and error message? It usually contains something more than just "cannot connect..."
"Admin" isn't usually a default username for MySQL. Can you check the CF log files to get the full trace and error message? It usually contains something more than just "cannot connect..."
ASKER
Another try at registering database with coldfusion
coldfusionregistration2.jpg
coldfusionerrormessage2.jpg
coldfusionregistration2.jpg
coldfusionerrormessage2.jpg
ASKER
"IIRC phpMyAdmin is just a tool used to simplify db administration. MySQL is totally separate. So I don't think it should make any difference. "
Yes - its the linked to apache server that is part of the xampp2 package.
Yes - its the linked to apache server that is part of the xampp2 package.
It definitely won't work with any sort physical path. The database name must be a simple string like "MyDatabase" or "CustomerDatabase". In the PhpMySQLAdmin screen shot (left side) all of those look like names of databases you've set up, ie "exampledb", "myfirstdb", etc...
https://filedb.experts-exchange.com/incoming/2016/11_w49/1130413/xampp2.jpg
Not sure if you saw this comment above:
https://filedb.experts-exchange.com/incoming/2016/11_w49/1130413/xampp2.jpg
Not sure if you saw this comment above:
"Admin" isn't usually a default username for MySQL. It is usually something like "root" (though that shouldn't be used in real apps). Can you check the CF log files to get the full trace and error message? It usually contains something more than just "cannot connect..."
ASKER
Attached is a list of log files from coldfusion. There is only one log for the 29th November - that's a mail log ??
Dont see any other log information
logfiles.jpg
Dont see any other log information
logfiles.jpg
Which version of CF? Later versions also log the full DSN errors to a file. They may have added that in a later version than you're using. Check the physical directories too. In older versions there's usually two: {cf_root}/logs and {cf_root}/runtime/logs (I believe).
I have been using XAMPP2 to manage my mysql databases
Silly question, but did you say you can connect to this new db with XAMPP2? If not, verify you can do that first. Otherwise, what username and password is it using?
ASKER
_agx_ Looking at this again, I figured it was a major mistake to use the mysql server underpinning xampp2 in linking up with coldfusion.
I have therefore :
- Moved over to another machine (W10)
- Installed the latest version of coldfusion developer version - vrs 2016
- Installed mysql server 5.7.16
- ported my access database via intelligent converter directly to mysql server (cutting out the xampp2 aspect of things)
I have ALMOST (I thnk ) been successful ( see attached images) in creating a data source for coldfusion
On the first screen there is a warning (see screen1 image attached) :
The standalone MySQL JDBC driver is no longer shipped with ColdFusion.
Please download it, put it in cf_root/lib folder and restart ColdFusion.
I ignored this and was not successfull. See screen2 image attached - which again referred (if I read it correctly) to the warning on screen1.
I have downloaded it (screen3 image) and find that it is a zip file. I was expecting a single file - but I get a folder full of stuff. Do I insert all the contents of this zip into the cf_root/lib folder ? OR am I misreading the whole thing like I did with the xampp2 approach !
thanks for sticking with me on this.
I have therefore :
- Moved over to another machine (W10)
- Installed the latest version of coldfusion developer version - vrs 2016
- Installed mysql server 5.7.16
- ported my access database via intelligent converter directly to mysql server (cutting out the xampp2 aspect of things)
I have ALMOST (I thnk ) been successful ( see attached images) in creating a data source for coldfusion
On the first screen there is a warning (see screen1 image attached) :
The standalone MySQL JDBC driver is no longer shipped with ColdFusion.
Please download it, put it in cf_root/lib folder and restart ColdFusion.
I ignored this and was not successfull. See screen2 image attached - which again referred (if I read it correctly) to the warning on screen1.
I have downloaded it (screen3 image) and find that it is a zip file. I was expecting a single file - but I get a folder full of stuff. Do I insert all the contents of this zip into the cf_root/lib folder ? OR am I misreading the whole thing like I did with the xampp2 approach !
thanks for sticking with me on this.
ASKER
I have downloaded it (screen3 image) and find that it is a zip file. I was expecting a single file - but I get a folder full of stuff. Do I insert all the contents of this zip into the cf_root/lib folder ? OR am I misreading the whole thing like I did with the xampp2 approach !
No, you definitely need to add the Connector/J driver jar to the CF class path - BUT don't add everything in the .zip file, only the driver jar. It should be named something like this, where 5.1.39 is whatever version you downloaded:
mysql-connector-java-5.1.3
After copying that JAR into {cfroot_lib} you must restart the CF server. Otherwise, it won't be detected. After restarting the "no suitable driver" error should go away.
ASKER
Ah, I screwed up - I unfortunately did what adobe told me before reading your comment !!! Result was that the problem did not get fixed. I am currently installing a new Coldfusion. Weirdly enough, I did take a copy of the lib before I added the extra items - BUT I was unable to delete the modified lib folder to replace it with the backup. The security of windows 10 family edition just did not allow it !! VERY frustrating !! But, anyhow, watch this space !!
The security of windows 10 family edition just did not allow it !!
Oh joy ... ;-)
BTW, you can also put custom jars in {cf_web_root}\WEB-INF\lib instead of the core {cf_root}\lib directory. Both work, but using {cf_web_root}\WEB-INF\lib makes it easier to avoid overwriting / accidental conflicts with core jar files.
ASKER
Ahhhhh !!!! I'm afraid it does not work.
I got the jar file ... first attachement
I then tried it in each of the lib folders (recommended by adobe and the althernative you mentioned) - second and third attachments
Unfortunately nothing changed. I was careful to use a fresh launch of coldfusion when trying out each scenrio
The warning appeared each time and the datas creation failed - with the same explanation as before
COLDFUSION_rework_file.JPG
COLDFUSION_rework_1.JPG
COLDFUSION_rework_2.JPG
I got the jar file ... first attachement
I then tried it in each of the lib folders (recommended by adobe and the althernative you mentioned) - second and third attachments
Unfortunately nothing changed. I was careful to use a fresh launch of coldfusion when trying out each scenrio
The warning appeared each time and the datas creation failed - with the same explanation as before
COLDFUSION_rework_file.JPG
COLDFUSION_rework_1.JPG
COLDFUSION_rework_2.JPG
Did you restart the CF server after copying the jar? Restarting is mandatory. If you don't, CF never detects the jar so the error won't change.
It definitely works with CF11. Though 2016 shouldn't be different, I'll double check.
It definitely works with CF11. Though 2016 shouldn't be different, I'll double check.
Tried it with a clean 2016 Express install and it worked perfectly. Maybe you forgot to restart?
1. Attempt to Create DSN on brand new CF2016 Express install
2. Received error message about missing driver
3. Copied driver jar to WEB-INF\lib
4. Restarted CF Server then recreated DSN:
5. DSN successfully verified
1. Attempt to Create DSN on brand new CF2016 Express install
2. Received error message about missing driver
3. Copied driver jar to WEB-INF\lib
4. Restarted CF Server then recreated DSN:
5. DSN successfully verified
ASKER
No, not there yet I'm afraid. I did not however get the pre-warning at the start about the need to update. So, I guess that shows that Coldfusion found the jar file.
I attach the error message - which refers to the jar file
That would then suggest that the jar file did not work properly.
I did note that after trying this I was asked to updata coldfusion for w10 updates.
I hope its not just a WIO problem !! I am using 1607 version.
COLDFUSION_rework_4.JPG
I attach the error message - which refers to the jar file
That would then suggest that the jar file did not work properly.
I did note that after trying this I was asked to updata coldfusion for w10 updates.
I hope its not just a WIO problem !! I am using 1607 version.
COLDFUSION_rework_4.JPG
ASKER
just rebooted the system and tried again after the updates.
Same result as above
Same result as above
I attach the error message - which refers to the jar file
That would then suggest that the jar file did not work properly.
Not necessarily. It sounds like it found the jar, but can't connect for some reason. Some common causes are a) the MySQL Server isn't running or b) you're being blocked by a firewall.
As a test, I turned off the MySQL Service on my Win7 machine. Then when I tried to verify the DSN I got the same error:
Connection verification failed for data source: MyDSN
com.mysql.jdbc.exceptions.jdbc4.MySQ LNonTransi entConnect ionExcepti on: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
The root cause was that: com.mysql.jdbc.exceptions.jdbc4.MySQ LNonTransi entConnect ionExcepti on: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
Best thing to do is verify you can connect to the database with some OTHER tool. Do you have MySQL Workbench installed? If so, use that. Otherwise, a simple "telnet" would work. Open up a command prompt and try and telnet to the IP and port number of your db. Assuming it's on the same machine, use:
telnet 127.0.0.1 3306
If you get a connection error like, then either the svc isn't running or it's a firewall issue.
Connecting To 127.0.0.1...Could not open connection to the host, on port 3306: Connect failed
ASKER
Ha, that's promising !!
Have mysql workbench installed and am attaching an image which shows it is running and also, bottom left, you see that OFFICE has been detected
mysqlworkbench.JPG
Have mysql workbench installed and am attaching an image which shows it is running and also, bottom left, you see that OFFICE has been detected
mysqlworkbench.JPG
Can you telnet into it? (By the way, it is the db on the same server, ie localhost)?
ASKER
"Open up a command prompt and try and telnet to the IP and port number of your db. Assuming it's on the same machine, use:"
Not sure how to do that... did a normal windows command line, but got nothing from that ??
Not sure how to do that... did a normal windows command line, but got nothing from that ??
Yes, a regular command prompt. Not sure what you mean "got nothing from that"?
Telnet is disabled by default in Win10 (of course...). If you haven't enabled it yet, see this section:
Section: Enabling the telnet client through the graphical user interface
https://www.rootusers.com/how-to-enable-the-telnet-client-in-windows-10/
Section: Enabling the telnet client through the graphical user interface
https://www.rootusers.com/how-to-enable-the-telnet-client-in-windows-10/
ASKER
I have the telnet prompt, but when I type telnet 127.0.0.1 3306 it tells me this is a non valid command ?
ASKER
attached is copy of telnet screen
telnet.JPG
telnet.JPG
Almost. Looks like you ran telnet.exe (without the server name and port #). Either
A) Open a command prompt, ie CMD.exe and enter:
telnet localhost 3306
.... OR
B) Open telnet.exe, then enter:
open localhost 3306
A) Open a command prompt, ie CMD.exe and enter:
telnet localhost 3306
.... OR
B) Open telnet.exe, then enter:
open localhost 3306
ASKER
Ahhh. I was sure I sent this to you yesterday - must have forgotton to click the submit button...
Still no luck with telnet. However, you will see from attached that I was able to connect to the database from mysql workbench. You will see a portion of the listing for table members2 .
Jim
mysql-workbench-data.JPG
Still no luck with telnet. However, you will see from attached that I was able to connect to the database from mysql workbench. You will see a portion of the listing for table members2 .
Jim
mysql-workbench-data.JPG
ASKER
Just established a connection from mysql workbench to coldfusion. See attachment
COLDFUSION-CONNECT-FROM-WORKBENCH.JPG
COLDFUSION-CONNECT-FROM-WORKBENCH.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looks like the problem is solved !!!
BELOW :
DATABASE CONNECT FROM WORKSTATION - shows the confirmation that I was able to connect to coldfusion. Note that the name of the user to connect with is root. Therein lies the problem. When I used coldfusion to create a datasource I had used admin as the user name instead of root. admin is the user name I use to access the coldfusion administrator - got them mixed up !!
SUCCESSFUL COLDFUSION DATA SOURCE CREATIONACHIEVED - confirmation that I was successful in creatinng data source in coldfusion
PARAMETER INPUTS FOR CREATION OF DATASOURCE - shows the coldfusion parameter setting to create the above datasource
NOTE -- I have used root for the username -- vrs admin which I had been using. admin is the username for accessing the coldfusion administrator !! As simple as that !!
I have just tried the office_17 application with its related office_17 data source and it works !!!!!!!!!!!!!!!!!!!!!
James
COLDFUSION-CONNECT-FROM-WORKBENCH.JPG
DATA-SOURCE-SUCCESS_1.JPG
DATA-SOURCE-SUCCESS_2.JPG
BELOW :
DATABASE CONNECT FROM WORKSTATION - shows the confirmation that I was able to connect to coldfusion. Note that the name of the user to connect with is root. Therein lies the problem. When I used coldfusion to create a datasource I had used admin as the user name instead of root. admin is the user name I use to access the coldfusion administrator - got them mixed up !!
SUCCESSFUL COLDFUSION DATA SOURCE CREATIONACHIEVED - confirmation that I was successful in creatinng data source in coldfusion
PARAMETER INPUTS FOR CREATION OF DATASOURCE - shows the coldfusion parameter setting to create the above datasource
NOTE -- I have used root for the username -- vrs admin which I had been using. admin is the username for accessing the coldfusion administrator !! As simple as that !!
I have just tried the office_17 application with its related office_17 data source and it works !!!!!!!!!!!!!!!!!!!!!
James
COLDFUSION-CONNECT-FROM-WORKBENCH.JPG
DATA-SOURCE-SUCCESS_1.JPG
DATA-SOURCE-SUCCESS_2.JPG
>> When I used coldfusion to create a datasource I had used admin as the user name
Yeah, makes sense it didn't work. I'd mentioned that "admin" isn't usually a default username for MySQL earlier. Not sure if you saw it. Glad it's working now :)
Yeah, makes sense it didn't work. I'd mentioned that "admin" isn't usually a default username for MySQL earlier. Not sure if you saw it. Glad it's working now :)
ASKER
Many thanks for all you help and, in particulalr, your PATIENCE !! Not only did you help me solve a problem but you have increased my understanding of coldfusion and mysql. That took time and a committment on your part which I truly appreciate
THJANKS !!
james
THJANKS !!
james
You're very welcome :)
Forgot to mention, testing with the "root" account is fine, but best not to use that account for DSN's. Reason being "root" has full db privileges, usually way more than is needed for a DSN. Better to set up a separate db user account instead. Then GRANT that account only the privileges needed, and use it for the DSN instead of "root".
* CREATE USER SYNTAX
* GRANT SYNTAX
* CREATE USER SYNTAX
* GRANT SYNTAX
I don't know what tool you used to convert the db, but it should've asked you to assign a "name" for the new MySQL database. Say you named it "MyNewDatabase", that's the value to enter in the "Database" field. Assuming the database is on the same server as CF, use "localhost" or "127.0.0.1" for the server IP address.
Datasource name: (Anything name you want)
Database: MyNewDatabase
Server: localhost
.... etc...
Edit: Side note, for security don't place db's in a web accessible directory.