Using Coldfusion MX w/Mysql at jodohost

[jag5311]

Hey guys,

Well, I am deciding to use mysql instead of access 2002 as my database because of if the site every becomes popular, I want it to be stable as can be. With that said, I have done the following.

Created my mysql database. I am using mysql-front btw. I granted all priveleges to "myusername" at host mysql.jodohost.com and "mypassword"

""- denotes replacement for what I really have.

Then I went through the hsphere and created a mysql database and user with

username_database name, then built a user with read/write priveleges. So far so good ????

Now, is all I have to do is upload my database to the root? Do I have to create a trouble ticket to have the database activated?

edit----

Just realized there is a method to do ODBC Drivers : MySQL ODBC 3.51 Driver. Which one do I use?

Thanks
Bryan

 

[danp5648]

DSNs are only for people who are new to CFMX. Since CFMX is now a J2EE app they stopped using them. The DSNs inside the Admin are just an easier way the edit an XML file and makes it fool proof.

Without downloading anything try to follow the example given
http://www.geocities.com/empiricallyspeaking/MySQL.html

the Object:
<cfset Object_test = CreateObject("java", "mySQL_connection_test")>

the connection:
<cfset connected_I_hope = Object_test.connect
("mysql.jodoshared.com", <!--- host --->
"3306", <!--- port --->
"helloworld_db", <!--- database --->
"your_user", <!--- user_name ---->
"your_password" <!--- password ---->)>

myData now has your data
<cfset myData = connected_I_hope.executeQuery("Select * from this_table")>

You need next() because the first row is metadata
<cfset myData.next()>

<cfset Data = myData.getString("ColumnName")>

Done. Of course you can use getInt(1) or any of the other
50 methods.

 

[jag5311]

Ok, what if I don't use the ODBC connection?

I currently have it working with my access database just fine at jodohost, but because of the instability access can have if a page is getting alot of hits, I want to take a safe route.

So far you did everything correctly. The next part is a whole new world. ODBC creation page works in Jodohost BUT and this is a BIG BUT CFMX is a 100% pure java application and it only uses JDBC. When you build a CFMX datasource in CF admin, all you are doing is filling out missing parts to complete another file which listens for CFQUERY calls. The cfquery tag is just interface (Function call) for JDBC which continues to fill out missing parts, such as the name attribute and the datasource attribute. Of course cfquery uses the type IV drivers in a jar file called ?macromedia_drivers.jar?. If you download this file and un-zip it you?ll see all drivers and the same drivers that you would see In CF admin own drop down box. In order to use CFQUERY you need to make this be know by CF admin NOT Just in ODBC datasources which is a Microsoft ONLY product. You can use ODBC BUT you need to Create a JDBC-ODBC bridge which is a type I driver. For Jodohost, you have create a all this stuff From scratch and build it just like Coldfusion would, which is not too bad. Only a few lines will do. But CFQUERY Is out. That?s right, out. If you build it from the ground up you can?t use cfquery as far as I know. If you could ... well ? that would be cool, because all this java stuff sucks. Ok I will end it here because the next part is code code code. And I think it?s all over Internet anyway. If you want me to do it, I am going to have to charge people. This stuff is not generally given out because people have to make a living doing this crap. Let me know what you think.

 

[danp5648]

<cfset Access_connection_test =
CreateObject("java", "Access_connection_test")>

<cfdump var="#Access_connection_test#">

<cfset test_results =
Access_connection_test.test_connection("c:/reports.mdb")>

<cfdump var="#test_results.isClosed()#">

 

[danp5648]

<cfset firebird = CreateObject("java", "firebird")>

<cfdump var="#firebird#">


<cfset firebird_worked
= firebird.connect(
"localhost",
"3050",
"C:\\EMPLOYEE.FDB",
"SYSDBA",
"zilo5648",
"select * from country")>

<cftry>
<cfdump var="#firebird_worked#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>

 

[hatton]

Your current ODBC connection string is working because it was made for you by Jodohost. And what they did was enter your information in CF admin and made a JDBC-ODBC connection string which CFQUERY can then be used to make function calls.
...

Maybe I will create a service for people to plug in some text and I will create the code which can be then
cut and pasted. After all this is what CF is doing.

I have also discussed the option of doing something like this with Yash. The problems are multi-fold and apparently there are some internal issues that must be overcome with some of the network rules at their data center. So far I have not been able to get a connection between my CF Server (Win6) and the MS SQL Server database. I have yet to try it with a MySQL database.

The one thing I would ask is whether you are going to use the CFRegistry way of doing things or build a DSN-Less connection string that people can then paste into their code? The latter would be the safer (and probably more server-approved) way of doing things.

