I have the basic coldfusion hosting plan, which includes a mysql database. How do I talk to that database through coldfusion? More specifically, what do I put in the datasource attribute of a cfquery tag?

The way I expected it to work was that I would create a datasource in the host-my-site user control panel that pointed to the mysql database, but on the CreateDSN page, the only two options are for access databases or microsoft SQL server databases.

So, what am I missing here?

Thanks Josh, but I don't think that link really has anything at all to do with the question I asked. If I was setting up my own personal coldfusion server then I would have access to the coldfusion administrator and then your link would help me. But I'm using hostmysite.com and all I have is their control panel, and nothing in your link applies to this situation.

I'm sure someone here has coldfusion code on hostmysite that's talking to a mysql database. How did you set that up? Thanks.

The first thing you need is a DSN. In control panel there is an option to setup the MySQL database, and the DSN.

After that you can set the variables in your Application.cfm, then add the variables to your CFQuery tag.

Here's a sample.....

<!--- Setting a variable for the data source in application.cfm --->
<CFSET DSN="Your Database DSN">
<!--- Setting a variable for the db user name use --->
<CFSET User="Your Database User name">
<!--- Setting a variable for the db password --->
<CFSET pass="Your Database Password">


<!--- query to get data --->
<CFQUERY NAME="Whatever" DATASOURCE="#Variables.DSN#" DBTYPE="ODBC" USERNAME="#Variables.user#" PASSWORD="#Variables.pass#">
Select stuff from stuff, etc. </CFQUERY>

HTH

Ted, I'm not seeing an option the control panel to add a mysql dsn. There is a list box with two options: MS Access and MS SQL.

Here's a screenshot of what I see:

Try using the database name as the DSN. Control Panel does MySQL a little different between SQL Server and MySQL.

I added more to my first reply. Didn't think anyone would be there that quick.

no luck. The error says:

Datasource nwfchris could not be found.

The error occurred in C:\Websites\157609ab7\dbtest.cfm: line 1

1 : <cfquery name="q" datasource="nwfchris" dbtype="odbc" username="nwfchris" password="xxxxxxxxx">
2 : select * from test
3 : </cfquery>

thanks for trying to help. I suspect that the hostmysite control panel simply does not allow me to do this and I'll need to open a trouble ticket. As a side note, the forum search option appears to be doing an "or" search, meaning that when I search for "coldfusion mysql" it's giving me threads that contain the word coldfusion and also threads that contain the word mysql. On what planet would that be at all useful??? obviously what it should be doing is giving me threads that have both words

Thanks for trying to help though!

Give support a call. It will take no time. They are really good.

ok, I put in a support ticket. I'll post a followup when I hear back from them.

they added the datasource for me after I put in the help ticket. Thanks for the advice tedjtw.

for future reference, you just need the datasource name in the cfquery tag. You don't need the dbtype, username, or password attributes. And apparently, as crazy as this sounds, hostmysite has no way to set this up on your own!

Using MS SQL here is different. I believe you can set a DSN.

I thought name and password were required here, maybe that's just SQL Server. We write on in-house servers first where we do need it so I guess that's why we added to HMS production sites.

Glad support helped.