At the moment the only "easy" way to get a CF DNS working is to send a request to have one of the Admins create one for you in the CFAdmin. While not a really automated way of doing it, it will get the job done.

 

[jag5311]

DanP5648, its not your writing, I am very new to coldfusion and extremely new to mysql.

I did send a ticket to Jodohost asking to create the connection. I am not sure if my question as the very beginning threw the whole topic off (you did answer it very thoroughly).

In a nutshell, all Jodohost has to do is create a ODBC connection for mysql and the database I gave a name to, right? They did that for my access database as well.

I have also discussed the option of doing something like this with Yash. The problems are multi-fold and apparently there are some internal issues that must be overcome with some of the network rules at their data center. So far I have not been able to get a connection between my CF Server (Win6) and the MS SQL Server database. I have yet to try it with a MySQL database.

The one thing I would ask is whether you are going to use the CFRegistry way of doing things or build a DSN-Less connection string that people can then paste into their code? The latter would be the safer (and probably more server-approved) way of doing things.

At the moment the only "easy" way to get a CF DNS working is to send a request to have one of the Admins create one for you in the CFAdmin. While not a really automated way of doing it, it will get the job done.

 

[danp5648]

I only had problems with MS SQL Server because I think when
I orginally created the database the password/ user name field didn't show up so I could not create a ODBC or any other kind of connection because I simply can't log in to the server in general. They said to recreate the database and try again which I have not done.
It sounds like you are coming from CF 5? No way would I ever get
people to start playing around with reg stuff. No need, not tested and reckless. I only talk about stuff which is tested and safe. I will not talk about any of the other issues here. I am from NY too LI. I tracert-ed and I think the servers are in PA! Only 10 hops from me. I would love to take a walk over.

 

[jag5311]

Sorry, I couldn't tell who that was in response to.

I am coming from Coldfusion MX btw.

I only had problems with MS SQL Server because I think when
I orginally created the database the password/ user name field didn't show up so I could not create a ODBC or any other kind of connection because I simply can't log in to the server in general. They said to recreate the database and try again which I have not done.
It sounds like you are coming from CF 5? No way would I ever get
people to start playing around with reg stuff. No need, not tested and reckless. I only talk about stuff which is tested and safe. I will not talk about any of the other issues here. I am from NY too LI. I tracert-ed and I think the servers are in PA! Only 10 hops from me. I would love to take a walk over.

 

[danp5648]

DanP5648, its not your writing, I am very new to coldfusion and extremely new to mysql.

I did send a ticket to Jodohost asking to create the connection. I am not sure if my question as the very beginning threw the whole topic off (you did answer it very thoroughly).

In a nutshell, all Jodohost has to do is create a ODBC connection for mysql and the database I gave a name to, right? They did that for my access database as well.

That will not work and I also suggest not bothering them anymore. They
might start getting mad and that is never a good thing.

As I said before if you want me to do it let me know and then we can
work on the details a little more with you but more trouble tickets will only cause more trouble.

 

[hatton]

DanP5648, its not your writing, I am very new to coldfusion and extremely new to mysql.

In a nutshell, all Jodohost has to do is create a ODBC connection for mysql and the database I gave a name to, right? They did that for my access database as well.

Exactly, they have the resources to create your connection, all you need to do is tell them your account name and the database name you created for the MySQL database. Once they create the DSN you should be able to access it using just the DSN via:
<cfquery name="qWhatever" datasource="<DatasourceName>" ...

Hope this helps!

 

[hatton]

I only had problems with MS SQL Server because I think when
I orginally created the database the password/ user name field didn't show up so I could not create a ODBC or any other kind of connection because I simply can't log in to the server in general.

I think there are more issues than just the DSN's between the CF box and the MSSQL Server. I was told that there were some routing issues that had to be dealt with and the new "cluster" was going to take care of them.

 

[danp5648]

I think hatton is coming from cf 5 because of the words he used are C/C++ based.

Hatton is also correct here .....

"Exactly, they have the resources to create your connection, all you need to do is tell them your account name and the database name you created for the MySQL database. Once they create the DSN you should be able to access it using just the DSN via:
<cfquery name="qWhatever" datasource="<DatasourceName>" ...

and I spoke about this.

These are NORMAL steps but as I said before with my first post it's
not going to happen. But maybe it will, who knows. I hope it does....

Maybe start reading here first then work your way up to my posts which a little bit over kill ish. And good luck!

 

[jag5311]

Thanks HAtton

These are NORMAL steps but as I said before with my first post it's
not going to happen. But maybe it will, who knows. I hope it does....

That they won't make the connection, or what? I am confused.

Thanks
Bryan

 

[hatton]

I think hatton is coming from cf 5 because of the words he used are C/C++ based.

These are NORMAL steps but as I said before with my first post it's
not going to happen. But maybe it will, who knows. I hope it does....

Maybe start reading here first then work your way up to my posts which a little bit over kill ish. And good luck!

Actually, I've been working with CF since 4.0 I actually *skipped* 5.0 (the company I worked for did not upgrade beyond 4.5 because they were worried about backwards-compatability) and now am playing catch-up. I am just now getting into using CFMX, still have to learn about CFC's and UDF's.

Every hosting company that I have dealt with in the past had to create my ColdFusion DSN's for me. I would love to be able to do everything myself (as I have also administered my own servers) but in some cases it's safer for someone else to push the button.

As for JoodHost's performace record and reply times on creating DSN's, they're pretty good. Such a menial task shouldn't be given an emergency priority but they got my DSN set up in less than 24 hours.

 

[danp5648]

Here is my old post again!

Doing the connection string yourself is a whole new world for most people, but its a good idea to start learning this method right away. Jodohost ODBC connection creation page works BUT CFMX is not connected to it and you should not be using ODBC connections strings with CFMX anyway. You should be using the drivers that are shipped with CFMX. A 100% pure java application demands that you use JDBC. When you build a CFMX datasource in CFMX, all you are doing is filling out missing parts to complete a JDBC connection anyway. This connection string uses type IV drivers found in a jar file called ?macromedia_drivers.jar?. If you download this file and unzip it and you?ll see all drivers that CFMX uses. Study this file.

If you need a connection to a database that is not an ACCESS file this method is the best and fastest way to do it. If you try to do it "the Hatton way" you will met with something to do with a "blocked port numbers for which a cluster will help solve some time in the future" which could be up and running anyday now. If you have CFMX loaded on your home machine you can control the database just like you would have control over an FTP or WEB server and you can use cfquery with it. Also you can use SQL select, add, delete or insert data just like you would if you were located on Jodohost. I am sure people have already made applications like WS FTP pro but only work with databases.

 

[jag5311]

Thanks Danp5648

I know most of that was your old post, but I studied it more carefully and understood it a little better. I have never been big into programming, so for me to get into it deeply would be tough I am more on the webdesign area of things, not really web development. The website I have hosted here I did for a family relative and I followed Coldfusion WACK very carefully in order to pull it off. I will take your advice if I ever delve deeper into it.

Thanks again for clarifying
Bryan

Here is my old post again!

Doing the connection string yourself is a whole new world for most people, but its a good idea to start learning this method right away. Jodohost ODBC connection creation page works BUT CFMX is not connected to it and you should not be using ODBC connections strings with CFMX anyway. You should be using the drivers that are shipped with CFMX. A 100% pure java application demands that you use JDBC. When you build a CFMX datasource in CFMX, all you are doing is filling out missing parts to complete a JDBC connection anyway. This connection string uses type IV drivers found in a jar file called ?macromedia_drivers.jar?. If you download this file and unzip it and you?ll see all drivers that CFMX uses. Study this file.

If you need a connection to a database that is not an ACCESS file this method is the best and fastest way to do it. If you try to do it "the Hatton way" you will met with something to do with a "blocked port numbers for which a cluster will help solve some time in the future" which could be up and running anyday now. If you have CFMX loaded on your home machine you can control the database just like you would have control over an FTP or WEB server and you can use cfquery with it. Also you can use SQL select, add, delete or insert data just like you would if you were located on Jodohost. I am sure people have already made applications like WS FTP pro but only work with databases.

 

[danp5648]

CFC's and UDF's.

CFC's and UDF's are great addition to CFMX along with the switch over to JRun / java! The last update to 6.1 was free and I think it was one big jar file. No uprooting to move from 6.0. CFC's are just a bunch of UDF's in a special house. Nothing to them. Good luck D

 

[danp5648]

I know most of that was your old post, but I studied it more carefully and understood it a little better. I have never been big into programming, so for me to get into it deeply would be tough


So far to create a table only took 6 lines of code. 5 lines of code are
reused 1 line the sql line changes to add update or delete. The only other part is one loop after a sql select statement. The other way is
just to use cfquery, but that has to be done via your home computer.
good luck!

 

[Yash]

We had a customer point to this thread so I'd just like to clarify.

We can now easily create a datasource (ever since we deployed our new cluster) in Cold Fusion to your MySQL database. All you have to do is open a ticket with the MySQL database access details.